JDBC Connectivity

Java DataBase Connectivity (JDBC) is a programming interface for accessing Relational Databases, its operation is based on the transmission and execution of Structured Query Language (SQL) on the database server. JDBC is based on a Call-Level Interface (CLI) to an engine that processes SQL.

The JDBC engine submits SQL query statements to the remote SQL processing engine and the SQL processing engine returns the result of the query in a set of data called a result set. A result set is zero or more rows of data.

JDBC operations are designed to do the following

The following is a typical JDBC connection

The same JDBC APIs can be used to connect to a number of different databases Oracle, MySQL, etc

JDBC Versions

Currently we are at JDBC version 4 which supports all the previous versions and addresses some features that make development easier, improve connection pooling, expose physical database row IDs for developers and introduce a new XML data type to JDBC. Some older databases may require an older version so check your documentation.

Here is a summary of the four different JDBC types

When then application makes a connection to a database it is CPU-, Memory-, execution time-intensive operation. It involves multiple layers of software and the transmission and receipt of network data. Many JSP requests may access the database and as you saw above this can be expensive in creating new database connections. To reduce this load on the server we use connection pooling.

Connection pooling reduces expensive session connection times by creating a pool of physical connections when the system starts up. When an application requires a connection, one of these physical connections is provided. When the application is finished the logical connection is disconnected but the physical connection is returned to the pool for reuse ready of the next application to use.

A pool manager creates the initial physical connections, manages the distribution of the physical connections to the Web application in the form of a logical connection, returns any closed logical connections to the pool and handles any errors.

JDBC 4 corrected a pooling problem if there were network errors and the physical connects had disconnected requiring a restart of the server, it provides a way for the pool manager to ask a connection if it is still valid for this to work properly, you will require

Tomcat and JDBC

Tomcat 6 provides JDBC 3 support and backward compatibility, Tomcat provides the following JDBC features

JNDI Emulation and Pooling

Tomcat enables running Web applications to do the following

The below diagram shows how Tomcat uses JNDI to lookup data sources

  1. A Web application obtains a JNDI initialContext from Tomcat, it then performs a lookup on the resource (JDBC data source) by name
  2. Tomcat handles the JNDI lookup by consulting the configuration files (context.xml, server.xml and web.xml) to determine which JDBC driver to use for a data source. Tomcat can also use database connection pooling (DBCP) to pool connections made; the connections obtained are logical connections

Remember that Tomcat is only emulating JNDI-compatible directory services. Using JNDI resources in Tomcat, provides Web applications access to JDBC connections, the following steps are need to setup a JNDI resource

  1. Add a <Resource> tag in the <Context> element (WEB-INF/context.xml) of the Web application or in a <DefaultContext> subelement of the <Host> element (server.xml) to configure the JNDI resource.
  2. Ensure that a <resource-ref> element is defined, corresponding to the <Resource> from step1 (web.xml).
  3. Use JNDI calls in the application code to lookup the JDBC data source
<Resource> Component Attributes
Attribute Description
name Create a JNDI resource that is accessible from the context (logical name). java:comp/env is added on for all Tomcat-managed contexts, use this code inside the application code to lookup the resource.
auth Specifies if the Tomcat container does the authentication on behalf of the the application
type The type of resource that will be returned during this lookup
maxActive Maximum number of active connections in the connection pool ( 0 means unlimited)
maxIdle Number of idle connections in the pool before they are evicted (-1 means unlimited)
maxWait Maximum number of milliseconds that the manager will wait for a database connection to respond, before throwing an exception (-1 means wait indefinitely)
url The JDBC URL to access the database
username The username for the JDBC connection
password The password for the user above for the JDBC connection
driverClassName
The driver used to access the Database (Oracle, MySQL, etc)
Example
Oracle <Resource name="jdbc/oracledb"
          auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.OracleDriver"
          url="jdbc:oracle:thin:@localhost:1521:D01"
          username="tomcat"
          password="tomcat"
          maxActive="20"
          maxIdle="30"
          maxWait="-1"
/>
MySQL <Resource name="jdbc/mysqldb"
          auth="Container"
          type="javax.sql.DataSource"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/D01?autoReconnect=true"
          username="tomcat"
          password="tomcat"
          maxActive="20"
          maxIdle="30"
          maxWait="-1"
/>

Tomcat 6 uses a data source factory to create the data source, it uses the Jakarta Commons DBCP to supply a data source factory and implement connection pooling.

Complete Example

Now for a complete example, I will be using Oracle but you can use MySQL or another database

Database setup

Download Oracle and the necessary Oracle JAR file (classes12.jar) which I placed in the lib directory so that all web applications can access it.

Note: I have a Oracle web topic that discusses installation, configuring, administrating, etc

Create Oracle table

I created a Tomcat schema and Tomcat User for this example

create table employee (
  employeeid varchar2(10) NOT NULL primary key,
  name varchar2(30) NOT NULL,
  phone varchar2(15) NOT NULL,
  department varchar2(15) NOT NULL,
  password varchar2(15) NOT NULL
);

Load some data into the table insert into employee values ('0001', 'Arthur Askey', '12345', 'Comedy', 'Hello Playmates');
insert into employee values ('0002', 'George Formby', '23456', 'Comedy', 'Turned out Nice');
insert into employee values ('0003', 'Bob Hope', '34567', 'Comedy', 'Oh My Lord');
insert into employee values ('0004', 'Norman Wisdom', '45678', 'Comedy', 'Mister Grimsdale');
JNDI Resource

I created a new Web application called jdbc in webapps

Add the below webapps/jdbc/META-INF/context.xml file

<Resource name="jdbc/oracledb"
          auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.OracleDriver"
          url="jdbc.oracle:thin:@localhost:1521:D01"
          username="tomcat"
          password="tomcat"
          maxActive="20"
          maxIdle="30"
          maxWait="-1"
/>

Note: I am presuming that the database is on the localhost and you have a user tomcat with a password of tomcat setup, if not then change the url to reflect your environment

Add the Resource Reference

Add the below in the webapps/jdbc/WEB-INF/web.xml file

<resource-ref>
  <res-ref-name>jdbc/oracledb</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

JSP page

Place the below in a webapps/jdbc called jdbcTest.jsp


<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page errorPage="errorpg.jsp" %>
  <html>
  <head>

    <sql:query var="employees" dataSource="jdbc/oracledb">
       select * from employee
    </sql:query>

  </head>
  <body>
    <h1>JDBC JNDI Resource Test</h1>
    <table width='600' border='1'>
      <tr>
        <th align='left'>Employee ID</th>
        <th align='left'>Name</th>
        <th align='left'>Department</th>
      </tr>
      <c:forEach var="employee" items="${employees.rows}">
        <tr>
           <td> ${employee.employeeid}</td>
           <td> ${employee.name} </td>
           <td> ${employee.department} </td>
       </tr>
      </c:forEach>
    </table>
  </body>
  </html>

Test the page

Restart Tomcat, then test the JDBC connection

http://localhost/jdbc/jdbcTest.jsp

There is another Connection Pool Manager called "c3p0" which is available but I am not going to discuss it here but leave it to you to find out about this Connection Pool Manager on the internet.