Tag Archives: Java/Java EE

Question mark ‘?’ characters as text in JDBC

Posted on by
1

Many people wonder how insert strings containing question mark characters into their database queries via JDBC. For those unfamiliar with the problem, ? is a reserved character in JDBC queries for parameterizing inputs. For example, if you have run the same query searching for a user but each time with a different name, JDBC offers you the ability to precompile and save the parameterized form of the query with ?’s, thereby saving the overhead of creating lots of new database statements. First, let’s frame the problem. Consider the following code:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM widgets WHERE note LIKE '%long?'");
ResultSet rs = ps.executeQuery();

Description: This code searches for all widgets with note field that ends in the phrase “long?”, such as “This is how long?”.

Your first thought might be why make this a PreparedStatement at all (which supports parameters), you could just as easily do it with a Statement (which does not support parameters). Under most circumstances, it is a good coding practice to use PreparedStatement over Statements, even if you don’t have any input parameters. It allows you add parameters easily such as:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM widgets WHERE size > ? AND description LIKE '%long?'");
ps.setInt(1,100);
ResultSet rs = ps.executeQuery();

Question: Will this code compile and run?

The answer is that it will compile, but under most circumstances (depending on the JDBC driver) it will not run. Why? The answer highlights just how dumb JDBC drivers really are. In short, they don’t really understand anything about the data they are parsing other than “I see a question mark, let me replace it with something!”. In this example, the user replaced the first ? with an integer, but did not replace the second question mark. In this regard, the JDBC driver will throw a runtime exception prior to sending the code to the database. Also note this code will have the same problem whether you are inserting the value ‘%long?’ into the database or reading it; as I said the JDBC driver knows very little about the query you’re constructing other than its find and replace mentality.

There’s a number of solutions available although my favorite is the following:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM widgets WHERE size > ? AND description LIKE ?");
ps.setInt(1,100);
ps.setString(2,"%long?");
ResultSet rs = ps.executeQuery();

Notice I don’t need the single quotes around the parameter, JDBC will do this for me. This is better not just because it solves our original problem, the code will now run, but we’ve parameterized a messy string query! Solving the problem and enforcing good code practices is a win-win. What might throw you for a loop is you’ve increased the number of question mark ?s in the code by one. Whereas before the second ? in the query was a character representing text to be searched on, the second ? now represents a parameter JDBC should replace. It could be replaced with our target string ‘%long?’ or something without a question mark at all such as ‘horse’. Part of the advantage of parameterizing your inputs in the first place is you don’t have to worry about such situations if a user enters a question mark as a value.

Finally Closing of JDBC Resources

Posted on by
8

I love reading Alex’s The Daily WTF and I noticed the recent Finally WTF is relevant to JDBC in an important way. All *good* JDBC developers already know you should close your result sets, statements, and connections (in that order) in a finally block when you are done with them, but do you all know how they should be closed? In particular, while a finally block will be entered under most circumstances, there’s no guarantee every line of code will be executed. Consider the following code I often come across:

Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
     // Do stuff
     ...
} catch (Exception e) {
     // Do exception recovery stuff
     ...
} finally {
     try {
          rs.close();
          stmt.close();
          con.close();
     } catch (Exception e) {  }
}

Now, can you figure out what’s wrong with this code? Imagine if the result set rs is never populated and stays null. The first line of the finally block will throw a NullPointerException, and the stmt.close() and con.close() will never be executed. In other words, your failure to close a result set would lead to a connection never be closed even though it was in a finally block! Sure the code is guaranteed to enter the finally block, but if it fails on the first line, the rest of the code will be skipped. Next, compare this other common but still incorrect solution:

...
} finally {
     try {
          if(rs!=null) {rs.close();}
          if(stmt!=null) {stmt.close();}
          if(con!=null) {con.close();}
     } catch (Exception e) {  }
}

This solution is a little safer in that it avoids NullPointerExceptions, but it’s equally as useless as the first solution in that there are a number of reasons why the first line of code could still fail, for example if the result set is already closed. This solution actually worries me the most because clearly the developer went through the trouble of setting up the finally block and null pointer catches, but failed to fully understand how a finally block works. Now, I present a superior solution:

...
} finally {
     try {rs.close();} catch (Exception e) {}
     try {stmt.close();} catch (Exception e) {}
     try {con.close();} catch (Exception e) {}
}

Now, is this solution safe? See that if rs or stmt fail to close, the call to con.close() will still be executed. Granted you could get fancy by adding logic to handle/log the exceptions or even catch Throwable (although catching Throwable’s never a good practice), but that’s a bit overkill. You could also nest your finally block with more finally blocks (just add finally block to the rs’s try/catch and put the rest inside it… and so on) although I tend to prefer this solution since it’s more readable.

Lastly, you could make helper methods for closing the the objects to make the code easier to work with such as:

...
} finally {
     DBUtil.close(rs);
     DBUtil.close(stmt);
     DBUtil.close(con);
}

Where the try/catch is inside the helper methods. Keep in mind this last solution isn’t really different from the previous solution, just a code management improvement.