Another way to boost performance is to precompile the SQL statement using what is termed a “Prepared statement.” That technique and the related one of “Stored procedures” are described in this section.
A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement repeatedly, often changing some of the argument values at run-time. You get a PreparedStatement using a method of your Connection object. It's easiest to see with a code example:
PreparedStatement pstmt = conn.prepareStatement(
"UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 150000.00);
pstmt.setInt(2, linden4303);
pstmt.executeUpdate();
// other code goes here
pstmt.setBigDecimal(1, 85000.00);
pstmt.setInt(2, jenkins2705);
pstmt.executeUpdate();
That code will set employee linden4303's salary to $150,000, and employee jenkins2705's salary to $85,000. The question marks in the SQL query represent data values that will be filled in before the statement is executed. It works like arguments to a procedure, with one difference: any of the question mark fields that you don't change will retain whatever value you have previously set them to, so you only need to set fields that change.
PreparedStatement has its own versions of the methods executeQuery(), executeUpdate(), and execute(). In particular, PreparedStatement objects do not take an SQL string as a parameter because they already contain the precompiled SQL statement you previously created.
Let's move on to take a look at stored procedures. These are a group of SQL statements bundled together as one unit that can be called from your program. That's where the “procedure” part of the name comes from. The “stored” part of the name is because the procedure can be pre-compiled by the SQL interpreter and actually stored within the database. Stored procedures improve database performance by reducing the amount of information that is sent over a network. A stored procedure is used when you have a group of SQL statements that, taken together, carry out some task like adding a new account and initializing it.
Up until now, stored procedures could not be moved outside the database, and could not be linked to software components or external libraries. These disadvantages disappear when you write stored procedures in Java. You have two choices for creating stored procedures.
-
You can create them using SQL commands to install and manage stored procedures, and submit these commands using
executeUpdate()in the normal way. -
You can write the stored procedure following the SQLJ conventions. SQLJ is an industry standard covering how to embed SQL statements into Java methods and how to use Java methods for stored procedures. There is more information on SQLJ at www.sqlj.org.
Using SQLJ means writing a stand-alone Java program to contain your stored procedure. This is exciting and interesting because it means that even your stored procedures are now portable between different databases. Write a public static void method in a Java class. That method will have the usual code to get a Connection, create a Statement, and execute it. You compile it and put it in a jar file. Then you use the SQLJ library to install the jar file in the database management system. There is a special SQL syntax (which varies between databases) that lets you invoke your stored procedure. We won't cover the specialized technique here, except to say that there are examples in urther Reading on page 631.