Connect to OceanBase Database by using Spring JDBC

This topic introduces how to build an application by using the Spring Data 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, data query, and other data operations.

Click to download the spring-jdbc-mysql-client sample project Connect to OceanBase Database by using Spring JDBC (MySQL mode)

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 of your preference to run the sample 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 from the menu bar.

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

   

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 the connection string, see Connect to an OceanBase tenant by using OBClient.

2. Fill in the URL below based on the OceanBase Database connection string, and specify the corresponding parameters.

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

   Parameter description:

   • $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (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 be accessed.

     Note

     The user used to connect to the 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 account for connecting to the tenant. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

   • $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 access port is 2881.
• The name of the database to be accessed is test.
• The connection account for the tenant is test_user001@mysql001. Here, mysql001 refers to a user tenant created in OceanBase Database with the MySQL mode, and test_user001 is the username of the tenant.
• The password is ******.

Sample code:

...
        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. View the output in the console window of Eclipse.

   

FAQ

1. Connection timeout

If a connection timeout occurs, you can configure the connection timeout parameter in the JDBC URL:

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

2. Character set issue

To ensure correct character encoding, set the character set parameter in the JDBC URL:

jdbc:mysql://host:port/database?characterEncoding=utf8&useUnicode=true

3. SSL connection

To establish an SSL connection with OceanBase Database, add the following parameter to the JDBC URL:

jdbc:mysql://host:port/database?useSSL=true&requireSSL=true

4. Special characters in account name or password

If the username or password contains special characters such as #, URL encode it:

String encodedPassword = URLEncoder.encode(password, "UTF-8");

Project code introduction

Click spring-jdbc-mysql-client to download the project code, which is a compressed package 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

File description:

• src: the root directory for source code.
• main: the main code directory, containing the core 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, used to manage project dependencies and build settings.

Introduction to pom.xml

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

The pom.xml file in this topic is mainly composed of the following parts:

1. Declaration statement.

   The file is declared as an XML file that uses XML standard 1.0 and character encoding UTF-8.

   Sample code:

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

2. Configure namespaces and the POM model version.

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

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

3. Configure basic 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.

   Sample code:

       <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 plugin 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 features, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This setup ensures that the project can correctly handle Java 8 syntax and features during compilation and execution.

   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>
   

5. Configure the components on which the project depends.

   1. Add the mysql-connector-java dependency library for interactions with the database, and configure the following parameters:

      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.

      Sample code:

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

   2. Add the spring-jdbc dependency library, and configure the following parameters:

      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.

      Sample code:

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

Introduction to Main.java

The Main.java file is part of the sample application and demonstrates how to use the Spring Data JDBC framework for database operations. It first configures the database connection information, then creates a JdbcTemplate object to perform database operations. The code also includes examples of creating tables, inserting data, and querying data.

The code in this topic for the Main.java file mainly includes the following sections:

1. Define packages and import necessary classes.

   1. Declare the name of the package to which the current code belongs as com.example.
   2. Import the JdbcTemplate class, which is a core class of the Spring Data JDBC framework and is used for database operations.
   3. Import the DriverManagerDataSource class, which is a data source class that implements the DataSource interface and is used to configure database connection information.

   Sample code:

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

2. Create a Main class and define the main method.

   1. Define a public class named Main, which serves as the entry point of the program. The class name must be the same as the file name.
   2. Define a public static method named main, which serves as the starting execution point of the program.
   3. Other database operations.

   Sample code:

   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
           // Delete a table
       }
   }
   

3. Define database connection information.

   Specify the URL, username, and password for connecting to the database. You need to replace $host, $port, $database_name, $user_name, and $password with actual database connection information.

   Sample code:

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

   Parameter description:

   • $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 be accessed.
   • $user_name: the username specified after the -u parameter, in the format of username@tenant name#cluster name or username@SERVICE:service name. When the username@tenant name#cluster name format is used, the default tenant is sys and the administrator user is root. The cluster name can be omitted when deleting a database, but is required when connecting to a database through ODP.
   • $password: the password of the account.

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 specific steps are as follows:

   1. Create a DriverManagerDataSource object to configure the database connection information.
   2. Set the driver class name: Call the setDriverClassName method and set the database driver class name to com.mysql.jdbc.Driver.
   3. Set the database URL: Call the setUrl method and set the database URL.
   4. Set the username: Call the setUsername method and set the database username.
   5. Set the password: Call the setPassword method and 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);
   

5. Create a JdbcTemplate object.

   Create a JdbcTemplate object, which is one of the core classes of the Spring Data JDBC framework. The JdbcTemplate provides methods for performing database operations, such as executing SQL statements, updating data, and querying data.

   Sample code:

           JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
   

6. Create a table.

   1. Define an SQL statement for creating a table. The statement specifies the table name as test_springjdbc and defines two columns: one named id of the INT type and the other named name of the VARCHAR(20) type.
   2. Execute the SQL statement for creating a table. The jdbcTemplate object calls the execute method to execute the preceding SQL statement.
   3. Output a message indicating that the table has been created.

   Sample code:

           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 for inserting data. The statement specifies the table name as test_springjdbc, the columns as id and name, and inserts two rows of data: (1,'A1'), (2,'A2'), and (3,'A3').
   2. Execute the SQL statement for inserting data. The jdbcTemplate object calls the update method to execute the preceding SQL statement.
   3. Output a message indicating that the data has been inserted.

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

8. Update data.

   1. Define an SQL statement for updating the name field of the record with id equal to 1 in the test_springjdbc table to A11.
   2. Execute the SQL statement by using the jdbcTemplate object to apply the update operation to the database.
   3. Output a message indicating that the data has been updated.

   Sample code:

           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 for deleting the record with id equal to 2 from the test_springjdbc table.
   2. Execute the SQL statement by using the jdbcTemplate object to apply the delete operation to the database.
   3. Output a message indicating that the data has been deleted.

   Sample code:

           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 for querying data. The statement uses SELECT * to select all columns from the test_springjdbc table.
    2. Execute the SQL statement for querying data. The jdbcTemplate object calls the query method to execute the preceding SQL statement and passes a callback function as a parameter.
    3. The code in the callback function uses the ResultSet object (rs) to obtain each row of data in the query result and process the data. The code in the callback function calls the getInt and getString methods to obtain the values in the id and name columns from the ResultSet object and prints them to the console. Finally, the callback function returns null.
    4. Output a message indicating that the data has been queried.

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

11. Delete a table.

    1. Define an SQL statement for deleting the test_springjdbc table from the database.
    2. Execute the SQL statement by using the jdbcTemplate object to apply the table deletion operation to the database.
    3. Output a message indicating that the table has been deleted.

    Sample code:

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

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