Application Servers ConnectionPoolDataSource

PostgreSQL® includes one implementation ofConnectionPoolDataSource namedorg.postgresql.ds.PGConnectionPoolDataSource .

JDBC requires that aConnectionPoolDataSource be configured via JavaBean properties, shown inTable 11.1, “ConnectionPoolDataSource Configuration Properties”,so there are get and set methods for each of these properties.

Table 11.1. ConnectionPoolDataSource Configuration Properties

Many application servers use a properties-style syntax to configure these properties, so it would not be unusual to enterproperties as a block of text. If the application server provides a single area to enter all the properties, they mightbe listed like this:

serverName=localhost

databaseName=test

user=testuser

password=testpassword

Or, if semicolons are used as separators instead of newlines, it could look like this:

serverName=localhost;databaseName=test;user=testuser;password=testpassword

Applications DataSource

PostgreSQL® includes two implementations ofDataSource , as shown inTable 11.2, “DataSource Implementations”.

One that does pooling and the other that does not. The pooling implementation does not actually close connections whenthe client calls theclose() method, but instead returns the connections to a pool of available connections for otherclients to use. This avoids any overhead of repeatedly opening and closing connections, and allows a large number ofclients to share a small number of database connections.

The pooling data-source implementation provided here is not the most feature-rich in the world. Among other things,connections are never closed until the pool itself is closed; there is no way to shrink the pool. As well, connectionsrequested for users other than the default configured user are not pooled. Its error handling sometimes cannot remove abroken connection from the pool. In general it is not recommended to use the PostgreSQL® provided connection pool. Checkyour application server or check out the excellentjakarta commons DBCP project.

Table 11.2. DataSource Implementations

Both implementations use the same configuration scheme. JDBC requires that aDataSource be configured via JavaBean properties,shown inTable 11.3, “DataSource Configuration Properties”,so there are get and set methods for each of these properties.

Table 11.3. DataSource Configuration Properties

The pooling implementation requires some additional configuration properties, which are shown inTable 11.4, “Additional PoolingDataSource Configuration Properties.

Table 11.4. Additional PoolingDataSource Configuration Properties

Example 11.1, “DataSource Code Example” shows an exampleof typical application code using a poolingDataSource.

Example 11.1. DataSource Code Example

Code to initialize a poolingDataSource might look like this:

PGPoolingDataSourcesource=newPGPoolingDataSource();source.setDataSourceName("A Data Source");source.setServerNames(newString[]{"localhost"});source.setDatabaseName("test");source.setUser("testuser");source.setPassword("testpassword");source.setMaxConnections(10);

Note

setServerName has been deprecated in favour of setServerNames. This was done to support multiple hosts.

Then code to use a connection from the pool might look like this.

Note

it is critical that the connections are eventually closed. Otherwise, the pool will “leak” connections and will eventuallylock all the clients out.

try(Connectionconn=source.getConnection()){// use connection}catch(SQLExceptione){// log error}

Data Sources and JNDI

All theConnectionPoolDataSource andDataSource implementations can be stored in JNDI. In the case of the non-poolingimplementations, a new instance will be created every time the object is retrieved from JNDI, with the same settings asthe instance that was stored. For the pooling implementations, the same instance will be retrieved as long as it is available(e.g., not a different JVM retrieving the pool from JNDI), or a new instance with the same settings created otherwise.

In the application server environment, typically the application server’sDataSource instance will be stored in JNDI,instead of the PostgreSQL®ConnectionPoolDataSource implementation.

In an application environment, the application may store theDataSource in JNDI so that it doesn’t have to make a referenceto theDataSource available to all application components that may need to use it. An example of this is shown inExample 11.2, “DataSource JNDI Code Example”.

Example 11.2. DataSource JNDI Code Example

Application code to initialize a poolingDataSource and add it to JNDI might look like this:

PGPoolingDataSourcesource=newPGPoolingDataSource();source.setDataSourceName("A Data Source");source.setServerName("localhost");source.setDatabaseName("test");source.setUser("testuser");source.setPassword("testpassword");source.setMaxConnections(10);newInitialContext().rebind("DataSource",source);

Then code to use a connection from the pool might look like this:

Connectionconn=null;try{DataSourcesource=(DataSource)newInitialContext().lookup("DataSource");conn=source.getConnection();// use connection}catch(SQLExceptione){// log error}catch(NamingExceptione){// DataSource wasn't found in JNDI}finally{if(con!=null){try{conn.close();}catch(SQLExceptione){}}}

Tomcat setup

NOTE

The postgresql.jar file must be placed in $CATALINA_HOME/common/lib in both Tomcat 4 and 5.

The absolute easiest way to set this up in either tomcat instance is to use theadmin web application that comes with Tomcat, simply add the datasource to thecontext you want to use it in.

Setup for Tomcat 4 place the following inside the < Context> tag insideconf/server.xml

<Resourcename="jdbc/postgres"scope="Shareable"type="javax.sql.DataSource"/><ResourceParamsname="jdbc/postgres"><parameter><name>validationQuery</name><value>select version();</value></parameter><parameter><name>url</name><value>jdbc:postgresql://localhost/davec</value></parameter><parameter><name>password</name><value>davec</value></parameter><parameter><name>maxActive</name><value>4</value></parameter><parameter><name>maxWait</name><value>5000</value></parameter><parameter><name>driverClassName</name><value>org.postgresql.Driver</value></parameter><parameter><name>username</name><value>davec</value></parameter><parameter><name>maxIdle</name><value>2</value></parameter></ResourceParams>

Setup for Tomcat 5, you can use the above method, except that it goes inside the< DefaultContext> tag inside the < Host> tag. eg. < Host> … < DefaultContext> …

Alternatively there is a conf/Catalina/hostname/context.xml file. For examplehttp://localhost:8080/servlet-example has a directory$CATALINA_HOME/conf/Catalina/localhost/servlet-example.xml file.Inside this file place the above xml inside the < Context> tag

Then you can use the following code to access the connection.

importjavax.naming.*;importjavax.sql.*;importjava.sql.*;publicclassDBTest{Stringfoo="Not Connected";intbar=-1;publicvoidinit(){try{Contextctx=newInitialContext();if(ctx==null)thrownewException("Boom - No Context");// /jdbc/postgres is the name of the resource aboveDataSourceds=(DataSource)ctx.lookup("java:comp/env/jdbc/postgres");if(ds!=null){Connectionconn=ds.getConnection();if(conn!=null){foo="Got Connection "+conn.toString();Statementstmt=conn.createStatement();ResultSetrst=stmt.executeQuery("select id, foo, bar from testdata");if(rst.next()){foo=rst.getString(2);bar=rst.getInt(3);}conn.close();}}}catch(Exceptione){e.printStackTrace();}}publicStringgetFoo(){returnfoo;}publicintgetBar(){returnbar;}}