DataSource

You learned about custom connection pools in the Connection Pooling section. Java Programmer needed a standard for connection pooling, so Sun Microsystems created a DataSource interface that all connection pools must implement. The servlet specification also changed so that servlet containers must provide a javax.sql.DataSource implementation. This section describes Tomcat's DataSource configuration and then how to modify the bulletin board source to use the dataSource.

Tomcat's DataSource configuration

Open the ROOT.xml in CATALINA_HOME/conf/Catalina/localhost and add the highlighted parts below.

ROOT.xml
<?xml version="1.0" encoding="UTF-8"?>
<Context
  docBase="C:/www/myapp"
  reloadable="true">

  <Resource
    name="jdbc/scott"
    auth="Container"
    type="javax.sql.DataSource"
    username="scott"
    password="tiger"
    driverClassName="oracle.jdbc.driver.OracleDriver"
    url="jdbc:oracle:thin:@127.0.0.1:1521:XE"
    maxActive="8"
    maxIdle="4" />

</Context>

Create the following JSP file in the document base of myapp application.

dataSourceTest.jsp
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%@ page import="net.java_school.util.Log" %>
<html>
<head>
   <title>DataSource Test</title>
</head>
<body>
<%
  Log log = new Log();
  DataSource ds = null;
  Context ic = null;
  Connection con = null;
  PreparedStatement stmt = null;
  ResultSet rs = null;
  String sql = null;
  int totalRecord = 0;
  	
  try {
    ic = new InitialContext();
    Context envCtx = (Context) ic.lookup("java:comp/env");
	ds = (DataSource) envCtx.lookup("jdbc/scott");
  } catch (NamingException e) {
    System.out.println(e.getMessage());
  }

  try {
    con = ds.getConnection();

    sql = "SELECT count(*) FROM emp";

    stmt = con.prepareStatement(sql);
    rs = stmt.executeQuery();
    rs.next();
    totalRecord = rs.getInt(1);
  } catch (SQLException e) {
      log.debug("Error Source:/DataSourceTest.jsp : SQLException");
      log.debug("SQLState : " + e.getSQLState());
      log.debug("Message : " + e.getMessage());
      log.debug("Oracle Error Code : " + e.getErrorCode());
      log.debug("sql : " + sql);
  } finally {
    try {
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
      if (con != null) con.close();
    } catch (SQLException e) {}
    log.close();
  }
%>
<%=totalRecord %>

For testing, the Oracle JDBC Driver must be in CATALINA_HOME/lib. As you can see in the code, the DataSource is used through a Java naming service based on the JNDI API.

Visit http://localhost:8080/dataSourceTest.jsp and confirm it prints a number.

Next, let's modify the source of the bulletin board. We implemented the JDBC code on BoardDao, so we only need to modify BoardDao.

BoardDao.java
package net.java_school.board;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import net.java_school.util.Log;

public class BoardDao {
  
  private DataSource ds;
  
  public BoardDao() {
    try {
      Context init = new InitialContext();
      ds  = (DataSource) init.lookup("java:comp/env/jdbc/scott");
    } catch (NamingException e) {
      System.out.println(e.getMessage());
    }
  }
	
  private Connection getConnection() throws SQLException {
    return ds.getConnection();
  }
	
  //Same as previous source

}
References