This topic provides a Java Database Connectivity (JDBC) connection example.
When you use JDBC to connect to a database, you need to configure relevant parameters to achieve the optimal 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
In this connection string, the relevant parameters are described as follows:
rewriteBatchedStatements: We recommend that you set this parameter to TRUE.By default, the OceanBase JDBC driver ignores the executeBatch() statement and sends a batch of SQL statements to the database one by one. In this case, batch inserts are actually executed as individual inserts, leading to lower performance. To enable true batch inserts, set this parameter to TRUE. When enabled, the driver will execute SQL statements in batches. Specifically, the addBatch method can group multiple INSERT statements for the same table into a single INSERT statement with multiple VALUES, significantly improving batch insert performance.
Note that you must use the prepareStatement method to prepare each INSERT statement before adding it to the batch. Otherwise, the statements cannot be merged for execution.
allowMultiQueries: We recommend that you set this parameter to TRUE.The JDBC driver allows you to concatenate multiple SQL statements with semicolons (;) and send them as a single SQL statement to the server.
useLocalSessionState: We recommend that you set this parameter to TRUE to avoid frequently sending session variable query SQL statements to OceanBase Database during transactions.The session variables include autocommit, read_only, and transaction isolation.
socketTimeout: The time that the socket waits for the SQL execution result to return.connectTimeout: The time to wait when establishing a connection.useCursorFetch: We recommend that you set this parameter toTRUE.For queries involving large amounts of data, the database server will create a cursor and distribute data to the client based on
FetchSize. When this property is set toTRUE, it will automatically setuseServerPrepStmts=TRUE.useServerPrepStms: Specifies whether to use the PS protocol to send SQL statements to the database server.If set to
TRUE, the SQL statement is executed in two steps in the database:Send the SQL text containing
?to the database server for preparation (SQL_audit: request_type=5).Execute the prepared SQL with actual values in the database (
SQL_audit: request_type=6).
cachePrepStmts: Specifies whether to enable the PS cache for the JDBC driver to cache prepared statements and avoid repeated preparation on both the client and server sides. Setting cachePrepStmts=TRUE is helpful in scenarios where useServerPrepStms=TRUE is used and the same SQL is repeatedly executed in batches. Each batch execution typically includes preparation, but enabling cachePrepStmts=TRUE can eliminate redundant preparation operations.
prepStmtCacheSQLLimit: The maximum length of an SQL statement that can be cached in the PS cache. An SQL statement longer than this length cannot be cached.prepStmtCacheSize: The maximum number of SQL statements that the PS cache can save.maxBatchTotalParamsNum: The maximum number of parameters supported by a single SQL statement for batch operations (the maximum number of?in a batch SQL statement). If the number of parameters exceeds the limit, the SQL statement will be split.