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

   Here is an example:

   obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@oracel001 -p******
   

   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: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), 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.

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

     Notice

     The user used to connect to the tenant must have the CREATE SESSION privilege and the CREATE TABLE, DROP TABLE, INSERT, DELETE, UPDATE, and SELECT privileges on this schema. For more information about user privileges, see Privilege types in Oracle 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 URL parameters in 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 Main.java file in the spring-jdbc-oceanbase-client/src/main/java/com/example/ directory 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 schema to be accessed is test_schema001.
• The tenant account is test_user001@oracle001. oracle001 is an Oracle user tenant created in OceanBase Database, and test_user001 is the username of a user in the oracle001 tenant.
• The password is ******.

Here is the sample code:

...
        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. In the Console window of Eclipse, view the output.

   

Project code

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

Decompress the package to obtain 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 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-oceanbase-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-oceanbase-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 the components on which the project depends.

   1. Add the oceanbase-client dependency library for interactions with the database, and configure the following parameters:

      1. <groupId>: the ID of the group to which the dependency belongs, which is set to com.oceanbase.
      2. <artifactId>: the name of the dependency, which is set to oceanbase-client.
      3. <version>: the version of the dependency, which is set to 2.4.2.

      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.

      Here is the sample code:

              <dependency>
                  <groupId>com.oceanbase</groupId>
                  <artifactId>oceanbase-client</artifactId>
                  <version>2.4.2</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 required 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, $schema_name, $user_name, and $password with the actual database connection information.

   Here is the sample code:

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

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

     For more information about URL parameters, see Database URL.

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

   Here is the sample code:

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

5. Create the JdbcTemplate object.

   JdbcTemplate is one of the core classes of the Spring Data JDBC framework. The class 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 NUMBER type and name of the VARCHAR2(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 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. 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'), (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 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-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) {
        // Specify the database connection information.
        String url = "jdbc:oceanbase://$host:$port/$schema_name";
        String username = "$user_name";
        String password = "$password";

        // Create a 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 a 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.");
        
        // Drop the 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.