Section 15.4. Querying Data

15.4. Querying Data

Back to our example. Do you remember the portion that built the query? Here it is again:

 // query String mySQL = "SELECT id, pw FROM Users WHERE name = ?"; PreparedStatement stmt = conn.prepareStatement(mySQL); stmt.setString(1, args[0]); 

If you're at all familiar with SQL then you'll recognize the SQL syntax within the String mySQL. Whatever you want your query to be, just build it as literal text. The query is "parameterized" by using the "?" character. Wherever a "?" appears in the query string, you can substitute a value with the setString() method on the PreparedStatement class.

There are a variety of setXXXX() methods where XXXX stands for different data types. Besides setString(), the most common ones are setInt(), setBigDecimal(), setDouble(), and setTimestamp() which set the parameter from an int, BigDecimal, Double, and Timestamp classes, respectively. The java.sql.Timestamp class is basically a java.util.Date augmented for compatibility with SQL's notion of TIMESTAMP. Read more about it on the Javadoc page for java.sql.Timestamp, or read the java.sql.PreparedStatement page for more on the other set methods available.

The two arguments to each of these set methods are the index and the value that you want to substitute. The index is simply the count of which question mark gets substituted, starting with 1 for the first one. Caution: The parameters start at one, even though most other things in Java, such as Arrays, ArrayLists, and so on, are zero-based. So it's not uncommon in code that uses JDBC to see something like this:

 setInt(i+1, args[i]); 

Note

Building SQL queries out of String literals is made easier in Java by a convenient mismatch between the two languages. In Java, Strings are delimited by double quotes (") whereas in SQL literals are bounded by single quotes ('). Thus in Java, you can construct SQL queries that contain literal string references without much trouble, as in:

 String clause = "WHERE name != 'Admin'" 

If this all seems rather simplistic, well, it is. It may not be a very sophisticated way of blending SQL with Java, but it is very effective. Notice that you don't get any syntax checking on the SQL query when you write your program, though it will throw an exception at runtime if you try to execute ungrammatical SQL. For this reason it is not uncommon to try out all your SQL beforehand, cutting and pasting the queries out of the SQL program that you use for directly talking with your database. Some developers even like to keep their queries in files, to be read at runtime. This has the added flexibility (and risk) of being able to change the query without recompiling the code. Since the recompile doesn't provide any syntax checking on your query string anyway, it seems a reasonable way to go, provided that you properly write-protect the files containing the queries.