JDBC configuration example

This topic describes JDBC configuration examples.

When you use JDBC to connect to a database, you must configure related parameters to ensure optimal performance of the database. This topic provides recommendations on the configuration of related parameters.

The following code shows an example of a JDBC connection:

conn=jdbc:oceanbase://xxx.xxx.xxx.xxx:2881/test?rewriteBatchedStatements=TRUE&allowMultiQueries=TRUE&useLocalSessionState=TRUE&useUnicode=TRUE&characterEncoding=utf-8&socketTimeout=10000&connectTimeout=30000

In this connection, the following parameters are involved:

• rewriteBatchedStatements: We recommend that you set this parameter to TRUE.

  • By default, the JDBC driver of OceanBase Database ignores the executeBatch() method. The driver splits a batch of SQL statements and sends the statements to the database one by one. In this case, batch insertion is actually single insertion, resulting in low performance. To enable batch insertion, set this parameter to TRUE. Then, the driver can execute SQL statements in batches. Even if you use the addBatch() method to combine multiple insert statements for the same table into one statement with multiple values, you must set this parameter to TRUE. Otherwise, the insert statements cannot be executed in a batch.

  • You must use prepareStatement to prepare each insert statement before you add the statement to the batch. Otherwise, the statements cannot be executed in a batch.

• allowMultiQueries: set to TRUE.

  The JDBC driver allows you to concatenate multiple SQL statements with semicolons (;) and send the resulting statement to the server.

• useLocalSessionState: We recommend that you set this parameter to TRUE to avoid frequent session variable queries to the OceanBase database.

  Session variables include autocommit, read_only, and transaction isolation.

• socketTimeout: The time during which the socket waits for an SQL response.

• connectTimeout: The time during which the connection is established.

• useCursorFetch: set to TRUE.

  For a query statement that involves a large amount of data, the database server establishes a cursor and sends data to the client based on the fetch size. If you set this parameter to TRUE, useServerPrepStms is automatically set to TRUE.

• useServerPrepStms: specifies whether to use the PS protocol to send SQL statements to the database server.

  If this parameter is set to TRUE, the SQL statements are executed in two steps in the database:

  • The SQL statement that contains ? is sent to the database server for preparation (SQL_audit: request_type=5).

  • The prepared SQL statement is executed using real values (SQL_audit: request_type=6).

• cachePrepStmts: Specifies whether to enable the PS cache of the JDBC driver to cache prepared statements. This way, the driver can avoid repeated preparation of statements on the client and server sides. If you set cachePrepStmts to TRUE and useServerPrepStms to TRUE, the driver avoids repeated preparation of the same SQL statement during batch execution. In each batch execution, the driver prepares the statement and uses the PS cache. If you do not want the driver to use the PS cache, set this parameter to FALSE.

• prepStmtCacheSQLLimit: the maximum length of an SQL statement that can be cached in the PS cache. SQL statements longer than this limit cannot be cached. prepStmtCacheSize: the maximum number of SQL statements that can be cached in the PS cache.

• maxBatchTotalParamsNum: the maximum number of parameters supported by one batch SQL statement. In other words, this parameter specifies the maximum number of ? that are allowed in one batch SQL statement. If the number of parameters exceeds the limit, the batch SQL statement is split.