A database URL is a string that is used to connect to OceanBase Database and implement specified features.
OceanBase Connector/J allows you to add additional connection properties to a database URL. The following example shows the complete URL syntax supported by OceanBase Connector/J:
jdbc:oceanbase:hamode://host:port/databasename?[username&password]&[opt1=val1&opt2=val2...]
OceanBase Connector/J allows you to add the following additional connection properties to a database URL: Usage notes:
hamode: This property indicates the high-availability mode. The optional parameter isloadbalance.[username/password]: This property indicates an optional username and password that uniquely identify the database to which the application is connected.[opt1=val1&opt2=val2...]: This property is an additional connection property, which is a URL parameter.
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 of OceanBase Connector/J.
Parameters specific to OceanBase Connector/J
| Parameters | Description |
|---|---|
| supportLobLocator | Specifies whether to enable the LOB locator. Default value: true. |
| useObChecksum | Specifies whether to enable checksum, which is available in OceanBase Protocol V2.0. Default value: true. |
| useOceanBaseProtocolV20 | Specifies whether to use OceanBase Protocol 2.0. Default value: false. |
| complexDataCacheSize | The size of ComplexData Cache. Default value: 50. |
| cacheComplexData | Specifies whether to cache ComplexData. Default value: true. |
| useSqlStringCache | Specifies whether to cache SQL strings on the client. Default value: false. |
| useServerPsStmtChecksum | Specifies whether to use the checksum of prepared statements. Default value: true. |
| connectProxy | Specifies whether to connect to ODP. If ODP is connected, you cannot execute business SQL queries but can only configure ODP or query ODP configurations. Default value: false. |
| obProxySocket | Specifies whether to enable the rich client feature. Default value: "". |
| enableOb20Checksum | Specifies whether to calculate the header checksum and tail checksum for OceanBase Connector/J request packets based on the OB2.0 protocol. The default value is true, which specifies to calculate the CRC checksum. If the value is set to false, the checksum is 0. |
| ocpAccessInterval | The interval for accessing OceanBase Cloud Platform (OCP), in minutes. Default value: 5 |
| httpConnectTimeout | The timeout period for connecting to a network resource over HTTP, in milliseconds. This parameter is used when a communications link to the resource referenced by this URLConnection is opened. Default value: 0. |
| httpReadTimeout | The timeout period for reading a network resource over HTTP when the URLConnection to the resource is established, in milliseconds. The value 0 indicates an infinite timeout period, which means not to specify a timeout period. Default value: 0. |
Basic parameters
| Parameter | Description |
|---|---|
| user | The name of the database user. |
| password | The password of the database user. |
| rewriteBatchedStatements | Specifies whether to rewrite batched statements. Batched statements referenced by batchedStatement of an INSERT query can be rewritten for execution in a single query referenced by executeQuery. For example, insert into ab (i) values (?)with first batch values = 1, second = 2 is rewritten into insert into ab (i) values (1), (2). If a batched statement cannot be rewritten by using multiple values, it will be rewritten by using multiple queries with additional values [1, 2] and [2, 3]. For example, INSERT INTO TABLE(col1) VALUES (?) ON DUPLICATE KEY UPDATE col2=? can be 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. If you set rewriteBatchedStatements to true, useServerPrepStmts is set to false. Default value: false. |
| useServerPrepStmts | Specifies whether to execute PrepareStatement on the server before execution. The applications that repeatedly use the same queries have a value to activate this option, but the general practice is to use a direct command (text protocol). If rewriteBatchedStatements is true, this parameter is set to false. Default value: false. |
| useBatchMultiSend | Specifies whether to enable the driver to send statements in batches. If you set it to false, the driver sends a statement only after the result for the previous one is returned. If you set it to true, the driver sends statements in batches based on the value of useBatchMultiSendNumber (default value: 100). If the number of query statements exceeds the maximum number of queries supported in a data packet, the driver sends query statements based on the value of max_allowed_packet of the server and then reads the results. This avoids high network latency when the client and server are deployed on different hosts. Default value: true. |
| allowLocalInfile | Specifies whether to allow data to be loaded from files. Default value: false. |
| useMysqlMetadata | Specifies whether to use MySQL metadata. The databaseMetaData.getDatabaseProductName() method returns Oracle or MySQL based on the server name. |
| useUnicode | Specifies whether to use the Unicode encodings. Default value: true.Note This parameter cannot be modified in the current version of OceanBase Connector/J. |
Network connection parameters
| Parameters | Description |
|---|---|
| socksProxyHost | The hostname or IP address of the SOCKS proxy server to be connected. Default value: null |
| socksProxyPort | The port number of the SOCKS proxy server. Default value: 1080. |
| socketFactory | The custom socket factory to be used. To use a custom socket factory, set this parameter to the full name of the javax.net.SocketFactory class. |
| connectTimeout | The connection timeout period, in ms. The value 0 specifies to disable timeout. Default value: 30000. |
| socketTimeout | The network socket timeout period (SO_TIMEOUT), in ms. The value 0 specifies to disable socket timeout. You can also set the max_statement_time system variable to limit the query time. Default value: 0 (standard) or 10000. |
| localSocketAddress | The hostname or IP address of the local (UNIX domain) socket to which the connection socket is bound. |
| tcpKeepAlive | Specifies whether the driver needs to set SO_KEEPALIVE if a TCP/IP connection is used. Default value: true. |
| tcpNoDelay | Specifies whether the driver needs to set SO_TCP_NODELAY (to disable the Nagle algorithm) if a TCP/IP connection is used. Default value: true. |
| tcpRcvBuf | The value of the SO_RCVBUF option of the TCP buffer. The default value is 0, which specifies to use the default value of the property on the platform. |
| tcpSndBuf | The value of the SO_SNDBUF option of the TCP buffer. The default value is 0, which specifies to use the default value of the property on the platform. |
TLS parameters
| Parameter | Description |
|---|---|
| useSSL | Specifies whether to use SSL/TLS during forced connections. Default value: false. |
| trustServerCertificate | Specifies whether to skip checking certificates of the server when SSL/TLS is in use. Default value: false. |
| serverSslCert | An SSL certificate of the server. The SSL certificate or CA certificate of the server can be provided in the DER format. Then, the server is added to trustStore, and a self-signed certificate is trusted. You can set the certificate in the following three ways:
|
| keyStore | The path to the keyStore file that contains the private key and the associated certificates of the client. This parameter is similar to the javax.net.ssl.keyStore property. However, make sure to use only the entry for the private key. Old alias: clientCertificateKeyStoreUrl. |
| keyStorePassword | The password for the client certificate keyStore. This parameter is similar to the javax.net.ssl.keyStorePassword property. Old alias: clientCertificateKeyStorePassword. |
| keyPassword | The password for the private key in the client certificate keyStore. This parameter is required only when the password for the private key is different from that for the client certificate keyStore. |
| trustStore | The path to the trustStore file. This parameter is similar to the javax.net.ssl.trustStore property. The old alias is trustCertificateKeyStoreUrl. You can set this parameter to use a specified file as the trusted root certificate. It overrides serverSslCert. |
| trustStorePassword | The password for the trusted root certificate file. This parameter is similar to the javax.net.ssl.trustStorePassword property. Old alias: trustCertificateKeyStorePassword. |
| enabledSslProtocolSuites | The TLS versions of the TLS/SSL protocol that must be used. Separate multiple versions with commas (,), for example: "TLSv1,TLSv1.1,TLSv1.2". The old alias is enabledSSLProtocolSuites. By default, default values of Java are used. |
| enabledSslCipherSuites | A set of TLS/SSL ciphers that must be used. Separate multiple ciphers with commas (,). Example: "TLS_DHE_RSA_WITH_AES_256_GCM_SHA384,TLS_DHE_DSS_WITH_AES_256_GCM_SHA384". By default, JRE ciphers are used. |
| disableSslHostnameVerification | Disables SSL hostname verification. When you use SSL, the driver verifies the hostname (the alternate name or certificate CN) against the server identity in the server certificate to prevent man-in-the-middle attacks. You can set this parameter to disable the verification. When trustServerCertificate is set to default, hostname verification is disabled. |
| keyStoreType | The keyStore type (JKS/PKCS12). The default value is null, which specifies to use the default type of Java. |
| trustStoreType | The trustStore type (JKS/PKCS12). The default value is null, which specifies to use the default type of Java. |
Performance extension parameters
| Parameter | Description |
|---|---|
| useLocalSessionState | Specifies whether the driver uses the internal values for automatic commit (specified by Connection.setAutoCommit()) and transaction isolation (specified by Connection.setTransactionIsolation()) and the transaction status maintained by the reference protocol. Default value: true.Note The current version does not support this parameter. |
| useLocalTransactionState | Specifies whether the driver uses the in-transaction status provided by the MySQL protocol to determine whether commit() or rollback() needs to be sent to the database. Default value: true.Note The current version does not support this parameter. |
| useOceanBaseProtocolV20 | Specifies whether to enable the OB2.0 protocol. This protocol is enabled by default. |
| enableFullLinkTrace | Specifies whether to enable Full-link Trace. This feature is disabled by default. When enableFullLinkTrace is set to true, useOceanBaseProtocolV20 is forcibly set to true. |
Connection pool parameters
| Parameter | Description |
|---|---|
| pool | Specifies whether to use the connection pool. This parameter takes effect only when connection objects are used and data source objects are not used. Default value: false. |
| poolName | The name of the connection pool that identifies threads. Default value: oceanbase-pool- <pool-index>. This value is automatically generated by the system. |
| maxPoolSize | The maximum number of physical connections in the connection pool. Default value: 8. |
| minPoolSize | The minimum number of physical connections that must always remain available in the connection pool. If a connection is deleted because its idle period exceeds maxIdleTime, it will be closed and removed from the pool. minPoolSize must be less than or equal to maxPoolSize. By default, the value you specified for maxPoolSize is used. |
| poolValidMinDelay | The minimum delay (in ms) after which the connection pool will start to verify the status of a connection when this connection is requested. If the connection was recently borrowed, you can set poolValidMinDelay to disable the verification, to avoid unnecessary verification when a connection is frequently reused. 0 specifies to verify the connection status each time a connection is requested. Default value: 1000. |
| maxIdleTime | The maximum period (in seconds) for retaining an inactive connection in the pool. The value must be less than that you specified for @wait_timeout-45s. Default value: 600. Minimum value: 60. |
| staticGlobal | Specifies whether to keep the values of the global variables max_allowed_packet, wait_timeout, autocommit, auto_increment_increment, time_zone, and system_time_zoneandtx_isolation unchanged to enable the connection pool to create connections more efficiently. Default value: false. |
| useResetConnection | Specifies whether to allow the connection pool to reset a connection in the closed() state when the connection is returned to the connection pool. When this parameter is set to true, if the server allows, the prepared command will be deleted, the session variables will be reset, and the user variables will be destroyed. As a result, memory usage of the server can be decreased when the application uses a large number of variables. This parameter cannot be used together with useServerPrepStmts. Default value: false. |
| registerJmxPool | Specifies whether to register a JMX monitoring pool. Default value: true. |
Log parameters
| Parameter | Description |
|---|---|
| log | Specifies whether to enable logging. Default value: false. |
| maxQuerySizeToLog | The maximum number of characters to be displayed in the logs. Default value: 1024. |
| slowQueryThresholdNanos | The threshold for determining whether a query is a slow query. Queries (if defined) whose execution time exceeds this parameter are logged. Default value: 1024. |
| profileSql | Specifies whether to trace the queries and their execution time. Default value: false. |
Other parameters
| Parameter | Description |
|---|---|
| passwordCharacterEncoding | The character set used for password encoding. The character set must be a Java character set, for example, UTF-8. Default value: null, which specifies to use the default character set of the platform. |
| useFractionalSeconds | Specifies whether to allow the use of timestamps with fractional seconds. Default value: true. |
| allowMultiQueries | Specifies whether to allow 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 | Specifies whether to dump queries on exceptions. If this parameter is set to true, an exception is thrown during query execution containing a query string. Default value: false. |
| useCompression | Specifies whether to compress data in the gzip format for network communication with the database. This format can improve the performance when the database network overhead is heavy. Default value: false. |
| tcpAbortiveClose | Specifies whether to reset TCP connections. This parameter applies to scenarios where connections are constantly created and disabled within a short time. In these scenarios, no socket can be created in a short time, because all local "temporary" ports are exhausted by the TCP connections and are in the TCP_WAIT state. In this case, you can set tcpAbortiveClose to reset (actively or forcibly close) the TCP connections instead of closing them in sequence. You can call socket.setSoLinger(true,0) to perform an abortive close. |
| tinyInt1isBit | The data type mapping tag that specifies whether to treat MySQL Tiny as BIT (Boolean). Default value: true. |
| yearIsDateType | Specifies whether to treat Year as a date instead of a number. Default value: true. |
| sessionVariables | The <var> = <value> pair specified when a connection was created. Separate multiple session variables with commas (,). |
| localSocket | The UNIX domain socket. If the server allows, you can connect to the database by using the UNIX domain socket. The value is the path to the UNIX domain socket, which is specified by the socket database parameter: select @@ socket. |
| sharedMemory | The shared memory. If the server allows, you can connect to the database by using the shared memory. The value is the base name of the shared memory. |
| interactiveClient | Specifies whether to enable session timeout. Session timeout is defined by the thewait_timeoutserver variable. When interactiveClient is set to true, the server uses the interactive_timeoutserver variable. Default value: false. |
| useOldAliasMetadataBehavior | Specifies whether to use old aliases. The ResultSetMetaData.getTableName() method returns the names of physical tables. If useOldAliasMetadataBehavior is set to true, table alias can be obtained. Default value: false. |
| createDatabaseIfNotExist | Specifies whether to create the specified database (if not existent) in the URL. Default value: false. |
| serverTimezone | The server time zone. This parameter is required only when the GRE server supports different server time zones (the same server time zone is preferred). |
| cachePrepStmts | Specifies whether to cache the prepared statements. If useServerPrepStmts = true, commands that are composed of the prepared statements are cached in the LRU cache to avoid re-preparing the commands. The next time you execute a prepared statement that is cached in the LRU cache, the prepared identifiers and parameters, if any, will be sent to the server. With this option, the server does not need to re-parse the statement. Default value: true. |
| prepStmtCacheSize | The maximum number of statements that can be cached. When useServerPrepStmts = true, you can set the size of the prepared statement cache size used by the option cachePrepStmts. Default value: 250. |
| prepStmtCacheSqlLimit | The maximum number of prepared statements that can be cached. If useServerPrepStmts = true, prepared statements beyond this threshold will not be cached. Default value: 2048. |
| jdbcCompliantTruncation | Specifies whether to treat truncation issues such as "Data in Column '%' is truncated after Row %" and "The value of Column '%' at Row % is out of range" as errors instead of alerts. Default value: true. |
| cacheCallableStmts | Specifies whether to enable caching Callable Statements. Default value: true. |
| callableStmtCacheSize | The maximum number of callable statements that OceanBase Connector/J can cache for each virtual machine (VM) if cacheCallableStmts is set to true. Default value: 150. |
| useBatchMultiSendNumber | When useBatchMultiSend is set to true, you can set this parameter to specify the maximum number of queries that can be sent continuously before results are read. Default value: 100. |
| connectionAttributes | The client information sent to the server in the format of key-value pairs, for example, connectionAttributes = key1:value1,key2:value2. This parameter takes effect when performance_schema is enabled. You can find the information in the performance_schema.session_connect_attrs and performance_schema.session_account_connect_attrs tables on the server. |
| usePipelineAuth | Specifies whether to use the pipeline to send query statements. During the connection, different queries will be executed. If this parameter is set to true, all query statements will be sent and all results will be read through the pipeline so that the connection can be created more efficiently. Default value: true. |
| enablePacketDebug | Specifies whether to enable packet debugging. The driver saves the last 16 MySQL packets (the first 1000 bytes for each packet). When an I/O exception occurs, hexadecimal values of the packets will be added to stacktrace. This parameter has no impact on performance, but the driver occupies more than 16 KB of memory. Default value: false. |
| useBulkStmts | Specifies whether to enable bulk insertion. We recommend that you use the COM_STMT_BULK_EXECUTE protocol for bulk insertion. This parameter is not applicable to batch processing of Statement.RETURN_GENERATED_KEYS and streams. Default value: false. |
| autocommit | Specifies whether to enable auto-commit when the connection is initialized. Default value: true. |
| galeraAllowedState | Specifies the allowed Galera server states. Separate multiple values with commas (,). Usually, Connection.isValid sends an empty packet to the server, and the server returns a small response packet to ensure connectivity. After you set this parameter, the connector ensures that the value of the Galera server state wsrep_local_state matches the allowed states. For example, if the value is "4,5", "4" is used. Default value: empty |
| includeInnodbStatusInDeadlockExceptions | Specifies whether to add the result of SHOW ENGINE INNODB STATUS to exception trace when a deadlock exception occurs. Default value: false. |
| includeThreadDumpInDeadlockExceptions | Specifies whether to add a thread dump to the exception trace when a deadlock exception occurs. Default value: false. |
| useReadAheadInput | Specifies whether to use buffered inputSteam to read available socket data. Default value: true. |
| servicePrincipalName | The service principal name (SPN) used for Generic Security Service Application Program Interface (GSSAPI) identity authentication. This value is used instead of the name defined for the user account on the OBServer node. |
| useMysqlMetadata | Specifies whether to force DatabaseMetadata.getDatabaseProductName() to return MySQL as the database type instead of the actual database type. Default value: false. |
| defaultFetchSize | The number of rows fetched each time. The driver calls setFetchSize(n) on all created statements. Default value: 0. |
| blankTableNameMeta | Specifies whether to enable the result set metadata getTableName method to always return a blank. This parameter is provided for compatibility with Oracle databases. Default value: false. |
| serverRsaPublicKeyFile | The path to the public key file of the RSA server for sha256_password and caching_sha2_password authentication. |
| allowPublicKeyRetrieval | Specifies whether to allow the client to retrieve the public key of the RSA server (for sha256_password and caching_sha2_password authentication) when serverRsaPublicKeyFile is not specified. Default value: false. |
| tlsSocketType | The type of the org.oceanbase.jdbc.tls.TlsSocketPlugin plug-in to be used. The plug-in must exist in classpath. |
| credentialType | The type of the credential plug-in to be used. The plug-in must exist in classpath. |
| trackSchema | Specifies whether session_track_schema can be disabled when the server has the CLIENT_SESSION_TRACK feature. Default value: true. |
| clobberStreamingResults | If another query is executed before all data is read from the server, the streaming result set will be automatically closed and the data being transmitted from the server in streaming mode will be discarded. Default value: false. |
| maxRows | The maximum number of rows returned. Default value: 0, which specifies to return all rows. |
| zeroDateTimeBehavior | The method for processing an invalid date in MySQL mode. Valid values: convertToNull, exception, and round, namely, ZERO_DATETIME_CONVERT_TO_NULL = "convertToNull";ZERO_DATETIME_EXCEPTION = "exception";ZERO_DATETIME_ROUND = "round";. Default value: ZERO_DATETIME_EXCEPTION. |
| allowNanAndInf | Specifies whether NaN or +/- INF is allowed in PreparedStatement.setDouble(). Default value: false. |
| defaultConnectionAttributesBanList | The list of connection attributes that are not sent to the server when sendConnectionAttributes is set to true. The connection attributes are separated with commas (,) and case-sensitive. Default value: null |