This topic describes how to build an application using the Spring JDBC framework, MySQL Connector/J, and OceanBase Cloud to perform basic database operations, including creating tables, inserting data, and querying data.
Features
- Supports basic CRUD operations.
- Provides transaction management.
- Includes an exception handling mechanism.
- Manages connection pools.
Application scenarios
- Scenarios where SQL statements need to be used directly.
- Scenarios with high performance requirements.
- Simple database operation scenarios.
Prerequisites
You have registered an OceanBase Cloud account and created a cluster instance and a tenant in MySQL mode. For more information, see Create a cluster instance and Create a tenant.
You have installed JDK 1.8 and Maven.
You have installed Eclipse.
Note
The tool used to run the sample code in this topic is Eclipse IDE for Java Developers 2022-03. You can also choose a tool that suits your personal preference to run the sample code.
Procedure
Note
The procedure described in this topic is based on using Eclipse IDE for Java Developers 2022-03 in a Windows environment to compile and run the project. If you are using a different operating system or compiler, the steps may vary slightly.
- Import the
spring-jdbc-mysql-clientproject into Eclipse. - Obtain the URL of the OceanBase Cloud database.
- Modify the database connection information in the
spring-jdbc-mysql-clientproject. - Run the
spring-jdbc-mysql-clientproject.
Step 1: Import the spring-jdbc-mysql-client project into Eclipse
Open Eclipse, and select File > Open Projects from File System from the menu bar.
In the dialog box that appears, click Directory, select the directory where the project is located, and then click Finish to complete the import.
Note
When importing a Maven project in Eclipse, it automatically detects the
pom.xmlfile in the project, downloads the required dependency libraries based on the dependencies described in the file, and adds them to the project.View the project.
Step 2: Obtain the URL of the OceanBase Cloud database
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information, and under the target tenant, select 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.
jdbc:mysql://$host:$port/$database_name?user=$user_name&password=$password&useSSL=falseParameter description:
$host: The connection address of the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.$port: The connection port of the OceanBase Cloud database, which is 3306 by default.$database_name: The name of the database to be accessed.Notice
The account used to connect to the tenant must have the
CREATE,INSERT,UPDATE,DELETE,SELECT, andDROPprivileges on this database. For more information about account privileges, see Create and manage accounts.$user_name: The account used to access the database.$password: The password of the account.
For more information about MySQL Connector/J connection properties, see Configuration Properties.
Example:
jdbc:mysql://t********.********.oceanbase.cloud:3306/test?user=mysql001&password=******&useSSL=false
Step 3: Modify the database connection information in the spring-jdbc-mysql-client project
Modify the database connection information in the spring-jdbc-mysql-client/src/main/java/com/example/Main.java file based on the information obtained in Step 2: Obtain the OceanBase Cloud database URL.
Example:
- The OceanBase Cloud database connection address is
t5******.********.oceanbase.cloud. - The access port is 3306.
- The name of the database to be accessed is
test. - The tenant connection account is
mysql001. - The password is
******.
Code:
...
String url = "t********.********.oceanbase.cloud:3306/test?useSSL=false";
String username = "mysql001";
String password = "******";
...
Step 4: Run the spring-jdbc-mysql-client project
In the project navigation view, locate and expand the src/main/java directory.
Right-click the Main.java file and select Run As > Java Application.
View the output results in the console window of Eclipse.
FAQ
1. Connection timeout
If you encounter a connection timeout issue, you can configure the connection timeout parameter in the JDBC URL:
jdbc:mysql://host:port/database?connectTimeout=30000&socketTimeout=60000
2. Character set issues
To ensure correct character encoding, set the appropriate character set parameter in the JDBC URL:
jdbc:mysql://host:port/database?characterEncoding=utf8&useUnicode=true
3. SSL connection
To enable SSL connection with OceanBase Cloud, add the following parameter to the JDBC URL:
jdbc:mysql://host:port/database?useSSL=true&requireSSL=true
4. Special characters in account credentials
If the username or password contains special characters (such as #), they need to be URL-encoded:
String encodedPassword = URLEncoder.encode(password, "UTF-8");
Note
When using MySQL Connector/J 8.x, ensure that the account credentials do not contain the number sign (#). Otherwise, you may encounter connection errors.
Sample project overview
Click spring-jdbc-mysql-client to download the project code, which is a compressed file named spring-jdbc-mysql-client.zip.
After decompressing it, you will get a folder named spring-jdbc-mysql-client. The directory structure is as follows:
spring-jdbc-mysql-client
├── src
│ └── main
│ └── java
│ └── com
│ └── example
│ └── Main.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.com: The Java package directory.example: The package directory for the sample project.Main.java: The main class, containing logic such as creating tables and inserting data.pom.xml: The Maven project configuration file, used to manage project dependencies and build settings.
Introduction to pom.xml code
The pom.xml file is the configuration file for a 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 basic information.
- Use
<groupId>to specify the project's organization ascom.example. - Use
<artifactId>to specify the project name asspring-jdbc-mysql-client. - Use
<version>to specify the project version as1.0-SNAPSHOT.
Code:
<groupId>com.example</groupId> <artifactId>spring-jdbc-mysql-client</artifactId> <version>1.0-SNAPSHOT</version>- Use
Configure the properties of the project's source files.
Specifies the Maven compiler plugin as
maven-compiler-pluginand sets both the source and target Java versions to 8. This means the project's source code is written using Java 8 features, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This configuration ensures the project can correctly handle Java 8 syntax and features during compilation and runtime.Note
Java 1.8 and Java 8 are different names for the same version.
Sample code:
<build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>8</source> <target>8</target> </configuration> </plugin> </plugins> </build>Configure the components on which the project depends.
Add the
mysql-connector-javadependency library to interact with the database:Note
The following code defines that the project depends on MySQL Connector/J version 5.1.40. For information about other versions, see MySQL Connector/J.
- Use
<groupId>to specify the dependency's group asmysql. - Use
<artifactId>to specify the dependency's name asmysql-connector-java. - Use
<version>to specify the dependency's version as5.1.40.
Sample code:
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.40</version> </dependency>- Use
Add the
spring-jdbcdependency library:- Use
<groupId>to specify the dependency's group asorg.springframework. - Use
<artifactId>to specify the dependency's name asspring-jdbc. - Use
<version>to specify the dependency's version as5.2.0.RELEASE.
Sample code:
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.2.0.RELEASE</version> </dependency>- Use
Introduction to Main.java
The Main.java file is part of the sample program, demonstrating how to use the Spring JDBC framework to perform database operations. It first configures the database connection information, then creates a JdbcTemplate object to execute database operations. The code also includes examples of creating tables, inserting data, and querying data.
The Main.java file in this topic mainly consists of the following parts:
Define packages and import necessary classes.
- Declare that the current code belongs to the package named
com.example. - Import the
JdbcTemplateclass, which is the core class in the Spring JDBC framework for executing database operations. - Import the
DriverManagerDataSourceclass, which is a data source class implemented with theDataSourceinterface provided by Spring, used to configure database connection information.
Code:
package com.example; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.DriverManagerDataSource;- Declare that the current code belongs to the package named
Create a
Mainclass and define themainmethod.- Define a public class named
Mainas the program's entry point. The class name must match the file name. - Define a public static method
mainas the starting execution point of the program. - Other database operations.
Code:
public class Main { public static void main(String[] args) { // Database connection information // Create Data Source // Create a JdbcTemplate object. // Create a table // Insert data // Update data // Delete data // Query data // Delete Table } }- Define a public class named
Define database connection information.
Define the URL, username, and password for connecting to the database. You need to replace
$host,$port,$database_name,$user_name, and$passwordwith actual database connection information.Code:
String url = "jdbc:mysql://$host:$port/$database_name?useSSL=false"; String username = "$user_name"; String password = "$password";Parameter description:
$host: The connection address for OceanBase Cloud, for example,t********.********.oceanbase.cloud.$port: The port for connecting to OceanBase Cloud, defaulting to 3306.$database_name: The name of the database to access.$user_name: The account for accessing the database.$password: The password for the account.
Create a data source.
Create a
DriverManagerDataSourceobject and configure the relevant database connection information, including the driver class name, database URL, username, and password. The specific steps are as follows:- Create a
DriverManagerDataSourceobject to configure the database connection information. - Set the driver class name: By calling the
setDriverClassNamemethod, set the database driver class name tocom.mysql.jdbc.Driver. - Set the database URL: By calling the
setUrlmethod, set the database URL. - Set the username: By calling the
setUsernamemethod, set the database username. - Set the password: By calling the
setPasswordmethod, set the database password.
Sample code:
DriverManagerDataSource dataSource = new DriverManagerDataSource(); dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUrl(url); dataSource.setUsername(username); dataSource.setPassword(password);- Create a
Create a
JdbcTemplateobject.Create a
JdbcTemplateobject, which is one of the core classes in the Spring JDBC framework.JdbcTemplateprovides methods for performing database operations, such as executing SQL statements, updating data, and querying data.Sample code:
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);Create a table.
- Define an SQL statement for creating a table. The SQL statement specifies the table name as
test_springjdbcand defines two columns: one namedidof typeINT, and another namednameof typeVARCHAR(20). - Execute the table creation SQL statement. The
jdbcTemplateobject executes the defined table creation SQL statement by calling theexecutemethod. - Output a message indicating the table was successfully created to the console.
Sample code:
String createTableSql = "CREATE TABLE test_springjdbc (id INT, name VARCHAR(20))"; jdbcTemplate.execute(createTableSql); System.out.println("Create table successfully.");- Define an SQL statement for creating a table. The SQL statement specifies the table name as
Insert data.
- Define an SQL statement for inserting data. The SQL statement specifies the table to insert into as
test_springjdbc, the columns asidandname, and inserts three rows of data:(1,'A1'),(2,'A2'), and(3,'A3'). - Execute the data insertion SQL statement. The
jdbcTemplateobject executes the defined data insertion SQL statement by calling theupdatemethod. - Output a message indicating the data was successfully inserted to the console.
Sample code:
String insertDataSql = "INSERT INTO test_springjdbc (id,name) VALUES (1,'A1'), (2,'A2'), (3,'A3')"; jdbcTemplate.update(insertDataSql); System.out.println("Insert data successfully.");- Define an SQL statement for inserting data. The SQL statement specifies the table to insert into as
Update data.
- Define an SQL statement to update the value of the
namefield toA11for the record withidequal to1in thetest_springjdbcdatabase table. - Execute the SQL statement through the
jdbcTemplateobject to apply the update operation to the database. - Output a message indicating the data was successfully updated to the console.
Sample code:
String updateDataSql = "UPDATE test_springjdbc SET name = 'A11' WHERE id = 1"; jdbcTemplate.update(updateDataSql); System.out.println("Update data successfully.");- Define an SQL statement to update the value of the
Delete data.
- Define an SQL statement to delete the record with
id2from the database tabletest_springjdbc. - Execute the SQL statement through the
jdbcTemplateobject to apply the deletion operation to the database. - Output a message indicating successful data deletion to the console.
Sample code:
String deleteDataSql = "DELETE FROM test_springjdbc WHERE id = 2"; jdbcTemplate.update(deleteDataSql); System.out.println("Delete data successfully.");- Define an SQL statement to delete the record with
Query data.
- Define an SQL statement for querying data. The SQL statement uses
SELECT *to select all columns and query data from thetest_springjdbctable. - Execute the SQL statement for querying data. The
jdbcTemplateobject calls thequerymethod to execute the defined SQL statement and accepts a callback function as a parameter. - The code in the callback function uses a
ResultSetobject (rs) to retrieve each row of data from the query result and process it. The callback function uses thegetIntandgetStringmethods to obtain the values of theidandnamecolumns from theResultSetobject and prints them to the console. Finally, the callback function returnsnull. - Output a message indicating successful data query to the console.
Sample code:
String selectDataSql = "SELECT * FROM test_springjdbc"; jdbcTemplate.query(selectDataSql, (rs, rowNum) -> { int id = rs.getInt("id"); String name = rs.getString("name"); System.out.println("id: " + id + ", name: " + name); return null; }); System.out.println("Query data successfully.");- Define an SQL statement for querying data. The SQL statement uses
Drop a table.
- Define an SQL statement to drop the
test_springjdbctable from the database. - Execute the SQL statement through the
jdbcTemplateobject to apply the table drop operation to the database. - Output a message indicating successful table drop.
Sample code:
String deleteTableSql = "DROP TABLE test_springjdbc"; jdbcTemplate.execute(deleteTableSql); System.out.println("Table drop successfully.");- Define an SQL statement to drop the
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>com.example</groupId>
<artifactId>spring-jdbc-mysql-client</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.40</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.0.RELEASE</version>
</dependency>
</dependencies>
</project>
package com.example;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class Main {
public static void main(String[] args) {
// Database connection information
String url = "jdbc:mysql://$host:$port/$database_name?useSSL=false";
String username = "$user_name";
String password = "$password";
// create data source
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
// Create a JdbcTemplate object
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
// Create table
String createTableSql = "CREATE TABLE test_springjdbc (id INT, name VARCHAR(20))";
jdbcTemplate.execute(createTableSql);
System.out.println("Create table successfully.");
// Insert data
String insertDataSql = "INSERT INTO test_springjdbc (id,name) VALUES (1,'A1'), (2,'A2'), (3,'A3')";
jdbcTemplate.update(insertDataSql);
System.out.println("Insert data successfully.");
// Update data
String updateDataSql = "UPDATE test_springjdbc SET name = 'A11' WHERE id = 1";
jdbcTemplate.update(updateDataSql);
System.out.println("Update data successfully.");
// Delete data
String deleteDataSql = "DELETE FROM test_springjdbc WHERE id = 2";
jdbcTemplate.update(deleteDataSql);
System.out.println("Delete data successfully.");
// Query data
String selectDataSql = "SELECT * FROM test_springjdbc";
jdbcTemplate.query(selectDataSql, (rs, rowNum) -> {
int id = rs.getInt("id");
String name = rs.getString("name");
System.out.println("id: " + id + ", name: " + name);
return null;
});
System.out.println("Query data successfully.");
// Delete table
String deleteTableSql = "DROP TABLE test_springjdbc";
jdbcTemplate.execute(deleteTableSql);
System.out.println("Table drop successfully.");
}
}
Best practices
Transaction management
@Transactional
public void transferMoney(String fromAccount, String toAccount, BigDecimal amount) {
// Transaction operations
}
References
For more information about MySQL Connector/J, see Overview of MySQL Connector/J.