1. Overview

1.综述

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.

在本教程中。我们将探讨JDBC的StatementPreparedStatement接口。我们不会涉及CallableStatement，这是一个JDBC API接口，它用于执行存储过程。

2. JDBC API Interface

2.JDBC API接口

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:

BothStatement和PreparedStatement可以用来执行SQL查询。这些接口看起来非常相似。然而，它们在功能和性能上有很大的不同。

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:

为了能够使用Statement和PreparedStatement在我们的例子中。我们将在我们的pom.xml文件中声明h2JDBC连接器为一个依赖项。xml文件中作为依赖。

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <version>1.4.200</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

3.声明

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

首先，Statement接口接受字符串作为SQL查询。因此，当我们将SQL 字符串连接起来时，代码的可读性就会降低。

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.

其次，它容易受到SQL注入。接下来的例子说明了这个弱点。

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:

在第一行中，更新将把所有行的”name“列设置为”hacker“，因为”-“后面的任何内容在SQL中被解释为一个注释，更新语句的条件将被忽略。在第二行中，插入将失败，因为”name“列的引号没有被转义：。

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:

第三，JDBC将带有内联值的查询传递给数据库。因此，没有查询优化，最重要的是，数据库引擎必须确保所有的检查。另外，查询在数据库中不会显示为相同的内容，它将防止缓存的使用。

public void insert(List<PersonEntity> personEntities) {
    for (PersonEntity personEntity: personEntities) {
        insert(personEntity);
    }
}

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

第四次。pair-s”>DDL查询，如CREATE。ALTER，以及DROP。

public void createTables() {
    String query = "create table if not exists PERSONS (ID INT, NAME VARCHAR(45))";
    connection.createStatement().executeUpdate(query);
}

Finally, the Statement interface can’t be used for storing and retrieving files and arrays.

最后，Statement接口不能用来存储和检索文件和数组。

4. PreparedStatement

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:

首先，PreparedStatement延伸了Statement接口。它有方法来绑定各种对象类型，包括文件和数组。因此，代码变得容易理解。

public void insert(PersonEntity personEntity) {
    String query = "INSERT INTO persons(id, name) VALUES( ?, ?)";

    PreparedStatement preparedStatement = connection.prepareStatement(query);
    preparedStatement.setInt(1, personEntity.getId());
    preparedStatement.setString(2, personEntity.getName());
    preparedStatement.executeUpdate();
}

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

其次，它通过对所有提供的参数值进行转义，防止SQL注入。

@Test 
void whenInsertAPersonWithQuoteInText_thenItNeverThrowsAnException() {
    assertDoesNotThrow(() -> dao.insert(new PersonEntity(1, "O'Brien")));
}

@Test 
void whenAHackerUpdateAPerson_thenItUpdatesTheTargetedPerson() throws SQLException {

    dao.insert(Arrays.asList(new PersonEntity(1, "john"), new PersonEntity(2, "skeet")));
    dao.update(new PersonEntity(1, "hacker' --"));

    List<PersonEntity> result = dao.getAll();
    assertEquals(Arrays.asList(
      new PersonEntity(1, "hacker' --"), 
      new PersonEntity(2, "skeet")), result);
}

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.

第三，PreparedStatement使用预编译。一旦数据库得到一个查询，它将在预编译查询之前检查缓存。因此，如果它没有被缓存，数据库引擎将保存它以备下次使用。。

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.

此外，该功能通过非SQL二进制协议加快了数据库和JVM之间的通信。也就是说，数据包中的数据较少，所以服务器之间的通信速度更快。

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

第四，PreparedStatement在单个数据库连接期间提供batch/执行。让我们来看看这个动作。

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.

接下来，PreparedStatement通过使用BLOB和CLOB数据类型，为存储和检索文件提供了简便的方法。同样，它通过将java.sql.Array转换为SQL数组来帮助存储列表。

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

最后一点。PreparedStatement实现方法，例如getMetadata()，这些方法包含有关返回结果的信息。

5. Conclusion

5.结论

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.

在本教程中，我们介绍了PreparedStatement和Statement之间的主要区别。这两个接口都提供了执行SQL查询的方法，但更适合使用Statement进行DDL查询，而PreparedStatement进行DML查询。

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

像往常一样，所有的代码实例都可以在GitHub上找到。