Enable and use prepared statements in Java clients and text mode - PolarDB

PolarDB-X supports prepared statements in two modes: binary mode for programmatic access via JDBC and other drivers, and text mode for SQL-level use. Both modes let you define a statement once and execute it repeatedly with different parameter values.

Benefits of prepared statements:

Reduced parsing overhead: The database parses the statement once. Subsequent executions only substitute parameter values, skipping repeated parsing.
SQL injection protection: Parameter values are transmitted separately from the SQL structure, preventing injection attacks.

Binary mode

Binary mode uses the client/server binary protocol and is the standard approach for application development. It supports Java Database Connectivity (JDBC) and other programming language drivers. For more information about prepared statements supported by MySQL, see Prepared statements.

PolarDB-X supports the following binary protocol commands:

Supported statement types:

All Data Manipulation Language (DML) statements: SELECT, UPDATE, DELETE, and INSERT.

Unsupported statement types:

Non-DML statements such as SHOW and SET cannot be used as prepared statements.

Use prepared statements in a Java client

To use binary mode in a Java client, add useServerPrepStmts=true to your JDBC connection URL. Without this parameter, the driver processes prepared statements as regular statements by default.

Example connection URL:

jdbc:mysql://xxxxxx:3306/xxxxxx?useServerPrepStmts=true

Example: insert a row using a prepared statement

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, "polardb-x");
preparedStatement.executeUpdate();

Text mode

Text mode uses SQL syntax. Use the following statements to manage prepared statements directly from a SQL client.

PREPARE

Define a prepared statement and assign it a name:

PREPARE stmt_name FROM preparable_stmt;

stmt_name is case-insensitive.
preparable_stmt must be a single SQL statement.

EXECUTE

Run the prepared statement. If the statement contains ? placeholders, supply values using the USING clause:

EXECUTE stmt_name [USING @var_name [, @var_name] ...];

DEALLOCATE PREPARE

Release the prepared statement and free its resources:

DEALLOCATE PREPARE stmt_name;

Example

mysql> PREPARE stmt2 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|         10 |
+------------+

mysql> DEALLOCATE PREPARE stmt2;