How to measure the performance of JDBC

Java Database Connectivity (JDBC) is widely used in Java applications. In this article, we will discuss how to measure the performance of JDBC, and how to determine which part of the JDBC subsystem needs to be optimized.

The core java.sql interface

Our goal is to improve the performance of the application. In general, we need to analyze the application to find the bottleneck. Of course, it is difficult to analyze a distributed application effectively, I/O is a focus of analysis, this is determined by the characteristics of distributed applications, the threads in a distributed application spend a lot of time waiting for I/O operations. It is not clear that the thread is blocked by waiting for read and write operations is part of the bottleneck or a trivial problem. In the analysis, it is important to have an independent test standard for communication systems. So when testing the performance of the JDBC subsystem, what metrics should we test?

In the java.sql package, there are three interfaces that make up the core of JDBC: Connection, statement, and ResultSet. The normal interaction with the database includes the following sections:

• Obtain a Connection object from the database driver.

• Get the statement object from the connection object that can execute the specified SQL statement

• If the SQL statement needs to read data from the database, use the statement object to obtain a ResultSet object that provides access to the data in the database.

The following example demonstrates the standard database interaction process by accessing all the fields in each row of the specified database table, storing the data for each row to string [], and putting all rows into a vector.

public static Vector getATable(String tablename, Connection Connection)
throws SQLException
{
String sqlQuery = "SELECT * FROM " + tablename;
Statement statement = Connection.createStatement();
ResultSet resultSet = statement.executeQuery(sqlQuery);
int numColumns = resultSet.getMetaData().getColumnCount();
String[] aRow;
Vector allRows = new Vector();
while(resultSet.next())
{
aRow = new String[numColumns];
for (int i = 0; i < numColumns; i++)
file://ResultSet的访问是从1开始的，数组是从0开始的。
aRow[i] = resultSet.getString(i+1);
allRows.addElement(aRow);
}
return allRows;
}

There is no concrete implementation of the three objects of connection, statement, and ResultSet in the java.sql or other SDK, and these objects and other JDBC interfaces are developed by the vendor of the database driver. And is included in the driver package as part of the database driver. If you want to print out the class name of the connection object or other object you are using, you may see strings like xxxconnection, Xxxstatement, Xxxconnectionimpl, Xxxstatementimpl, and so on. where xxx is the name of the database being used, such as Oracle.

If we want to test the JDBC performance of the Getatable () method in the example, you can simply add System.currenttimemillis () at the beginning and end of the method, and the difference between the two is the time that the Getatable () method is used to perform. This method can be used to test the JDBC performance of a method as long as the database interaction process is not mixed with other processes. Typically, however, the database interaction processes of Java applications are distributed across many methods of many classes, and it is difficult to isolate the database interaction process separately. So how do we test the performance of the database interaction process in this situation?

An ideal approach is to have the ability to measure performance in all JDBC classes, and then simply turn on the monitoring function when the performance needs to be monitored. Normally, the JDBC class does not provide this capability, but we can replace them with classes that have this functionality, and the goal of our replacement class is to provide objects that are very similar to the proxy.

The object that encapsulates the interface using a proprietary encapsulation object of an interface is a sophisticated technology with many uses, and the encapsulated object of the collection class synchronization is one of the most famous examples, but there are many other uses. There is even a class in the SDK that generates encapsulated objects specifically at run time: the Java.lang.reflect.Proxy class. Encapsulated objects are also referred to as proxy objects, and if the term proxy object is used in this article, the interpretation of the encapsulated JDBC object is more complex, so the encapsulation class is still used in this article.

To add the ability to test the database interaction process on the basis of the above features, you will also need to make some changes to the rest of the application, and it is clear that this is a price to pay.

Fortunately, when a framework is defined almost entirely by an interface like JDBC, replacing it with a different implementation is fairly straightforward. We can use a wrapper class to replace any implementation of an interface that encapsulates the original class and forwards all calls to the method of the original class. In this article, we can use a wrapper class to replace the JDBC class, place our ability to monitor JDBC performance in the encapsulation class, and then make the monitoring function run along with the entire application.