在 Java 中连接 OceanBase 数据库并进行增删改查(CRUD)操作,一般使用 JDBC 或 MyBatis 进行数据库交互。以下是完整的步骤和示例代码:
1. 添加 OceanBase JDBC 依赖
如果使用 Maven,在 pom.xml
文件中添加 OceanBase JDBC 依赖:
<dependency>
<groupId>com.oceanbase</groupId>
<artifactId>oceanbase-client</artifactId>
<version>2.4.2</version> <!-- 请使用最新版本 -->
</dependency>
如果是手动添加 JAR 包,可以在 OceanBase 官方下载 获取 JDBC 连接驱动。
2. 连接 OceanBase 数据库
OceanBase 兼容 MySQL 和 Oracle 语法,选择合适的 JDBC URL 进行连接:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class OceanBaseJDBC {
private static final String URL = "jdbc:oceanbase://<host>:<port>/<database>";
private static final String USER = "root";
private static final String PASSWORD = "password";
static {
try {
Class.forName("com.oceanbase.jdbc.Driver"); // 加载 OceanBase JDBC 驱动
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
}
URL 示例
- MySQL 模式:
jdbc:oceanbase://127.0.0.1:2881/testdb
- Oracle 模式:
jdbc:oceanbase:oracle://127.0.0.1:2881/testdb
3. 实现增删改查(CRUD)
(1)新增数据(INSERT)
public static void insertData() {
String sql = "INSERT INTO users (id, name, age) VALUES (?, ?, ?)";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, 1);
pstmt.setString(2, "Alice");
pstmt.setInt(3, 25);
pstmt.executeUpdate();
System.out.println("数据插入成功!");
} catch (SQLException e) {
e.printStackTrace();
}
}
(2)查询数据(SELECT)
public static void queryData() {
String sql = "SELECT id, name, age FROM users";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
(3)更新数据(UPDATE)
public static void updateData() {
String sql = "UPDATE users SET age = ? WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, 30);
pstmt.setInt(2, 1);
pstmt.executeUpdate();
System.out.println("数据更新成功!");
} catch (SQLException e) {
e.printStackTrace();
}
}
(4)删除数据(DELETE)
public static void deleteData() {
String sql = "DELETE FROM users WHERE id = ?";
try (Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, 1);
pstmt.executeUpdate();
System.out.println("数据删除成功!");
} catch (SQLException e) {
e.printStackTrace();
}
}
4. 事务处理
public static void transactionExample() {
String insertSql = "INSERT INTO users (id, name, age) VALUES (?, ?, ?)";
String updateSql = "UPDATE users SET age = ? WHERE id = ?";
try (Connection conn = getConnection()) {
conn.setAutoCommit(false); // 开启事务
try (PreparedStatement insertStmt = conn.prepareStatement(insertSql);
PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
// 插入数据
insertStmt.setInt(1, 2);
insertStmt.setString(2, "Bob");
insertStmt.setInt(3, 28);
insertStmt.executeUpdate();
// 更新数据
updateStmt.setInt(1, 35);
updateStmt.setInt(2, 2);
updateStmt.executeUpdate();
conn.commit(); // 提交事务
System.out.println("事务执行成功!");
} catch (SQLException e) {
conn.rollback(); // 事务回滚
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
5. 关闭数据库连接
JDBC 连接建议使用 try-with-resources 语法,自动管理资源,避免连接泄漏。
6. 使用 MyBatis 进行操作
如果希望更方便地操作数据库,可以使用 MyBatis,示例如下:
配置 MyBatis
mybatis-config.xml
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.oceanbase.jdbc.Driver"/>
<property name="url" value="jdbc:oceanbase://127.0.0.1:2881/testdb"/>
<property name="username" value="root"/>
<property name="password" value="password"/>
</dataSource>
</environment>
</environments>
</configuration>
创建 Mapper 接口
public interface UserMapper {
@Insert("INSERT INTO users (id, name, age) VALUES (#{id}, #{name}, #{age})")
void insertUser(User user);
@Select("SELECT * FROM users WHERE id = #{id}")
User getUserById(int id);
@Update("UPDATE users SET age = #{age} WHERE id = #{id}")
void updateUser(User user);
@Delete("DELETE FROM users WHERE id = #{id}")
void deleteUser(int id);
}
调用 MyBatis
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("mybatis-config.xml"));
try (SqlSession session = sqlSessionFactory.openSession()) {
UserMapper mapper = session.getMapper(UserMapper.class);
mapper.insertUser(new User(1, "Alice", 25));
session.commit();
}
7. 总结
- 连接 OceanBase 需要
oceanbase-client
JDBC 驱动。 - 使用 JDBC 进行增删改查(CRUD):
PreparedStatement
避免 SQL 注入- 事务处理 提高数据一致性
- 可以用 MyBatis 进一步优化数据库操作,提高开发效率。
这样,Java 代码就可以顺利连接 OceanBase 并进行数据操作了!🎉