Configuration example of JDBC connections

This topic provides a Java Database Connectivity (JDBC) connection example.

When you use JDBC to connect to a database, you need to configure the relevant parameters properly to achieve the best performance of the database. This topic provides some recommended configurations for these parameters.

A JDBC connection example is as follows:

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

This connection example involves the following parameters:

• rewriteBatchedStatements: We recommend that you set the value to TRUE.

  • By default, the JDBC driver of OceanBase Database disregards the executeBatch() statement. The driver splits a group of SQL statements to be executed in a batch, and sends them to the database one by one. In this case, a batch insert operation is actually a bunch of single insert operations, resulting in low performance. To implement real batch insertion, you must set this parameter to TRUE, so that the driver can execute SQL statements in batches. You can use the addBatch method to combine multiple INSERT statements on the same table into one INSERT statement that contains multiple values to improve the performance of batch insert operations.

  • You must use the prepareStatement statement to prepare each INSERT statement and then execute addBatch. Otherwise, the statements cannot be merged for execution.

• allowMultiQueries: We recommend that you set the value to TRUE.

  The JDBC driver allows you to concatenate multiple SQL statements using semicolons (;) in application code and send them as one SQL statement to the server.

• useLocalSessionState: We recommend that you set the value to TRUE to prevent transactions from frequently sending session variable queries to OceanBase Database.

  Session variables mainly include autocommit, read_only, and transaction isolation.

• socketTimeout: the time that the socket waits for the response to an SQL statement when the SQL statement is executed.

• connectTimeout: the time to wait for a connection to be established.

• useCursorFetch: We recommend that you set the value to TRUE.

  For query statements with a large data volume, the database server creates a cursor and distributes data to clients based on the value of the FetchSize parameter. If you set this parameter to TRUE, useServerPrepStms is automatically set to TRUE.

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

  If you set this parameter to TRUE, an SQL statement is executed in the following two steps in the database:

  • Send the SQL text that contains a question mark (?) to the database server for preparation (SQL_audit: request_type=5).

  • Execute the statement based on real values in the database (SQL_audit: request_type=6).

• cachePrepStmts: specifies whether to enable the PS cache on the JDBC driver to avoid repeated preparation on the client side and server side. cachePrepStmts=TRUE applies to scenarios where useServerPrepStms is set to TRUE and batch execution is performed repeatedly for the same SQL statement. Each batch execute operation involves the prepare operation and the setting of executecachePrepStmts=TRUE. This avoids repeated preparations.

• prepStmtCacheSQLLimit: the maximum SQL length allowed for the PS cache. SQL statements with a length exceeding the limit cannot be placed into the PS cache. prepStmtCacheSize: the maximum number of SQL statements that can be stored in the PS cache.

• maxBatchTotalParamsNum: the maximum number of parameters that an SQL statement supports for a batch operation. This parameter equals the number of questions marks (?) allowed for an SQL statement. If the number of parameters exceeds the limit, the batch SQL statement will be split.