Skip to main content

JDBC configuration example

This topic provides a JDBC configuration example. JDBC is an interface for Java database connections. OceanBase provides a JDBC driver to support Java applications in connecting to seekdb. You can download the JDBC driver from OceanBase JDBC Driver.

To obtain the best performance for database connections, you need to configure relevant parameters. This topic recommends some parameter configurations.

Here is 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

The following parameters are involved in the connection:

  • rewriteBatchedStatements: Set this parameter to TRUE.

    • By default, the OceanBase JDBC driver ignores the executeBatch() statement and executes a group of SQL statements in batches as individual statements. In this case, batch insertions are actually single insertions, which result in low performance. To actually execute batch insertions, set this parameter to TRUE. In this case, the driver executes SQL statements in batches. For example, you can use the addBatch() method to combine multiple insert statements on the same table into a single insert statement with multiple values.

    • You must use the prepareStatement() method to prepare each insert statement, and then use the addBatch() method. Otherwise, the statements cannot be executed in batches.

  • allowMultiQueries: Set this parameter to TRUE.

    • The JDBC driver allows application code to concatenate multiple SQL statements with semicolons (;) and send them to the server as a single SQL statement.

  • useLocalSessionState: Set this parameter to TRUE to avoid frequent session variable queries to the seekdb.

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

  • socketTimeout: The time that the socket waits for SQL results.

  • connectTimeout: The time that the connection waits for a connection to be established.

  • useCursorFetch: Set this parameter to TRUE.

    • For queries that involve a large amount of data, the database server creates a cursor and distributes data to the client based on the fetch size. If this parameter is set to TRUE, the useServerPrepStmts parameter is automatically set to TRUE.

  • useServerPrepStmts: Specifies whether to use the PS protocol to send SQL statements to the database server.

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

      • Send the SQL statement that contains ? to the database server for preparation (SQL_audit: request_type=5).
      • Execute the SQL statement with real values in the database (SQL_audit: request_type=6).

  • cachePrepStmts: Specifies whether to enable the PS cache to cache prepared statements to avoid repeated preparation (on the client and server sides). If cachePrepStmts=TRUE, the prepared statements can be cached to avoid repeated preparation when you use useServerPrepStmts=TRUE and execute the same SQL statement in batches. Each batch execution includes preparation and execution. If cachePrepStmts=TRUE, the repeated preparation operations can be avoided.

  • prepStmtCacheSQLLimit: The maximum length of an SQL statement that can be cached in the PS cache. Long SQL statements cannot be cached. prepStmtCacheSize: The maximum number of SQL statements that can be cached in the PS cache.

  • maxBatchTotalParamsNum: The maximum number of parameters that can be supported in a single SQL statement for batch operations (i.e., the number of ? in the batch). If the number of parameters exceeds the limit, the batch SQL statement will be split.