As mentioned before, a JDBC database connection is an expensive operation and connection objects should be reused. Connection pools are used for this purpose. Most web containers provide their own implementation of a connection pool along with ways to configure it using JNDI. Tomcat also lets you configure a connection pool using JNDI. The advantage of configuring a connection pool using JNDI is that the database configuration parameters, such as hostname and port, remain outside the source code and can be easily modified. See http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html.

However, a Tomcat connection pool can also be used without JNDI, as described in the preceding link. In this example, we will use a connection pool without JNDI. The advantage is that you can use the connection pool implementation provided by a third party; your application then becomes easily portable to other web containers. With JNDI, you can also port your application, as long as you create the JNDI context and resources in the web container that you are switching to.

We will add the dependency of the Tomcat connection pool library to Maven's pom.xml. Open the pom.xml file and add the following dependencies (see Chapter 2, Creating a Simple JEE Web Application, to know how to add dependencies to Maven):

  <dependency> 
    <groupId>org.apache.tomcat</groupId> 
    <artifactId>tomcat-jdbc</artifactId> 
    <version>9.0.6</version> 
  </dependency> 

Note that you can use any other implementation of the JDBC connection pool. One such connection pool library is HikariCP (https://github.com/brettwooldridge/HikariCP).

We also want to move the database properties out of the code. Therefore, create a file called db.properties in src/main/resources. Maven puts all files in this folder in the classpath of the application. Add the following properties in db.properties:

db_host=localhost 
db_port=3306 
db_name=course_management 
db_user_name=your_user_name 
db_password=your_password 
db_driver_class_name=com.mysql.cj.jdbc.Driver 

We will create a singleton class to create JDBC connections using the Tomcat connection pool. Create a packt.book.jee.eclipse.ch4.db.connection package and create a DatabaseConnectionFactory class in it:

package packt.book.jee.eclipse.ch4.db.connection; 
 
// skipping imports to save space here
 
/** 
 * Singleton Factory class to create JDBC database connections 
 * 
 */ 
public class DatabaseConnectionFactory { 
  //singleton instance 
  private static DatabaseConnectionFactory conFactory = new 
   DatabaseConnectionFactory(); 
 
  private DataSource dataSource = null; 
 
  //Make the construction private 
  private DatabaseConnectionFactory() {} 
 
  /** 
   * Must be called before any other method in this class. 
   * Initializes the data source and saves it in an instance 
   variable 
   * 
   * @throws IOException 
   */ 
  public synchronized void init() throws IOException { 
    //Check if init was already called 
  if (dataSource != null) 
    return; 
 
    //load db.properties file first 
    InputStream inStream = 
 this.getClass().getClassLoader().getResourceAsStream("db.properties"); 
    Properties dbProperties = new Properties(); 
    dbProperties.load(inStream); 
    inStream.close(); 
 
    //create Tomcat specific pool properties 
    PoolProperties p = new PoolProperties(); 
p.setUrl("jdbc:mysql://" + dbProperties.getProperty("db_host") + 
":" + dbProperties.getProperty("db_port") + "/" + 
dbProperties.getProperty("db_name")); 
 
p.setDriverClassName(dbProperties.getProperty("db_driver_class_name")); 
    p.setUsername(dbProperties.getProperty("db_user_name")); 
    p.setPassword(dbProperties.getProperty("db_password")); 
    p.setMaxActive(10); 
 
    dataSource = new DataSource(); 
    dataSource.setPoolProperties(p); 
  } 
 
  //Provides access to singleton instance 
  public static DatabaseConnectionFactory getConnectionFactory() { 
    return conFactory; 
  } 
 
  //returns database connection object  
  public Connection getConnection () throws SQLException { 
    if (dataSource == null) 
      throw new SQLException("Error initializing datasource"); 
    return dataSource.getConnection(); 
  } 
} 

We must call the init method of DatabaseConnectionFactory before getting connections from it. We will create a servlet and load it on startup. Then, we will call DatabaseConnectionFactory.init from the init method of the servlet.

Create package packt.book.jee.eclipse.ch4.servlet and then create an InitServlet class in it:

package packt.book.jee.eclipse.ch4.servlet; 
import java.io.IOException; 
import javax.servlet.ServletConfig; 
import javax.servlet.ServletException; 
import javax.servlet.annotation.WebServlet; 
import javax.servlet.http.HttpServlet; 
 
import packt.book.jee.eclipse.ch4.db.connection.DatabaseConnectionFactory; 
 
@WebServlet(value="/initServlet", loadOnStartup=1) 
public class InitServlet extends HttpServlet { 
  private static final long serialVersionUID = 1L; 
 
  public InitServlet() { 
    super(); 
  } 
 
  public void init(ServletConfig config) throws ServletException { 
    try { 
      DatabaseConnectionFactory.getConnectionFactory().init(); 
    } 
    catch (IOException e) { 
      config.getServletContext().log(e.getLocalizedMessage(),e); 
    } 
  } 
} 

Note that we have used the @WebServlet annotation to mark this class as a servlet and the loadOnStartup attribute is set to 1, to tell the web container to load this servlet on startup.

Now we can call the following statement to get a Connection object from anywhere in the application:

Connection con = DatabaseConnectionFactory.getConnectionFactory().getConnection(); 

If there are no more connections available in the pool, then the getConnection method throws an exception (in particular, in the case of the Tomcat datasource, it throws PoolExhaustedException). When you close the connection that was obtained from the connection pool, the connection is returned to the pool for reuse.