Connect to OceanBase Database by using Spring Data JDBC

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

Download the spring-jdbc-oceanbase-client sample project

Prerequisites

• You have installed OceanBase Database and created an Oracle 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-oceanbase-client project into Eclipse.
2. Obtain the URL of OceanBase Database.
3. Modify the database connection information in the spring-jdbc-oceanbase-client project.
4. Run the spring-jdbc-oceanbase-client project.

Step 1: Import the spring-jdbc-oceanbase-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 use Eclipse to import a Maven project, Eclipse automatically detects the pom.xml file in the project, downloads the required dependency libraries based on the dependencies described in the file, and adds 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@oracel001 -p******
   

   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:oceanbase://$host:$port/$schema_name?user=$user_name&password=$password
   

   where

   • $host specifies 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 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.

   • $schema_name specifies the name of the schema to be accessed.

     Notice

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

   • $user_name specifies 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 specifies the password of the account.

   For more information about the URL parameters of OceanBase Connector/J, see Database URL.

   Here is an example:

   jdbc:oceanbase://xxx.xxx.xxx.xxx:2881/test_schema001?user=test_user001@oracel001&password=******
   

Step 3: Modify the database connection information in the spring-jdbc-oceanbase-client project

Modify the database connection information in the spring-jdbc-oceanbase-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 schema to be accessed is test_schema001.
• The tenant account is test_user001@oracle001, where oracle001 is a user tenant created in the Oracle mode of OceanBase Database, and test_user001 is a username in the oracle001 tenant.
• The password is ******.

The sample code is as follows:

...
        String url = "jdbc:oceanbase://xxx.xxx.xxx.xxx:2881/test_schema001";
        String username = "test_user001@oracle001";
        String password = "******";
...

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

1. In the Project Explorer view, locate and expand the src/main/java folder.

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

   

3. View the output in the Console window of Eclipse.

   

Project code introduction

Click spring-jdbc-oceanbase-client to download the project code, which is a compressed file named spring-jdbc-oceanbase-client.zip.

After decompressing it, you will find a folder named spring-jdbc-oceanbase-client. The directory structure is as follows:

spring-jdbc-oceanbase-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 specify http://maven.apache.org/POM/4.0.0 as the default XML namespace for the POM.
   2. Use xmlns:xsi to specify http://www.w3.org/2001/XMLSchema-instance as the XML namespace for xsi-prefixed elements.
   3. Use xsi:schemaLocation to provide a mapping from the default XML namespace for the POM (http://maven.apache.org/POM/4.0.0) to the location of the POM’s XML schema definition (XSD) file (http://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. Use <modelVersion> to specify 4.0.0 as the model version used by the POM.

   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 com.example as the ID of the project group.
   2. Use <artifactId> to specify spring-jdbc-oceanbase-client as the ID of the project.
   3. Use <version> to specify 1.0-SNAPSHOT as the project version.

   The sample code is as follows:

       <groupId>com.example</groupId>
       <artifactId>spring-jdbc-oceanbase-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 oceanbase-client dependency to interact with the database.

      1. Use <groupId> to specify com.oceanbase as the ID of the group that the dependency belongs to.
      2. Use <artifactId> to specify oceanbase-client as the dependency ID.
      3. Use <version> to specify 2.4.2 as the dependency version.

      Note

      The following code defines that the project depends on OceanBase Connector/J V2.4.2. For more information about other versions, see OceanBase Connector/J.

      The sample code is as follows:

              <dependency>
                  <groupId>com.oceanbase</groupId>
                  <artifactId>oceanbase-client</artifactId>
                  <version>2.4.2</version>
              </dependency>
      

   2. Add the spring-jdbc dependency.

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

      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 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 parent package of the current code to be com.example.
   2. Import the JdbcTemplate class, which is the core class in the Spring Data 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, $schema_name, $user_name, and $password with actual database connection information.

   The sample code is as follows:

           String url = "jdbc:oceanbase://$host:$port/$schema_name";
           String username = "$user_name";
           String password = "$password";
   

   where

   • $host specifies the IP address for connecting to OceanBase Database. For connection through ODP, this parameter is the IP address of an ODP. For direct connection, this parameter is 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.

   • $schema_name specifies the name of the database to access.

     For more information about URL parameters, see Database URL.

   • $user_name specifies 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 specifies 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. Call the setDriverClassName method to set the name of the database driver class to com.oceanbase.jdbc.Driver. This driver class is used to connect to OceanBase Database.
   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.

   The sample code is as follows:

           DriverManagerDataSource dataSource = new DriverManagerDataSource();
           dataSource.setDriverClassName("com.oceanbase.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 Data 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: id of the NUMBER 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 success message to the console.

   The sample code is as follows:

           String createTableSql = "CREATE TABLE test_springjdbc (id NUMBER, name VARCHAR2(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 (1,'A3').
   2. Execute the SQL statement to insert data. The jdbcTemplate object executes the statement by calling the update method.
   3. Print a success message to the console.

   The sample code is as follows:

           String insertDataSql = "INSERT INTO test_springjdbc (id,name) VALUES (1,'A1'), (2,'A2'), (1,'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 success message to the console.

   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 success message to the console.

   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 success message to the console.

    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 success message to the console.

    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-oceanbase-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>com.oceanbase</groupId>
            <artifactId>oceanbase-client</artifactId>
            <version>2.4.2</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:oceanbase://$host:$port/$schema_name";
        String username = "$user_name";
        String password = "$password";

        // create data source
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.oceanbase.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 NUMBER, name VARCHAR2(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'), (1,'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.");
    }
}

References

For more information about OceanBase Connector/J, see OceanBase Connector/J.