On Java Development

All things related to Java development, from the perspective of a caveman.

JDBC Connections vs JNDI Connections

without comments

Introduction

This post describe differences between the Java Database Connection API (JDBC) and the Java Naming and Directory Interface (JNDI). These technologies are used to obtain database connections.

Using JDBC to Obtain a DB Connection

JDBC is described in the following manner.

JDBC is a Java-based data access technology (Java Standard Edition platform) from Oracle Corporation. This technology is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the JVM host environment.

JDBC allows multiple implementations to exist and be used by the same application. The API provides a mechanism for dynamically loading the correct Java packages and registering them with the JDBC Driver Manager. The Driver Manager is used as a connection factory for creating JDBC connections.

JDBC connections support creating and executing statements. These may be update statements such as SQL’s CREATE, INSERT, UPDATE and DELETE, or they may be query statements such as SELECT. Additionally, stored procedures may be invoked through a JDBC connection.

While JDBC allows multiple implementations to exist and be used by the same application, it implies that the connections are managed in a pool of connections.

One of the more vexing issues for the web developer is the management of database connections. There are two conflicting concerns. First, opening a connection to a database can be time consuming. Several seconds may elapse for the processes of connecting, authenticating, and acquiring resources to be completed. Thus, you cannot simply open a new connection for every page request.

On the flip side, you cannot keep open a huge number of connections to the database. Connections consume resources, both in the client program and in the database server. Commonly, a database puts a limit on the maximum number of concurrent connections that it allows. Thus, your application cannot simply open a connection whenever a user logs on and leave it open until the user logs off. After all, your user might walk away and never log off.

One common mechanism for solving these concerns is to pool the database connections. A connection pool holds database connections that are already opened. Application programs obtain connections from the pool. When the connections are no longer needed, they are returned to the pool, but they are not closed. Thus, the pool minimizes the time lag of establishing database connections.

Implementing a database connection pool is not easy, and it certainly should not be the responsibility of the application programmer. As of version 2.0, JDBC supports pooling in a pleasantly transparent way. When you receive a pooled Connection object, it is actually instrumented so that its close method merely returns it to the pool. It is up to the application server to set up the pool and to give you a data source whose getConnection method yields pooled connections.

Each application server has its own way of configuring the database connection pool. The details are not part of any Java standard—the JDBC specification is completely silent on this issue. In the next section, we describe how to configure GlassFish and Tomcat for connection pooling. The basic principle is the same with other application servers, but of course the details may differ considerably.

To maintain the pool, it is still essential that you close every connection object when you are done using it. Otherwise the pool will run dry, and new physical connections to the database will need to be opened.

 
A JDBC Connection Class

The following class, released in February of 2004, is designed to accept values used to establish a connection to a given database. The values are:

  • DB Driver Name – The driver name is used by the class to identify the type of database for which a connection is required. This class supports the following databases:
    • MySQL – The driver class is org.gjt.mm.mysql.Driver
    • DB2 – The driver class is COM.ibm.db2.jdbc.app.DB2Driver
    • DB2/400 – The driver class is com.ibm.as400.access.AS400JDBCDriver
    • Sybase – The driver class is com.sybase.jdbc2.jdbc.SybDriver
  • User Id – The ID of the user making the connection. This is a name known by the database and is stored within configuration files. For the iSeries, such configuration is provided by a user profile.
  • User Password – The password associated with the user Id. For the iSeries, such configuration is provided by a user profile.

The constructors show that the database type is passed along with the user id and password. Using a Java switch construct, the database type is associated with the proper database driver class and a connection is established and then stored into the class variable connection.

 
Functions Provided by this Class

The following list summarizes the main function and purpose of this class.

  • Creates the database connection object to be used by the DAO.
  • Creates a prepared statement object to be used by the DAO.
  • Creates a SQL statement object to be used by the DAO.

Essentially, that’s most of what this class does. It provides connections for a family of relational databases and supports the creation of SQL statements to be used by the Data Access Objects (DAO).

 
The Limitations Of JDBC

Although the features of the class are essential for conducting SQL operations, it can only realistically be used for a single-user application that establishes a unique connection using the user’s unique id. It does nothing to support connection pooling for a multi-user application. For these reasons, this class and classes like this one are not good candidates for web application development and should not be used in such an environment.

There is the additional problem about the origin of the user-id and password needed to complete the connection. With the fact this class resides with the application, the user-id and password must reside with the application as well. The worst thing to do is to hard code the values in the class. The barely better option is to store them into a configuration file, typically bundled with the application and deployed along with it. When thinking about how to manage the user-id and password problem, it is soon realized that after awhile many applications will exist and each will have its own configuration file containing the user-id and password. The question then arises about what happens when user-id and/or password need to be changed. If it’s a server application, what about connection pool management? These problems are created simply because the class managing connections is an integral part of the application. After some experience is accumulated with these issues it doesn’t take long to arrive at the conclusion there has to be a better way. Fortunately, there is.

 
 

Using JNDI to Obtain a DB Connection

JNDI is described in the following manner.

The Java Naming and Directory Interface (JNDI) is a Java API for a directory service that allows Java software clients to discover and look up data and objects via a name. Like all Java APIs that interface with host systems, JNDI is independent of the underlying implementation. Additionally, it specifies a service provider interface (SPI) that allows directory service implementations to be plugged into the framework. It may make use of a server, a flat file, or a database; the choice is up to the vendor.

Typical uses of JNDI include:

  • connecting a Java application to an external directory service (such as an address database or an LDAP server)
  • allowing a Java Servlet to look up configuration information provided by the hosting web container

 
A JNDI Configuration

Shown below are the configuration entries of a Spring configuration file that describes a bean named ArbitraryDBServerName. Using this bean definition, the application will be able to evoke a JNDI lookup operation referencing the JNDI resource defined on the server and identified by the jndiName parameter value.

The configuration file containing these entries is packaged with the application. Note that there are no database names, user id’s or passwords. The only entry that ties it to a database is represented by the jndiName parameter value.

 
Obtaining a Connection

In an application employing the above Spring definition, the connection is obtained by retrieving the bean defined above. The Java code used to do this is presented below and uses the bean’s id: ArbitraryDBServerName .

The datasource object returned is then passed to the application’s Service class which uses it to establish transaction and commitment control boundaries within which the related DAOs operate.

The lookup mechanism uses the JNDI definition defined on the server. The entries for this is presented next.

 
The Server’s JNDI Configuration.

Shown below are the lines added to Tomcat’s context.xml file that are needed to define a SQL datasource for a MySQL database. It includes the entries for the connection pool management process in addition to the values for the database’s user id and password.

 
Benefits Provided by JNDI

Using JNDI definitions defined on the server, it becomes clear that there is a single connection point to the database. If a change is required to the type of database being used or the user id and password (which can be encrypted) need to be updated, these configuration lines represent the single point of change. Therefore, changes to the applications using this connection definition are not required, saving much time and effort on the part of the support staff.

 
 

JNDI in baseproject

The JNDI configurations presented in this post are integrated into baseproject and are designed to use the JNDI resource established on the Tomcat servers. This implementation can also be established on WebSphere. To the developer, when starting off using baseproject, the JNDI configuration does not have to be written and tested for every new application. The design of the service layer must use Spring to retrieve the JNDI datasource. The classes involved to support this process are also integrated into baseproject.

Shown below is the BaseService class that is designed to be extended by all service classes written by the developer. It is declared as an abstract class so that it cannot be directly instantiated enforcing the design to only be extended by other classes. At the time of this writing, it has only one major method, getEntityManager(), that retrieves the JPA entity manager.

Underneath, it is using the JNDI lookup mechanism described earler. When all services extend this class, they have access to this method. Below is a method in a service class that extends BaseService. It is employing the Hibernate Transaction Coding Idiom.

This is the getJPAEntityManager() method that is in the class ApplicationContextManager that provides access to the JNDI bean defined using Spring.

 
 
Summary

This post highlighted the different approaches to using JDBC connections and connections provided by a JNDI lookup mechanism. In addition, it provided guidelines pertaining to the context of their use. It should be clear, that JNDI provides benefits above those of a pure JDBC solution. In fact, JNDI can even be used for applications designed for the fat-client and gain the same benefits. For a server based application, the opposite becomes true if a pure JDBC solution is employed.

 
Resources:

Written by admin

June 8th, 2014 at 11:16 am

Leave a Reply

You must be logged in to post a comment.