In this example, we shall demonstrate how to create the Data-source using Java Naming and Directory Interface or JNDI, for short.
The JNDI Data-source created in web-server uses one of the connection pooling libraries like DBCP, C3P0 or the BoneCP. The connection pool can be created in the Web-Server or it can also be created in the web-application, itself. Creating connection pool in server is beneficial as it is managed by web-server. Also, when using JNDI Data-Source, we need not include the JDBC JAR and Connection pooling JARS in our Web-Application lib. All applications deployed in that web-server can then share the same JNDI Data-source.
Now the question that may arise to the reader is that why to use connection pooling at all?
While the simple way to acquire connection using DriverManager.getConnection(url,username,password) suffices for test and development purposes, opening and closing connection each time when executing a query on production servers will slow down the application and reduce the throughput. In connection pool, a number of connection objects are created and stored for use. A program fetches a connection object from this pool, uses it to run a query and returns it back to the pool, to be used by others. This also helps in PreparedStatement caching the queries.
Now that we know its benefits, we will see how we can create a JNDI Data-source for a MySQL database in Apache Tomcat 7. Creating Data-Source in Tomcat is very easy. We just need to follow the steps described below:
First of all, download and add the JDBC JAR to the Apache Tomcat lib folder. In this example, we will drop mysql JDBC JAR into the Tomcat_Home/lib folder.
Secondly We need to add the following lines to the context.xml in the conf folder of Apache Tomcat:
APACHE_TOMCAT_DIR/conf/context.xml:
< Resource auth="Container" driverClassName="com.mysql.jdbc.Driver" maxActive="100" maxIdle="30" maxWait="10000" name="jdbc/TestDB" type="javax.sql.DataSource" url="jdbc:mysql://localhost:3306/jcg?rewriteBatchedStatements=true" username="username" password="password" />
Here is a brief on what the various attributes in the Resource tag mean:
driverClassName: JDBC Driver class of the underlying database.maxActive: Maximum number of alive connections in the pool.maxIdle: Maximum number of connections that can remain alive in the pool.name: Datasource lookup name.URL: Underlying Database URL to connect.Username: Underlying Database Username.Password: Underlying Database Password.
Thirdly, add the following lines to your web-application’s web.xml to enable JNDI Data-Source discovery by the application:
web.xml:
<resource-ref > <description >MySQL Datasource example </description > <res-ref-name >jdbc/TestDB </res-ref-name > <res-type >javax.sql.DataSource </res-type > <res-auth >Container </res-auth > </resource-ref >
And, finally we can use it in our web-Application in the following manner:
DBConnection.java:
package com.javacodegeeks.examples;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
/**
* @author Chandan Singh
*/
public class DBConnection
{
public static Connection getConnection() throws SQLException, ClassNotFoundException, NamingException
{
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");
Connection connection = ds.getConnection();
System.out.println(connection);
return connection;
}
}
OUTPUT:
jdbc:mysql://localhost:3306/jcg?rewriteBatchedStatements=true, UserName=root@localhost, MySQL-AB JDBC Driver
Conclusion
Thus we studied how to create a JNDI Data-source in Apache Tomcat.
You can download the source code of this example here: JavaEEProject.zip
