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 to 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 to the URL. The attributes are described as follows:
The
hamodeattribute specifies the high-availability mode. The value can beloadbalance.The
[username/password]attribute specifies the username and password for uniquely identifying the database to which the application is connected.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
This topic describes the optional URL parameters supported by OceanBase Connector/J.
OceanBase Connector/J specific parameters
Parameter |
Description |
|---|---|
emulateLocators| Default value:False. Specifies whether the driver should use locators to simulatejava.sql.Blob`. |
|
locatorFetchBufferSize |
Default value: 1048576. Specifies the buffer size to be used when retrieving BLOB data by calling getBinaryInputStream() if 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 the ODP. If this parameter is set to true, you can only configure or query the ODP, but cannot execute business SQL queries. Default value: false. |
| obProxySocket | Specifies whether to enable the rich client feature. Default value: "". |
| enableOb20Checksum | Specifies whether to enable the checksum feature for OceanBase Connector/J requests based on OceanBase Protocol 2.0. Default value: ture. If this parameter is set to ture, the CRC checksum is calculated. If not, the checksum is 0. |
| ocpAccessInterval | Specifies the interval for accessing OCP (in minutes). Default value: 5. |
| httpConnectTimeout | Specifies the timeout value (in milliseconds) for establishing a communication link to the network resource referenced by the specified URLConnection. Default value: 0. |
| httpReadTimeout | Specifies the timeout value (in milliseconds) for reading from the input stream when a URLConnection is established to a resource. Default value: 0. |
| compatibleOjdbcVersion | Specifies the target ojdbc version. Valid values: 6 and 8. If this parameter is set to other values, the default value is used. Default value: 6. |
| useOraclePrepareExecute | Specifies whether to enable the two-in-one protocol for prepared statements. In Oracle mode, the preparedStatement does not communicate with the server before it is executed by using COM_STMT_PREPARE_EXECUTE. Default value: false. In the current version, if useOraclePrepareExecute is set to true, useServerPrepStmts is set to true. In versions earlier than V2.4.5, you must set both parameters to true to enable the two-in-one protocol for prepared statements. |
| compatibleMysqlVersion | Specifies the target mysql-jdbc version. Valid values: 5 and 8. If this parameter is set to other values, the default value is used. Default value: 5. |
| mapDateToTimeStamp | If this parameter is set to true, the Date data type in Oracle mode contains time and date information. If this parameter is set to false, only the date part is displayed. Default value true. |
| obDateTypeOptimization | Specifies whether to use the value from the byte array directly instead of converting it to a string and then truncating it. Default value false. |
| useNewResultSetMetaData | Specifies whether to use metadata compatible with native MySQL JDBC. Default value false. |
| usePieceData | Specifies whether to use the piece 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. If usePieceData is set to true, useOraclePrepareExecute is set to true and useCursorFetch is set to true. |
| useArrayBinding | Specifies whether to use array binding in Oracle mode to reduce network round trips and improve performance. Default value: false. If this parameter is set to true, you can enable array binding, that is, pass multiple parameters as an array. |
| oracleXaPrepareThrowException | If the return value of dbms_xa.xa_prepare is not 0 or 3, an exception is thrown. Default value: false |
| convertNoneNanoSecsToDate | In Oracle mode, specifies whether to convert the parameter value of the setTimestamp interface to the Date type if the value does not contain nanoseconds (nanos). Default value: true |
| oracleUseNumberForSetDouble | In Oracle mode, specifies whether to serialize the type of the setDouble interface to the number type. Default value: false |
| lowercaseRoutinesInMetadata | In Oracle mode, specifies whether to use lowercase for the name of procedures and functions when you obtain their metadata. Default value: true |
| obConvertCallsToBlocks | In Oracle mode, specifies whether to convert {call xxx} statements to anonymous blocks in the begin...end format. Default value: false |
| obIncludeOutOrNullParamTypeInfo | In Oracle mode, 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 where procedures have 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 it in a single executeQuery. For example, insert into ab (i) values (?)with first batch values = 1, second = 2 is rewritten as insert into ab (i) values (1), (2). If a query cannot be rewritten with multiple values, it is rewritten with multiple queries. For example, INSERT INTO TABLE(col1) VALUES (?) ON DUPLICATE KEY UPDATE col2=? with additional values [1,2] and [2,3] is rewritten as 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, useServerPrepStmts is set to false. Default value: false. |
| useServerPrepStmts | Prepares PrepareStatement on the server before execution. Applications that reuse the same query can activate this option, but in most cases, direct commands (text protocol) are used. If rewriteBatchedStatements is set to true, this option is set to false. Default value: false. |
| useBatchMultiSend | Indicates whether the driver can batch send queries. If this option is set to false, queries are sent one by one, waiting for the results before sending the next. If this option is set to true, queries are batch sent based on the value of the useBatchMultiSendNumber option (default value: 100). If the number of queries exceeds the maximum allowed by the max_allowed_packet server variable, queries are sent based on the value of max_allowed_packet, and then the results are read to avoid significant network latency when the client and server are not on the same host. Default value: true. |
| allowLocalInfile | Indicates whether to allow loading data from a file. Default value: false. |
| useMysqlMetadata | databaseMetaData.getDatabaseProductName() returns Oracle or MySQL based on the server type. |
| characterEncoding | The character encoding supported by the MySQL URL option. Default value: utf8. The supported character sets include 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 | The full name of the javax.net.SocketFactory class to use a custom Socket Factory. |
| connectTimeout | The connection timeout in milliseconds. If the timeout is not specified, the value is 0. Default value: 30000. |
| maxReconnects | The maximum number of attempts to reconnect. Default value: 3. |
| socketTimeout | The network Socket timeout (SO_TIMEOUT) in milliseconds. If the value is 0, this timeout is disabled. You can also limit the query time by setting the system variable max_statement_time. Default value: 0 (standard configuration) or 10000 ms. |
| localSocketAddress | The host name or IP address to bind the connected Socket to a local (UNIX domain) Socket. |
| tcpKeepAlive | Whether to set SO_KEEPALIVE for the driver when using a TCP/IP connection. Default value: true. |
| tcpNoDelay | Whether to set SO_TCP_NODELAY (disable Nagle's algorithm) for the driver when using a TCP/IP connection. Default value: true. |
| tcpRcvBuf | The size of the TCP buffer (SO_RCVBUF). The default value is 0, which indicates to use the default value of the platform for this attribute. |
| tcpSndBuf | The size of the TCP buffer (SO_SNDBUF). The default value is 0, which indicates to use the default value of the platform for this attribute. |
TLS parameters
Parameter |
Description |
|---|---|
| useSSL | Whether to use SSL/TLS when connecting. Default value: false. |
| trustServerCertificate | Whether to skip certificate verification when using SSL/TLS. Default value: false. |
| serverSslCert | Allows you to provide the server certificate or the CA certificate of the server in DER format. The server will be added to trustStor, so that self-signed certificates can be trusted. You can use one of the following three methods:
|
| keyStore | The file path of the keyStore file that contains the client private key store and the associated certificate (similar to the Java system property javax.net.ssl.keyStore, but only private key entries are used). Old alias: clientCertificateKeyStoreUrl. |
| keyStorePassword | The password of the client certificate keyStore (similar to the Java system property javax.net.ssl.keyStorePassword). Old alias: clientCertificateKeyStorePassword |
| keyPassword | The password of the private key in the client certificate keyStore. (This is required only when 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). The specified file will be used for trusted root certificates. After this parameter is set, the serverSslCert parameter will be overridden. |
| trustStorePassword | The password of the trusted root certificate file (similar to the Java system property javax.net.ssl.trustStorePassword, old alias: trustCertificateKeyStorePassword). |
| enabledSslProtocolSuites | Forces the use of a specific set of TLS versions (as a comma-separated list) for the TLS/SSL protocol. Example: "TLSv1, TLSv1.1, TLSv1.2" (You can also use the alias enabledSSLProtocolSuites). Default value: Java default value. |
| enabledSslCipherSuites | Forces the use of TLS/SSL ciphers (as a 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 will check the server's identity against the hostname in the server certificate (check the subject alternative name or certificate CN) to prevent man-in-the-middle attacks. This option allows you to disable 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, which means that the Java default type is used. |
| trustStoreType | Specifies the trust store type (JKS/PKCS12). The default value is null, which means that the Java default type is used. |
Performance tuning parameters
Parameter |
Description |
|---|---|
| useLocalSessionState | Controls whether the driver uses cached session states (such as transaction mode, auto-commit state, and current database) to avoid frequent queries to the server for these states. When the parameter value is false, queries are always sent. When the parameter value is true, requests are sent to OBServer only when the session state changes. Default value: true. |
| useLocalTransactionState | Specifies whether the driver uses the transaction states provided by the MySQL protocol to determine whether commit() or rollback() has been sent to the database. Default value: true. Note You cannot modify this parameter in the current version. |
| useOceanBaseProtocolV20 | Specifies whether to enable the OB2.0 protocol. Default value: true. |
| enableFullLinkTrace | Specifies whether to enable full-link tracing. Default value: false. When enableFullLinkTrace is set to true, useOceanBaseProtocolV20 is also forcibly set to true. |
Connection pool parameters
Parameter |
Description |
|---|---|
| pool | Specifies whether to use a connection pool. This option is useful only when you do not use a DataSource object but use only a connection 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 in the connection pool. 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 must always be available in the connection pool, which must 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 has been 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 must be validated for each connection request. Default value: 1000 (milliseconds). |
| maxIdleTime | The maximum time a connection can remain in the pool when it is 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 whether to keep the values of the global variables max_allowed_packet, wait_timeout, autocommit, auto_increment_increment, time_zone, system_time_zoneandtx_isolation unchanged, which allows the connection pool to create new connections more quickly. Default value: false. |
| useResetConnection | If a connection is closed and returned to the connection pool, the connection pool resets the connection status. After you set this option, if the server allows it, prepared statements are deleted, session variables are reset, and user variables are destroyed. This saves memory when variables are frequently used in the application. This option cannot be used with the useServerPrepStmts option. Default value: false. |
| registerJmxPool | Specifies whether to register the JMX monitoring pool. Default value: true. |
Log parameters
Parameter |
Description |
|---|---|
| log | Specifies whether to enable log information. Default value: false. |
| maxQuerySizeToLog | Specifies the maximum number of characters to be displayed in the log. Default value: 1024. |
| slowQueryThresholdNanos | Specifies the threshold for the execution time of queries to be recorded (if defined). Default value: 1024. |
| profileSql | Specifies whether to 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 default character set of the platform). |
| useFractionalSeconds | Specifies whether to process timestamps with subsecond precision. Default value: true. |
| allowMultiQueries | Specifies the SQL string format for executing multiple queries in a single operation. 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 | Specifies whether to compress network communication with the database using gzip. When the network overhead of the database is significant, this can improve performance. Default value: false. |
| tcpAbortiveClose | This option is used in environments where connections are created and closed quickly. Typically, in such environments, it is difficult to create a socket within a short period because all local "temporary" ports are occupied by TCP connections and are in the TCP_WAIT state. Using tcpAbortiveClose solves this issue by resetting the TCP connection (either actively or forcibly closing) instead of performing an orderly shutdown. Use socket.setSoLinger(true,0) for a forced shutdown. |
| tinyInt1isBit | A data type mapping flag that treats MySQL Tiny as BIT (Boolean). Default value: true. |
| yearIsDateType | Specifies whether to treat Year as a date type instead of a number. Default value: true. |
| sessionVariables | A list of <var> = <value> pairs separated by commas for setting session variables upon successful connection establishment. |
| localSocket | If the server allows it, you can connect to the database using a Unix domain socket. The value is the path of the Unix domain socket (i.e., the socket database parameter: select @@ socket). |
| sharedMemory | If the server allows it, you can connect to the database using shared memory. The value is the base name of the shared memory. |
| interactiveClient | The 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() specifies the physical table name. If useOldAliasMetadataBehavior is set, you can obtain the table alias. Default value: false |
| createDatabaseIfNotExist | Specifies whether to create the specified database in the URL if it does not exist. Default value: false |
| serverTimezone | Defines the server time zone. This parameter is used only when the GRE server implementation has different server time zones (preferably the same server time zone). |
| 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, thus avoiding the server from re-parsing the query. Default value: false |
| prepStmtCacheSize | If useServerPrepStmts = true, you can specify the size of the prepared statement cache used by the cachePrepStmts option. Default value: 250. |
| prepStmtCacheSqlLimit | If useServerPrepStmts = true, queries exceeding this threshold are not cached. Default value: 2048. |
| jdbcCompliantTruncation | Truncation errors ("Data in column '%s' of row %d was truncated" and "Value in column '%s' of row %d is out of range") are treated as errors instead of warnings. Default value: true |
| cacheCallableStmts | Enables or disables caching of Callable Statements. Default value: true |
| callableStmtCacheSize | If cacheCallableStmts is enabled, the number of Callable Statements cached by the driver per VM is set. Default value: 150. |
| useBatchMultiSendNumber | When the useBatchMultiSend option is active, specifies the maximum number of queries that can be sent consecutively before reading the 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, different queries are executed. If this option is active, queries are sent through a pipeline (sending all queries and reading all results), which can speed up connection creation. Default value: true |
| enablePacketDebug | The driver saves the most recent 16 MySQL data exchange packets (limited to the first 1000 bytes). When an IOException occurs, the hexadecimal values of these packets are added to the stacktrace. This option does not affect performance, but the driver will use more than 16 KB of memory. Default value: false |
| useBulkStmts | Uses the dedicated 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 | Specifies the default value for automatic commit upon connection initialization. Default value: true |
| galeraAllowedState | Normally, Connection.isValid sends an empty data packet to the server, and the server sends a small response to ensure connectivity. After setting this option, the connector ensures 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 | Uses 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 instead of the actual database type. Default value: false |
| defaultFetchSize | The driver calls setFetchSize(n) on all newly created statements. Default value: 0. |
| blankTableNameMeta | The result set metadata getTableName 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. |
| allowPublicKeyRetrieval | When serverRsaPublicKeyFile is not set, allows clients to retrieve the RSA server public key for sha256_password and caching_sha2_password authentication. 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 but not yet transmitted will be discarded. Default value: false |
| maxRows | The maximum number of rows to return. 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 | Specifies whether to allow 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. The list is separated by commas and is case-sensitive. Default value: null. |
| useInformationSchema | Specifies whether to use INFORMATION_SCHEMA to derive information for "DatabaseMetaData". Default value: false |