You are here: Home | MJDBC
 
Optimized Java Database Access
    MJDBC is a single API that could be used as a shared language through which applications can talk to different database engines without knowing their programming interface. MJDBC is a freeware pure java package that optimize JDBC operations: it reduces the need to load the JDBC driver and to open and close database connections. The typical use of  MJDBC is in multithread servlet environment: instead of opening a connection to the database for each incoming request, you can reuse a yet opened connection. In MJDBC terminology, connections are thought as shared objects to be used in mutex way by a set of concurrent threads (i.e. the servlets).
 

 

  • See a good guide to servlet programming

  • See a good guide to JDBC programming

  • Download MJDBC Package Source

  • Demo Servlet using MJDBC to access a postgress DB 
 

MJDBC: a freeware package to optimize JDBC access in a Multithread environment

MJDBC is a package that i developed to increase the performance reached by JDBC in a multithread environment. Just to remember, JDBC is the standard Java API to access databases and  Java is supposed to bring us the ability to "write once, compile one, run anywhere". JDBC is coherent with this philosophy: in fact it is an abstraction that allows the programmer to interact with any database without knowing its own programming interface. A good guide to JDBC programming is available here.

 
Without going deeply in JDBC programming let me cite a classic JDBC piece of code. You can trace four different operations:
  • Loading JDBC driver to access the given database. This is accomplished loading for name driver's class.
  • Opening JDBC connection, using DriverManager class
  • Submitting SQL query, using the Statement class
  • Closing JDBC connection
import java.sql.*;
public class App{
    public static void main (String args[]) {
 
        String url = "jdbc:subprotocol:db";
// load JDBC driver for my Database
        try {
            Class.forName("jdbcDriver");
        } catch(Exception e){
            System.out.println("Failed to load given jdbc Driver");
        }
        try {
// connect DB as specifed in the url, as user gulli and pwd gulli
            Connection con = DriverManager.getConnection(url, "gulli", "gulli");
            Statement select = con.createStatement();
            ResultSet result  = select.executeQuery("SELECT key, val FROM db_test");
// get the results for the yet submitted  query
            while (result.next()){
                int key = result.getInt(1);
                String val = result.getString(2);
 
                System.out.println("key = " + key + "/nval =  " + val);
           }
// close connection and statement
           select.close();
           con.close();
 
       } catch (Exception e){
           e.printStackTrace();
       }
   }
}
This four step interaction is not very efficient, if there are many SQL operations to be accomplished from time to time. For example in servlets paradigm, each incoming HTTP request directed to a servlet is handled by a separate thread that performs the above four step interaction with the available database.

A good guide to servlet is available here. Let me do a little summarization. Remember that servlets are standard API to extend HTTP server functionality:
  • The first time a servlet is loaded in HTTP server, it executes a "public void init(ServletConfig config)" method that allows the initialization of servlet' status (single thread operation).
  • When a servlet is invoked through a specific HTTP client request, a new thread is spawned and the method    public void "doGet(HttpServletRequest req, HttpServletResponse res)"  is executed.
  • When the servlet is discarded from HTTP server (for example for off line operation) the method "public void destroy()"  is executed to clean status (single thread operation).
So, servlets are cool !. Unlike cgi-bin, servlets have a good abstraction to maintain their status without using external pattern of memory (like files or so on..). Besides, servlets works in a real multi-thread environment. And they have a transparent way to maintain client status from connections through connections (but this is another story and it is out of the scope of MJDBC).

Having in mind the servlets model, the solution the reduce latency of answer of a database accessed via JDBC, is to minimize the need to load the JDBC driver and  to open and close connections to the database. In MJDBC terminology a connection is thought as a shared object. It is opened once and it is accessed by a set of concurrent threads in mutex way. MJDBC acts as a connection manager: before submitting SQL statements, every thread T asks for an non used connection among the opened ones. After retrieving results T informs MJDBC that the assigned connection is no more used and it is available for later requests. Using servlets,  all the connections are opened during the execution of the "public void init(ServletConfig config)" method and all the connections are closed during the execution of the  "public void destroy()method".

In order to better understand MJDBC let me show a little piece of a servlet that use it. In this example i use postgresql as database and Petet T Mount as JDBC driver

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

// to gain benefits of having a pool of connections used in mutex
// manner and never closed until the destroy() of the servlet

import com.studioitech.gulli.MJDBC.*;

public class adserver extends HttpServlet{
 
     // Statics
     final static String JDBC_DriverName = "postgresql.Driver";
     final static String url = "jdbc:postgresql:ad";
     final static String usr = "test";
     final static String pwd = "test";
     final static int connection = 2;

     //MJDBC arbiter
     dbArbiterConnections arbiter;       // arbiter to get available con

     // Init method, loaded in single thread mode by HTTP server

     public void init(ServletConfig config)
      throws ServletException
     {
      super.init(config);

      try {

       // Load arbiter to get available connections
       arbiter = new dbArbiterConnections(JDBC_DriverName, url,
                                          usr, pwd, connection);
      } catch (Exception e1){
          E.debug("Problems initing arbiter " + e1.toString(), 0);
      }
      E.debug("Initing done", 0);
     }

    // doGet Method - method that runs when the servlet is hit via GET

    public void doGet(HttpServletRequest req, HttpServletResponse res)
      throws ServletException, IOException
    {
      PrintWriter out;
      res.setContentType("text/html");
      out = res.getWriter ();
      E.setOutput(out);    // set output channel
      E.flush();           // flush the acquired buffer, if any
      E.setAutoFlush(true);// now set autoflush

      // A connection in the pool
      dbConnection conInPool;

      // get what i have to serve I can specify a MIME TYPE
      // or a KEY1 [KEY2], [KEY3] I must specify a DIM

      E.debug("Getting a request", 1);
      String type = req.getParameter("type");
      String dim  = req.getParameter("dim");
      String key1 = req.getParameter("key1");
      String key2 = req.getParameter("key2");
      String key3 = req.getParameter("key3");
      E.debug("type=" + type + " dim=" + dim + " key1=" + key1 +
        " key2=" + key2 + " key3=" + key3, 3);

      try{

        // Ask MJDBC to give you a connection

       conInPool = arbiter.getAnAvailableCon();
       SQLwork sw = new SQLwork(conInPool.st, out);
       sw.doWork(type, dim, key1, key2, key3);

       // Tell MJDBC this connection is available
       conInPool.freeConnection();
 
      } catch (Exception e){
       E.debug("Got Exception: " + e.toString(), 0);
      }
 }

// here goes all de-initialization
 public void destroy()
 {
  try {
    // Shutdown MJDBC
   arbiter.shutdown();
  } catch(SQLException sqle)
  {
  }
 }
}
 
You can notice that during the init phase of the servlet i create an object arbiter istantiating the class dbArbiterConnection. I pass to the constructor the name of the JDBC driver, the database url to connect to, the user and its pwd, the number of  connection to open with underlying database.

arbiter = new dbArbiterConnections(JDBC_DriverName, url, usr, pwd, connection);

The core of MJDBC using is exploited during multithread execution of public void doGet(HttpServletRequest req, HttpServletResponse res) method. For each incoming request to servlet i perform something like this:

// Ask MJDBC to give you a connection

conInPool = arbiter.getAnAvailableCon();
SQLwork sw = new SQLwork(conInPool.st, out);
sw.doWork(type, dim, key1, key2, key3);

// Tell MJDBC this connection is available
conInPool.freeConnection();

First at all, i ask the arbiter to give me an available MJDBC connection and, if none is available the arbiter simple waits for one to became free to be used.  A MJDBC could be thought as a couple composed by a [JDBC Connection, JDBC statement]. Then i use this connection to perform my SQL work (SQLwork is just a class that in this example masquerades my raw SQL query). Finally i free the yet used MJDBC connection. The cool thing here is that the connection is not closed but just is become ready for another use by a concurrent thread.

As you can see, using MJDBC is very simple and it is coherent with multithread servlet environment and its idea of HTTP server's status maintained from connection to connection. To conclude just few links:

 
 
 

Antonio Gullì
gulli@di.unipi.it