Function - What a program never does on the first run, seldom does on subsequent runs, and when it finally does, is a good indication that the program is obsolete.

Friday, February 17, 2006

Java Data Access - Prepared Statements

If you just happen to be looking for, or currently working on any project, in any language, that contains data access code PLEASE do not write spaghetti SQL code. You will be doing the world of developers a favor. Here is what I refer to spaghetti code:

A simple query to authenticate a user against a database:
String sql = "SELECT count(username) FROM login where username='" + username + "' and pwd='" + pwd + "'";

The spaghetti SQL above is a really easy way to compose SQL statements before you execute them on your favorite SQL management system. Unfortunately, if you are not careful, a guy like O'Neal can come along and break your code by inserting a single quote on his username or password. The SQL has not been properly parsed.

The solution involves using parameterized command objects to handle this type of situation cleanly and efficiently:

A simple query to authenticate a user against a database in parameterized form:
PreparedStatement st = db.prepareStatement("SELECT count(username) FROM login WHERE username=? and pwd=?");
st.setString(1, username);
st.setString(2, pwd);

Voila! Problem solved. The Java PreparedStatement class handles escaping of your SQL string based on the type of the parameters. The question mark ordering corresponds to the position of each parameter. For example: 1 => username, 2 => password. Set the parameters, then execute the query.

This solution is also efficient if you have to run several statements in a loop. You can prepare the statement once, and then loop its execution with different parameters:

Inserting products into a database:
PreparedStatement st = db.prepareStatement("INSERT INTO products (name, sku, price) values (?,?,?)");
for(int j = 0; j < productsVector.size(); j++) {
   Product p = (Product)productsVector.get(j);
   st.setString(1, p.getName());
   st.setString(2, p.getSku());
   st.setDouble(3, p.getDouble());

In this example the code loops through a vector of products, and reuses the prepared statement object several times to perform inserts in the database. Note that I purposedly left out several error checking code in order to keep this post small. Most of the data access calls in this examples raise SQL Exceptions.

A better and cleaner approach to handling SQL in your code.
Next time: Parameterized queries for C#, ASP, and PHP.

Questions or comments --> Click on the comments link.


Post a Comment

Links to this post:

Create a Link

<< Home