Java操作OceanBase数据库 查询和事务操作

pom.xml中

<dependencies>
    <!-- 加入下面两项 -->
    <!-- mysql -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.28</version>  <!-- 推荐 8.0+ 版本 -->
    </dependency>
    <dependency>
      <groupId>com.zaxxer</groupId>
      <artifactId>HikariCP</artifactId>
      <version>3.4.5</version>
    </dependency>
</dependencies>

创建dbOceanBase文件夹

 创建 OcanBaseUtil.java

package com.JavaOceanBase.dbOceanBase;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * OceanBase 数据库连接工具类（基于 HikariCP 连接池）
 */
public class OceanBaseUtil {
    private static volatile HikariDataSource dataSource;

    // 私有构造方法（禁止外部实例化）
    private OceanBaseUtil() {}

    /**
     * 初始化连接池（线程安全）
     */
    private static void initDataSource() {
        if (dataSource == null) {
            synchronized (OceanBaseUtil.class) {
                if (dataSource == null) {
                    HikariConfig config = new HikariConfig();
                    config.setJdbcUrl("jdbc:mysql://192.168.1.145:2881/callcenter2_db_joincallcc");
                    config.setUsername("root");
                    config.setPassword("YourStrong@Password123");
                    // 优化配置
                    config.setMaximumPoolSize(10);      // 最大连接数
                    config.setConnectionTimeout(5000);   // 连接超时（毫秒）
                    config.setIdleTimeout(60000);        // 空闲超时
                    config.addDataSourceProperty("useSSL", "false");
                    config.addDataSourceProperty("serverTimezone", "UTC");
                    dataSource = new HikariDataSource(config);
                }
            }
        }
    }

    /**
     * 获取数据库连接
     */
    public static Connection getConnection() throws SQLException {
        if (dataSource == null) {
            initDataSource();
        }
        return dataSource.getConnection();
    }

    /**
     * 关闭连接池（应用退出时调用）
     */
    public static void closeDataSource() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
            System.out.println("OceanBase 连接池已关闭");
        }
    }


}

 创建 dbCDR.java

package com.JavaOceanBase.dbOceanBase;

import java.sql.*;
import java.util.UUID;
import com.alibaba.fastjson.JSONObject;

public class dbCDR {

    //查询
    public void select() {
        try (Connection conn = OceanBaseUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("select timestart  ,callfrom  from ls_cdr")) {

            System.out.println("连接 OceanBase 成功！");
            while (rs.next()) {
                System.out.println("电话记录: " + rs.getString("timestart") +"  "+ rs.getString("callfrom") );
            }
        } catch (Exception e) {
            System.err.println("连接操作失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            OceanBaseUtil.closeDataSource(); // 应用结束时调用
        }
    }

    //插入事务
    public void insert(String eventJson) {
        Connection conn = null;
        try {
            //{"event":"NewCdr","callid":"1627349862.433","timestart":"2021-07-27 09:37:42","callfrom":"1008","callto":"1009"
            // ,"callduraction":"16","talkduraction":"11","srctrunkname":"","dsttrcunkname":"","pincode":"","status":"ANSWERED"
            // ,"type":"Internal","callPath":"","recording":"20210727093747-1627349862.433-1008-1009-Internal.wav","didnumber":"","agentringtime":"0"
            // ,"sn":"369264842620" ,"sessionid":"1627349862.433-1008" ,"agentname":"1001"}
            //-----------------------------参数分解---------------------------------------
            String strId = UUID.randomUUID().toString();//唯一码
            JSONObject jsonObj = JSONObject.parseObject(eventJson);//

            String event = jsonObj.get("event").toString();
            String callid = jsonObj.get("callid").toString();
            String timestart = jsonObj.get("timestart").toString();
            String callfrom = jsonObj.get("callfrom").toString();  //主叫号码
            String callto = jsonObj.get("callto").toString();//被叫号码
            //如果有 6703(1008) 时将队列号6703 去掉
            if(callto.contains("(")==true){
                callto = callto.substring(5);  //6703(
                callto = callto.substring(0, callto.indexOf(")"));//去掉 )
            }
            String callduraction = jsonObj.get("callduraction").toString();
            String talkduraction = jsonObj.get("talkduraction").toString();
            String srctrunkname = jsonObj.get("srctrunkname").toString();
            String dsttrcunkname = jsonObj.get("dsttrcunkname").toString();

            String pincode = jsonObj.get("pincode").toString();
            String status = jsonObj.get("status").toString();
            String type = jsonObj.get("type").toString();
            String recording = jsonObj.get("recording").toString();
            String didnumber = jsonObj.get("didnumber").toString();

            String agentringtime = jsonObj.get("agentringtime").toString();
            String sn = jsonObj.get("sn").toString();
            String callPath =  jsonObj.get("callPath").toString();

            String sessionid = "";//240701 改 callidunique 为 sessionid
            if(jsonObj.containsKey("sessionid")==true){
                sessionid=jsonObj.get("sessionid").toString();
            }
            String agentname = "";
            if(jsonObj.containsKey("agentname")==true){
                agentname=jsonObj.get("agentname").toString();
            }
            //---------------------------------------------------------------

            conn = OceanBaseUtil.getConnection();
            conn.setAutoCommit(false); // 开启事务

            // 执行 SQL
            try (PreparedStatement pstmt = conn.prepareStatement(
                    "INSERT INTO ls_cdr (" +
                            "id,event, callid, timestart, callfrom, callto, " +
                            "callduraction, talkduraction, srctrunkname, dsttrcunkname, pincode, " +
                            "status, type, recording, didnumber, " +
                            "agentringtime, sn, callpath, agent_name, sessionid" +
                            ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" )) {

                pstmt.setString(1, strId);
                pstmt.setString(2, event);
                pstmt.setString(3, callid);
                pstmt.setString(4, timestart);
                pstmt.setString(5, callfrom);

                pstmt.setString(6, callto);
                pstmt.setString(7, callduraction);
                pstmt.setString(8, talkduraction);
                pstmt.setString(9, srctrunkname);
                pstmt.setString(10, dsttrcunkname);

                pstmt.setString(11, pincode);
                pstmt.setString(12, status);
                pstmt.setString(13, type);
                pstmt.setString(14, recording);
                pstmt.setString(15, didnumber);

                pstmt.setString(16, agentringtime);
                pstmt.setString(17, sn);
                pstmt.setString(18, callPath);
                pstmt.setString(19, agentname);
                pstmt.setString(20, sessionid);

                pstmt.executeUpdate();
            }

            conn.commit(); // 提交事务
            System.out.println("插入事务执行成功！");
        } catch (Exception e) {
            if (conn != null) {
                try {
                    conn.rollback(); // 回滚事务
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            System.err.println("插入事务失败: " + e.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

}

App.java

package com.JavaOceanBase;
import com.JavaOceanBase.dbOceanBase.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Hello world!
 */
public class App {
    public static void main(String[] args) {
        System.out.println("Hello World!");


        dbCDR myCDR=new dbCDR();

        // 模拟从JSON/事件中获取的数据
        String jsonData = "{\"event\":\"NewCdr\",\"callid\":\"1627349862.433\",\"timestart\":\"2021-07-27 09:37:42\",\"callfrom\":\"1008\",\"callto\":\"1009\"," +
                "\"callduraction\":\"16\",\"talkduraction\":\"11\",\"srctrunkname\":\"\",\"dsttrcunkname\":\"\",\"pincode\":\"\",\"status\":\"ANSWERED\"," +
                "\"type\":\"Internal\",\"callPath\":\"\",\"recording\":\"20210727093747-1627349862.433-1008-1009-Internal.wav\",\"didnumber\":\"\",\"agentringtime\":\"0\"," +
                "\"sn\":\"369264842620\"}";
        myCDR.insert(jsonData);//插入一条记录

        //myCDR.select();


    }
}

执行插入事务的效果

 执行查询的效果