One purpose for an abstraction is to remove boilerplate codes. Boilerplate codes are that portion of codes that you wrote again and again, many times, and in many places.

Let’s look at an example, where your main concern here is to execute the SQL statement:

"INSERT INTO product (product_name, product_price)

VALUES (‘Superman Returns’, 90.00"

To just execute that single SQL statement, you have to go thru a hassle of prerequisites codes. These are:

1. Load the database driver.

2. Get the java.sql.Connection object by providing the url, username and password.

3. Create an instance of java.sql.Statement from the java.sql.Connection object.

4. Execute the SQL statement against the java.sql.Statement object that will return the java.sql.ResultSet object.

5. Close the database connection.

6. All those codes must be written inside a try… catch… block.

What if you can get rid of all those 6 hassles, but instead just write a code to execute the SQL statement? Would it make your life easier?

Mecca provides a DataHelper class that has a static method for you to execute just the SQL, minus all the prerequisites. The method name is executeSELECT(String) that accepts one arguments which is ofcourse the SQL statement.

Here is an example, with all the hassle.

String url = "jdbc:odbc:yourdatabasename";

String driver = "sun.jdbc.odbc.JdbcOdbcDriver";

String user = "guest";

String password = "guest";

Class.forName(driver);

Connection connection =

DriverManager.getConnection(url, user, password);

Statement stmt =

connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_READ_ONLY);

ResultSet rs =

stmt.executeQuery(

"INSERT INTO product (product_name, product_price)

VALUES (‘Superman Returns’, 90.00"

);

connection.close();

And here is an example with DataHelper.

DataHelper.executeSQL("INSERT INTO product (product_name, product_price) VALUES (‘Superman Returns’, 90.00");

That’s it. You don’t have to worry about creating a database driver, or creating a connetion. Or even closing the connection when done.

THE CONNECTION POOL



I have added the connection pooling capability for the mecca framework. The program that manage the connection pool is mecca.db.ConnectionPool.java. Little modification was also done in the mecca.db.Db.java.

Whenever a program execute:

Db db = new Db();

It will lookup for available connection object in the pool first, if there are any, it will just reuse that connection, but if there are no more connection objects in the pool, it will create new connection. Therefore, connection shall always be reuse (when it is free), and a new connection will be created only when needed (i.e. when there are no more free connection in the pool).

When the program execute:

db.close();

It will put the connection object into the pool for later reuse. This pool shall contains a number of connection objects limited by a threshold define by the program.

You can check out the latest source code from the sourceforge CVS (http://www.sf.net/projects/university).

TESTING THE CONNECTION POOL
COMPARISON BETWEEN NOT POOL AND POOL

The dbconnection.properties file now contains an additional parameter named ConnectionPool. The value for this parameter is either true or false.

To enable connection pooling, set this value to true. The program however shall assume a false value if this parameter is missing from the properties file.

I have created a program as a Proof Of Concept to test the application's performance with or without connection pooling. The program is mecca.db.test.DbConnectionPoolTest.java. This program only contain the main(String[]) method with below implementation:

int count = 10000;

long start = System.currentTimeMillis();

for ( int i=0; i < count; i++ ) {

Db db = null;

try {

//Open the database connection

db = new Db();

System.out.println(i +") " + db.getConnection());

String sql ="select count(*) from student_subject";

//get some data

ResultSet rs = db.getStatement().executeQuery(sql);

if ( rs.next() ) {

int total = rs.getInt(1);

}

} finally {

//Close the database connection

if ( db != null ) db.close();

}

}

long elapsedTimeMillis = System.currentTimeMillis() - start;

float elapsedTimeSecond = elapsedTimeMillis/1000f;

System.out.println("Elapsed Time = " + elapsedTimeSecond + " seconds");

To run this program, first, I set the connection pooling to false, in the dbconnection.properties, set ConnectionPool=false, and ran this program, that will open the database connection, execute a SQL select statement, then close the connection, 10000 times.

The result when connection pooling is disabled are:

Elapsed Time = 46.953 seconds

Then, I set the connection pooling to true, ConnectionPool=true, and ran the program again. The result are:

Elapsed Time = 2.296 seconds

As you can see, with connection pool enabled, the program ran 20 times faster than without connection pool.

A TEMPLATE for executing SQL Statements!

DataHelper is a template to execute SQL Statements, and then to return the results of the execution.  The results could be as single object or as a collection of objects.  You can consider the DataHelper as a frameworks where the database issues has been taken away from you - so that you need not think or worry about it.

DataHelper provides some EMPTY methods such as doSQL() and createObject(ResultSet):

public String doSQL() { ........ }

public Object createObject(ResultSet rs) throws Exception { ...... }

The body of the method are left for you to implement them.  It is up to you, what you did within the method.  Basically, you can do ANYTHING within it, like executing other methods... takes your girlfriend for dinner!  or, go for a walk, but before you leaves the doSQL() method, you must create a SQL statement and return it, while for the createObject() method, you must tell how the object should be created for each resultset.

Anyway, how you do it, is 100% UP TO YOU.

As an example:

Collection names = return new DataHelper() {

  public String doSQL() { 
    takesGirlfriendToDinner();
    goForAWalkInThePark(); 
    return mySQLStatement(); //"SELECT name FROM student";
  }

  public Object createObject(ResultSet rs) throws Exception {
    return rs.getString("name");
  }

}.getObjectList();

 

When to use Prepared Statement

Prepared Statement should be of benefit if the same SQL statement is reuse many times with variables bind to it (not literal values). When PreparedStatement is first issued by the program, it will be compiled and will stay cached in the DBMS. So that, the compiled SQL will be executed many times.

A wrong usage by programmers

Sometimes programmers make a wrong decision by using Prepared Statement at the wrong place – when you want to issue a SQL statement only once. This is actually just as the same as if you just execute a SQL statement without compiled it first – and because you will not be using it again and again, you will not get the benefit of compiled SQL statements. Therefore, it will make not much difference from an execution of a SQL statement by using only the Statement class.

Example of PreparedStatement using the SQLPStmtRenderer Object

The mecca framework (University Project) has provided a class that can be used to simplify the use of java.sql.PreparedStatement.

To INSERT:

  Db db = null;
  try {
   db = new Db();
   SQLPStmtRenderer r = new SQLPStmtRenderer();
   
   r.add("id", "")
   .add("name", "")
   .add("icno", "") 
   //more fields
   ; 
    
   r.getPStmtInsert(db.getConnection(), "person"); 
   for ( int i=0; i < 10; i++ ) {
    r.set("id", "id - " + i)
    .set("name", "Name - " + i)
    .set("icno", "IC No - " + i)
    //more fields
    ;
    r.getPStmt().executeUpdate(); //execute 10 times
   }
  } finally {
   if ( db != null ) db.close();
  }

 

And, the to UPDATE :

  Db db = null;
  try {
   db = new Db();
   SQLPStmtRenderer r = new SQLPStmtRenderer();
   
   r.update("id", "")
   .add("name", "")
   .add("icno", "")
   //more fields
   ; 
    
   r.getPStmtUpdate(db.getConnection(), "person"); 
   for ( int i=0; i < 10; i++ ) {
    r.setUpdate("id", "id - " + i)
    .set("name", "Name - " + i)
    .set("icno", "IC No - " + i)
    //more fields
    ;
    r.getPStmt().executeUpdate(); //execute 10 times
   }
  } finally {
   if ( db != null ) db.close();
  }

Of course, it also supports the SELECT and DELETE statement.

The Advantages

Well, you will likely to create NO mistakes when trying to map the fields with its values.  This is because, the Renderer always provides you with the field to value mapping, whereas in normal SQL statement you may lost track - this value is for what field?

When field to value always stands side-by-side, how can you make mistake?