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
- Take the JDBC API calls and transform them into an SQL query
- Submit that query to the SQL processing engine on the RDBMS
- Retrieve the result set that is returned from the query and transform it into a Java-accessible data structure
The following is a typical JDBC connection
- Obtain a connection to the remote database server
- Create and prepare an SQL statement for execution
- Execute the SQL statement
- Obtain the returned result set and work on it
- Disconnect from the remote database
The same JDBC APIs can be used to connect to a number of different databases Oracle, MySQL, etc
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
- TYPE I – These are basic data access adapters, they adapt another data access mechanism (such as OBDC) to JDBC, they completely rely on the other data access mechanism.
- TYPE II – These drivers are partially written in native data access languages (typical C or C++)
- TYPE III – These drivers are pure Java drivers on the client side, which gives them the portability benefit of Java, however they rely on a middleware engine running externally to operate.
- TYPE IV – These are 100% Java and talk directly to the network protocols supported by the RDBMS, this results in giving the highest performance and are the most portable. Most JDBC drivers are now type IV.
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
- The JDBC driver to implement this JDBC 4 feature, enabling a connection’s validity to be queried
- The application server pool manager to support this feature – to query connection validity regularly to avoid having bad connections remaining in the pool.
Tomcat and JDBC
Tomcat 6 provides JDBC 3 support and backward compatibility, Tomcat provides the following JDBC features
- Application server-managed database connection pools – Tomcat 6 uses Jakarta Commons Database Connection Pooling (DBCP) to provide container-managed connection pooling and ResourceParams
- Using the JNDI-API to look up data source within an application server – Tomcat 6 can emulate JNDI Web applications running under it.
- Ease of migration to the connector architecture – Tomcat 6 decoupled architecture for access to JDBC data sources (through JNDI lookup) is a first step in the migration toward the JCA Connector-based architecture
JNDI Emulation and Pooling
Tomcat enables running Web applications to do the following
- Access JDBC data sources using standard JNDI lookup
- Use connection pooling value-added service
The below diagram shows how Tomcat uses JNDI to lookup data sources
- A Web application obtains a JNDI initialContext from Tomcat, it then performs a lookup on the resource (JDBC data source) by name
- 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
- 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.
- Ensure that a <resource-ref> element is defined, corresponding to the <Resource> from step1 (web.xml).
- Use JNDI calls in the application code to lookup the JDBC data source
|<Resource> Component Attributes|
|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)|
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.
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
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
|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” %>
<sql:query var=”employees” dataSource=”jdbc/oracledb”>
select * from employee
|Test the page||Restart Tomcat, then test the JDBC connection
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.