Close
Data TutorialsSQL Tips

The Difference Between a JDBC Statement and Prepared Statement

Posted by AJ Welch

While it is true that, in many cases, a basic SQL statement will get the job done for many database changes or queries, it is often a best practice to make use of the flexibility and advantages afforded to you by using PreparedStatements.

The primary differences between a standard JDBC statement and a PreparedStatement are best defined by the benefits that a PreparedStatement affords you and your application. Below we’ll examine the three core advantages of PreparedStatements over regular JDBC/SQL statements.

SQL Injection Prevention

The first benefit of using a PreparedStatement is you can take advantage of the multitude of .setXYZ() methods, such as .setString(), which allows your code to automatically escape special characters such as quotations within the passed in SQL statement, preventing the always dangerous SQL injection attack.

For example, in a standard SQL statement, it may be typical to insert values directly inline with the statement, like so:

statement = "INSERT INTO books (title, primary_author, published_date) VALUES ('" + book.getTitle() + "', '" + book.getPrimaryAuthor() + "', '" + new Timestamp(book.getPublishedDate().getTime()) + "'";

This would force you to execute your own code to prevent SQL injections by escaping quotes and other special characters from the inserted values.

Conversely, a PreparedStatement might be invoked as follows, using the .setXYZ() methods to insert values with automatic character escaping during method execution:

ps = connection.prepareStatement("INSERT INTO books (title, primary_author, published_date) VALUES (?, ?, ?)");
ps.setString(1, book.getTitle());
ps.setString(2, book.getPrimaryAuthor());
ps.setTimestamp(3, new Timestamp(book.getPublishedDate().getTime()));
ps.executeUpdate();

Pre-Compilation

Another benefit of a PreparedStatement is that the SQL itself is pre-compiled a single time and then retained in memory by the system, rather than being compiled each and every time the statement is called. This allows for faster execution, particularly when a PreparedStatement is used in conjunction with batches, which allow you to execute a series (or batch) of SQL statements all at once during a single database connection.

For example, here we have a function that accepts a List of books. For each book in the list, we want to execute an INSERT statement, but we’re going to add all of them to a batch of PreparedStatements and execute them all in one fell swoop:

public void createBooks(List<Entity> books) throws SQLException {
  try (
    Connection connection = dataSource.getConnection();
    PreparedStatement ps = connection.prepareStatement("INSERT INTO books (title, primary_author, published_date) VALUES (?, ?, ?)");
  ) {
    for (Entity book : books) {
      ps.setString(1, book.getTitle());
      ps.setString(2, book.getPrimaryAuthor());
      ps.setTimestamp(3, new Timestamp(book.getPublishedDate().getTime()));

      ps.addBatch();
    }
    ps.executeBatch();
  }
}

Insertion of Abnormal Data Types in SQL Statement

The final advantage of PreparedStatements that we’ll cover is the ability to insert abnormal data types into the SQL statement itself, such as Timestamp, InputStream, and many more.

For example, we can use a PreparedStatement to add a cover photo to our book record using the .setBinaryStream() method:

ps = connection.prepareStatement("INSERT INTO books (cover_photo) VALUES (?)");
ps.setBinaryStream(1, book.getPhoto());
ps.executeUpdate();

similar articles