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.
- 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.
- Basic information about the prepared statement protocol
- 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();