Connect to OceanBase Cloud by using Spring JDBC

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

The files and directories are described as follows:

• 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 logic.
• pom.xml: the configuration file of the Maven project, which is used to manage project dependencies and build settings.

Code in pom.xml

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

Perform the following steps to configure the pom.xml file:

1. Declare the file.

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

   The sample code is as follows:

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

2. Configure namespaces and the POM model 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 model version used by the POM file, which is set 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 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.

   The sample code is as follows:

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

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

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

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

Perform the following steps to configure the Main.java file:

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

   The sample code is as follows:

   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.

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

   The sample code is as follows:

           String url = "jdbc:mysql://$host:$port/$database_name?useSSL=false";
           String username = "$user_name";
           String password = "$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. Perform the following steps:

   1. Create a DriverManagerDataSource object that configures the database connection information.
   2. Set the class name of the database driver. Call the setDriverClassName method to set the name of the database driver class to com.mysql.jdbc.Driver.
   3. Specify the URL for connecting to the database. 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 the JdbcTemplate object.

   JdbcTemplate 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: 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. Print a success message to the console.

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

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

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