Connect to OceanBase Database by using Spring Data 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, and data query.

Download the spring-jdbc-mysql-client sample project

Prerequisites

• You have installed OceanBase Database and created a MySQL tenant.

• You have installed Java Development Kit (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 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.

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

   where

   • $host specifies 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 specifies 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 specifies the name of the database to be accessed.

     Notice

     The user for connecting 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 specifies the username of the account in the format of user@tenant#cluster name or username@SERVICE:service name. It is specified by the -u parameter. When the value is in the user@tenant#cluster name format, the default tenant is sys and the default administrator is root. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through ODP.

   • $password specifies the password of the account.

   For more information about the connection attributes 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. mysql001 is a MySQL user tenant created in OceanBase Database, and test_user001 is the username of the mysql001 tenant.
• The password is ******.

Here is the 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, find 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 here to download the project code, which is a package named spring-jdbc-mysql-client.zip.

Decompress the package to obtain 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 that stores the source code.
• main: a directory that stores the main code, including the major logic of the application.
• java: a directory that stores the Java source code.
• com: a directory that stores the Java package.
• example: a directory that stores the packages of the sample project.
• Main.java: the main class that contains logic such as the 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 as an XML file that uses the XML standard 1.0 and character encoding format UTF-8.

   Here is the sample code:

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

2. Configure namespaces and the POM version.

   1. xmlns: the default XML namespace for the POM, which is set to http://maven.apache.org/POM/4.0.0.
   2. xmlns:xsi: the XML namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   3. xsi:schemaLocation: the location of an XML schema definition (XSD) file. The value consists of two parts: the default XML namespace (http://maven.apache.org/POM/4.0.0) and the URI of the XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. <modelVersion>: the POM version used by the POM file, which is set to 4.0.0.

   Here is the 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. <groupId>: the ID of the group to which the project belongs, which is set to com.example.
   2. <artifactId>: the name of the project, which is set to spring-jdbc-mysql-client.
   3. <version>: the project version, which is set to 1.0-SNAPSHOT.

   Here is the sample code:

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

4. Configure the attributes of the project source file.

   Specify maven-compiler-plugin as the compiler plug-in of Maven, and set the source code version and target code version of the compiler to Java 8. This means that the project source code is compiled by using Java 8 and the compiled bytecode is compatible with the Java 8 runtime environment. This ensures that Java 8 syntax and characteristics can be correctly processed during the compilation and running of the project.

   Note

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

   Here is the 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 project dependencies.

   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. <groupId>: the ID of the group to which the dependency belongs, which is set to mysql.
      2. <artifactId>: the name of the dependency, which is set to mysql-connector-java.
      3. <version>: the version of the dependency, which is set to 5.1.40.

      Here is the 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. <groupId>: the ID of the group to which the dependency belongs, which is set to org.springframework.
      2. <artifactId>: the name of the dependency, which is set to spring-jdbc.
      3. <version>: the version of the dependency, which is set to 5.2.0.RELEASE.

      Here is the sample code:

              <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 application. Code in this file demonstrates how to perform database operations by using the Spring Data 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 package to which the current code belongs as com.example.
   2. Import the JdbcTemplate class, which is the core class in the Spring Data JDBC framework and is used to perform database operations.
   3. Import the DriverManagerDataSource class, which is a data source class provided by Spring that implements the DataSource interface and is used to configure database connection information.

   Here is the sample code:

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

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

   1. Define a public class named Main as the entry to the application. The class name must be the same as the file name.
   2. Define a public static method named main, which is used as the execution start point of the application.
   3. Define other database operations.

   Here is the 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.
           // Drop the table.
       }
   }
   

3. Define the database connection information.

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

   Here is the sample code:

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

   where:

   • $host specifies 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 specifies 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 specifies the name of the database to be accessed.
   • $user_name specifies the username of the account in the format of user@tenant#cluster name or username@SERVICE:service name. It is specified by the -u parameter. When the value is in the user@tenant#cluster name format, the default tenant is sys and the default administrator is root. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through ODP.
   • $password specifies 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. To do this, perform the following steps:

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

   Here is the sample code:

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

5. Create the JdbcTemplate object.

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

   Here is the sample code:

           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: id of the INT type and name of the VARCHAR(20) type.
   2. Execute the SQL statement to create the table. The jdbcTemplate object executes the statement by calling the execute method.
   3. If a success message is displayed in the console, the table is successfully created.

   Here is the 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 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. If a success message is displayed in the console, the data is successfully inserted into the table.

   Here is the 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 that updates the name field to A11 for the row where id is 1 in the test_springjdbc table.
   2. Execute the SQL statement. The jdbcTemplate object executes the statement by calling the update method.
   3. If a success message is displayed in the console, the data is successfully updated.

   Here is the 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 that deletes the row where id is 2 from the test_springjdbc table.
   2. Execute the SQL statement. The jdbcTemplate object executes the statement by calling the update method.
   3. If a success message is displayed in the console, the data is successfully deleted.

   Here is the 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 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. If a success message is displayed in the console, the data is successfully queried.

    Here is the 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. Drop the 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. If a success message is displayed in the console, the table is successfully dropped.

    Here is the sample code:

            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) {
        // Specify the database connection information.
        String url = "jdbc:mysql://$host:$port/$database_name?useSSL=false";
        String username = "$user_name";
        String password = "$password";

        // Create a 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 a 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.");
        
        // Drop the table.
        String deleteTableSql = "DROP TABLE test_springjdbc";
        jdbcTemplate.execute(deleteTableSql);
        System.out.println("Table drop successfully.");
    }
}

References

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