在 Java 中连接 OceanBase 数据库并进行增删改查(CRUD)操作,一般使用 JDBCMyBatis 进行数据库交互。以下是完整的步骤和示例代码:

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 并进行数据操作了!🎉