Example of configuring a JDBC connection pool

This topic provides an example of configuring a JDBC connection pool.

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.

JDBC connection example:

conn=jdbc:oceanbase://x.x.x.x(ip):xx(port)/xxxx(dbname)?rewriteBatchedStatements=TRUE&allowMultiQueries=TRUE&useLocalSessionState=TRUE&useUnicode=TRUE&characterEncoding=utf-8&socketTimeout=3000000&connectTimeout=60000

This connection example involves the following configuration parameters:

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

  • By default, the JDBC driver of OceanBase Database disregards the executeBatch() statement. It 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 indeed 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 by 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 SQL statements for querying session variables to OceanBase Database.

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

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

• connectTimeout: The time to wait for the connection when the connection is established.

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

  For query statements with a large data volume, the database server establishes a cursor and distributes data to clients according to the value of FetchSize. If this attribute is set 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 you set this parameter to TRUE, SQL statements are 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 to cache PreparedStatment to avoid repeated preparation on the client side and server side. cachePrepStmts=TRUE is applicable to scenarios where useServerPrepStms is set to TRUE and the same SQL statement is repeatedly batch executed. Each batch execute operation contains the prepare operation and the setting of executecachePrepStmts=TRUE. This avoids repeated preparations.

• prepStmtCacheSQLLimit: The maximum SQL length allowed for the PS cache. prepStmtCacheSize: The 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, that is, 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.