Advantages of a PreparedStatement
:
-
Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches.
-
Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters. Note that this requires that you use any of the
PreparedStatement
setXxx()
methods to set the valuespreparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)"); preparedStatement.setString(1, person.getName()); preparedStatement.setString(2, person.getEmail()); preparedStatement.setTimestamp(3, new Timestamp(person.getBirthdate().getTime())); preparedStatement.setBinaryStream(4, person.getPhoto()); preparedStatement.executeUpdate();
and thus don’t inline the values in the SQL string by string-concatenating.
preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email) VALUES ('" + person.getName() + "', '" + person.getEmail() + "'"); preparedStatement.executeUpdate();
-
Eases setting of non-standard Java objects in a SQL string, e.g.
Date
,Time
,Timestamp
,BigDecimal
,InputStream
(Blob
) andReader
(Clob
). On most of those types you can’t “just” do atoString()
as you would do in a simpleStatement
. You could even refactor it all to usingPreparedStatement#setObject()
inside a loop as demonstrated in the utility method below:public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException { for (int i = 0; i < values.length; i++) { preparedStatement.setObject(i + 1, values[i]); } }
Which can be used as below:
preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)"); setValues(preparedStatement, person.getName(), person.getEmail(), new Timestamp(person.getBirthdate().getTime()), person.getPhoto()); preparedStatement.executeUpdate();