This topic describes how to build an application using OceanBase Connector/J and OceanBase Cloud to perform basic operations such as creating tables, inserting data, and querying data.
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
java-oceanbase-jdbcproject into Eclipse. - Obtain the OceanBase Cloud database URL.
- Modify the database connection information in the
java-oceanbase-jdbcproject. - Run the
java-oceanbase-jdbcproject.
Step 1: Import the java-oceanbase-jdbc project into Eclipse
Open Eclipse and select Import from the File menu.
In the import window that appears, select Maven > Existing Maven Projects and click Next.
On the next page, click Browse, select the root directory of the Maven project, and then click Finish.
Eclipse automatically identifies the
pom.xmlfile in the project and displays all Maven projects in the Package Explorer. You can see the Package Explorer icon in the left panel of Eclipse. If it is not displayed, you can select Window > Show View > Package Explorer.Note
When importing 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.Check the project status.
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 URL below based on your created OceanBase Cloud database.
Example URL for connecting to an OceanBase Cloud database in Oracle-compatible mode:
jdbc:oceanbase://$host:$port/$schema_name?user=$user_name&password=$passwordParameter description:
$host: The connection address for the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.$port: The connection port for the OceanBase Cloud database, defaulting to 1521.$schema_name: The name of the schema to access.$user_name: The account for accessing the database.$password: The password for the account.
For more information about URL parameters, see Database URL.
Step 3: Modify the database connection information in the java-oceanbase-jdbc project
Modify the database connection information in the InsertAndSelectExample.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
******.
Sample code:
String url = "jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/sys";
String user = "oracle001";
String password = "******";
Step 4: Run the java-oceanbase-jdbc project
You can view the project structure in the Package Explorer.
Find the Main class in the project, right-click it, and select Run As > Run Configurations... to open the Run Configurations page.
On the Run Configurations page, select the
com.oceanbase.example.InsertAndSelectExampleproject for Main class on the Java Application > New_configuration page.View the project's log information and output results through the console in Eclipse.
Sample project overview
Click java-oceanbase-jdbc to download the project code, which is a compressed file named java-oceanbase-jdbc.zip.
After decompressing it, you will get a folder named java-oceanbase-jdbc. The directory structure is as follows:
.
|-- README-CN.md
|-- README.md
|-- pom.xml
|-- run.sh
`-- src
`-- main
`-- java
`-- com
`-- oceanbase
`-- example
|-- InsertAndSelectExample.java
`-- OceanBaseClientTest.java
File description:
README-CN.md: This file is the documentation for the project, primarily aimed at Chinese users.README.md: This file is also the documentation for the project, but written in English and primarily aimed at English-speaking users.pom.xml: This file is the configuration file for the Maven project, defining the project's dependencies, plugins, build rules, and other information.run.sh: This file is a Shell script, mainly used to automatically compile and run the Java application.src: This directory contains the project's source code and resource files and is the main directory of the project.main: This directory is a Java source code directory that contains the project's main code.java: This directory is the root directory for Java source code.com: This directory is the root directory for Java packages.oceanbase: This directory is a sub-package under thecompackage, indicating that this project is related to OceanBase Cloud database.example: This directory is a sub-package under theoceanbasepackage, indicating that this project is an example program used to demonstrate how to connect to and operate OceanBase Cloud database using the OceanBase JDBC driver.InsertAndSelectExample.java: This file is part of the example program and contains an example classInsertAndSelectExample, used to demonstrate how to insert and query data.OceanBaseClientTest.java: This file is also part of the example program and contains an example classOceanBaseClientTest, used to demonstrate operations such as connecting to the database, executing SQL statements, and querying data.
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
Basic information configuration.
- Use
<groupId>to set the group ID tocom.oceanbase.example. - Use
<artifactId>to set the artifact ID tooceanbase-client. - Use
<version>to set the project version as1.0-SNAPSHOT. - Use
<name>to set the project name asob-example-oceanbase-client.
Code:
<groupId>com.oceanbase.example</groupId> <artifactId>oceanbase-client</artifactId> <version>1.0-SNAPSHOT</version> <name>ob-example-oceanbase-client</name>- Use
Define properties for the project source files using
<properties>.- Use
<project.build.sourceEncoding>to specify the encoding for the project source files asUTF-8. - Use
<maven.compiler.source>to specify the Java source code version used by the Maven compiler as1.8. - Use
<maven.compiler.target>to specify the Java bytecode version generated by the Maven compiler as1.8. - Use
<exec.mainClass>to specify an example program in the project,com.oceanbase.example.InsertAndSelectExample.
Code:
<properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> <exec.mainClass>com.oceanbase.example.InsertAndSelectExample</exec.mainClass> </properties>- Use
Define the components on which the project depends using
<dependencies>.Note
The following code defines that the project depends on OceanBase Connector/J version 2.4.2. For information about other versions, see OceanBase JDBC driver
- Define a dependency using
<dependency>. - Use
<groupId>to specify the dependency's organization ascom.oceanbase. - Use
<artifactId>to specify the dependency's name asoceanbase-client. - Use
<version>to specify the dependency's version as2.4.2.
Sample code:
<dependencies> <dependency> <groupId>com.oceanbase</groupId> <artifactId>oceanbase-client</artifactId> <version>2.4.2</version> </dependency> </dependencies>- Define a dependency using
Introduction to the InsertAndSelectExample.java code
The InsertAndSelectExample.java file is part of a sample program and contains a sample class InsertAndSelectExample, which demonstrates how to insert and query data.
The code in the InsertAndSelectExample.java file mainly includes the following parts:
Define the package and import interfaces from
java.sql.Declare that the current file belongs to the package named
com.oceanbase.example.Import the following interfaces and classes from the
java.sqlpackage:- The
Connectioninterface: represents a connection to a database. - The
DriverManagerclass: it provides a set of static methods for managing the registration of JDBC drivers and obtaining database connections. The most commonly used method isgetConnection(), which creates a connection to a database. - The
PreparedStatementinterface: represents a precompiled SQL statement. - The
ResultSetinterface: represents a query result set. - The
SQLExceptionexception class: represents various exceptions that occur when using JDBC to connect to a database, such as connection failure or SQL command execution failure. - The
Statementinterface: represents a statement in a database.
Code:
package com.oceanbase.example; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;- The
Define the class name and the
mainmethod.Define the class name as
InsertAndSelectExample, which must match the file name.The
mainmethod is defined aspublic static void, indicating it is a public static method that accepts a string arrayargsas a parameter. Usingthrows ClassNotFoundException, SQLExceptionindicates this method may throwClassNotFoundExceptionandSQLExceptionexceptions, which need to be handled at the method call site.Code:
public class InsertAndSelectExample { public static void main(String[] args) throws ClassNotFoundException, SQLException { // to do: // 3.connect to your database // 4.create a table // 5.insert records // 6.fetch all records // 7.release all resources } }Connect to the database and obtain a database connection object.
Define the URL string and additional connection properties required to connect to OceanBase Cloud:
jdbc:oceanbase: specifies to use the OceanBase JDBC driver to connect to the database.host:port: specifies the address and port number of OceanBase Cloud.opt1=val1&opt2=val2...: additional connection properties, i.e., URL parameters. For more information about additional connection properties in the URL, see Database URL.schema_name: specifies the name of the schema to connect to.String user = "user_name": defines the username required to log in to the database.String password = "******": defines the password required to log in to the database.
Obtain a database connection object:
- Load the driver class with the specified name
com.oceanbase.jdbc.Driverby calling theforNamemethod ofClass. - Obtain a
Connectionobjectconnby calling thegetConnectionmethod of theDriverManagerclass.
Code:
String url = "jdbc:oceanbase://host:port/schema_name?[opt1=val1&opt2=val2...]"; String user = "user_name"; String password = "******"; Class.forName("com.oceanbase.jdbc.Driver"); Connection conn = DriverManager.getConnection(url, user, password);Create a table.
Create a table named
personwith two columns,nameandage. If the table already exists, drop it and then create it. The specific steps are as follows:- Create a
Statementobject namedstmtto send SQL statements to the database. - Call the
executemethod of thestmtobject and pass in the SQL statementdrop table personto drop the table namedperson. Since we cannot determine whether thepersontable already exists, we use atry-catchblock to catch exceptions, so that the program does not crash when dropping a non-existent table. - Call the
executemethod of thestmtobject again and pass in the SQL statementcreate table person (name varchar(50), age int)to create a table namedpersonwith two columns,nameandage. Thenamecolumn is of typevarchar(50)and theagecolumn is of typeint.
Code:
Statement stmt = conn.createStatement(); try {stmt.execute("drop table person");} catch (Exception ignore) {} stmt.execute("create table person (name varchar(50), age int)");- Create a
Insert data into the table.
Use the
PreparedStatementinterface to insert two rows of data into thepersontable in the database. The specific steps are as follows:- Create a
PreparedStatementobject namedpsto execute SQL statements. The SQL statement isinsert into person values(?, ?), where?is a placeholder indicating that specific values need to be inserted. - Call the
setString()method to set the first placeholder (the first question mark) toAdam. - Call the
setInt()method to set the second placeholder (the second question mark) to28. - Perform the update operation to insert the data into the database.
- Call the
setString()method again to set the first placeholder to "Eve". - Call the
setInt()method again to set the second placeholder to26. - Perform the update operation again to insert the second row of data into the database.
Code:
PreparedStatement ps = conn.prepareStatement("insert into person values(?, ?)"); ps.setString(1, "Adam"); ps.setInt(2, 28); ps.executeUpdate(); ps.setString(1, "Eve"); ps.setInt(2, 26); ps.executeUpdate();- Create a
Retrieve all records.
Query all data from the
persontable and output the results to the console. The specific steps are as follows:- Create a
PreparedStatementobject namedpsto execute SQL statements. The SQL statement isselect * from person, which means to query all data in thepersontable. PassResultSet.TYPE_FORWARD_ONLYandResultSet.CONCUR_READ_ONLYtoprepareStatement()to create a forward-only, read-only result set. - Perform the query operation and save the query results in a
ResultSetobject namedrs. - Use a
whileloop to traverse theResultSetobject. Use thers.next()method to move the cursor to the next row of data. If there is more data, it returnstrue. Inside the loop, use thers.getString(1)method to get the value of the first column (i.e.,namein thepersontable), and users.getInt(2)to get the value of the second column (i.e.,age). Output these values to the console in the formatname is age years old.. - After the loop finishes, close the
ResultSetandPreparedStatementobjects.
Code:
ps = conn.prepareStatement("select * from person", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ResultSet rs = ps.executeQuery(); while (rs.next()) { System.out.println(rs.getString(1) + " is " + rs.getInt(2) + " years old."); }- Create a
Release database resources.
Close the
PreparedStatement,Statement, andConnectionobjects to release the associated database resources, thereby avoiding resource consumption and performance issues.Code:
ps.close(); stmt.close(); conn.close();
Complete sample 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.oceanbase.example</groupId>
<artifactId>oceanbase-client</artifactId>
<version>1.0-SNAPSHOT</version>
<name>ob-example-oceanbase-client</name>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<exec.mainClass>com.oceanbase.example.InsertAndSelectExample</exec.mainClass>
</properties>
<dependencies>
<dependency>
<groupId>com.oceanbase</groupId>
<artifactId>oceanbase-client</artifactId>
<version>2.4.2</version>
</dependency>
</dependencies>
</project>
package com.oceanbase.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertAndSelectExample {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url = "jdbc:oceanbase://host:port/schema_name?[opt1=val1&opt2=val2...]";
String user = "user_name";
String password = "******";
Class.forName("com.oceanbase.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
try {
stmt.execute("drop table person");
} catch (Exception ignore) {
}
stmt.execute("create table person (name varchar2(50), age int)");
PreparedStatement ps = conn.prepareStatement("insert into person values(?, ?)");
ps.setString(1, "Adam");
ps.setInt(2, 28);
ps.executeUpdate();
ps.setString(1, "Eve");
ps.setInt(2, 26);
ps.executeUpdate();
ps = conn.prepareStatement("select * from person", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1) + " is " + rs.getInt(2) + " years old.");
}
ps.close();
stmt.close();
conn.close();
}
}
References
For more information about OceanBase Connector/J, see OceanBase JDBC Driver