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
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 6 provides JDBC 3 support and backward compatibility, Tomcat provides the following JDBC features
Tomcat enables running Web applications to do the following
The below diagram shows how Tomcat uses JNDI to lookup data sources
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
<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.
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 ( |
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 <Resource name="jdbc/oracledb" 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> |
JSP page | Place the below in a webapps/jdbc called jdbcTest.jsp <sql:query var="employees" dataSource="jdbc/oracledb"> </head> |
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.