Connect to OceanBase Database by using Spring JDBC

This topic introduces how to build an application by using the Spring JDBC framework, MySQL Connector/J, and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, and data query.

Download the spring-jdbc-mysql-client sample project

Features

• Supports basic CRUD operations.
• Provides transaction management.
• Exception handling mechanism
• Connection pool management

Scenarios

• Scenarios requiring direct use of SQL statements
• Scenarios with high performance requirements
• Simple database operation scenarios

Prerequisites

• You have installed OceanBase Database and created a MySQL 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), but you can also choose a tool that suits your personal preference to run the code.

Procedure

1. Import the spring-jdbc-mysql-client project into Eclipse.
2. Obtain the URL of OceanBase Database.
3. Modify the database connection information in the spring-jdbc-mysql-client project.
4. Run the spring-jdbc-mysql-client project.

Step 1: Import the spring-jdbc-mysql-client project into Eclipse

1. Start Eclipse and choose File > Open Projects from File System.

2. In the dialog box that appears, click Directory to browse and select the project, and then click Finish.

   Note

   When you import a Maven project using Eclipse, it will automatically detect the pom.xml file in the project, download the required dependency libraries based on the described dependencies in the file, and add them to the project.

   

3. View the project.

   

Step 2: Obtain the URL of OceanBase Database

1. Contact the deployment personnel or administrator of OceanBase Database to obtain the database connection string.

   Here is an example:

   obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest
   

   For more information about connection strings, see Connect to an OceanBase tenant by using OBClient.

2. Fill in the URL below based on the OceanBase Database connection string.

   jdbc:mysql://$host:$port/$database_name?user=$user_name&password=$password&useSSL=false
   

   The parameters are described as follows:

   • $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.

   • $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

   • $database_name: the name of the database to be accessed.

     Notice

     The user used to connect to a tenant must have the CREATE, INSERT, UPDATE, DELETE, SELECT, and DROP privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

   • $user_name: the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.

   • $password: the password of the account.

   For more information about the connection properties of MySQL Connector/J, see Configuration Properties.

   Here is an example:

   jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?user=test_user001@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 URL of OceanBase Database.

Here is an example:

• The IP address of the OBServer node is xxx.xxx.xxx.xxx.
• The port is 2881.
• The name of the database to be accessed is test.
• The tenant account is test_user001@mysql001, where mysql001 is a MySQL user tenant created in OceanBase Database, and test_user001 is the username of the mysql001 tenant.
• The password is ******.

The sample code is as follows:

...
        String url = "jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?useSSL=false";
        String username = "test_user001@mysql001";
        String password = "******";
...

Step 4: Run the spring-jdbc-mysql-client project

1. In the project navigation view, locate and expand the src/main/java directory.

2. Right-click the Main.java file and choose Run As > Java Application.

   

3. In the console window of Eclipse, view the output results.

   

Project code introduction

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 find 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

Here is a breakdown of the files and directories:

• src: the root directory for storing the source code.
• main: the directory for storing the main code, including the major logic of the application.
• java: the directory for storing the Java source code.
• com: the directory for storing the Java package.
• example: the directory for storing the packages of the sample project.
• Main.java: the main class that contains logic such as table creation and data insertion.
• pom.xml: the configuration file of the Maven project, which is used to manage project dependencies and build settings.

Code in pom.xml

The pom.xml file is a configuration file for Maven projects, which defines the dependencies, plug-ins, and build rules of the projects. Maven is a Java project management tool that can automatically download dependencies, compile, and package the projects.

To configure the pom.xml file, perform the following steps:

1. Declare the file.

   Declare the file to be an XML file that uses XML standard 1.0 and character encoding UTF-8.

   The sample code is as follows:

   <?xml version="1.0" encoding="UTF-8"?>
   

2. Configure the namespaces and the POM model version.

   1. Use xmlns to set the POM namespace to http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to set the XML namespace to http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to set the POM namespace to http://maven.apache.org/POM/4.0.0, and the URI of the corresponding XSD file to http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use <modelVersion> to set the POM model version used by the POM file to 4.0.0.

   The sample code is as follows:

   <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>
   

3. Configure basic project information.

   1. Use <groupId> to specify the project group as com.example.
   2. Use <artifactId> to specify the project name as spring-jdbc-mysql-client.
   3. Use <version> to specify the project version as 1.0-SNAPSHOT.

   The sample code is as follows:

       <groupId>com.example</groupId>
       <artifactId>spring-jdbc-mysql-client</artifactId>
       <version>1.0-SNAPSHOT</version>
   

4. Configure the properties of the project's source file.

   Specify the Maven compiler plug-in as maven-compiler-plugin, and set both the source code and target Java versions to 8. This means that the project's source code is written using Java 8, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This setup ensures that the project can correctly handle the syntax and features of Java 8 during compilation and runtime.

   Note

   Java 1.8 and Java 8 are different names for the same version.

   The sample code is as follows:

       <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>
   

5. Configure project dependencies.

   1. Add the mysql-connector-java dependency to interact with the database.

      Note

      The following code defines that the project depends on MySQL Connector/J V5.1.40. For more information about other versions, see MySQL Connector/J.

      1. Use <groupId> to specify the dependency group as mysql.
      2. Use <artifactId> to specify the dependency name as mysql-connector-java.
      3. Use <version> to specify the dependency version as 5.1.40.

      The sample code is as follows:

              <dependency>
                  <groupId>mysql</groupId>
                  <artifactId>mysql-connector-java</artifactId>
                  <version>5.1.40</version>
              </dependency>
      

   2. Add the spring-jdbc dependency library.

      1. Use <groupId> to specify the dependency group as org.springframework.
      2. Use <artifactId> to specify the dependency name as spring-jdbc.
      3. Use <version> to specify the dependency version as 5.2.0.RELEASE.

      The sample code is as follows:

              <dependency>
                  <groupId>org.springframework</groupId>
                  <artifactId>spring-jdbc</artifactId>
                  <version>5.2.0.RELEASE</version>
              </dependency>
      

Code in Main.java

The Main.java file is a part of the sample program. Code in this file demonstrates how to perform database operations by using the Spring JDBC framework. The code first configures the database connection information, and then creates a JdbcTemplate object to perform database operations. It also provides examples of creating tables, inserting data, and querying data.

To configure the Main.java file, perform the following steps:

1. Define the package and import necessary classes.

   1. Declare the name of the parent package of the current code to be com.example.
   2. Import the JdbcTemplate class, which is the core class in the Spring JDBC framework for performing database operations.
   3. Import the DriverManagerDataSource class, which is a DataSource implementation provided by Spring for configuring database connection information.

   The sample code is as follows:

   package com.example;
   
   import org.springframework.jdbc.core.JdbcTemplate;
   import org.springframework.jdbc.datasource.DriverManagerDataSource;
   

2. Define the Main class and the main method.

   1. Define a public class named Main as the entry to the program. The class name must be the same as the file name.
   2. Define a public static method main as the execution start point for the program.
   3. Define other database operations.

   The sample code is as follows:

   public class Main {
       public static void main(String[] args) {
           // Database connection information
           // Create a data source.
           // Create a JdbcTemplate object.
           // Create a table.
           // Insert data.
           // Update data.
           // Delete data.
           // Query data.
           // Drop a table.
       }
   }
   

3. Define the database connection information.

   Define the URL, username, and password for connecting to the database. Replace $host, $port, $database_name, $user_name, and $password with the actual database connection information.

   The sample code is as follows:

           String url = "jdbc:mysql://$host:$port/$database_name?useSSL=false";
           String username = "$user_name";
           String password = "$password";
   

   The parameters are described as follows:

   • $host: the IP address for connecting to OceanBase Database. For connection through ODP, use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.
   • $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.
   • $database_name: the name of the database to access.
   • $user_name: the username in the username@tenant name#cluster name or username@SERVICE:service name format. When the username@tenant name#cluster name format is used, the default tenant is sys and the default administrator is root. The cluster name can be omitted when you directly connect to the database, but is required when you connect to the database through ODP.
   • $password: the account password.

4. Create a data source.

   Create a DriverManagerDataSource object and configure the database connection information, including the driver class name, database URL, username, and password. The steps are as follows:

   1. Create a DriverManagerDataSource object for configuring the database connection information.
   2. Set the driver class name: Call the setDriverClassName method to set the driver class name to com.mysql.jdbc.Driver.
   3. Set the database URL: Call the setUrl method to set the database URL.
   4. Set the username: Call the setUsername method to set the database username.
   5. Set the password: Call the setPassword method to set the database password.

   The sample code is as follows:

           DriverManagerDataSource dataSource = new DriverManagerDataSource();
           dataSource.setDriverClassName("com.mysql.jdbc.Driver");
           dataSource.setUrl(url);
           dataSource.setUsername(username);
           dataSource.setPassword(password);
   

5. Create a JdbcTemplate object.

   The JdbcTemplate object is one of the core classes of the Spring JDBC framework. JdbcTemplate provides methods to perform database operations, such as executing SQL statements, updating data, and querying data.

   The sample code is as follows:

           JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
   

6. Create a table.

   1. Define an SQL statement that creates a table. In the SQL statement, set the table name to test_springjdbc and define two columns, namely, id of the INT type and name of the VARCHAR(20) type.
   2. Execute the SQL statement to create a table. The jdbcTemplate object executes the statement by calling the execute method.
   3. Print a message to the console indicating that the table is created successfully.

   The sample code is as follows:

           String createTableSql = "CREATE TABLE test_springjdbc (id INT, name VARCHAR(20))";
           jdbcTemplate.execute(createTableSql);
           System.out.println("Create table successfully.");
   

7. Insert data.

   1. Define an SQL statement that inserts data. In the SQL statement, set the target table to test_springjdbc and the target columns to id and name, and insert (1,'A1'), (2,'A2'), and (3,'A3').
   2. Execute the SQL statement to insert data. The jdbcTemplate object executes the statement by calling the update method.
   3. Print a message to the console indicating that the data is inserted successfully.

   The sample code is as follows:

           String insertDataSql = "INSERT INTO test_springjdbc (id,name) VALUES (1,'A1'), (2,'A2'), (3,'A3')";
           jdbcTemplate.update(insertDataSql);
           System.out.println("Insert data successfully.");
   

8. Update data.

   1. Define an SQL statement that updates the name field to A11 for the row where id is 1 in the table named test_springjdbc.
   2. Execute the SQL statement. The jdbcTemplate object executes the statement by calling the update method.
   3. Print a message to the console indicating that the data is updated successfully.

   The sample code is as follows:

           String updateDataSql = "UPDATE test_springjdbc SET name = 'A11' WHERE id = 1";
           jdbcTemplate.update(updateDataSql);
           System.out.println("Update data successfully.");
   

9. Delete data.

   1. Define an SQL statement that deletes the row where id is 2 from the table test_springjdbc.
   2. Execute the SQL statement. The jdbcTemplate object executes the statement by calling the update method.
   3. Print a message to the console indicating that the data is deleted successfully.

   The sample code is as follows:

           String deleteDataSql = "DELETE FROM test_springjdbc WHERE id = 2";
           jdbcTemplate.update(deleteDataSql);
           System.out.println("Delete data successfully.");
   

10. Query data.

    1. Define an SQL statement that queries data. In the SQL statement, use SELECT * to select all columns and specify to query data from the test_springjdbc table.
    2. Execute the SQL statement to query data. The jdbcTemplate object executes the statement by calling the query method and accepts a callback function as a parameter.
    3. Code in the callback function uses a ResultSet object (rs) to obtain and process each row in the query result. The callback function uses the getInt and getString methods to get the values of the id and name columns from the ResultSet object and prints them to the console. Finally, the callback function returns null.
    4. Print a message to the console indicating that the data is queried successfully.

    The sample code is as follows:

            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.");
    

11. Drop a table.

    1. Define an SQL statement that drops the test_springjdbc table from the database.
    2. Execute the SQL statement. The jdbcTemplate object executes the statement by calling the execute method.
    3. Print a message to indicate that the table is dropped successfully.

    The sample code is as follows:

            String deleteTableSql = "DROP TABLE test_springjdbc";
            jdbcTemplate.execute(deleteTableSql);
            System.out.println("Table drop successfully.");
    

Complete code examples

<?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 Connect 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
}

FAQ

1. What do I do when a connection times out?

Configure appropriate connection timeout parameters:

jdbc:mysql://host:port/database?connectTimeout=30000&socketTimeout=60000

2. How do I handle batch data processing?

Use batch processing operations:

jdbcTemplate.batchUpdate(sql, batchArgs);

3. How do I optimize query performance?

• Use a connection pool.
• Configure the fetchSize parameter appropriately.
• Do not use the SELECT * statement.

References

For more information about MySQL Connector/J, see Overview of MySQL Connector/J.