All Products
Search
Document Center

Instructions

Last Updated: Aug 17, 2020

Introduction

Distributed Relational Database Service (DRDS) provides support for server-side prepared statements. This support uses the efficient client/server binary protocol. Using prepared statements with placeholders for parameter values has the following benefits:

  • Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical statements, with only changes to variable values in clauses of prepared statements.
  • Protection against SQL injection attacks.

Descriptions

  • Supported syntax
  • Supported SQL statements
    • All data manipulation language (DML) statements are allowed in DRDS prepared statements, including SELECT, UPDATE, DELETE, and INSERT.
  • Non-supported SQL statements:
    • Other SQL statements than DML statements are not allowed in DRDS prepared statements, for example, SHOW and SET.
  • Prepared statements cannot be used in the MySQL command lines.
    • For example, the following statements are not supported: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 prepared statements in Java

  • To use prepared statements on a Java client, you must forcibly add the useServerPrepStmts=true parameter to the URL string. If you do not specify this parameter, PreparedStatement will perform a normal query by default.
  • For example, jdbc:mysql:// xxxxxx:3306/xxxxxx? useServerPrepStmts=true

Example in Java:

  1. Class.forName("com.mysql.jdbc.Driver");
  2. Connection connection = DriverManager.getConnection("jdbc:mysql://xxxxxx:3306/xxxxxx? useServerPrepStmts=true", "xxxxx", "xxxxx");
  3. String sql = "insert into batch values(?,?)" ;
  4. PreparedStatement preparedStatement = connection.prepareStatement(sql);
  5. preparedStatement.setInt(1, 0);
  6. preparedStatement.setString(2, "corona-db");
  7. preparedStatement.executeUpdate();