The PreparedStatement Interface

The PreparedStatement Interface

Statement objects are the simplest way to execute SQL statements. But the Statement objects are not optimized or tuned and so they suffer from poor performance when complex SQL statements are executed. This happens primarily because the Statement object is responsible for building the SQL statement. The Statement object sends the SQL statement to the database where it is then checked for syntax and semantics, compiled, and executed. Because this process occurs for each SQL statement executed using the Statement object, complex SQL statements or repetitive SQL statements perform poorly. To get around this, the JDBC API provides an improved and optimized version of the Statement object called the PreparedStatement. Similar to the Statement, the PreparedStatement is an interface and inherits from the Statement interface. The JDBC driver implements the PreparedStatement interface and provides the underlying functions.

What exactly is the difference between PreparedStatement and Statement? The difference lies in the way these two interfaces process SQL statements. The PreparedStatement leverages the JDBC driver to store and precompile SQL statements on the database. When a precompiled SQL statement is to be executed, the overhead of syntax, semantic checks, and so on is removed. You can use the PreparedStatement to your advantage if you parameterize your SQL statement and execute the SQL statement using the PreparedStatement object. Also, in cases where an SQL statement is repetitively executed, in a loop condition for example, the PreparedStatement will give a far better performance than a Statement object. Now take a look at how a PreparedStatement object is created.

Methods

To create a PreparedStatement object, use the prepareStatement() method of the Connection object. The prepareStatement() method takes the SQL statement as a parameter and returns a PreparedStatement object. A code snippet follows:

 PreparedStatement myPStmt = myConn.prepareStatement("SELECT BOOK_NAME from          book_tbl"); 

Because the PreparedStatement interface is a subinterface of the Statement interface, it inherits the methods of the Statement interface. Some of the methods that you looked at earlier for the Statement object are listed here:

• execute()

• executeQuery()

• executeUpdate()

• getResultSet()

• close()

All of the preceding methods perform in ways that are similar to the Statement interface.

An interesting feature of the PreparedStatement object is its ability to parameterize SQL statements. You can set parameters to an SQL statement when the PreparedStatement object is created. Placeholders such as "?" (question mark) are used in place of the actual parameters when the SQL statement is defined. At runtime, you can set the value to these parameter placeholders in your application using the setXXX() methods. The setXXX() methods are available for the database-supported data types. These include all the primitive Java data types and special database data types such as DATE, TIME, and so on. A full listing of the mappings between Java data types and SQL data types is provided in the java.sql.Types class. The setXXX() method accepts the parameter index (which is the position of the placeholder used in the SQL statement) and the actual data. A code snippet follows:

 PreparedStatement myPStmt =          myConn.prepareStatement("SELECT * FROM book_tbl WHERE BOOK_NAME = ?"); myPStmt.setString(1, "SAMS Teach Yourself WebLogic in 21 Days"); 

Notice that in the preceding code the book name is not passed directly but it is passed as a parameter using the ? as a placeholder. The setString() method of the PreparedStatement interface is then used to pass the name of the book at runtime. All parameter indices for the placeholders begin from 1.