Setting connection options for PostgreSQL JDBC connection from Java

Question

My application was using Mongo DB earlier. Now, I'm shifting to PostgreSQL. For that, I've been migrating queries and all. But, I was being blocked by issue. In MongoDB connection, we've some MongoClientOptions used to improve the performance of the application. In some way, I want to set these options with JDBC for PostgreSQL also.

I've tried and searched the same functions in JDBC DriverManager class. But didn't find any.

MongoDB connection options used are added below, How can I set these options for JDBC client for PostgreSQL?

MongoClientOptions.Builder builder = new MongoClientOptions.Builder();
builder.threadsAllowedToBlockForConnectionMultiplier(1000);
builder.maxConnectionIdleTime(60* 1000 * 5);
builder.connectionsPerHost(100000);
MongoClientOptions options = builder.build();
mongoClient = new MongoClient(hostname, options);

I don't know what those options do in MongoDB, but from the names of it, it looks like you are looking for a connection pool. In which environment does your application run? Web Application? Swing Rich Client? Something else? — user330315
– user330315, Commented Apr 10, 2019 at 9:29
Then use the connection pool that comes with your application server (e.g. Tomcat's pool) — user330315
– user330315, Commented Apr 10, 2019 at 9:33
I didn't get that. You're saying I've to Tomcat's pool in place of JDBC driver? please light me on this. I'm new to this web application and all — Muhammed Thabjeel
– Muhammed Thabjeel, Commented Apr 10, 2019 at 9:38
The pool will use the JDBC driver to connect to the database: tomcat.apache.org/tomcat-9.0-doc/… — user330315
– user330315, Commented Apr 10, 2019 at 9:40

Mark Rotteveel · Accepted Answer · 2019-04-10 09:30:32Z

1

In JDBC you pass a Properties object with some JDBC-standard properties ("user" and "password") and driver-specific properties, or pass the properties as part of the JDBC-url (with driver-specific properties and driver-specific syntax), or you configure things using a DataSource and its getters and setters.

For PostgreSQL JDBC refer to the section Connecting to the Database

For almost any serious usage of JDBC, you should not use DriverManager directly as it will create a new physical connection for each request. Instead use a javax.sql.DataSource implementation that provides connection pooling, either provided by your driver (those usually aren't very good though), a third-party library like HikariCP, or one built into your JavaEE application server.

answered Apr 10, 2019 at 9:30

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Muhammed Thabjeel Over a year ago

Thanks. But one question. I've been using DriverManager.getConnection() function. It will return a Connection object. On that Connection object I've been executing queries. How it's going to use new physical connection for each request?

Mark Rotteveel Over a year ago

@MuhammedThabjeel Each time you call DriverManager.getConnection, you'll create a new physical connection. And given you should not share a connection concurrently, you'll need to call DriverManager.getConnection for individual requests, thus you'll be creating a new connection per request (and if you aren't currently doing that, you have even bigger problems with race conditions and other concurrency bugs). The solution is to use a connection pool, as instead of closing physical connections, a Connection.close() will return it to the pool for reuse.

Muhammed Thabjeel Over a year ago

Oh, then that's a big mess. But I've all my CRUD functions(Where I actually use this singleton Connection object and making queries) as synchronized. Will that help? And can you please share some links for connection pool?

Mark Rotteveel Over a year ago

@MuhammedThabjeel Individual actions might be safe (but probably not), but larger interactions might still suffer from race conditions (guess what happens when one request rolls back and another one commits, while another one is updating / inserting something that depends on data it inserted earlier). In addition, having a single synchronization bottle neck in your application will make your application unnecessarily slow. For links, Google and DuckDuckGo are your friend. Correctly using a connection pool will make things simpler and usually faster.

Muhammed Thabjeel Over a year ago

Ok. Thank you. If I'm using a connection pool, I don't need to synchronize methods, right?

|

Collectives™ on Stack Overflow

Setting connection options for PostgreSQL JDBC connection from Java

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related