Difference Between Statement and 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>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

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:

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:

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.

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:

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:

@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.

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.

SQL语句和编程语言一样，仅仅就会普通的文本字符串，首先数据库引擎无法识别这种文本字符串，而底层的CPU更不理解这些文本字符串（只懂二进制机器指令），因此SQL语句在执行之前肯定需要编译的；
SQL语句的执行过程：提交SQL语句 -> 数据库引擎对SQL语句进行编译得到数据库可执行的代码 -> 执行SQL代码；这里涉及到sql语句的执行过程。

现在再来看Statement的执行机制：

Statement的execute系列方法直接将SQL语句作为参数传入并提交给数据库执行；也就是说每提交一次都需要先经过编译然后再执行；

那么有一个最大的问题就是如果一条SQL语句需要再短时间内被反复执行，那么每次都需要经过编译这样不是效率非常非常低吗？？

可能你会问哪有需要反复大量执行的相同语句呢？仔细一想可能是的，因此上面说的并不完全精确，精确地讲应该是反复执行一系列模型相似的语句，比如：

insert into table1 values(1, "Peter");

你每次执行时只是values中的值不同，但是总体的语句还是insert into语句，那么你每次提交都需要编译岂不是会把大把时间浪费在编译上面了，非常不值；

PreparedStatement的预编译机制——类似于Properties配置文件：

通过Connection（conn）还可以得到另一种SQL语句对象，即PreparedStatement，该方法就是：PreparedStatement Connection.prepareStatement(String sql);

注意细节：这里就不是create了，而是准备一个SQL语句句柄，精确地讲是一个PreparedStatement语句句柄，并且创建该句柄时直接传入了SQL语句；

预编译机制：

调用prepareStatement时会直接将该SQL语句提交给数据库进行编译，得到的PreparedStatement句柄其实是一个预编译好的SQL语句；
之后调用PreparedStatement的execute方法（其execute系列方法都是无参的），就直接将该预编译的语句提交给数据库直接运行而不需要再编译一次了；
因此这种方法只需要编译一次就够了，后面就是直接提交执行无需再编译，因此效率最高；注意：缓存是针对链接的，每个链接都是独立的，不共享缓存。

而预编译语句最大的特点就是支持占位符（支持的占位符就是?，代表任意长度的字符串），比如：insert into table1 values(null, ?, ?);

也就是说可以用带占位符的SQL语句来创建预编译SQL句柄：PreparedStatement pstmt = conn.prepareStatement("insert into table1 values(null, ?, ?)");

这样的语句也能通过，也可以成功编译，并且可以再后期决定这些占位符具体的值，即使改变这些值后依然不需要编译而直接提交运行；

预编译SQL的安全性能：

首先最明显的一点就是Statement不支持占位符，因此SQL语句中包含可变内容时必须要进行字符串拼接，而字符串拼接不仅加大了编程的难度，降低了代码的可读性，而且非常容易发生因拼接错误而导致地极难发现的bug，因此从这点来看PreparedStatement更加安全；
其次是字符串拼接容易埋下SQL注入的漏洞。

SQL注入是指黑客在应用程序端恶意地往查询信息中填写SQL语句实现入侵（因为客户端输入的要查询的信息往往都是一些正常信息，例如姓名、电话、学号等，没人会无聊地往里面输入代码之类的东西）；

一个典型的例子：比如SQL语句的目的是select * from member_table where name = input_name and pass = input_pass; input_name和input_pass是用户在客户端输入框中输入的账号名和登陆密码，如果该查询语句能查询到该用户（即返回记录不为空）就表示该用户登陆成功；

如果用预编译占位符来表示该语句就是：select * from member_table where name = ? and pass = ?; // 然后后期用input_name和input_pass来填补占位符，这没什么问题

但如果用Statement拼接的方式来写该语句就是："select * from member_table where name = '" + input_name + "' and pass = '" + input_pass +"'";，而此时如果黑客在任意一个输入框（账户名或者密码）中填入'or true or'（就比如账户名输入框吧），那么得到的结果就是：

select * from member_table where name = '' or true or '' and pass = '';

也就是说最后的逻辑表达式变成了name = ''、true、'' and pass = ''三者通过or连接在了一起，因为or了一个true因此整个where表达式的结果都是true，因此必然会select处记录，因此即使这样也可以正常登陆！！这就被成功入侵了

这最主要是由于不带占位符的拼接必须要用单引号'来包裹SQL字符串，而占位符的填写无需单引号，JDBC会自动将Java变量转换成纯字符串然后再自动加上SQL单引号填入占位符中，即使填入的变量是String str = "'Lala'"，那么JDBC也会将其中的单引号' '转化成纯字符单引号处理，而不会被当做SQL的特殊字符单引号'来处理，因为在SQL中单引号'是字符串常量符号！

占位符使用问题注意：

占位符只能占位SQL语句中的普通值，决不能占位表名、列名、SQL关键字（select、insert等）；
原因很简单，以为PreparedStatement的SQL语句是要预编译的，如果关键字、列名、表名等被占位那就直接代表该SQL语句语法错误而无法编译，会直接抛出异常，因此只有不影响编译的部分可用占位符占位！！
mysql是否支持预编译有两层意思：db是否支持预编译
连接数据库的url是否指定了需要预编译，比如：jdbc:mysql://127.0.0.1:3306/user?useServerPrepStmts=true，useServerPrepStmts=true是非常非常重要的参数。如果不配置PreparedStatement 实际是个假的 PreparedStatement
当手动调用prepareStatement.close()时PrepareStatement对象只会将关闭状态置为关闭，并不会向mysql发送关闭请求，prepareStatement对象会被缓存起来，等下次使用的时候直接从缓存中取出来使用。没有开启缓存，则会向mysql发送closeStmt的请求。和mybatis的一级缓存很像？

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.

最后更新于1年前