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