This topic provides a Java Database Connectivity (JDBC) connection example. JDBC is an interface for connecting Java applications to databases. OceanBase AI Database provides a JDBC driver that supports Java applications in connecting to OceanBase AI Database. For more information, see OceanBase JDBC Driver.
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:2881/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 toTRUE.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 toTRUE. When enabled, the driver will execute SQL statements in batches. Specifically, theaddBatchmethod 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
prepareStatementmethod 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 toTRUE.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 toTRUEto avoid frequently sending session variable query SQL statements to OceanBase AI 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 the value of
FetchSize. When this property is set toTRUE, it will automatically setuseServerPrepStmts=TRUE.useServerPrepStmts: 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. SettingcachePrepStmts=TRUEis helpful in scenarios whereuseServerPrepStmts=TRUEis used and the same SQL is repeatedly executed in batches. Each batch execution typically includes preparation, but enablingcachePrepStmts=TRUEcan 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: For batch operations, the maximum number of parameters supported in a single SQL statement (that is, the maximum number of?placeholders in a batch SQL statement). If the number of parameters exceeds this limit, the batch SQL statement will be split.