Use Spring JDBC to connect to OceanBase Database

This topic describes how to build an application by using the Spring JDBC framework, MySQL Connector/J, and OceanBase Database. The application implements basic database operations, including table creation, data insertion, and data query.

Download the spring-jdbc-mysql-client sample project

Use Spring JDBC to connect to OceanBase Database (MySQL mode)

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 code examples in this topic are run in Eclipse IDE for Java Developers 2022-03. You can also use any other tool of your choice to run the code examples.

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 select the project directory, and then click Finish.

   Note

   When you import a Maven project into Eclipse, it automatically detects the pom.xml file in the project, downloads the required dependency libraries based on the described dependencies, and adds them to the project.

   

3. View the project.

   

Step 2: Obtain the URL of OceanBase Database

1. Contact the OceanBase Database deployment personnel or administrator to obtain the database connection string.

   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 corresponding information in the URL based on the OceanBase Database connection string.

   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 port is 2883, which can be customized when ODP is deployed. For direct connection, the default port is 2881, which can be customized when OceanBase Database is deployed.

   • $database_name: the name of the database to be accessed.

     Notice

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

   • $password: the account password.

   For more information about the connection properties of MySQL Connector/J, see Configuration Properties.

   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.

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

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 Explorer view, find 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.

   

Project code introduction

Click spring-jdbc-mysql-client to download the project code, which is a compressed file 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 Java source code.
• com: the directory for Java packages.
• example: the directory for packages of the sample project.
• Main.java: the main class, containing logic for creating tables and inserting data.
• pom.xml: the configuration file for the Maven project, used to manage project dependencies and build settings.

Code in pom.xml

The pom.xml file is a configuration file for a Maven project. It defines the project's dependencies, plugins, and build rules. Maven is a Java project management tool that can automatically download dependencies, compile, and package projects.

The pom.xml file in this topic contains the following main parts:

1. The file declaration statement.

   This statement declares the file as an XML file using XML version 1.0 and character encoding UTF-8.

   Sample code:

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

2. The POM namespace and model version.

   1. The xmlns attribute specifies the POM namespace as http://maven.apache.org/POM/4.0.0.
   2. The xmlns:xsi attribute specifies the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
   3. The xsi:schemaLocation attribute specifies the POM namespace as http://maven.apache.org/POM/4.0.0 and the location of the POM XSD file as http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. The <modelVersion> element specifies 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. Basic information configuration.

   1. The <groupId> element specifies the project's organization as com.example.
   2. The <artifactId> element specifies the project name as spring-jdbc-mysql-client.
   3. The <version> element specifies 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. Project source file properties configuration.

   The Maven compiler plugin is set to maven-compiler-plugin, and both the source and target Java versions are set to 8. This means that the project's source code is written using Java 8 features, and the compiled bytecode will be compatible with the Java 8 runtime environment. This configuration ensures that the project can correctly handle Java 8 syntax and features during compilation and runtime.

   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. Project dependency configuration.

   1. Add the mysql-connector-java dependency library for database interaction:

      Note

      This section of code defines the project's dependency as MySQL Connector/J V5.1.40. For information about other versions, see MySQL Connector/J.

      1. The <groupId> element specifies the dependency's organization as mysql.
      2. The <artifactId> element specifies the dependency name as mysql-connector-java.
      3. The <version> element specifies 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:

      1. The <groupId> element specifies the dependency's organization as org.springframework.
      2. The <artifactId> element specifies the dependency name as spring-jdbc.
      3. The <version> element specifies 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>
      

Main.java code introduction

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

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

1. Define the package and import the necessary classes.

   1. Declare the package name of the current code as com.example.
   2. Import the JdbcTemplate class, which is the core class of the Spring JDBC framework and is used to execute 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.

   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 as the entry point of the program. The class name must be consistent with the file name.
   2. Define a public static method main as the starting 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 the database connection information.

   Define the URL, username, and password for connecting to the database. You need to replace $host, $port, $database_name, $user_name, and $password with the 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 ODP connection, use an ODP address. For direct connection, use the IP address of an OBServer node.
   • $port: the port for connecting to OceanBase Database. For ODP connection, the default port is 2883, which can be customized when ODP is deployed. For direct connection, the default port is 2881, which can be customized when OceanBase Database is deployed.
   • $database_name: the name of the database to be accessed.
   • $user_name: specified by the -u parameter, in the format of user@tenant#cluster name or username@SERVICE:service name. When the format user@tenant#cluster name is used, the default tenant is sys and the administrator user is root. When directly connecting to the database, the cluster name can be omitted. When connecting through ODP, the cluster name must be specified.
   • $password: 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 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 to set the database driver class name to com.mysql.jdbc.Driver.
   3. Set the database URL: 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.

   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 JDBC framework. The JdbcTemplate class 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 SQL statement specifies the table name as test_springjdbc and defines two columns: one is an INT type column named id, and the other is a VARCHAR(20) type column named name.
   2. Execute the SQL statement for creating a table. The jdbcTemplate object calls the execute method to execute the SQL statement defined above.
   3. Output a message indicating that the table was successfully 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 SQL statement specifies the table to insert data into as test_springjdbc, with columns id and name, and inserts three 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 SQL statement defined above.
   3. Output a message indicating that the data was successfully 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 through the jdbcTemplate object to apply the update operation to the database.
   3. Output a message indicating that the data was successfully 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 through the jdbcTemplate object to apply the delete operation to the database.
   3. Output a message indicating that the data was successfully 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 to query data from the test_springjdbc table. The SQL statement uses SELECT * to select all columns.
    2. Execute the SQL statement to query data. The jdbcTemplate object calls the query method to execute the SQL statement and receives a callback function as a parameter.
    3. In the callback function, use the ResultSet object (rs) to obtain each row of data from the query result and process it. The callback function uses the getInt and getString methods to obtain 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. Output a message indicating that the data query was successful.

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

    1. Define an SQL statement to delete the test_springjdbc table from the database.
    2. Execute the SQL statement using the jdbcTemplate object to apply the table deletion operation to the database.
    3. Output a message indicating that the table was successfully 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.");
    }
}

References

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

This topic describes how to build an application by using the Spring JDBC framework, MySQL Connector/J, and OceanBase Database. The application can perform basic database operations, such as creating tables, inserting data, and querying data.

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

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 code examples in this topic are run in Eclipse IDE for Java Developers 2022-03. You can also use other tools that you prefer.

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 select the project directory and then click Finish.

   Note

   When you import a Maven project into Eclipse, it automatically detects the pom.xml file in the project, downloads the required dependency libraries based on the described dependencies, 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@mysql001 -p****** -Dtest
   

   For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

2. Fill in the corresponding information in the URL based on the OceanBase Database connection string.

   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 the 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 the connection through ODP, the default port is 2883, which can be customized when ODP is deployed. For direct connection, the default port is 2881, which can be customized when OceanBase Database is deployed.

   • $database_name: the name of the database to be accessed.

     Notice

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

   • $password: the account password.

   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

Based on the information obtained in Step 2: Obtain the URL of OceanBase Database, modify the database connection information in the spring-jdbc-mysql-client/src/main/java/com/example/Main.java file.

Here is an example:

• The IP address of an OBServer node is xxx.xxx.xxx.xxx.
• The access port is 2881.
• The name of the database to be accessed is test.
• The tenant account is test_user001@mysql001. mysql001 is a MySQL tenant created in OceanBase Database, and test_user001 is the username of the mysql001 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 Explorer view, find 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.

   

Project code introduction

Click spring-jdbc-mysql-client to download the project code, which is a compressed file 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 Java source code.
• com: the directory for Java packages.
• example: the directory for packages in the sample project.
• Main.java: the main class, containing logic such as table creation and data insertion.
• pom.xml: the configuration file for the Maven project, used to manage project dependencies and build settings.

Introduction to the pom.xml file

The pom.xml file is a configuration file for Maven projects. It defines the project's dependencies, plugins, and build rules. Maven is a Java project management tool that can automatically download dependencies, compile, and package projects.

The pom.xml file in this topic includes the following main sections:

1. File declaration statement.

   This statement declares the file as an XML file using XML version 1.0 and character encoding UTF-8.

   Sample code:

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

2. Configure the namespace and POM model version.

   1. Use xmlns to set the POM namespace to http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to set the XML namespace to http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to set the POM namespace to http://maven.apache.org/POM/4.0.0 and the location of the POM XSD file to http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use <modelVersion> to set the POM model version to 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 set the project's group ID to com.example.
   2. Use <artifactId> to set the project name to spring-jdbc-mysql-client.
   3. Use <version> to set the project version to 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 files.

   Set the Maven compiler plugin to maven-compiler-plugin and specify that the source and target Java versions are both 8. This means that the project's source code uses Java 8 features, and the compiled bytecode will be compatible with the Java 8 runtime environment. This configuration ensures that the project can correctly handle Java 8 syntax and features during compilation and runtime.

   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 that the project depends on.

   1. Add the mysql-connector-java dependency library for database interaction:

      Note

      This section defines the project's dependency on MySQL Connector/J version 5.1.40. For information about other versions, see MySQL Connector/J.

      1. Use <groupId> to set the dependency's group ID to mysql.
      2. Use <artifactId> to set the dependency name to mysql-connector-java.
      3. Use <version> to set the dependency version to 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:

      1. Use <groupId> to set the dependency's group ID to org.springframework.
      2. Use <artifactId> to set the dependency name to spring-jdbc.
      3. Use <version> to set the dependency version to 5.2.0.RELEASE.

      Sample code:

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

Main.java code introduction

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

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

1. Define the package and import the necessary classes.

   1. Declare the package name of the current code as com.example.
   2. Import the JdbcTemplate class, which is the core class in the Spring JDBC framework for executing 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.

   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 as the entry point of the program. The class name must be consistent with the file name.
   2. Define a public static method main as the starting 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 the table
       }
   }
   

3. Define the database connection information.

   Define the URL, username, and password for connecting to the database. You need to replace $host, $port, $database_name, $user_name, and $password with the 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 ODP connection, use an ODP address. For direct connection, use the IP address of an OBServer node.
   • $port: the port for connecting to OceanBase Database. For ODP connection, 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 tenant connection account. For ODP connection, the common format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.
   • $password: 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 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 to set the database driver class name to com.mysql.jdbc.Driver.
   3. Set the database URL: 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.

   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 in the Spring JDBC framework. The JdbcTemplate class provides methods for executing 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 SQL statement specifies the table name as test_springjdbc and defines two columns: an id column of the INT type and a name column of the VARCHAR(20) type.
   2. Execute the SQL statement for creating the table. The jdbcTemplate object calls the execute method to execute the SQL statement for creating the table.
   3. Output a message indicating that the table was successfully created to the console.

   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 SQL statement specifies the table to be inserted into as test_springjdbc, the columns as id and name, and the data to be inserted as (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 SQL statement for inserting data.
   3. Output a message indicating that the data was successfully inserted to the console.

   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 using the jdbcTemplate object to apply the update operation to the database.
   3. Output a message indicating that the data was successfully updated to the console.

   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 using the jdbcTemplate object to apply the delete operation to the database.
   3. Output a message indicating that the data was successfully deleted to the console.

   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 SQL 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 SQL statement for querying data and receives a callback function as a parameter.
    3. The code in the callback function uses the ResultSet object (rs) to retrieve each row of data from the query result and process it. The callback function uses the getInt and getString methods to retrieve 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. Output a message indicating that the data was successfully queried to the console.

    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 to drop the test_springjdbc table in the database.
    2. Execute the SQL statement using the jdbcTemplate object to apply the drop table operation to the database.
    3. Output a message indicating that the table was successfully dropped.

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

References

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