This topic describes how to configure and use the Java Database Connectivity (JDBC) drivers of OceanBase Database and MySQL: oceanbase-client (also known as OBClient) and mysql-connector-Java.
We recommend that you use oceanbase-client, which is the JDBC driver of OceanBase Database.
If you want to connect to MySQL tenants, you can use the official JDBC driver of MySQL, mysql-connector-Java.
Notice
oceanbase-client is fully compatible with the JDBC driver of MySQL. oceanbase-client automatically identifies whether OceanBase Database runs in MySQL or Oracle mode and supports both modes at the protocol layer.
Only the MySQL mode is supported by mysql-connector-Java.
Use oceanbase-client
You must set the prefix of the connection string to jdbc:oceanbase. All other settings are the same as those in native MySQL.
Notice
In oceanbase-client V1.0.9, the name of the driver class is
com.alipay.oceanbase.obproxy.mysql.jdbc.Driver. In later versions, the name of the driver class iscom.alipay.oceanbase.jdbc.Driver.
Sample code:
String url = "jdbc:oceanbase://xxx.xxx.xxx.xxx:2883/SYS?useUnicode=true&characterEncoding=utf-8"; //Specify the connection string in the format of IP address:ODP port number/Database name.
String username = "SYS@test1#obtest"; // Specify the username in the format of Username@Tenant name#Cluster name.
String password = "test"; // The password.
Connection conn = null;
try {
Class.forName("com.alipay.oceanbase.obproxy.mysql.jdbc.Driver"); // The driver class name.
conn = DriverManager.getConnection(url, username, password);
PreparedStatement ps = conn.prepareStatement("select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;");
ResultSet rs = ps.executeQuery();
rs.next();
System.out.println("sysdate is:" + rs.getString(1));
rs.close();
ps.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != conn) {
conn.close();
}
}
Use mysql-connector-Java
You must set the prefix of the connection string to jdbc:mysql. The name of the driver class is com.mysql.jdbc.Driver.
Sample code:
String url = "jdbc:mysql://xxx.xxx.xxx.xxx:2883/hr?useUnicode=true&characterEncoding=utf-8"; //Specify the connection string in the format of IP address:ODP port number/Database name.
String username = "root@test2#obtest"; // Specify the username in the format of Username@Tenant name#Cluster name.
String password = "test"; // The password.
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver"); // The driver class name.
conn = DriverManager.getConnection(url, username, password);
PreparedStatement ps = conn.prepareStatement("select date_format(now(),'%Y-%m-%d %H:%i:%s');");
ResultSet rs = ps.executeQuery();
rs.next();
System.out.println("sysdate is:" + rs.getString(1));
rs.close();
ps.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != conn) {
conn.close();
}
}
Use JDBC drivers
The following table describes some parameters required to use JDBC drivers. You can specify these parameters in the connection properties of the connection pool or in the JDBC URL.
| Parameter | Description | Recommended value |
|---|---|---|
| socketTimeout | The network socket timeout period, in milliseconds. The default value is 0, indicating to disable socket timeout. |
None. You can set it based on your business needs. |
| connectTimeout | The timeout period for establishing a connection. The default value is 0, which indicates that the default timeout period of the operating system is used. |
500 ms |
Connection pool configuration of applications
We recommend that you use connection pools to connect applications to databases for your business operations. For Java applications, we recommend that you use the Druid connection pool. The following code shows a sample configuration:
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<!-- Specify basic properties such as URL, username, and password -->
<property name="url" value="jdbc:mysql://ip:port/db?socketTimeout=30000&connectTimeout=3000" />
<property name="username" value="{user}" />
<property name="password" value="{password}" />
<!-- Configure the initial size, minimum size, and maximum size -->
<property name="maxActive" value="4" /> // Set the initialSize, minIdle, and maxActive properties based on your business scale.
<property name="initialSize" value="2" />
<property name="minIdle" value="2" />
<!-- Specify the connection timeout period, in milliseconds -->
<property name="maxWait" value="1000" />
<!-- The time interval for detecting idle connections to be released, in milliseconds -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- The minimum duration in which a connection remains idle in the connection pool, in milliseconds -->
<property name="minEvictableIdleTimeMillis" value="300000" />
<!-- The SQL statement for checking whether a connection is available-->
<property name="validationQuery" value="SELECT foo FROM bar" /> // Use a real business table with only a few records in the statement for checking
<!-- Specifies whether to enable idle connection detection -->
<property name="testWhileIdle" value="true" />
<!-- Specifies whether to check the connection status before obtaining a connection -->
<property name="testOnBorrow" value="false" />
<!-- Specifies whether to check the connection status when returning a connection -->
<property name="testOnReturn" value="false" />
</bean>
More information
For more information about OceanBase Connector/J, see OceanBase Connector/J.