準備された声明
プリペアドステートメントパラメータのみが異なる複数の SQL ステートメントをテンプレート化します。SQL ステートメントとパラメータを分離します。これを使用して、SQL ステートメントの次の側面を改善できます。
- Security: パラメータとステートメントが分離されているため、 SQLインジェクション攻撃のリスクを回避できます。
- パフォーマンス: ステートメントは TiDBサーバー上で事前に解析されるため、後続の実行ではパラメータのみが渡され、SQL ステートメント全体の解析、SQL ステートメント文字列の結合、およびネットワーク転送のコストが節約されます。
ほとんどのアプリケーションでは、SQL ステートメントを列挙できます。限られた数の SQL ステートメントを使用して、アプリケーション全体のデータ クエリを完了できます。そのため、プリペアドステートメントを使用するのがベスト プラクティスです。
SQL構文
このセクションでは、プリペアドステートメントを作成、実行、および削除するための SQL 構文について説明します。
プリペアドステートメントを作成する
PREPARE {prepared_statement_name} FROM '{prepared_statement_sql}';
| パラメータ名 | 説明 |
|---|---|
{prepared_statement_name} | プリペアドステートメントの名前 |
{prepared_statement_sql} | プレースホルダとして疑問符が付いたプリペアドステートメントSQL |
詳細についてはPREPARE ステートメント参照してください。
プリペアドステートメントを使用する
プリペアドステートメントは、ユーザー変数のみをパラメーターとして使用できるため、 EXECUTEステートメントがプリペアドステートメントを呼び出す前に、 SETステートメントを使用して変数を設定します。
SET @{parameter_name} = {parameter_value};
EXECUTE {prepared_statement_name} USING @{parameter_name};
| パラメータ名 | 説明 |
|---|---|
{parameter_name} | ユーザー変数名 |
{parameter_value} | ユーザー変数値 |
{prepared_statement_name} | 前処理ステートメントの名前プリペアドステートメントを作成するで定義された名前と同じである必要があります。 |
詳細についてはEXECUTEステートメント参照してください。
プリペアドステートメントを削除する
DEALLOCATE PREPARE {prepared_statement_name};
| パラメータ名 | 説明 |
|---|---|
{prepared_statement_name} | 前処理ステートメントの名前プリペアドステートメントを作成するで定義された名前と同じである必要があります。 |
詳細についてはDEALLOCATEステートメント参照してください。
例
このセクションでは、準備されたステートメントの 2 つの例 ( SELECTデータとINSERTデータ) について説明します。
SELECT例
たとえば、 bookshopアプリケーションのうちid = 1を含む本をクエリする必要があります。
- SQL
- Java
PREPARE `books_query` FROM 'SELECT * FROM `books` WHERE `id` = ?';
実行結果:
Query OK, 0 rows affected (0.01 sec)
SET @id = 1;
実行結果:
Query OK, 0 rows affected (0.04 sec)
EXECUTE `books_query` USING @id;
実行結果:
+---------+---------------------------------+--------+---------------------+-------+--------+
| id | title | type | published_at | stock | price |
+---------+---------------------------------+--------+---------------------+-------+--------+
| 1 | The Adventures of Pierce Wehner | Comics | 1904-06-06 20:46:25 | 586 | 411.66 |
+---------+---------------------------------+--------+---------------------+-------+--------+
1 row in set (0.05 sec)
// ds is an entity of com.mysql.cj.jdbc.MysqlDataSource
try (Connection connection = ds.getConnection()) {
PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM `books` WHERE `id` = ?");
preparedStatement.setLong(1, 1);
ResultSet res = preparedStatement.executeQuery();
if(!res.next()) {
System.out.println("No books in the table with id 1");
} else {
// got book's info, which id is 1
System.out.println(res.getLong("id"));
System.out.println(res.getString("title"));
System.out.println(res.getString("type"));
}
} catch (SQLException e) {
e.printStackTrace();
}
INSERT例
books例にすると、 title = TiDB Developer Guide 、 type = Science & Technology 、 stock = 100 、 price = 0.0 、 published_at = NOW() (挿入時の現在時刻) を含む本を挿入する必要があります。 booksテーブルの主キーにAUTO_RANDOM属性を指定する必要がないことに注意してください。データの挿入の詳細については、 データの挿入参照してください。
- SQL
- Java
PREPARE `books_insert` FROM 'INSERT INTO `books` (`title`, `type`, `stock`, `price`, `published_at`) VALUES (?, ?, ?, ?, ?);';
実行結果:
Query OK, 0 rows affected (0.03 sec)
SET @title = 'TiDB Developer Guide';
SET @type = 'Science & Technology';
SET @stock = 100;
SET @price = 0.0;
SET @published_at = NOW();
実行結果:
Query OK, 0 rows affected (0.04 sec)
EXECUTE `books_insert` USING @title, @type, @stock, @price, @published_at;
実行結果:
Query OK, 1 row affected (0.03 sec)
try (Connection connection = ds.getConnection()) {
String sql = "INSERT INTO `books` (`title`, `type`, `stock`, `price`, `published_at`) VALUES (?, ?, ?, ?, ?);";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "TiDB Developer Guide");
preparedStatement.setString(2, "Science & Technology");
preparedStatement.setInt(3, 100);
preparedStatement.setBigDecimal(4, new BigDecimal("0.0"));
preparedStatement.setTimestamp(5, new Timestamp(Calendar.getInstance().getTimeInMillis()));
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
ご覧のとおり、JDBC を使用すると準備済みステートメントのライフ サイクルを制御でき、アプリケーションで準備済みステートメントを手動で作成、使用、または削除する必要がありません。ただし、TiDB は MySQL と互換性があるため、クライアント側で MySQL JDBCDriverを使用するためのデフォルト構成では、サーバー側のプリペアドステートメントオプションを有効にするのではなく、クライアント側のプリペアドステートメントを使用することに注意してください。
次の構成は、JDBC で TiDB サーバー側の準備済みステートメントを使用するのに役立ちます。
| パラメータ | 手段 | 推奨シナリオ | 推奨コンフィグレーション |
|---|---|---|---|
useServerPrepStmts | サーバー側を使用して準備済みステートメントを有効にするかどうか | プリペアドステートメントを複数回使用する必要がある場合 | true |
cachePrepStmts | クライアントが準備されたステートメントをキャッシュするかどうか | useServerPrepStmts=true | true |
prepStmtCacheSqlLimit | プリペアドステートメントの最大サイズ（デフォルトでは 256 文字） | プリペアドステートメントが256文字を超える場合 | プリペアドステートメントの実際のサイズに応じて構成されます |
prepStmtCacheSize | 準備されたステートメントの最大数（デフォルトでは 25） | 準備されたステートメントの数が25を超える場合 | 準備されたステートメントの実際の数に応じて構成されます |
以下は、JDBC 接続文字列構成の一般的なシナリオです。ホスト: 127.0.0.1 、ポート: 4000 、ユーザー名: root 、パスワード: null、デフォルト データベース: test :
jdbc:mysql://127.0.0.1:4000/test?user=root&useConfigs=maxPerformance&useServerPrepStmts=true&prepStmtCacheSqlLimit=2048&prepStmtCacheSize=256&rewriteBatchedStatements=true&allowMultiQueries=true
データを挿入するときに他の JDBC パラメータを変更する必要がある場合は、第行を挿入する章も参照してください。
Javaでの完全な例については、以下を参照してください。
ヘルプが必要ですか?
不和またはスラック 、またはサポートチケットを送信するについてコミュニティに質問してください。