DataSource

You learned about custom connection pools in the Connection Pooling section. Sun Microsystems has created a DataSource interface that all connection pools must implement, such as the JDBC API, and the servlet specification also changes the servlet container to provide a javax.sql.DataSource implementation. This section introduces the configuration for Tomcat DataSource, and then modifies the bulletin board to use the dataSource.

Tomcat DataSource Settings

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.

If http://localhost:8080/dataSourceTest.jsp prints a number, the test is successful. If the test is successful, 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