Using PreparedStatement

I l @ ve RuBoard

Using PreparedStatement

The ordinary Statement class is fine for running queries that don't have parameters, but as soon as you need to be able to add Java variables to SQL statements, they get clumsy. For example, let's say that you have a variable called findLast that is holding a string containing a last name . You want to find all the employees with that last name. To do it with a statement, it would have to look like this:

 Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM employees where lname_txt = '" + findLast + graphics/ccc.gif "'"); 

That's an unattractive piece of code, especially because you have to remember to put single quotes around the string value. But worse , if there are "special characters such as ' that are meaningful to SQL in the string (for example, if the name were O'Donnell), the query will throw a SQL exception.

Thankfully, Java offers a way around this complication. Instead of using Statement , you can use PreparedStatement . Let's look at that code again, this time using PreparedStatement :

 PreparedStatement st = conn.prepareStatement("SELECT * FROM employees where lname_txt = graphics/ccc.gif ?"); st.setString(1, findLast); ResultSet rs = st.executeQuery(); 

The setString call takes a positional first argument and replaces the n th ? in the prepared statement with its second argument. It automatically determines whether it needs to put quotes around the value or escape any special characters inside the value. There are equivalent versions of all the getX functions, setString , setFloat , setDate , and so on.

MORE BENEFITS OF PREPARED STATEMENTS

In addition to cleaning up your code by letting you drop positional values into SQL statements, there are other reasons to use PreparedStatement .

If you need to run a SQL statement repeatedly, you can use the same prepared statement with different arguments. The database has to parse and compile the SQL statement only once, which means that you gain a performance boost.

Also, if you want to insert binary data into a database, the only way to do it is with a PreparedStatement .

I l @ ve RuBoard


MySQL and JSP Web Applications. Data-Driven Programming Using Tomcat and MySQL
MySQL and JSP Web Applications: Data-Driven Programming Using Tomcat and MySQL
ISBN: 0672323095
EAN: 2147483647
Year: 2002
Pages: 203
Authors: James Turner

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net