Posted In: JDBC

How to configure PostgreSQL DataSource in Tomcat 9

1. PostgreSQL datasource setup

1. Download PostgreSQL JDBC driver
Location to download PostgreSQL JDBC DRIVER (postgresql-9.4.1212.jre7.jar)
Download version suited for your JRE level
JDBC4 Postgresql Driver, Version 9.4.1212
JDBC41 Postgresql Driver, Version 9.4.1212
JDBC42 Postgresql Driver, Version 9.4.1212

Copy connector jar to either TOMCAT/LIB or application’s WEB-INF/lib
Note
In an enterprise application avoid adding it in application’s WEB-INF/lib. Mostly this connector will be configured at server level so that same version will be shared among multiple applications. It helps in upgrading jar at server without touching the application.

or use maven entry in pom.xml

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4.1212.jre7</version>
</dependency>

2. Change context.xml to create PostgreSQL DB datasource in TOMCAT
a. Add Server to Eclipse. Open /Servers/Tomcat v9.0 Server at XXXXXX/context.xml
b. or if you are using direct tomcat without eclipse then change /tomcat_install_dir/context.xml
c. or create META-INF/context.xml in your application

Then add ‘Resource’ to context.xml

Tomcat is installed as same server as PostgreSQL DB
Tomcat 8 and 9 uses org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory which uses maxTotal, maxWaitMillis

<Resource name="jdbc/javausecasedb" auth="Container" type="javax.sql.DataSource" 
maxTotal="50" maxIdle="30" maxWaitMillis="10000" 
username="javausecasedbuser" password="javausecasedbpassword" 
driverClassName="org.postgresql.Driver" 
url="jdbc:postgresql://localhost:5432/javausecase_db"/>

Tomcat is not installed as same server as PostgreSQL DB

<Resource name="jdbc/javausecasedb" auth="Container" type="javax.sql.DataSource" 
maxTotal="50" maxIdle="30" maxWaitMillis="10000" 
username="javausecasedbuser" password="javausecasedbpassword" 
driverClassName="org.postgresql.Driver" 
url="jdbc:postgresql://DB_SERVER:5432/javausecase_db"/>

Note
maxTotal – Maximum number of database connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to -1 for no limit.
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 total connections.

maxIdle: Maximum number of idle database connections to retain in pool. Set to -1 for no limit. See also the DBCP documentation on this nd the minEvictableIdleTimeMillis configuration parameter.
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 idle connections.

maxWaitMillis: Maximum time to wait for a database connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely.

Tomcat 7 and below uses org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory which uses maxActive, maxWait

<Resource name="jdbc/javausecasedb" auth="Container" type="javax.sql.DataSource" 
maxActive="50" maxIdle="30" maxWait="10000" 
username="javausecasedbuser" password="javausecasedbpassword" 
driverClassName="org.postgresql.Driver" 
url="jdbc:postgresql://localhost:5432/javausecase_db"/>

Note
maxActive – (int) The maximum number of active connections that can be allocated from this pool at the same time. The default value is 100
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 active connections.

maxIdle – (int) The maximum number of connections that should be kept in the pool at all times. Default value is maxActive:100 Idle connections are checked periodically (if enabled) and connections that been idle for longer than minEvictableIdleTimeMillis will be released. (also see testWhileIdle)
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 idle connections.

initialSize – (int)The initial number of connections that are created when the pool is started. Default value is 10
Connecting to shared DB in DEV environment in an organization do not use more than 1 or 2 initial connections.

3. Change web.xml, This is optional step. Rather if you are going to deploy it to Websphere in Production environment do not add this entry.

<resource-ref>
<description>PostgreSQL Datasource example</description>
<res-ref-name>jdbc/javausecasedb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

4. You can even create PostgreSQL DB datasource through spring DBCP configuration. This is useful for testing application without any server.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" 
destroy-method="close">
   <property name="driverClassName" value="org.postgresql.Driver"/>
   <property name="url" value="jdbc:postgresql://DB_SERVER_IP:5432/javausecase_db"/>
   <property name="username" value="javausecasedbuser"/>
   <property name="password" value="javausecasedbpassword"/>
</bean>

Create PostgreSQL DB datasource through spring DBCP2
Apache Commons DBCP 2.1.1 for JDBC 4.1 (Java 7.0+)

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" 
destroy-method="close">
   <property name="driverClassName" value="org.postgresql.Driver"/>
   <property name="url" value="jdbc:postgresql://DB_SERVER_IP:5432/javausecase_db"/>
   <property name="username" value="javausecasedbuser"/>
   <property name="password" value="javausecasedbpassword"/>
</bean>

Create PostgreSQL DB datasource through java code

BasicDataSource bds = new BasicDataSource();
bds.setDriverClassName("org.postgresql.Driver");
bds.setUrl("jdbc:postgresql://localhost:5432/javausecase_db");
bds.setUsername("javausecasedbuser");
bds.setPassword("javausecasedbpassword");
bds.setInitialSize(5);

2. Use it in your application

1. Once datasource is configured on tomcat access it through Spring configuration

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/javausecasedb" />

2. To access PostgreSQL DB through initial context

import javax.naming.Context;
import javax.naming.InitialContext;
..
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/javausecasedb");

Notice java:comp/env
The InitialContext is configured as a web application is initially deployed, and is made available to web application components (for read-only access). All configured entries and resources are placed in the java:comp/env portion of the JNDI namespace.

3. To access PostgreSQL DB through @Resource (javax.annotation.Resource)

package com.javausecase;

import java.io.IOException;
import java.sql.Connection;

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

@WebServlet("/Servlet2")
public class Servlet2 extends HttpServlet {
	private static final long serialVersionUID = 1L;

	@Resource(name = "jdbc/javausecasedb")
	private DataSource ds;

	public Servlet2() {
		super();
		System.out.println("Servlet2()");
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		Connection conn = null;
		try {
			// check datasource
			System.out.println("ds=" + ds);
			conn = ds.getConnection();
			System.out.println("ds.getConnection()=" + conn);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		response.getWriter().append("Served at: ").append(request.getContextPath());
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}

}

3. Common issues

a. Incorrect parameters warning
Jan 22, 2017 12:14:14 PM org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory getObjectInstance
WARNING: Name = javausecasedb Property maxActive is not used in DBCP2, use maxTotal instead. maxTotal default value is 8. You have set value of “50” for “maxActive” property, which is being ignored.
Jan 22, 2017 12:14:14 PM org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory getObjectInstance
WARNING: Name = javausecasedb Property maxWait is not used in DBCP2 , use maxWaitMillis instead. maxWaitMillis default value is -1. You have set value of “10000” for “maxWait” property, which is being ignored.

Solution – Check tomcat version and dbcp version

b. org.postgresql.Driver Class not found

Solution – Check connector jar is in classpath

c. java.sql.SQLException: Cannot create PoolableConnectionFactory (FATAL: password authentication failed for user “dbuser”)

Solution – Check use id / password in datasource configuration

Reference
1. JNDI Datasource HOW-TO

Tags: , , , , ,

by , on January 23rd, 2017

  • Categories