Statement与PreparedStatement区别

1. Overview

In this tutorial, we’ll explore the differences between JDBC‘s Statement and PreparedStatement interfaces. We won’t be covering CallableStatement, a JDBC API interface that is used to execute stored procedures.

2. JDBC API Interface

Both Statement and PreparedStatement can be used to execute SQL queries. These interfaces look very similar. However, they differ significantly from one another in features and performance: 

• Statement – Used to execute string-based SQL queries
• PreparedStatement – Used to execute parameterized SQL queries

To be able to use Statement and PreparedStatement in our examples, we’ll declare the h2 JDBC connector as a dependency in our pom.xml file: 

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>2.1.214</version>
</dependency>

Let’s define an entity that we’ll be using throughout this article:

public class PersonEntity {
    private int id;
    private String name;

    // standard setters and getters
}

3. Statement

Firstly, the Statement interface accepts strings as SQL queries. Thus, the code becomes less readable when we concatenate SQL strings:

public void insert(PersonEntity personEntity) {
    String query = "INSERT INTO persons(id, name) VALUES(" + personEntity.getId() + ", '"
      + personEntity.getName() + "')";

    Statement statement = connection.createStatement();
    statement.executeUpdate(query);
}

Secondly, it is vulnerable to SQL injection. The next examples illustrate this weakness.

In the first line, the update will set the column “name” on all the rows to “hacker“, as anything after “—” is interpreted as a comment in SQL and the conditions of the update statement will be ignored.  In the second line, the insert will fail because the quote on the “name” column has not been escaped:

dao.update(new PersonEntity(1, "hacker' --"));
dao.insert(new PersonEntity(1, "O'Brien"))

Thirdly, JDBC passes the query with inline values to the database. Therefore, there’s no query optimization, and most importantly, the database engine must ensure all the checks. Also, the query will not appear as the same to the database and it will prevent cache usage. Similarly, batch updates need to be executed separately:

Fourthly, the Statement interface is suitable for DDL queries like CREATE, ALTER, and DROP:

4. PreparedStatement

Firstly, the PreparedStatement extends the Statement interface. It has methods to bind various object types, including files and arrays. Hence, the code becomes easy to understand:

Secondly, it protects against SQL injection, by escaping the text for all the parameter values provided:

Thirdly, the PreparedStatement uses pre-compilation. As soon as the database gets a query, it will check the cache before pre-compiling the query. Consequently, if it is not cached, the database engine will save it for the next usage.

Moreover, this feature speeds up the communication between the database and the JVM through a non-SQL binary protocol. That is to say, there is less data in the packets, so the communication between the servers goes faster.

Fourthly, the PreparedStatement provides a batch execution during a single database connection. Let’s see this in action:

public void insert(List<PersonEntity> personEntities) throws SQLException {
    String query = "INSERT INTO persons(id, name) VALUES( ?, ?)";
    PreparedStatement preparedStatement = connection.prepareStatement(query);
    for (PersonEntity personEntity: personEntities) {
        preparedStatement.setInt(1, personEntity.getId());
        preparedStatement.setString(2, personEntity.getName());
        preparedStatement.addBatch();
    }
    preparedStatement.executeBatch();
}

Next, the PreparedStatement provides an easy way to store and retrieve files by using BLOB and CLOB data types. In the same vein, it helps to store lists by converting java.sql.Array to a SQL Array.

Lastly, the PreparedStatement implements methods like getMetadata() that contain information about the returned result.

5. Conclusion

In this tutorial, we presented the main differences between PreparedStatement and Statement. Both interfaces offer methods to execute SQL queries, but it is more suitable to use Statement for DDL queries and PreparedStatement for DML queries.

As usual, all the code examples are available over on GitHub.