This topic describes how to build an application using the Spring JDBC framework, OceanBase Connector/J, and OceanBase Cloud to perform basic database operations, including creating tables, inserting data, updating data, deleting data, querying data, and dropping tables.
Prerequisites
You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud tenant in Oracle-compatible mode. For more information, see Create a cluster instance and Create a 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. You can also choose a tool that suits your personal preference to run the sample code.
Procedure
Note
The procedure described in this topic is based on using Eclipse IDE for Java Developers 2022-03 in a Windows environment to compile and run the project. If you are using a different operating system or compiler, the steps may vary slightly.
- Import the
spring-jdbc-oceanbase-clientproject into Eclipse. - Obtain the URL of the OceanBase Cloud database.
- Modify the database connection information in the
spring-jdbc-oceanbase-clientproject. - Run the
spring-jdbc-oceanbase-clientproject.
Step 1: Import the spring-jdbc-oceanbase-client project into Eclipse
Open Eclipse, and select File > Open Projects from File System from the menu bar.
In the dialog box that appears, click Directory, select the directory where the project is located, and then click Finish to complete the import.
Note
When you import a Maven project in Eclipse, it automatically detects the
pom.xmlfile in the project, downloads the required dependency libraries based on the dependencies described in the file, and adds them to the project.View the project.
Step 2: Obtain the OceanBase Cloud database URL
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information, and under the target tenant, select Connect > Get connection string.
For more detailed operations, see Get connection string.
Fill in the corresponding information in the following URL based on your created OceanBase Cloud database.
jdbc:oceanbase://$host:$port/$schema_name?user=$user_name&password=$passwordParameter description:
$host: The connection address of the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.$port: The connection port of the OceanBase Cloud database, which is 1521 by default.$schema_name: The name of the schema to be accessed.Notice
The user connecting to the tenant must have the
CREATE SESSIONprivilege and theCREATE TABLE,DROP TABLE,INSERT,DELETE,UPDATE, andSELECTprivileges on that schema. For more information about user privileges, see Create and manage accounts.$user_name: The account used to access the database.$password: The password for the account.
For more information about OceanBase Connector/J URL parameters, see Database URL.
Example:
jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/sys?user=oracle001&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 OceanBase Cloud database URL.
Example:
- The OceanBase Cloud database connection address is
t5******.********.oceanbase.cloud. - The access port is 1521.
- The name of the schema to be accessed is
sys. - The tenant connection account is oracle001.
- The password is
******.
Code:
...
String url = "t5******.********.oceanbase.cloud:1521/sys";
String username = "oracle001";
String password = "******";
...
Step 4: Run the spring-jdbc-oceanbase-client project
In the project navigation view, locate and expand the src/main/java directory.
Right-click the Main.java file and choose Run As > Java Application.
View the output results in the console window of Eclipse.
Sample project overview
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 get 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
File description:
src: The root directory for source code.main: The main code directory, containing the application's core logic.java: The Java source code directory.com: The Java package directory.example: The package directory for the sample project.Main.java: The main class, containing logic such as creating tables and inserting data.pom.xml: The Maven project configuration file, used to manage project dependencies and build settings.
Introduction to pom.xml code
The pom.xml file is the configuration file for a Maven project, defining information such as 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 code in this pom.xml file mainly includes the following parts:
File declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.Code:
<?xml version="1.0" encoding="UTF-8"?>Configure the POM namespace and POM model version.
- Use
xmlnsto specify the POM namespace ashttp://maven.apache.org/POM/4.0.0. - Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xsi:schemaLocationto specify the POM namespace,http://maven.apache.org/POM/4.0.0, and the location of its XSD file,http://maven.apache.org/xsd/maven-4.0.0.xsd. - Use the
<modelVersion>element to specify the POM model version used by this POM file as4.0.0.
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>- Use
Configure basic information.
- Use
<groupId>to specify the project's organization ascom.example. - Use
<artifactId>to specify the project name asspring-jdbc-oceanbase-client. - Use
<version>to specify the project version as1.0-SNAPSHOT.
Code:
<groupId>com.example</groupId> <artifactId>spring-jdbc-oceanbase-client</artifactId> <version>1.0-SNAPSHOT</version>- Use
Configure the properties of the project's source files.
Specifies the Maven compiler plugin as
maven-compiler-pluginand sets both the source and target Java versions to 8. This means the project's source code is written using Java 8 features, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This configuration ensures 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>Configure the components on which the project depends.
Add the
oceanbase-clientdependency library to interact with the database:- Use
<groupId>to specify the dependency's organization ascom.oceanbase. - Use
<artifactId>to specify the dependency name asoceanbase-client. - Use
<version>to specify the dependency version as2.4.2.
Note
The sample code defines that the project depends on OceanBase Connector/J V2.4.2. For information about other versions, see OceanBase JDBC driver.
Sample code:
<dependency> <groupId>com.oceanbase</groupId> <artifactId>oceanbase-client</artifactId> <version>2.4.2</version> </dependency>- Use
Add the
spring-jdbcdependency library:- Use
<groupId>to specify the dependency's organization asorg.springframework. - Use
<artifactId>to specify the dependency name asspring-jdbc. - Use
<version>to specify the dependency version as5.2.0.RELEASE.
Sample code:
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.2.0.RELEASE</version> </dependency>- Use
Introduction to Main.java
The Main.java file is part of the sample program and demonstrates how to use the Spring JDBC framework to perform database operations. It first configures the database connection information, 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 mainly consists of the following parts:
Define packages and import necessary classes.
- Declare that the current code belongs to the package named
com.example. - Import the
JdbcTemplateclass, which is the core class in the Spring JDBC framework for executing database operations. - Import the
DriverManagerDataSourceclass, which is a data source class implemented with theDataSourceinterface provided by Spring, used to configure database connection information.
Code:
package com.example; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.DriverManagerDataSource;- Declare that the current code belongs to the package named
Create a
Mainclass and define themainmethod.- Define a public class named
Mainas the program's entry point. The class name must match the file name. - Define a public static method
mainas the program's starting execution point. - Other database operations.
Code:
public class Main { public static void main(String[] args) { // Database connection information // Create Data Source // Create a JdbcTemplate object. // Create a table // Insert data // Update data // Delete data // Query data // Delete Table } }- Define a public class named
Define database connection information.
Define the URL, username, and password for connecting to the database. You need to replace
$host,$port,$schema_name,$user_name, and$passwordwith actual database connection information.Code:
String url = "jdbc:oceanbase://$host:$port/$schema_name"; String username = "$user_name"; String password = "$password";Parameter description:
$host: The connection address for OceanBase Cloud, for example,t********.********.oceanbase.cloud.$port: The connection port for OceanBase Cloud, defaulting to 1521.$schema_name: The name of the schema to access.For more information about URL parameters, see Database URL.
$user_name: The account for accessing the database.$password: The account password.
Create a data source.
Create a
DriverManagerDataSourceobject and configure the relevant database connection information, including the driver class name, database URL, username, and password. The specific steps are as follows:- Create a
DriverManagerDataSourceobject to configure the database connection information. - Set the driver class name: By calling the
setDriverClassNamemethod, set the database driver class name tocom.oceanbase.jdbc.Driver. This is the driver class used to connect to OceanBase Cloud. - Set the database URL: By calling the
setUrlmethod, set the database URL. - Set the username: By calling the
setUsernamemethod, set the database username. - Set the password: By calling the
setPasswordmethod, set the database password.
Sample code:
DriverManagerDataSource dataSource = new DriverManagerDataSource(); dataSource.setDriverClassName("com.oceanbase.jdbc.Driver"); dataSource.setUrl(url); dataSource.setUsername(username); dataSource.setPassword(password);- Create a
Create a
JdbcTemplateobject.Create a
JdbcTemplateobject, which is one of the core classes in the Spring JDBC framework.JdbcTemplateprovides methods for performing database operations, such as executing SQL statements, updating data, and querying data.Sample code:
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);Create a table.
- Define an SQL statement for creating a table. The SQL statement specifies the table name as
test_springjdbcand defines two columns: one namedidof typeNUMBER, and another namednameof typeVARCHAR2(20). - Execute the table creation SQL statement. The
jdbcTemplateobject executes the defined SQL statement by calling theexecutemethod. - Output a message indicating the table was created successfully to the console.
Sample code:
String createTableSql = "CREATE TABLE test_springjdbc (id NUMBER, name VARCHAR2(20))"; jdbcTemplate.execute(createTableSql); System.out.println("Create table successfully.");- Define an SQL statement for creating a table. The SQL statement specifies the table name as
Insert data.
- Define an SQL statement for inserting data. The SQL statement specifies the table to be inserted into as
test_springjdbc, the columns asidandname, and inserts three rows of data:(1,'A1'),(2,'A2'), and(1,'A3'). - Execute the data insertion SQL statement. The
jdbcTemplateobject executes the defined SQL statement by calling theupdatemethod. - Output a message indicating the data was inserted successfully to the console.
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.");- Define an SQL statement for inserting data. The SQL statement specifies the table to be inserted into as
Update data.
- Define an SQL statement to update the value of the
namefield toA11for the record withidequal to1in thetest_springjdbcdatabase table. - Execute the SQL statement through the
jdbcTemplateobject to apply the update operation to the database. - Output a message indicating the data was updated successfully 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.");- Define an SQL statement to update the value of the
Delete data.
- Define an SQL statement to delete the record with
id2from thetest_springjdbctable. - Execute the SQL statement through the
jdbcTemplateobject to apply the deletion operation to the database. - Output a message indicating successful data deletion to the console.
Sample code:
String deleteDataSql = "DELETE FROM test_springjdbc WHERE id = 2"; jdbcTemplate.update(deleteDataSql); System.out.println("Delete data successfully.");- Define an SQL statement to delete the record with
Query data.
- Define an SQL statement for querying data. The SQL statement uses
SELECT *to select all columns and query data from thetest_springjdbctable. - Execute the data query SQL statement. The
jdbcTemplateobject calls thequerymethod to execute the defined SQL statement and accepts a callback function as a parameter. - The code in the callback function uses a
ResultSetobject (rs) to retrieve each row of data from the query results and process it. The callback function uses thegetIntandgetStringmethods to obtain the values of theidandnamecolumns from theResultSetobject and prints them to the console. Finally, the callback function returnsnull. - Output a message indicating successful data query 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.");- Define an SQL statement for querying data. The SQL statement uses
Drop a table.
- Define an SQL statement to drop the
test_springjdbctable from the database. - Execute the SQL statement through the
jdbcTemplateobject to apply the table drop operation to the database. - Output a message indicating successful table drop.
Sample code:
String deleteTableSql = "DROP TABLE test_springjdbc"; jdbcTemplate.execute(deleteTableSql); System.out.println("Table drop successfully.");- Define an SQL statement to drop the
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-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 connection 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 JDBC Driver.