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 complete URL syntax supported is as follows:
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:
hamode: This property indicates the high-availability mode. The optional parameter isloadbalance.[username/password]: This property indicates an optional user name 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.1.0.0:1001/unittests?user=**u**@sys&password=***1**&
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
| Parameter | 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 set up a connection to a OBProxy. If you set the value to true, you cannot execute business SQL queries. You can only configure an OBProxy or query the configuration of an OBProxy. Default value: false. |
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. 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 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 OceanBase Connector/J version. |
Network connection parameters
| Parameter | 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 | Defines 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 10,000. |
| 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, indicating that the platform default value is used for this property. |
| tcpSndBuf | The value of the SO_SNDBUF option of the TCP buffer. The default value is 0, indicating that the platform default value is used for this property. |
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 | Specifies 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 trustStor, and a self-signed certificate is trusted. You can set the certificate in the following three ways: * 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 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 | Specifies 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 | Specifies 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 | Specifies the keyStore type (JKS/PKCS12). The default value is null, indicating that the default type of Java is used. |
| trustStoreType | Specifies the trustStore type (JKS/PKCS12). The default value is null, indicating that the default type of Java is used. |
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 This parameter cannot be modified in the current OceanBase Connector/J version. |
| 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 This parameter cannot be modified in the current OceanBase Connector/J version. |
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, the server can save memory 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 years as dates instead of numbers. 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 ("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 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 database server. |
| 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 resultset metadata getTableName method to always return 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. |