Optimizing Database Transactions with Java Database Connection (JDBC)
by
Chád Darby
Introduction
Currently, Java developers are delivering enterprise database applications. By using the Java Database Connection (JDBC) developers are able to query and update enterprise information stored in databases. However, the issue that continues to surface with database applications is performance. Application performance is critical when developing an enterprise application because typically the application sends database queries to a remote database server.
In a quest to increase performance, this article will discuss the Java classes available in the JDBC Application Programming Interface (API) for optimizing database access. There are two methods available for optimization: prepared statements and stored procedures. This article examines the implementation of prepared statements and stored procedures by analyzing a sample application.
The Consultant Job Selector Application
A technical recruiter to fill contracting positions primarily uses this application. The recruiter has the option to search for consultants with a maximum hourly rate and job category. Also, the recruiter has the capability to update the hourly rate for consultants in a given field. Figure 1 provides a snapshot of the database:
FIGURE 1: SNAP SHOT OF DATABASE TABLE
Figure 2 is a screen shot of the sample application.
FIGURE 2: SCREEN SHOT OF CONSULTANT’S JOB SELECTOR
The sample application is used to illustrate the implementation of prepared statements and stored procedures. Listings 1 - 5 contain the full source code for the sample application. The full source code can also be found at www.j-nine.com/pubs/javareport.
Analyzing the SQL Execution Plan
A SQL statement is used to access and update information stored in a database. When you submit a SQL statement, the database engine performs a number of steps in order to build an execution plan as shown in figure 3.
For every SQL statement submitted to the database, the execution plan is followed. The complete process is also followed if you submit the same SQL statement to the database engine.
Now let’s take a look at the "Consultant Job Selector" application. If the recruiter is searching for consultants with a maximum hourly rate of $50.00 to fill a Windows NT contract position, the application submits the following SQL statement to the database engine:
SELECT LastName, FirstName, Email, HourlyRate FROM consultants
WHERE HourlyRate <= 50.00 AND JobCategory = ‘Windows NT’;
The JDBC code for this SQL statement follows:
Connection connection = DriverManager.getConnection(databaseURL, userid, passwd);
Statement searchStatement = connection.createStatement();
ResultSet myResultSet = searchStatement.executeQuery (
"SELECT LastName, FirstName, Email, HourlyRate " +
"FROM consultants " +
"WHERE HourlyRate <= 50.0 and JobCategory = ‘Windows NT’ ");
If the recruiter made multiple queries using this statement, the database engine parses, compiles, plans and executes the statement each time. As you can see, this process is inefficient for multiple transactions with the same SQL statement.
You can optimize the execution plan by parsing, compiling and planning the SQL statement ahead of time. Then when you submit the SQL statement again, you only have to perform the "execute" step of the SQL execution plan.
Prepared Statements
A prepared statement is a pre-compiled SQL statement. The application sends the prepared statement to the database engine before the query is executed. This process allows the database engine to parse, compile and plan the query. Figure 4 illustrates the optimization of prepared statements.
FIGURE 4: Optimized Execution Plan for Prepared Statements
When the same query is submitted again, the
database engine only has to perform the "execute" step of the SQL execution
plan. This minimizes the overhead of repeating the entire process for building an
execution plan.
CAUTION: JDBC does not guarantee that the database engine will take advantage of
prepared statements and provide this optimization. You should consult the technical
documentation provided by your database vendor. Creating and Executing Prepared Statement
In order to create a prepared statement, you must call the prepareStatement(String sqlString) method of your Connection object. Sample code for preparing a statement to find all Windows NT consultants with maximum hourly rate of $50.00 follows:
Connection connection = DriverManager.getConnection(dbURL, userid, passwd);
searchPreparedStatement = connection.prepareStatement (
"SELECT LastName, FirstName, Email, HourlyRate " +
"FROM consultants " +
"WHERE HourlyRate <= 50.0 and JobCategory = ‘Windows NT’ ");
The above statement is prepared during your application startup. This normally occurs after you have received your database connection from the driver manager.
Repeat submissions of this query can be executed using the following code:
ResultSet rs = searchPrepStmt.executeQuery();
Multiple submissions of the prepared SQL statement results in processing the "execute" step of the SQL execution plan only. This execution process effectively giving you the desired optimization. This optimization is useful in the "Consultant Job Selector" application because the recruiter normally makes multiple queries during an application session.
Prepared Statement Limitations
By now, you may have noticed the constraints imposed on the prepared statement. The prepared statement in the previous example is inflexible because the values for the hourly rate and job category are predefined.
If the recruiter wanted to search for Windows NT consultants with a maximum hourly rate of $75.00, the program submits the following query
SELECT LastName, FirstName, Email, HourlyRate FROM consultants
WHERE HourlyRate <= 75.00 AND JobCategory = ‘Windows NT’;
Also during the same session, the recruiter may want to search for lower-paid Windows NT consultants with an hourly rate of $50.00. The program then submits an entirely different query
SELECT LastName, FirstName, Email, HourlyRate FROM consultants
WHERE HourlyRate <= 50.00 AND JobCategory = ‘Windows NT’;
Please note that the SQL statements differ only in the hourly rate. There may be situations in which you would like to make small modifications to the SQL statement before re-executing.
Parameterized Prepared Statements
The real power of a prepared statement is realized when used with parameters. Parameterized prepared statements gives you the ability to create a pre-compiled SQL statement and bind new parameter values prior to execution.
Creating Parameterized Prepared Statements
Now, let’s take the previous example a step further. The recruiter needs to submit a query based on their input of hourly rate and job category. Here is the code for creating parameterized prepared statements:
searchPreparedStatement = connection.prepareStatement (
"SELECT LastName, FirstName, Email, HourlyRate " +
"FROM consultants " +
"WHERE HourlyRate <= ? and JobCategory = ?");
Please make note of the question marks ("?"). The question marks serve as parameters or placeholders.
Setting Prepared Statement Parameters
In order to set the parameters, the PreparedStatement class provides a collection of setXXX methods for setting strings, ints, and floats and other Java data types.
public void setInt(int parameterIndex, int anInt)
public void setString(int parameterIndex, String aString)
public void setFloat(int parameterIndex, float aFloat)
… many others available
When the prepared statement, searchPreparedStatement, was created, two parameters were given. The first parameter was for the hourly rate and the second parameter was for the job category as shown below:
searchPreparedStatement = connection.prepareStatement (
"SELECT LastName, FirstName, Email, HourlyRate " +
"FROM consultants " +
"WHERE HourlyRate <= ? and JobCategory = ?");
Setting the parameter values is accomplished by binding a value to the parameter position. Parameter positions are numbered from left to right starting at 1.
Given this information, the parameters for the prepared statement are set using the following code:
searchPreparedStatement.setInt(1, theRate); // theRate is an int
searchPreparedStatement.setString(2, theJobCategory);// theJobCategory is a String
To execute this SQL statement, use the following code:
ResultSet myResultSet = searchPreparedStatement.executeQuery();
Now the SQL statements can be built based on the user input. The "Consultant Job Selector" prompts the recruiter for the hourly rate and job category. Given that information, the application uses the pre-compiled prepared statement and bind the user supplied data to the statement parameters. Figure 5 shows the dialog box for user input.
FIGURE 5 - User Input Dialog Box
The recruiter can now retrieve a list of all Macintosh consultants whose hourly rate is $50.00 maximum and analyze the results of the query. If the recruiter would like to view consultants with a lower hourly rate, then he simply provides new values and the application binds these new values to the statement parameters.
Benefits of Using Parameterized Prepared Statements
By using parameterized prepared statements, the application can efficiently submit multiple queries to the database. The end user of the application, which was the recruiter in this example, received a quicker response from the database engine. As you can see, prepared statements with parameters give you the ability to create efficient pre-compiled SQL statements.
Stored Procedures
A stored procedure is pre-compiled SQL code that resides on the database server. They take input parameters and return a result.
Stored procedures are compiled only once in their lifetime. As a result, they actually execute faster than prepared statements. Recall that prepared statements need to build an execution plan each time the application is run.
In addition to the speed advantage, a stored procedure gives you the ability to centralize business logic. This centralization of the business logic directly supports reuse. By using stored procedures, you actually move complex business logic onto the database server as opposed to including it in every application you develop.
In the sample application, "Consultant Job Selector", a stored procedure is used to update the hourly rates for a given job category. For example, the recruiter can increase the hourly rate of all Macintosh consultants to $60.00.
Creating A Stored Procedure
The task at hand is to create the stored procedure on the database server. Depending on your database, there is a different syntax for creating stored procedures.
CAUTION: Not all databases support stored procedures, namely MS Access. You can query the database metadata and see if stored procedures are supported.
For example, the following is SQL code for creating the stored procedure sp_updateHourlyRate on an Oracle database:
CREATE OR REPLACE PROCEDURE sp_updateHourlyRate
( rate IN INTEGER,
theJobCategory IN VARCHAR ) IS
BEGIN
UPDATE consultants SET HourlyRate = rate
WHERE JobCategory = theJobCategory
END;
Once the SQL code for the stored procedure is successfully compiled, then it is callable by outside applications. The challenge now is to call the stored procedure from the "Consultant Job Selector" application.
Calling Stored Procedures From JDBC
In order to call the stored procedure, you must use the CallableStatement available in the JDBC API. The following code creates a CallableStatement to access the stored procedure sp_updateHourlyRate.
CallableStatement updateHourlyStatement;
updateHourlyStatement = connection.prepareCall
("{call sp_updateHourlyRate [(?, ?)]}");
Passing Parameters To Stored Procedures
At this point, you have a callable statement. However, you are not finished because you must bind values to the parameters. This process is very similar to setting parameters for prepared statements.
So, to set the parameters for updating the hourly rate of Macintosh consultants to $60.00, you use the following code:
updateHourlyStatement.setInt(1, 60); // set the rate
updateHourlyStatement.setString(2, "Macintosh"); // set the job category
Once the parameters are set, you execute the statement using the following code:
updateHourlyStatement.executeUpdate();
Getting Results From Stored Procedures
You may have noticed that the stored procedure sp_updateHourlyRate only takes input parameters. You can modify the stored procedure to also return information to the calling application. This is accomplished by declaring an out parameter in the SQL code of the stored procedure. The SQL code for the revised stored procedure follows:
CREATE OR REPLACE PROCEDURE sp_updateHourlyRate
( rate IN INTEGER,
theJobCategory IN VARCHAR,
recordsAffected OUT INTEGER ) IS
BEGIN
… perform the update
… count the changes and store result in parameter recordsAffected
END;
This revised stored procedure makes the updates and stores the number of updates in the recordsAffected out parameter.
One additional step is required before calling the revised stored procedure. You must register the out parameters. Sample code follows for registering out parameters:
updateHourlyStatement = connection.prepareCall
("{call sp_updateHourlyRate [(?, ?, ?)]}");
updateHourlyStatement.setInt(1, 50); // set the rate
updateHourlyStatement.setString(2, "Macintosh"); // set the job category
updateHourlyStatement.registerOutParameter(3, java.sql.Types.INTEGER)
// set out param
Now you can execute the stored procedure and get the results of the out parameter using the following code:
updateHourlyStmt.executeUpdate();
int theRecordsChanged = updateHourlyStmt.getInt(3);
The point of this example was to illustrate the basic steps for getting results from stored procedures.
Benefits Of Stored Procedures
Earlier in the section, you learned how stored procedures are used to increase execution speed and centralize business logic. Stored procedures also reduce network traffic because multiple, complex transactions are grouped into a single stored procedure. By having the SQL code pre-compiled as a stored procedure you effectively hide sensitive transaction information. This also reduces the amount of SQL code that you have to embed in your actual Java program. As you can see, stored procedures provide a number of advantages.
Conclusion
The sample application, "Consultant Job Selector", was a good example of using prepared statements and stored procedures. The prepared statements were used in conjunction with parameters to provide dynamic SQL statements to the database engine. Also, stored procedures were used to centralize the business logic on the database server. The challenge now is to integrate these techniques in your next enterprise application to optimize database performance. Are you ready for the challenge?
About The Author
Chád (shod) Darby is a Java consultant for J9 Consulting, www.j-nine.com. He specializes in developing server-side Java applications and database applications. He also provides Java training for Learning Tree International. Chád can be reached at: darby@j-nine.com.
References
Jepson, Brian. Java Database Programming, New York : Wiley Computer Publishing, 1997.
Reese, George. Database Programming with JDBC and Java, Cambridge, MA : O’Reilly & Associates, 1997
CONSULTING | DEVELOPMENT | PUBLICATIONS | TALKS | ABOUT
Copyright © 1999, J9 Consulting
April 03, 1999
