Prepared Statements

A prepared statement templatizes multiple SQL statements in which only parameters are different. It separates the SQL statements from the parameters. You can use it to improve the following aspects of SQL statements:

• Security: Because parameters and statements are separated, the risk of SQL injection attacks is avoided.
• Performance: Because the statement is parsed in advance on the TiDB server, only parameters are passed for subsequent executions, saving the cost of parsing the entire SQL statements, splicing SQL statement strings, and network transmission.

In most applications, SQL statements can be enumerated. You can use a limited number of SQL statements to complete data queries for the entire application. So using a prepared statement is a best practice.

SQL syntax

This section describes the SQL syntax for creating, running and deleting a prepared statement.

Create a prepared statement

PREPARE {prepared_statement_name} FROM '{prepared_statement_sql}';

See PREPARE statement for more information.

Use the prepared statement

A prepared statement can only use user variables as parameters, so use the SET statement to set the variables before the EXECUTE statement can call the prepared statement.

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

See the EXECUTE statement for more information.

Delete the prepared statement

DEALLOCATE PREPARE {prepared_statement_name};

See the DEALLOCATE statement for more information.

Examples

This section describes two examples of prepared statements: SELECT data and INSERT data.

SELECT example

For example, you need to query a book with id = 1 in the bookshop application.

• SQL
• Java

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

Running result:

Query OK, 0 rows affected (0.01 sec)

SET @id = 1;

Running result:

Query OK, 0 rows affected (0.04 sec)

EXECUTE `books_query` USING @id;

Running result:

+---------+---------------------------------+--------+---------------------+-------+--------+
| 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)

// 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();
}

INSERT example

Using the books table as an example, you need to insert a book with title = TiDB Developer Guide, type = Science & Technology, stock = 100, price = 0.0, and published_at = NOW() (current time of insertion). Note that you don’t need to specify the AUTO_RANDOM attribute in the primary key of the books table. For more information about inserting data, see Insert Data.

• SQL
• Java

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

Running result:

Query OK, 0 rows affected (0.03 sec)

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

Running result:

Query OK, 0 rows affected (0.04 sec)

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

Running result:

Query OK, 1 row affected (0.03 sec)

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();
}

As you can see, JDBC helps you control the life cycle of prepared statements and you don’t need to manually create, use, or delete prepared statements in your application. However, note that because TiDB is compatible with MySQL, the default configuration for using MySQL JDBC Driver on the client-side is not to enable the server-side prepared statement option, but to use the client-side prepared statement.

The following configurations help you use the TiDB server-side prepared statements under JDBC:

The following is a typical scenario of JDBC connection string configurations. Host: 127.0.0.1, Port: 4000, User name: root, Password: null, Default database: test:

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

You can also see the insert rows chapter if you need to change other JDBC parameters when inserting data.

For a complete example in Java, see:

• Connect to TiDB with JDBC
• Connect to TiDB with Hibernate
• Connect to TiDB with Spring Boot