Produce a `DataSource` object for Postgres JDBC, programmatically

Question

The JDBC Tutorial recommends using a DataSource object to obtain database connections rather than using the DriverManager class. To quote the Connecting with DataSource Objects page:

DataSource objects … the preferred means of getting a connection to a data source.

How do I get such an object for a JDBC connection to Postgres? I have a JDBC driver in place.

Right now, I do not want to fiddle around with JNDI like this or this.

Can I instantiate a DataSource programmatically within my Java app? Or must I implement that DataSource interface myself?

Answer 1

tl;dr

The PGSimpleDataSource class bundled with the JDBC driver from jdbc.postgresql.org implements DataSource interface. Configure your database connection details in a PGSimpleDataSource object, and pass around as a DataSource object.

PGSimpleDataSource ds = new PGSimpleDataSource() ;  
ds.setServerName( "localhost" );  
ds.setDatabaseName( "your_db_name_here" );   
ds.setUser( "scott" );       
ds.setPassword( "tiger" );   

Use that object to make a connection to the database as needed. Use convenient try-with-resources syntax.

try
(
    Connection conn = ds.getConnection() ;
) 
{ … }

JDBC driver’s implementation

Your JDBC driver may provide for you an implementation of the DataSource interface.

An object of this implementation contains the information needed to make and configure a connection to the database, such as:

• Name & password of database user
• IP address & port number of database server

Up to three kinds of implementation provided may be available:

• Often such an implementation is a thin wrapper around the DriverManager. Each time you call DataSource::getConnection on the object of such an implementation, you get a fresh database connection.
• Alternatively, an implementation may be using a connection pool underneath to supply already-existing connections. These connections are handed out and checked back in, like books in a library, to be recycled for repeated use.
• An implementation may support the Java Transaction API, supporting X/Open XA, for sophisticated needs like coordinating the transactions across multiple resources such as databases and message queues. Not as commonly used, so I ignore this type here.

Driver from jdbc.postgresql.org

The open-source free-of-cost driver from jdbc.postgresql.org provides all three types of DataSource implementation. But the authors do not recommend actually using their connection pool type in production; if you want pooling, use a third-party connection pooling library. And we are ignoring the XA type.

So let's look at the simple fresh-connection-each-time implementation of DataSource: org.postgresql.ds.PGSimpleDataSource

Configuring the data source object

Instantiate an empty object, then call a series of setter methods to configure for your particular database scenario. The setter methods are inherited from org.postgresql.ds.common.BaseDataSource.

We are not yet upcasting to the interface DataSource, so that we can call the various setter methods. See example code and discussion on the Data Sources and JNDI page.

PGSimpleDataSource ds = new PGSimpleDataSource() ;  // Empty instance.
ds.setServerName( "localhost" );  // The value `localhost` means the Postgres cluster running locally on the same machine.
ds.setDatabaseName( "testdb" );   // A connection to Postgres must be made to a specific database rather than to the server as a whole. You likely have an initial database created named `public`.
ds.setUser( "testuser" );         // Or use the super-user 'postgres' for user name if you installed Postgres with defaults and have not yet created user(s) for your application.
ds.setPassword( "password" );     // You would not really use 'password' as a password, would you?

Generally I would use these separate setter methods. Alternatively, you construct a String, a URL, with the various pieces of info to be set on the DataSource in one stroke. If you want to go that route, call setUrl.

That covers the basics. But you might want or need some of the other setters. Most of these are setting Postgres property values on the server. The properties all have smart defaults, but you may wish to override for special situations.

ds.setPortNumber( 6787 ) ;  // If not using the default '5432'.
ds.setApplicationName( "whatever" ) ;   // Identify the application making this connection to the database. Also a clever way to back-door some information to the Postgres server, as you can encode small values into this string for later parsing. 
ds.setConnectTimeout( … ) ;  // The timeout value used for socket connect operations, in whole seconds. If connecting to the server takes longer than this value, the connection is broken.
ds.setSocketTimeout( … ) ;  // The timeout value used for socket read operations. If reading from the server takes longer than this value, the connection is closed. This can be used as both a brute force global query timeout and a method of detecting network problems.
ds.setReadOnly( boolean ) ;  // Puts this connection in read-only mode.

If using TLS (formerly known as SSL) to encrypt the database connection to protect against eavesdropping or malevolent manipulation, use several setters for that.

For any Postgres property without a specific setter method, you may call setProperty( PGProperty property, String value ).

You can inspect or verify settings on this data source by calling any of the many getter methods.

After configuring your PGSimpleDataSource, you can pass off to the rest of your codebase as simply a DataSource object. This insulates your codebase from the shock of changing to another DataSource implementation or changing to another JDBC driver.

DataSource dataSource = ds ;  // Upcasting from concrete class to interface.
return dataSource ; 

Using the data source

Using a DataSource is utterly simple as it provides for only two methods, a pair of variations on getConnection to get a Connection object for your database work.

Connection conn = dataSource.getConnection() ; 

When finished with your Connection, best practice is to be sure to close it. Either use a try-with-resources syntax to automatically close the connection, or explicitly close it.

conn.close() ;

Keep clear in your mind that a DataSource is not actually a data source. A DataSource is really a source for generating/accessing connections to the database. To my mind, this is a misnomer, as I think of it as ConnectionSource. The DataSource talks to your database only long enough to sign-in with user name and password. After that sign-in, you use the Connection object to interact with the database.

Storing your DataSource

Once configured, you want to keep that DataSource object around, cached. No need to re-configure repeatedly. The implementation should be written to be thread-safe. You may call getConnection at anytime from anywhere.

For a simple little Java app, you may want to store it as a field on a singleton or in a static global variable.

For a Servlet-based app such as a Vaadin app, you would create a class implementing ServletContextListener interface. In that class your would establish your DataSource object when your web app is launching. From there you would store the object in the ServletContext object by passing to setAttribute. Context is the technical term for 'web app'. Retrieve by calling getAttribute and casting to DataSource.

In an enterprise scenario, the DataSource may be stored in a JNDI-compliant implementation. Some Servlet containers such as Apache Tomcat may provide a JNDI implementation. Some organizations use a server such as an LDAP server. Registering & retrieving your DataSource object with JNDI is covered in many other Questions & Answers on Stack Overflow.