OOP PooledStatement : Pooling Mechanism of JDBC PreparedStatement / Thread Safe JSP or Servlet on Apache Tomcat

With a few lines of code, JSP or Servlets on Apache Tomcat can use JDBC PreparedStatement. The pool of JDBC PreparedStatement makes JSP or Servlets on Apache Tomcat truly Thread Safe.

OOP PooledStatement is the Java API for JDBC PreparedStatement with the pooling mechanism. By this Java API, you can easily create the truly thread safe JSP or Servlets on Apache Tomcat. Once JDBC Connection is established, JDBC Connection and JDBC PreparedStatement in JSP or Servlets on Apache Tomcat can be pooled and re-used by this Java API. Its sophisticated thread control gives you the significant performance improvement. You can restrict the maximum number of JDBC Connections and JDBC PreparedStatements.

In the multi thread environment like JSP (JavaServer Pages) or Servlets on Apache Tomcat, the JDBC Connection and JDBC PreparedStatement can be shared among threads. By this way, JSP or Servlets on Apache Tomcat can be really Thread Safe. The life time of JDBC Connection and JDBC PreparedStatement can also be specified, i.e. JDBC connection will be closed after some time interval.

Putting it all togather : OOP Bento framework

This Java API is now a part of OOP Bento framework. In this MVC framework by XML, you can write all the GUIs in the web application in the normal HTML, XHTML, WML, HDML or any other mark-up languages. And you no longer need to bother about the user authentication and the session tracking. Because its full working source code example will be the good starting point of this Java API, please also visit the web site of Bento framework.

• Bento framework home page

Related Pages:

• the API documentation of OOP PooledStatement
• README of OOP PooledStatement

For details about JDBC, please visit JDBC (TM) Home page.

What is it?

The main class of this API is PooledPS. The instance of PoolesPS caches the pairs of JDBC Connection and JDBC PreparedStatement in its pools.

Pools of JDBC Connection and JDBC PreparedStatement are shared within the single ClassLoader, and the new instance of JDBC PreparedStatement will be created only when no more free instance of JDBC PreparedStatement is available in the pool. Once the execution on the instance is finished, it will be returned back to the appropriate pool and can be re-used.

PooledPS is responsible for the following tasks:

• Read the property resource file, which supplies this class with the JDBC driver class name, database URL, the user name and the password to the database. The upper limit number of the instances of JDBC PreparedStatement is also specified.
• Load the JDBC driver class.
• Establish the connection to the specified database.
• When requested, create the instance of JDBC PreparedStatement for the given SQL statement. And pass it to the caller.
• Receive the used instance of JDBC PreparedStatement from the caller and store it in the pool. Pools are allocated for each SQL statement. The returned instance is now ready for re-used.
• Before shutdown, close all the instances of JDBC PreparedStatements and their corresponding JDBC Connections. If some of the JDBC PreparedStatement instances are in use at that time, wait until they are returned to the pool.

There may be a case that SQL database does not support pre-compilation of queries, even if its JDBC driver implements JDBC PreparedStatement. For example, MM.Mysql Driver is the complete JDBC 2.0 implementation, but MySQL database itself does not actually cache the pre-compiled queries. Thus, it is not guaranteed that the pre-compiled queries are always cached on the database side. If it is true for your database, PooledPS does not necessarily result in the significant performance improvement. (Please refer to the documentation of your SQL database and its JDBC driver.) But even in such a case, re-using the JDBC PreparedStatement instances will result in the higher performance to some extent.

Are you realy Thread Safe?

Your JSP or Servlet on Apache Tomcat should prepare for a case that multiple requests arrive in concurrently. As you know, this can be done either by:

• Make your Servlet to implement SingleThreadModel interface
• Make your Servlet Thread Safe

• Invoking service( ) method on the single instance of the Servlet sequentially
• Or, create one instance of the Servlet per thread.

Easy programming interface

Thus, PooledPS takes care of all the tasks related with JDBC Connection and JDBC PreparedStatement. And the source code of your Java program can be as simple as possible. It looks like this:

    //The SQL statement for PreparedStatement
    private String sql="INSERT INTO sometable ...";

    //Get the instance of PooledPS.
    //You need not keep the reference to it.
    //Don't worry, just a single instance can
    //be always shared within the ClassLoader.
    PooledPS pool=PooledPS.getInstance();

    PreparedStatement ps=null;
    try{
	//Get the free instance of PreparedStatement.
	//If no free instance is available in the pool,
	//wait until another instance is pushed back
	//to the pool, or create the new instance.
	ps=pool.getPS(sql);

	ps.setLong(1,date.getTime());
	ps.setString(2,log_who);
	ps.setString(3,log_where);
	ps.setString(4,log_what);
	ps.executeUpdate();

	//Push it back to the pool.
	//Will be re-used.
	pool.reusePS(ps);
    }
    catch(Exception ex){
        //Push it back as the dead one.
	//Will be disposed by PooledPS,
	//i.e. the PreparedStatement and Connection
	//will be closed.
        pool.disposePS(ps);
    }

.....

    public void destroy(){
        PooledPS.closeIfOpen();
    }

<%!
    public void jspDestroy(){
        PooledPS.closeIfOpen();
    }
%>

For details, please read the API documentation of OOP PooledStatement

The max age of PreparedStatement

To ensure the integrity of the JDBC PreparedStatement and JDBC Connection, it is desirable to restrict the max age of the JDBC PreparedStatement. PooledPS is also responsible for keeping only the young JDBC PreparedStatement in its pools, i.e. when some JDBC PreparedStatement reaches the specified age since the last access, PooledPS discards it.
The age of the JDBC PreparedStatement is checked by the following 2 stpeps:

1. When getPS( ) is invoked, PooledPS sees if the specified time is passed since the last access to each JDBC PreparedStatement or not, and selects the younger one. The older ones are closed along with their corresponding JDBC Connections.
2. At the specified time interval, PooledPS repeatedly sees if the specified time is passed since the last access to each JDBC PreparedStatement or not. And if the older ones are found, they are closed along with their corresponding JDBC Connections. As you notice, this procedure is done by the background thread dispatched in PooledPS.

Documentation

Before you start to develop your JSP/Servlet by this API, please read the Tips for the Java API from OOP-Research. For details, please read the API documentation of OOP PooledStatement

Requirement

The following API is required :

• JDBC 2.0 driver for your SQL database

Download

This API is now a part of Bento framework. There are the better documentations and source code examples on the web site for Bento framework. There, you will also find the link to download the entire framework or each API in it.

• Bento framework home page

• [Home]
• [SiteMap]
• [About Us]
• [Privacy Policy]
• [Contact Us]

• [How to upload file by JSP]
• [How to send e-mail by JSP]
• [JDBC PreparedStatement]
• [Shift_JIS/BIG5/GB2312]
• [Servlet by HTML]
• [Java Swing GUI]
• [JCE/RSA]
• [How to for Apache Tomcat]
• [How to for JOnAS EJB/Mysql]
• [Source code example]
• [Archives]
• [Reference]
• [Links]
• [Download]