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