OceanBase Cloud supports connections through the official MySQL JDBC driver. This topic describes how to connect to and use OceanBase Cloud with MySQL Connector/J.
Prerequisites
- You have downloaded and installed IntelliJ IDEA.
- You have downloaded JDK 1.8.0.
- You have downloaded Maven and configured it in IntelliJ IDEA.
- You have downloaded the sample project code used in this topic.
Version compatibility
MySQL Connector/J Version |
Recommended Version |
Description |
|---|---|---|
| 5.x | 5.1.40 ~ 5.1.49 | Stable version |
| 8.x | 8.0.7 ~ 8.0.25, 8.2.0 ~ 8.4.0 | New Feature Support |
Features
- Supports standard JDBC APIs
- Provides connection pool management
- Supports SSL connections
- Supports batch operations
- Supports prepared statements
Post-installation check
Check whether JDK is installed successfully:
java -versionCheck whether Maven is installed successfully:
mvn -version(Optional) Check whether Maven is correctly configured in IntelliJ IDEA:
This topic provides only a simple sample project for your reference. If you want to develop a more complex sample, you can download Maven of a version suitable for your development environment and configure it in IntelliJ IDEA by following the steps below.
Check whether Maven is installed successfully:
mvn -versionOpen IntelliJ IDEA, click IntelliJ IDEA > Settings > Build,Execution,Development > Build Tools > Maven, and set Maven home path to the installation directory of Maven.
Procedure
- Obtain the connection information of OceanBase Cloud.
- Modify the sample project code you have downloaded.
- Run the sample project code.
Create a Java application
Step 1: Obtain the database connection string
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance information. Under the target tenant, choose Connect > Get connection string.
For more detailed operations, see Get a connection string.
Fill in the corresponding information in the URL below based on your created OceanBase Cloud database.
obclient -h$host -P$port -u$user_name -p$password -D$database_nameParameter description:
$host: The connection address of the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.$port: The connection port of the OceanBase Cloud database. Default is 3306.$database_name: The name of the database to be accessed.Notice
The account used to connect to the tenant must have the
CREATE,INSERT,DROP, andSELECTprivileges on this database. For more information about account privileges, see Create and manage accounts.$user_name: The account used to access the database, specified by the-uparameter.$password: The password of the account.
Example:
obclient -h t********.********.oceanbase.cloud -P3306 -u mysql001 -p****** -D test
Step 2: Modify the sample project code
Extract the downloaded sample project.
Open IntelliJ IDEA and click File > Open to add the sample project file.
Modify the following database connection parameters in the project code file JDBCDemo > src > main > java > JDBCTest based on the connection string obtained in Step 1.
Modify the database connection parameters in the code. Refer to the following fields and concatenation method; the corresponding values are taken from the database connection string obtained in Step 1.
connection = DriverManager.getConnection("jdbc:mysql://{host}:{port}/{dbname}?user={username}&password={******}") //Example jdbc:mysql://t********.********.oceanbase.cloud:3306/test?user=r***&password=******`host: Taken from the
-hparameter, which is the connection address of the OceanBase Cloud database.port: Taken from the
-Pparameter, which is the connection port of the OceanBase Cloud database.dbname: Taken from the
-Dparameter, which is the name of the database to be accessed.username: Taken from the
-uparameter, which is the account used to access the database.password: Taken from the
-pparameter, which is the account password.
An example of the modified code snippet is as follows:
//Example Connection connection = DriverManager.getConnection("jdbc:mysql://t********.********.oceanbase.cloud:3306/test?user=r***&password=******");Notice
If you use MySQL Connector/J 8.x, avoid including
#in the account password. Otherwise, the sample project will report an error during runtime.
After correctly installing the MySQL Connector/J 5.1.47 driver and configuring the environment, you can connect to and use the database using the following sample code from the Test.java file.
Notice
For MySQL Connector/J 8.x, replace com.mysql.jdbc.Driver with com.mysql.cj.jdbc.Driver in Class.forName("com.mysql.jdbc.Driver").
A complete code example is as follows:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCTest {
public static void main(String[] args) {
try {
//load driver
Class.forName("com.mysql.jdbc.Driver"); //mysql-jdbc 5
// Class.forName("com.mysql.cj.jdbc.Driver"); //mysql-jdbc 8
//create connection
Connection connection = DriverManager.getConnection("jdbc:mysql://t********.********.oceanbase.cloud:3306/test?user=r***&password=");
System.out.println("jdbc version : " + connection.getMetaData().getDriverVersion());
Statement stmt = connection.createStatement();
//create table
stmt.execute("drop table if exists test");
stmt.execute("create table test (id int, name varchar(25))");
System.out.println("create table successfully");
//insert data
stmt.execute("insert into test values (1, 'aaa'),(2, 'bbb')");
System.out.println("insert data successfully");
//query data
System.out.println("query data : ");
ResultSet rs = stmt.executeQuery("select * from test");
while (rs.next()) {
System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
}
//update data
stmt.execute("update test set name = 'bbb' where id = 1");
System.out.println("update data successfully");
//query data after update
System.out.println("query data after update : ");
rs = stmt.executeQuery("select * from test");
while (rs.next()) {
System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
}
//delete data
stmt.execute("delete from test where id = 1");
System.out.println("delete data successfully");
//query data after delete
System.out.println("query data after delete : ");
rs = stmt.executeQuery("select * from test");
while (rs.next()) {
System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
}
//drop table
stmt.execute("drop table test");
//close
rs.close();
stmt.close();
connection.close();
} catch (Exception e) {
System.out.println("error!");
e.printStackTrace();
}
}
}
Step 3: Run the application
Run the sample project in IntelliJ IDEA. If the following result is returned, the database connection is successful and the sample project is executed correctly.
Sample project overview
Click mysql-connector/j to download the project code, which is a compressed file named JDBCDemo.zip.
After decompressing it, you will get a folder named JDBCDemo. The directory structure is as follows:
JDBCDemo
├── src
│ └── main
│ └── java
│ └── JDBCTest.java
└── pom.xml
File description:
src: The root directory for source code.main: The main code directory, containing the application's core logic.java: The Java source code directory.JDBCTest.java: The main class, containing logic such as creating tables and inserting data.pom.xml: The configuration file for the Maven project, used to manage project dependencies and build settings.
Introduction to pom.xml code
The pom.xml file is the configuration file for the Maven project, defining information such as the project's dependencies, plugins, and build rules. Maven is a Java project management tool that can automatically download dependencies, compile, and package projects.
The code in this pom.xml file mainly includes the following parts:
File declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.Code:
<?xml version="1.0" encoding="UTF-8"?>Configure the POM namespace and POM model version.
- Use
xmlnsto specify the POM namespace ashttp://maven.apache.org/POM/4.0.0. - Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xsi:schemaLocationto specify the POM namespace,http://maven.apache.org/POM/4.0.0, and the location of its XSD file,http://maven.apache.org/xsd/maven-4.0.0.xsd. - Use the
<modelVersion>element to specify the POM model version used by this POM file as4.0.0.
Code:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <!-- Other configurations --> </project>- Use
Configure the basic information.
- Use
<groupId>to specify the project's organization ascom.example. - Use
<artifactId>to specify the project name asJDBCDemo. - Use
<version>to specify the project version as1.0-SNAPSHOT.
Sample code:
<groupId>org.example</groupId> <artifactId>JDBCDemo</artifactId> <version>1.0-SNAPSHOT</version>- Use
Configure the components on which the project depends.
Add the
mysql-connector-javadependency library for interacting with the database:Note
The following code defines the MySQL Connector/J V5.1.40 dependency for the project. For information about other versions, see MySQL Connector/J.
- Use
<groupId>to specify the dependency's organization asmysql. - Use
<artifactId>to specify the dependency name asmysql-connector-java. - Use
<version>to specify the dependency version as5.1.40.
Sample code:
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.40</version> </dependency>- Use
Introduction to JDBCTest.java
The JDBCTest.java file is part of a sample program that demonstrates how to use MySQL Connector/J to perform database operations. It first configures database connection information, then creates a JDBCTest object to execute database operations. The code also includes examples of creating tables, inserting data, and querying data.
The code in the JDBCTest.java file mainly consists of the following parts:
Import Java classes required for using the JDBC API in the Java program.
- Connection: Used to establish a connection with the database.
- DriverManager: Responsible for managing a set of JDBC drivers.
- ResultSet: Used to process data returned by SQL queries.
- Statement: Used to execute static SQL statements and return results.
Sample code:
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement;Create a
JDBCTestclass and define themainmethod.- Define a public class named
JDBCTestas the program's entry point. The class name must match the file name. - Define a public static method
mainas the program's starting execution point. - Other database operations.
Sample code:
public class Main { public static void main(String[] args) { // Database connection information // Create Data Source // Create a table // Insert data // Update data // Delete data } }- Define a public class named
Define the database connection information.
- Use
Class.forName()to load and register the MySQL JDBC driver. - The
DriverManager.getConnection()method defines the URL, username, and password for connecting to the database. You need to replace$host,$port,$database_name,$user_name, and$passwordwith your actual database connection information.
Code:
Class.forName("com.mysql.jdbc.Driver"); //mysql-jdbc 5 //Class.forName("com.mysql.cj.jdbc.Driver"); //mysql-jdbc 8 Connection connection = DriverManager.getConnection("jdbc:mysql://t********.********.oceanbase.cloud:3306/test?user=mysql001&password=********");Parameter description:
$host: the connection address of OceanBase Cloud, for example,t********.********.oceanbase.cloud.$port: The port for connecting to OceanBase Cloud. Default: 3306.$database_name: The name of the database to be accessed.$user_name: the account used to access the database.$password: the account password.
- Use
Create and execute the SQL statement.
Statement stmt = connection.createStatement(); stmt.execute("...");Create a table.
Code:
// Delete the table named "test" if it exists using an SQL statement. stmt.execute("drop table if exists test"); // Create a new table named "test" with two columns: an integer column "id" and a character column "name". stmt.execute("create table test (id int, name varchar(25))"); // Print a message indicating the table has been created successfully System.out.println("create table successfully");Insert data.
Sample code:
// Insert two rows of data into the "test" table. stmt.execute("insert into test values (1, 'aaa'),(2, 'bbb')"); // Print a message indicating that data was inserted successfully System.out.println("insert data successfully");Query data.
Sample code:
// Print query data messages System.out.println("query data : "); // Execute the query SQL statement to retrieve all data from the "test" table. ResultSet rs = stmt.executeQuery("select * from test"); // Traverse result set while (rs.next()) { // Print the "id" and "name" fields of each data record. System.out.println(rs.getString("id") + "\t" + rs.getString("name"));Update the data.
Code:
// Update the 'name' field of the record with id 1 in the "test" table to 'bbb'. stmt.execute("update test set name = 'bbb' where id = 1"); // Print a message indicating that the data has been updated successfully System.out.println("update data successfully");Delete data.
Sample code:
// Delete the "test" table. stmt.execute("drop table test"); // Close result sets, statements, and database connections rs.close(); stmt.close(); connection.close();Exception handling.
Any exception that occurs during the execution of the preceding database operations will be caught, and error information along with stack trace details will be printed.
Sample code:
} catch (Exception e) { System.out.println("error!"); e.printStackTrace(); }
Complete code
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>JDBCDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.40</version>
</dependency>
<!-- <dependency>-->
<!-- <groupId>com.mysql</groupId>-->
<!-- <artifactId>mysql-connector-j</artifactId>-->
<!-- <version>8.0.33</version>-->
<!-- </dependency>-->
</dependencies>
</project>
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCTest {
public static void main(String[] args) {
try {
//load driver
Class.forName("com.mysql.jdbc.Driver"); //mysql-jdbc 5
// Class.forName("com.mysql.cj.jdbc.Driver"); //mysql-jdbc 8
//create connection
Connection connection = DriverManager.getConnection("jdbc:mysql://{host}:{port}/{dbname}?user={username}&password={******}");
System.out.println("jdbc version : " + connection.getMetaData().getDriverVersion());
Statement stmt = connection.createStatement();
//create table
stmt.execute("drop table if exists test");
stmt.execute("create table test (id int, name varchar(25))");
System.out.println("create table successfully");
//insert data
stmt.execute("insert into test values (1, 'aaa'),(2, 'bbb')");
System.out.println("insert data successfully");
//query data
System.out.println("query data : ");
ResultSet rs = stmt.executeQuery("select * from test");
while (rs.next()) {
System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
}
//update data
stmt.execute("update test set name = 'bbb' where id = 1");
System.out.println("update data successfully");
//query data after update
System.out.println("query data after update : ");
rs = stmt.executeQuery("select * from test");
while (rs.next()) {
System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
}
//delete data
stmt.execute("delete from test where id = 1");
System.out.println("delete data successfully");
//query data after delete
System.out.println("query data after delete : ");
rs = stmt.executeQuery("select * from test");
while (rs.next()) {
System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
}
//drop table
stmt.execute("drop table test");
//close
rs.close();
stmt.close();
connection.close();
} catch (Exception e) {
System.out.println("error!");
e.printStackTrace();
}
}
}
Best practices
Batch operations
Use batching to improve performance:
PreparedStatement pstmt = connection.prepareStatement("INSERT INTO test VALUES (?, ?)");
for (int i = 0; i < 1000; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "name" + i);
pstmt.addBatch();
}
pstmt.executeBatch();
Prepared statements
Use prepared statements to prevent SQL injection:
PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM test WHERE id = ?");
pstmt.setInt(1, 1);
ResultSet rs = pstmt.executeQuery();
FAQ
1. Connection timeout
Configure the connection timeout parameters:
jdbc:mysql://host:port/database?connectTimeout=30000&socketTimeout=60000
2. Character set issue
Set the correct character set:
jdbc:mysql://host:port/database?characterEncoding=utf8&useUnicode=true
3. SSL connection
Enable SSL connection:
jdbc:mysql://host:port/database?useSSL=true&requireSSL=true
4. Special characters in account password
If the account password contains special characters (such as #), it needs to be URL-encoded:
String encodedPassword = URLEncoder.encode(password, "UTF-8");
Notice
When using MySQL Connector/J 8.x, avoid including the # character in the account password, as it may cause connection failure.
More information
There is also a complete example for creating a Java sample application in the OceanBase Cloud open-source community. For details, refer to Java sample application.