预处理语句
- SQL 语法
- 例子
  - 查询示例
  - 插入示例

预处理语句

预处理语句是一种将多个仅有参数不同的 SQL 语句进行模板化的语句，它让 SQL 语句与参数进行了分离。可以用它提升 SQL 语句的：

安全性：因为参数和语句已经分离，所以避免了 SQL 注入攻击的风险。
性能：因为语句在 TiDB 端被预先解析，后续执行只需要传递参数，节省了完整 SQL 解析、拼接 SQL 语句字符串以及网络传输的代价。

在大部分的应用程序中，SQL 语句是可以被枚举的，可以使用有限个 SQL 语句来完成整个应用程序的数据查询，所以使用预处理语句是最佳实践之一。

SQL 语法

本节将介绍创建、使用及删除预处理语句的 SQL 语法。

创建预处理语句

PREPARE {prepared_statement_name} FROM '{prepared_statement_sql}';

参数	描述
`{prepared_statement_name}`	预处理语句名称
`{prepared_statement_sql}`	预处理语句 SQL，以英文半角问号做占位符

你可查看 PREPARE 语句获得更多信息。

使用预处理语句

预处理语句仅可使用用户变量作为参数，因此，需先使用 SET 语句设置变量后，供 EXECUTE 语句调用预处理语句。

SET @{parameter_name} = {parameter_value};
EXECUTE {prepared_statement_name} USING @{parameter_name};

参数	描述
`{parameter_name}`	用户参数名
`{parameter_value}`	用户参数值
`{prepared_statement_name}`	预处理语句名称，需和创建预处理语句中定义的名称一致

你可查看 EXECUTE 语句获得更多信息。

删除预处理语句

DEALLOCATE PREPARE {prepared_statement_name};

参数	描述
`{prepared_statement_name}`	预处理语句名称，需和创建预处理语句中定义的名称一致

你可查看 DEALLOCATE 语句获得更多信息。

例子

本节以使用预处理语句，完成查询数据和插入数据两个场景的示例。

查询示例

例如，需要查询 Bookshop 应用中，id 为 1 的书籍信息。

使用 SQL 查询示例：

sql
PREPARE `books_query` FROM 'SELECT * FROM `books` WHERE `id` = ?';

运行结果为： Query OK, 0 rows affected (0.01 sec)

sql
SET @id = 1;

运行结果为： Query OK, 0 rows affected (0.04 sec)

sql
EXECUTE `books_query` USING @id;

运行结果为：

+---------+---------------------------------+--------+---------------------+-------+--------+
| id      | title                           | type   | published_at        | stock | price  |
+---------+---------------------------------+--------+---------------------+-------+--------+
| 1       | The Adventures of Pierce Wehner | Comics | 1904-06-06 20:46:25 |   586 | 411.66 |
+---------+---------------------------------+--------+---------------------+-------+--------+
1 row in set (0.05 sec)

使用 Java 查询示例：

java
// ds is an entity of com.mysql.cj.jdbc.MysqlDataSource
try (Connection connection = ds.getConnection()) {
    PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM `books` WHERE `id` = ?");
    preparedStatement.setLong(1, 1);

    ResultSet res = preparedStatement.executeQuery();
    if(!res.next()) {
        System.out.println("No books in the table with id 1");
    } else {
        // got book's info, which id is 1
        System.out.println(res.getLong("id"));
        System.out.println(res.getString("title"));
        System.out.println(res.getString("type"));
    }
} catch (SQLException e) {
    e.printStackTrace();
}

插入示例

还是使用 books 表为例，需要插入一个 title 为 TiDB Developer Guide, type 为 Science & Technology, stock 为 100, price 为 0.0, published_at 为 插入的当前时间 的书籍信息。需要注意的是，books 表的主键包含 AUTO_RANDOM 属性，无需指定它。如果你对插入数据还不了解，可以在插入数据一节了解更多数据插入的相关信息。

使用 SQL 插入数据示例如下：

sql
PREPARE `books_insert` FROM 'INSERT INTO `books` (`title`, `type`, `stock`, `price`, `published_at`) VALUES (?, ?, ?, ?, ?);';

运行结果为： Query OK, 0 rows affected (0.03 sec)

sql
SET @title = 'TiDB Developer Guide';
SET @type = 'Science & Technology';
SET @stock = 100;
SET @price = 0.0;
SET @published_at = NOW();

运行结果为： Query OK, 0 rows affected (0.04 sec)

sql
EXECUTE `books_insert` USING @title, @type, @stock, @price, @published_at;

运行结果为： Query OK, 1 row affected (0.03 sec)

使用 Java 插入数据示例如下：

java
try (Connection connection = ds.getConnection()) {
    String sql = "INSERT INTO `books` (`title`, `type`, `stock`, `price`, `published_at`) VALUES (?, ?, ?, ?, ?);";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);

    preparedStatement.setString(1, "TiDB Developer Guide");
    preparedStatement.setString(2, "Science & Technology");
    preparedStatement.setInt(3, 100);
    preparedStatement.setBigDecimal(4, new BigDecimal("0.0"));
    preparedStatement.setTimestamp(5, new Timestamp(Calendar.getInstance().getTimeInMillis()));

    preparedStatement.executeUpdate();
} catch (SQLException e) {
    e.printStackTrace();
}

jdbc:mysql://127.0.0.1:4000/test?user=root&useConfigs=maxPerformance&useServerPrepStmts=true&prepStmtCacheSqlLimit=2048&prepStmtCacheSize=256&rewriteBatchedStatements=true&allowMultiQueries=true

你也可以查看插入行一章，来查看是否需要在插入数据场景下更改其他 JDBC 的参数。有关 Java 的完整示例，可参阅： - TiDB 和 Java 的简单 CRUD 应用程序 - 使用 JDBC。 - TiDB 和 Java 的简单 CRUD 应用程序 - 使用 Hibernate。 - Build the TiDB Application using Spring Boot。