136Sneaking in JDBC Drivers

Author: Dr. Heinz M. KabutzDate: 2006-12-11Java Version: 5Category: Tips and Tricks

Abstract: In this newsletter, we look at a technique of how we can replace an existing database driver with our own one. This could be used to migrate an application to a new database where you only have the compiled classes. Or it could be used to insert a monitoring JDBC connection that measures the length of database queries.

Welcome to the 136th edition of The Java(tm) Specialists' Newsletter.

We are slowly settling in to life in Greece. The first 6 weeks was spent running around from department to department. A funny story was when we wanted to get a tax number. We were told to go to the 3rd floor. They sent us to the 1st floor. After waiting patiently, we were told that we needed to go to the 2nd floor. After waiting there for about 30 minutes, we were told that we had to go to the 3rd floor, where we were (very) reluctantly helped. It seems that the bureaucratic types the world over have an infinite supply of time and they use it to frustrate us. Perhaps they are a completely different species to us?

If you ever care to visit the island of Crete, please let me know. Even in December, I am walking around with short sleeves. We are still swimming in the sea, though we are the only ones (besides the fish). But despite the colder weather, Crete is still buzzing with young people.

javaspecialists.teachable.com: Please visit our new self-study course catalog to see how you can upskill your Java knowledge.

Sneaking in JDBC Drivers

One of the biggest mistakes in software development is to hardcode configuration information. Whilst you might not believe this to be possible in today's software development environment, it does still happen. Another big mistake is to distribute similar setup information throughout your application, making it really hard to find all the places should you wish to change.

This seems to happen quite frequently with database drivers. There might be several instances of Class.forName("...") for loading the correct driver. In addition, the connection URL, username and password could be hardcoded or read from obscure config files.

It is sometimes quite useful to know how long a database statement takes to complete and how frequently it is called. A great tool for this is JAMon. In the latest edition, they have support for monitoring JDBC calls. All that you need to do is use their JDBC driver, point it to your driver, and you're done. This is extremely easy when all the config is in one place, but if you are connecting to several databases in various locations in your codebase, it will require code changes. Ideally we should not need any code changes.

Not that many developers know why we call Class.forName(driver class). Inside the driver class there will be a static initializer block that is executed when it is loaded for the first time. It calls DriverManager.registerDriver(instance of driver), thus adding the driver to a chain of JDBC drivers. When we call DriverManager.getConnection(url, username, password), the DriverManager iterates through the chain of drivers and asks each one whether it can accept your URL. Thus, if your URL starts with jdbc:odbc, then the JDBC-ODBC bridge will be able to accept it.

The JAMon library requires me to replace the existing JDBC drivers with their driver. This can become problematic, as already discussed, since it requires code changes. I wrote a SneakerDriver that when loaded, will always accept a URL, find the correct driver, and then wrap that with the JAMon connection. You will need to download at least JAMon 2.2.

The code is really simple. The acceptsURL() method always returns true. In the connect() method, we get all of the drivers from the DriverManager, and try to get a connection from each one, excluding the SneakerDriver itself. Once I have a live connection, I wrap it with the JAMon JDBC driver and give that back to the caller. The JAMon JDBC driver then collects statistics on JDBC performance.

package com.cretesoft.jdbc;

import com.jamonapi.proxy.MonProxyFactory;
import java.sql.*;
import java.util.*;

public class SneakerDriver implements Driver {
  static {
    try {
      DriverManager.registerDriver(new SneakerDriver());
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  public boolean acceptsURL(String url) throws SQLException {
    return true;
  }
  public Connection connect(String url, Properties info)
      throws SQLException {
    SQLException reason = null;
    Enumeration en = DriverManager.getDrivers();
    while (en.hasMoreElements()) {
      Driver driver = (Driver) en.nextElement();
      if (!(driver instanceof SneakerDriver)) {
        try {
          Connection result = driver.connect(url, info);
          if (result != null) {
            // Success!
            return MonProxyFactory.monitor(result);
          }
        } catch (SQLException ex) {
          reason = ex;
        }
      }
    }
    // if we got here nobody could connect.
    if (reason != null) {
      throw reason;
    }

    throw new SQLException(
        "No suitable driver found for " + url, "08001");
  }
  public DriverPropertyInfo[] getPropertyInfo(String url,
                                              Properties info)
      throws SQLException {
    return new DriverPropertyInfo[0];
  }
  public int getMajorVersion() {
    return 0;
  }
  public int getMinorVersion() {
    return 1;
  }
  public boolean jdbcCompliant() {
    return true;
  }
}

Sneaking in First

Having a SneakerDriver is great, but how can we ensure that it is the very first JDBC driver that is loaded? We cannot rely on doing this inside code, for several reasons. First off, we do not want to change code. Secondly, we cannot be sure that we are first if we do it in code.

Fortunately there is a solution that is straightforward. In the DriverManager.registerDriver() method, the first thing that happens is that the initial JDBC drivers are loaded. These are specified in the -Djdbc.drivers JVM startup parameter. So all you need to do is start your application with -Djdbc.drivers=com.cretesoft.jdbc.SneakerDriver and it will automatically be #1 in the chain of drivers. No further code changes or config changes are necessary.

I have pondered for a while how best to extract the performance statistics from the JAMon tool. They do provide a bunch of JSP pages that you can hide somewhere in your admin section of your website. This will not help if you have a rich client with Swing. Another option is to start a thread in the constructor of the SneakerDriver that periodically writes the stats to a logfile. These options should not require any code changes.

Here is a sample where I connect to the database and do a query. You will have to put your own JDBC connection settings in there to test it.

package com.cretesoft.jdbc;

import com.jamonapi.proxy.MonProxyFactory;
import java.sql.*;
import java.util.Arrays;

public class SneakerDriverTest {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://myserver:3306/db";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "root";
  // Whatever query you use here, it should take a long time
  private static final String LONG_QUERY = "SELECT name FROM " +
      "very_large_table WHERE ssn='23432343'";

  public static void main(String[] args)
      throws ClassNotFoundException, SQLException {
    Class.forName(DRIVER);
    Connection con = DriverManager.getConnection(
        URL, USERNAME, PASSWORD);
    Statement st = con.createStatement();
    long time = System.currentTimeMillis();
    st.executeQuery(LONG_QUERY);
    time = System.currentTimeMillis() - time;
    System.out.println("time = " + time + "ms");
    System.out.println(
        Arrays.toString(MonProxyFactory.getSQLDetailHeader()));
    Object[][] sqlDetail = MonProxyFactory.getSQLDetail();
    for (int i = 0; i < sqlDetail.length; i++) {
      System.out.println(Arrays.toString(sqlDetail[i]));
    }
    st.close();
    con.close();
  }
}

In my case, I do a simple "SELECT 1" from a remote server. The bottleneck here is then of course not the database, but rather, the network going to the database. Make sure before testing the database that it is atually a bottleneck in your application.

I hope that you have learned something new about JDBC drivers and how we can sneak our own one into the front.

Kind regards from Greece

Heinz

Comments

We are always happy to receive comments from our readers. Feel free to send me a comment via email or discuss the newsletter in our JavaSpecialists Slack Channel (Get an invite here)

Related Articles

Browse the Newsletter Archive