11.3 Portable and Efficient Code Using PreparedStatement
Although easy to use, the Statement object is not suited for large volume queries and updates over several different databases.
11.3.1 Problems with the Statement Interface
Using the Statement interface, developers have to hardcode all SQL statements. There are several serious disadvantages to this approach. We have to manually escape all special characters and null values. String presentations of data types like DateTime and TimeStamp are difficult to remember. Also, it is very difficult to hardcode binary content, such as a picture in a blob field, into a SQL text string. Since different databases have slightly different escaping and formatting schemes, those hardcoded SQL strings are not portable. Every time we pass the SQL string to the Statement object, it has to be parsed to an internal data format. It is inefficient if the same statement is reused many times. The PreparedStatement interface is designed to solve the above problems.
11.3.2 Use of the PreparedStatement Interface
A PreparedStatement object is instantiated with a parameterized SQL template.
String SQLTemplate =
"INSERT INTO PersonRecords " +
"(USERID, NAME, ENTRYTIME, PICTURE) " +
"VALUES (?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(SQLTemplate);
The template is parsed only once and can be efficiently reused. Those ? marks are template parameters that can be specified dynamically at runtime. We use methods setXXXX, where the XXXX indicates supported JDBC types, to assign values to those parameters. For example, we can do
int userID;
String name;
Timestamp entryTime;
byte [] picture;
// For every userID, populate the name, entryTime and picture variables
pstmt.setInt(1, userID);
pstmt.setString(2, name);
pstmt.setTimestamp(3, entryTime);
pstmt.set(4, picture);
pstmt.executeUpdate();
// End
All the database-specific formatting and escaping will be automatically taken care of by the JDBC driver. We should almost always use the PreparedStatement class instead of the Statement class unless doing a simple statement only once. |