This topic describes the prepared statement protocol and its support for prepared statements. This topic also describes how to enable the prepared statement protocol in a Java client.
Overview
Distributed Relational Database Service (PolarDB-X 1.0) allows you to enable the client/server binary protocol to execute server-side prepared
statements. Prepared statements with placeholders for parameter values have the following
benefits:
- Minimized overhead of statement parsing each time a statement is executed. In most cases, database applications process large numbers of near-identical statements in which only a few variable values are different. To execute these near-identical statements in an efficient manner, you need only to change the variable values in a prepared statement.
- Protection against SQL injection attacks.
Description
- Basic information about the prepared statement protocol
- The protocol supports the following commands:
- The protocol supports Java and other programming languages.
- For information about the commands supported by prepared statements in MySQL, see Prepared statements.
- All SQL DML statements can be used as prepared statements, such as SELECT, UPDATE, DELETE, and INSERT statements.
- Non-DML SQL statements cannot be used as prepared statements, such as SHOW and SET statements.
- The following statements cannot be used as prepared statements in a MySQL CLI:
mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> PREPARE stmt2 FROM @s; mysql> SET @a = 6; mysql> SET @b = 8; mysql> EXECUTE stmt2 USING @a, @b;
Enable the prepared statement protocol in a Java client
- If you want to execute prepared statements in your Java client, forcibly add the useServerPrepStmts=true field to the URL for connecting to MySQL. If you do not add this field, a regular query is performed.
- Example: jdbc:mysql://xxxxxx:3306/xxxxxx?useServerPrepStmts=true
Example in Java:
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://xxxxxx:3306/xxxxxx?useServerPrepStmts=true", "xxxxx", "xxxxx");
String sql = "insert into batch values(?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 0);
preparedStatement.setString(2, "corona-db");
preparedStatement.executeUpdate();