Database URL|V2.4.17|OceanBase Connector/J

A database URL is a string used to connect to OceanBase Database and implement the specified features.

OceanBase Connector/J allows you to add additional connection attributes in the URL. The complete URL syntax is as follows:

jdbc:oceanbase:hamode://host:port/databasename?[username&password]&[opt1=val1&opt2=val2...]

OceanBase Connector/J allows you to add additional connection attributes in the URL. The attributes are described as follows:

The hamode attribute specifies the high availability mode. The optional value is loadbalance.
The [username/password] attribute specifies the username and password of the database to which the application connects. This attribute is optional.
The [opt1=val1&opt2=val2...] attribute specifies additional connection attributes, that is, URL parameters.

Here is an example:

jdbc:oceanbase://10.XXX.XXX.XXX:1001/unittests?user=**u**@sys&password=******&
pool=false&useBulkStmts=true&rewriteBatchedStatements=false&useServerPrepStmts=true

Fields in a JDBC URL

A JDBC URL consists of the following fields. The following table describes these fields.

Field	Description	Notes
`jdbc:oceanbase:`	Protocol prefix	A fixed value that indicates that OceanBase Connector/J is used to connect to OceanBase Database.
`hamode`	High-availability mode	Optional. Valid value: `loadbalance`, which indicates that load balancing is enabled. If this parameter is omitted, direct connection is used.
`host`	Host address	The IP address or host name of an OBServer node or an ODP node.
`port`	Port number	The port number of an OBServer node or an ODP node. The default value is 2881 for direct connection to an OBServer node and 2883 for connection through an ODP node.
`databasename`	Database name	The name of the database (MySQL mode) or schema (Oracle mode) to connect to.
`username`	Username	Optional. You can specify the username in the URL by using `user=` or pass it to `getConnection(url, user, password)`. In MySQL mode, you can also specify the username in the `user@tenant` format.
`password`	Password	Optional. You can specify the password in the URL by using `password=` or pass it to `getConnection`.
`opt1=val1&...`	URL parameters	Additional connection attributes used to configure timeouts, SSL, connection pools, and so on. For more information, see the parameter tables below.

This topic describes the optional URL parameters of OceanBase Connector/J.

Parameters specific to OceanBase Connector/J

Parameter	Description
emulateLocators`\| Default value:`False`. Specifies whether the driver should emulate`java.sql.Blob` using locators.
`locatorFetchBufferSize`	Default value: `1048576`. Specifies the buffer size to use when fetching BLOB data with `getBinaryInputStream()` when `emulateLocators` is set to true.
supportLobLocator	Specifies whether to enable LOB locators. Default value: `true`.
useObChecksum	Specifies whether to enable checksums for OceanBase Protocol 2.0. Default value: `true`.
useOceanBaseProtocolV20	Specifies whether to use OceanBase Protocol 2.0. Default value: `true`.
complexDataCacheSize	Specifies the size of the ComplexData cache. Default value: 50.
cacheComplexData	Specifies whether to cache `ComplexData`. Default value: `true`.
useSqlStringCache	Specifies whether to cache SQLString on the client. Default value: `false`.
useServerPsStmtChecksum	Specifies whether to use checksums for prepared statements. Default value: `true`.
connectProxy	Specifies whether to connect to ODP. When set to true, you can only configure or query ODP, but cannot execute business SQL queries. Default value: `false`.
obProxySocket	Specifies whether to enable the rich client feature. Default value: "".
enableOb20Checksum	Based on the OB2.0 protocol, controls the header and tail checksums of OceanBase Connector/J request packets. Default value: `ture`, indicating CRC checksums; otherwise, the checksum is 0.
ocpAccessInterval	Specifies the interval (in minutes) for accessing OCP. Default value: 5.
httpConnectTimeout	Specifies the timeout value (in milliseconds) for establishing a communication link to the network resource referenced by this URLConnection. Default value: 0.
httpReadTimeout	Specifies the timeout (in milliseconds) for reading from the input stream when a URLConnection is established to the resource. Default value: 0.
compatibleOjdbcVersion	Specifies the target ojdbc version to be compatible with. Valid values: 6 and 8. If other integer values are specified, no error is returned and the default value is used. Default value: 6.
useOraclePrepareExecute	Specifies whether to enable the combined prepare and execute protocol for Oracle mode. In Oracle mode, the `preparedStatement` does not communicate with the server before using `COM_STMT_PREPARE_EXECUTE`. Default value: `false`. In the current version, when `useOraclePrepareExecute` is set to `true`, `useServerPrepStmts` is set to `true`. In versions earlier than V2.4.5, both parameters must be set to enable the combined prepare and execute protocol.
compatibleMysqlVersion	Specifies the target mysql-jdbc version to be compatible with. Valid values: 5 and 8. If other integer values are specified, no error is returned and the default value is used. Default value: 5.
mapDateToTimeStamp	If set to `true`, the Date data type in Oracle mode includes time, minute, and second information; if set to `false`, only the date part is displayed. Default value `true`.
obDateTypeOptimization	Specifies whether to retrieve values from a Byte array instead of converting them to strings and then truncating them for the `getDate` method. Default value `false`.
useNewResultSetMetaData	Specifies whether to use native MySQL JDBC metadata. Default value `false`.
usePieceData	Specifies whether to use the sharding data transmission mode. Default value: false. In Oracle mode, the `COM_STMT_SEND_PIECE_DATA` protocol is used to set the `InputStream` and `Reader` parameters. When `usePieceData` is set to true, `useOraclePrepareExecute` is set to true and `useCursorFetch` is changed to true.
useArrayBinding	Specifies whether to use array binding in Oracle mode to reduce network round trips and improve performance. Default value: false. When this parameter is set to true, array binding is enabled, allowing multiple parameters to be passed as an array.
oracleXaPrepareThrowException	If the return value of `dbms_xa.xa_prepare` is neither 0 nor 3, an exception is thrown. Default value: `false`
convertNoneNanoSecsToDate	In Oracle mode, specifies whether to convert the `setTimestamp` interface parameter value without nanoseconds (nanos) to the Date type. Default value: `true`
oracleUseNumberForSetDouble	In an Oracle tenant, specifies whether to serialize the type of `setDouble` as number. Default value: `false`
lowercaseRoutinesInMetadata	In an Oracle tenant, specifies whether to allow the name of a procedure or function to be in lowercase when retrieving metadata. Default value: `true`
obConvertCallsToBlocks	In an Oracle tenant, specifies whether to convert {call xxx} statements to anonymous blocks in the begin...end format. Default value: `false`
obIncludeOutOrNullParamTypeInfo	In an Oracle tenant, specifies whether to send the data types of out and null parameters in a procedure to the OBServer. This is typically used to handle scenarios with procedures of the same name. Default value: `false`

Basic parameters

Parameter	Description
user	The name of the database user.
password	The password of the database user.
rewriteBatchedStatements	For insert queries, rewrite `batchedStatement` to execute in a single `executeQuery`. For example, `insert into ab (i) values (?)with first batch values = 1, second = 2` is rewritten to `insert into ab (i) values (1), (2)`. If a query cannot be rewritten with "multi-value" syntax, it will be rewritten as multiple queries. For example, `INSERT INTO TABLE(col1) VALUES (?) ON DUPLICATE KEY UPDATE col2=?` with additional values [1,2] and [2,3] will be rewritten to `INSERT INTO TABLE(col1) VALUES (1) ON DUPLICATE KEY UPDATE col2=2;INSERT INTO TABLE(col1) VALUES (3) ON DUPLICATE KEY UPDATE col2=4`. When `rewriteBatchedStatements` is enabled, the `useServerPrepStmts` option is set to `false`. Default value: `false`.
useServerPrepStmts	Prepare `PrepareStatement` on the server side before execution. Applications that reuse the same query should enable this option, but typically, direct commands (text protocol) are used. If `rewriteBatchedStatements` is set to `true`, this option will be set to `false`. Default value: `false`.
useBatchMultiSend	Allows the driver to batch send queries. If set to `false`, queries will be sent one at a time, waiting for results before sending the next. If set to `true`, queries will be batched based on the value of the `useBatchMultiSendNumber` option (default is `100`). If the number of queries exceeds what can be sent in a single packet, they will be sent based on the `max_allowed_packet` server variable, and results will be read afterward, avoiding significant network latency when the client and server are on different hosts. Default value: `true`.
allowLocalInfile	Allows loading data from a file. Default value: `false`.
useMysqlMetadata	Returns "Oracle" or "MySQL" based on the server type for `databaseMetaData.getDatabaseProductName()`.
characterEncoding	The character encoding supported by the MySQL URL option. Default value: `utf8`. Supports the Hong Kong character set `HKSCS/HKSCS31`.

Network connection parameters

Parameter	Description
socksProxyHost	The name or IP address of the `SOCKS` host to connect to. Default value: `null`
socksProxyPort	The port of the `SOCKS` server. Default value: 1080.
socketFactory	If you want to use a custom Socket Factory, set it to the full name of the `javax.net.SocketFactory` class.
connectTimeout	The connection timeout in milliseconds. If no timeout is specified, the value is zero. Default value: 30000.
maxReconnects	Default value: `3`. The maximum number of retries when `autoReconnect` is true.
socketTimeout	Defines the network Socket timeout (`SO_TIMEOUT`) in milliseconds. A value of 0 disables this timeout. You can also limit query time by setting the system variable `max_statement_time`. Default value: 0 (standard configuration) or 10000 ms.
localSocketAddress	The hostname or IP address to bind the connection Socket to the local (UNIX domain) Socket.
tcpKeepAlive	If using a TCP/IP connection, whether the driver should set `SO_KEEPALIVE`. Default value: `true`.
tcpNoDelay	If using a TCP/IP connection, whether the driver should set `SO_TCP_NODELAY` (disable Nagle's algorithm). Default value: `true`.
tcpRcvBuf	Sets the size of the TCP receive buffer (`SO_RCVBUF`). Default value is 0, which means the platform's default value is used.
tcpSndBuf	Sets the size of the TCP send buffer (`SO_SNDBUF`). Default value is 0, which means the platform's default value is used.

TLS parameters

Parameter	Description
useSSL	Whether to use SSL/TLS for forced connections. Default value: `false`.
trustServerCertificate	Whether to trust the server certificate when using SSL/TLS. Default value: `false`.
serverSslCert	Allows providing the server's certificate or CA certificate in DER format. This server will be added to `trustStor`, allowing trust in self-signed certificates. You can use any of the following three methods: serverSslCert=/path/to/cert.pem (full path to certificate) serverSslCert=classpath:relative/cert.pem (relative to current classpath) as verbatim DER-encoded certificate string "------BEGIN CERTIFICATE-----"
keyStore	The file path of the keyStore file containing the client's private key store and associated certificate (similar to the Java system property `javax.net.ssl.keyStore`, but ensuring only entries with private keys are used). Old alias: `clientCertificateKeyStoreUrl`.
keyStorePassword	The password for the client certificate keyStore (similar to the Java system property `javax.net.ssl.keyStorePassword`). Old alias: `clientCertificateKeyStorePassword`
keyPassword	The password for the private key in the client certificate keyStore. (Only required if the private key password is different from the keyStore password.)
trustStore	The file path of the trustStore file (similar to the Java system property `javax.net.ssl.trustStore`, old alias: `trustCertificateKeyStoreUrl`) specifying the file for trusted root certificates. After setting, it will override `serverSslCert`.
trustStorePassword	The password for the trusted root certificate file (similar to the Java system property `javax.net.ssl.trustStorePassword`, old alias: `trustCertificateKeyStorePassword`).
enabledSslProtocolSuites	Forces the TLS/SSL protocol to a specific set of TLS versions (comma-separated list). Example: "TLSv1, TLSv1.1, TLSv1.2" (You can also use the alias `enabledSSLProtocolSuites`). Default value: Java default.
enabledSslCipherSuites	Forces the use of TLS/SSL ciphers (comma-separated list). Example: " `TLS_DHE_RSA_WITH_AES_256_GCM_SHA384, TLS_DHE_DSS_WITH_AES_256_GCM_SHA384`". Default value: Use JRE ciphers.
disableSslHostnameVerification	When using SSL, the driver checks the server's identity against the hostname in the server certificate (checking SAN or certificate CN) to prevent man-in-the-middle attacks. This option allows disabling this verification. Hostname verification is disabled when the `trustServerCertificate` option is set to `default`.
keyStoreType	Specifies the key store type (JKS/PKCS12). The default value is `null`, indicating the use of the Java default type.
trustStoreType	Specifies the trust store type (JKS/PKCS12). The default value is `null`, indicating the use of the Java default type.

Performance scaling parameters

Parameter	Description
useLocalSessionState	Controls whether the driver uses locally cached session state (such as transaction mode, auto-commit status, current database, etc.) to avoid frequently sending queries to the server to retrieve these states. When the parameter value is False, queries are always sent. When the value is True, requests are only sent to the OBServer if the session state has changed. Default value: `true`.
useLocalTransactionState	Whether the driver uses the transaction state provided by the MySQL protocol to determine if `commit()` or `rollback()` has been sent to the database. Default value: `true`. Note This parameter cannot be modified in the current version.
useOceanBaseProtocolV20	Whether to enable the OB2.0 protocol, which is enabled by default.
enableFullLinkTrace	Whether to enable full-link tracing, which is disabled by default. When `enableFullLinkTrace` is set to `true`, `useOceanBaseProtocolV20` is also forcibly modified to `true`.

Connection pool parameters

Parameter	Description
pool	Use a connection pool. This option is useful only when you use connection objects instead of a data source object. Default value: `false`.
poolName	The name of the connection pool for identifying threads. Default value: automatically generated as `oceanbase-pool- <pool-index>`.
maxPoolSize	The maximum number of physical connections that the connection pool can contain. Default value: 8.
minPoolSize	If a connection is deleted because it has been idle for more than `maxIdleTime`, the connection is closed and removed from the pool. `minPoolSize` specifies the minimum number of physical connections that the connection pool must always keep available, which should be less than or equal to `maxPoolSize`. Default value: `maxPoolSize`.
poolValidMinDelay	When a connection is requested, the connection pool validates the connection status. If a connection was recently borrowed, `poolValidMinDelay` allows you to disable this validation to avoid unnecessary validations when connections are frequently reused. A value of 0 indicates that the connection status is validated for every connection request. Default value: 1000 (milliseconds).
maxIdleTime	The maximum time that a connection can remain in the pool when not in use, in seconds. This value must always be less than `@wait_timeout-45s`. Default value: 600 seconds (10 minutes), minimum value: 60 seconds.
staticGlobal	Specifies that the values of the global variables `max_allowed_packet`, `wait_timeout`, `autocommit`, `auto_increment_increment`, `time_zone`, `system_time_zoneandtx_isolation` will not be changed, allowing the connection pool to create new connections more quickly. Default value: `false`.
useResetConnection	When a connection is `closed()` (returned to the connection pool), the connection pool resets the connection status. After this option is set, if the server allows it, prepared commands will be deleted, session variables will be reset, and user variables will be destroyed, saving memory when variables are extensively used in the application. This option cannot be used with the `useServerPrepStmts` option. Default value: `false`.
registerJmxPool	Register the JMX monitoring pool. Default value: `true`.

Log parameters

Parameter	Description
log	Enable log information. Default value: `false`.
maxQuerySizeToLog	Only display a number of characters corresponding to the size of this option in the log. Default value: 1024.
slowQueryThresholdNanos	Record queries that take longer than this value to execute (if defined). Default value: 1024.
profileSql	Log the execution time of queries. Default value: `false`.

Other parameters

Parameter	Description
passwordCharacterEncoding	Specifies the character set for password encoding. The character set value must be a Java character set. For example: UTF-8. Default value: `null` (the platform's default character set).
useFractionalSeconds	Enables handling of timestamps with subsecond precision. Default value: `true`.
allowMultiQueries	Allows executing multiple queries in a single SQL string. For example `insert into ab (i) values (1); insert into ab (i) values (2)`. Default value: `false`.
dumpQueriesOnException	If set to `true`, exceptions during query execution will include the query string. Default value: `false`.
useCompression	Compresses network communication with the database using `gzip`. This can improve performance when database network overhead is significant. Default value: `false`.
tcpAbortiveClose	This option is useful in environments with rapid connection creation and closure. Typically, in such environments, sockets cannot be created quickly because all local "ephemeral" ports are exhausted and in the `TCP_WAIT` state. Using `tcpAbortiveClose` resolves this by resetting the TCP connection (either actively or forcibly) instead of closing it orderly. Use `socket.setSoLinger(true,0)` for forced closure.
tinyInt1isBit	A data type mapping flag that treats MySQL Tiny as BIT (Boolean). Default value: `true`.
yearIsDateType	Treats Year as a date type instead of a number. Default value: `true`.
sessionVariables	A list of `<var> = <value>` pairs for session variables, separated by commas.
localSocket	If allowed by the server, connects to the database using a Unix domain socket. The value is the path to the Unix domain socket (i.e., the socket database parameter: `select @@ socket`).
sharedMemory	If allowed by the server, connects to the database using shared memory. The value is the base name of the shared memory.
interactiveClient	Session timeout is defined by the `thewait_timeoutserver` variable. Setting `interactiveClient` to `true` tells the server to use the `interactive_timeoutserver` variable. Default value: `false`.
useOldAliasMetadataBehavior	Metadata returned by `ResultSetMetaData.getTableName()` provides the physical table name. If `useOldAliasMetadataBehavior` is set, table aliases can be retrieved. Default value: `false`.
createDatabaseIfNotExist	Creates the specified database in the URL if it does not exist. Default value: `false`.
serverTimezone	Defines the server timezone. Only used when the GRE server implementation has different server timezones (preferably the same server timezone).
cachePrepStmts	If `useServerPrepStmts = true`, prepared statements are cached in an LRU cache to avoid re-preparing commands. When the command is used again, the prepared identifier and parameters (if any) are sent to the server, avoiding the need for the server to re-parse the query. Default value: `false`.
prepStmtCacheSize	If `useServerPrepStmts = true`, defines the size of the prepared statement cache used by the `cachePrepStmts` option. Default value: 250.
prepStmtCacheSqlLimit	If `useServerPrepStmts = true`, queries exceeding this threshold will not be cached. Default value: 2048.
jdbcCompliantTruncation	Truncation errors ("Data in column '％' of row ％ was truncated" and "Value in column '％' of row ％ is out of range") are treated as errors rather than warnings. Default value: `true`.
cacheCallableStmts	Enables/disables caching of Callable Statements. Default value: `true`.
callableStmtCacheSize	If `cacheCallableStmts` is enabled, sets the number of Callable Statements cached per VM by the driver. Default value: 150.
useBatchMultiSendNumber	When the `useBatchMultiSend` option is active, sets the maximum number of queries that can be sent consecutively before reading results. Default value: 100.
connectionAttributes	When `performance_schema` is active, allows sending some client information to the server in key-value pairs (e.g., `connectionAttributes = key1: value1, key2: value2`). This information can be retrieved from the `performance_schema.session_connect_attrs` and `performance_schema.session_account_connect_attrs` tables on the server.
usePipelineAuth	During connection, executes different queries. If this option is active, queries are sent via a pipeline (all queries are sent, then all results are read), allowing faster connection creation. Default value: `true`.
enablePacketDebug	The driver will save the most recent 16 MySQL data exchange packets (limited to the first 1000 bytes). When an IOException occurs, the hexadecimal values of these packets will be added to the `stacktrace`. This option has no performance impact, but the driver will use more than 16 KB of memory. Default value: `false`.
useBulkStmts	Use the `COM_STMT_BULK_EXECUTE` protocol for bulk inserts as much as possible. (Does not include `Statement.RETURN_GENERATED_KEYS` and streaming batch processing). Default value: `false`.
autocommit	Sets the default value for autocommit when the connection is initialized. Default value: `true`.
galeraAllowedState	Typically, `Connection.isValid` sends an empty packet to the server, and the server responds with a small response to ensure connectivity. After setting this option, the connector will ensure that the Galera server state `wsrep_local_state` corresponds to the allowed values (separated by commas). For example, "4,5", recommended value is "4". Default value: empty.
includeInnodbStatusInDeadlockExceptions	When a deadlock exception occurs, the `SHOW ENGINE INNODB STATUS` result is added to the exception trace. Default value: `false`.
includeThreadDumpInDeadlockExceptions	When a deadlock exception occurs, the thread dump is added to the exception trace. Default value: `false`.
useReadAheadInput	Use a buffered `inputSteam` to read available socket data. Default value: `true`.
servicePrincipalName	When using GSSAPI authentication, this value is used as the service principal name (SPN) instead of the name defined for the user account on the database server.
useMysqlMetadata	Forces `DatabaseMetadata.getDatabaseProductName()` to return MySQL as the database type instead of the actual database type. Default value: `false`.
defaultFetchSize	The driver will call `setFetchSize(n)` on all newly created Statements. Default value: 0.
blankTableNameMeta	The `getTableName` method of the result set metadata always returns an empty string. This option is mainly for compatibility with Oracle databases. Default value: `false`.
serverRsaPublicKeyFile	Specifies the path to the RSA server public key file used for `sha256_password` and `caching_sha2_password` authentication passwords.
allowPublicKeyRetrieval	When `serverRsaPublicKeyFile` is not set, allows clients to retrieve the RSA server public key (for `sha256_password` and `caching_sha2_password` authentication passwords). Default value: `false`.
tlsSocketType	Specifies the type of TLS `org.oceanbase.jdbc.tls.TlsSocketPlugin` plugin to use. The plugin must exist in the `classpath`.
credentialType	Specifies the type of credential plugin to use. The plugin must exist in the `classpath`.
trackSchema	When the server has the `CLIENT_SESSION_TRACK` feature, allows disabling the `session_track_schema` setting. Default value: `true`.
clobberStreamingResults	If another query is executed before all data is read from the server, this will cause the streaming result set to be automatically closed, and any data being streamed from the server but not yet transmitted will be discarded. Default value: `false`.
maxRows	The maximum number of rows to return. The default value is `0`, which means all rows are returned.
zeroDateTimeBehavior	Specifies how to handle invalid dates in MySQL mode. Valid values are `convertToNull`, `exception`, or `round`. That is, `ZERO_DATETIME_CONVERT_TO_NULL = "convertToNull";ZERO_DATETIME_EXCEPTION = "exception";ZERO_DATETIME_ROUND = "round";` Default value: `ZERO_DATETIME_EXCEPTION`.
allowNanAndInf	Whether to allow the use of NaN or +/- INF values in PreparedStatement.setDouble(). Default value: `false`.
defaultConnectionAttributesBanList	When `sendConnectionAttributes=true`, this parameter controls the list of connection attributes not to send to the server, separated by commas and case-sensitive. Default value: `null`.
useInformationSchema	Whether to use INFORMATION_SCHEMA to derive the information used by "DatabaseMetaData". Default value: `false`.

OceanBase

Customer Stories

Documentation

Database URL