java 连接 达梦数据库 新增和查询

java连接 达梦数据库

第一步是加载 达梦数据库的驱动

从达梦官网下载驱动 https://eco.dameng.com/download/

具体方法详看：https://www.cnblogs.com/hailexuexi/p/19010577

建表sql语句

CREATE TABLE "CTI_CDR" 
   (    
   "ID" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    "EVENT" VARCHAR2(20 BYTE), 
    "CALLID" VARCHAR2(50 BYTE), 
    "TIMESTART" VARCHAR2(20 BYTE), 
    "CALLFROM" VARCHAR2(20 BYTE), 
    "CALLTO" VARCHAR2(20 BYTE), 
    "CALLDURACTION" VARCHAR2(20 BYTE), 
    "TALKDURACTION" VARCHAR2(20 BYTE), 
    "SRCTRUNKNAME" VARCHAR2(20 BYTE), 
    "DSTTRCUNKNAME" VARCHAR2(20 BYTE), 
    "PINCODE" VARCHAR2(20 BYTE), 
    "STATUS" VARCHAR2(20 BYTE), 
    "TYPE" VARCHAR2(20 BYTE), 
    "RECORDING" VARCHAR2(100 BYTE), 
    "DIDNUMBER" VARCHAR2(20 BYTE), 
    "AGENTRINGTIME" VARCHAR2(20 BYTE), 
    "SN" VARCHAR2(20 BYTE), 
    "CALLPATH" VARCHAR2(20 BYTE), 
    "AGENTNAME" VARCHAR2(20 BYTE), 
    "CHID1" VARCHAR2(20 BYTE), 
    "CHID2" VARCHAR2(20 BYTE), 
    "TIMESTOP" VARCHAR2(20 BYTE), 
    "TIMELEN" VARCHAR2(20 BYTE), 
    "CALLRESULT" VARCHAR2(20 BYTE), 
    "DTMF" VARCHAR2(50 BYTE), 
    "CALLIDUNIQUE" VARCHAR2(50 BYTE), 
     CONSTRAINT "CTI_CDR_PK" PRIMARY KEY ("ID")
 
   )

完整代码

DamengUtil.java    达梦数据库操作工具类

package com.DamengDB.dbDameng;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * 达梦数据库操作工具类，提供连接池管理、查询和插入功能
 */

public class DamengUtil {
    private static final Logger logger = LoggerFactory.getLogger(DamengUtil.class);
    private static final String JDBC_DRIVER = "dm.jdbc.driver.DmDriver";

    // 数据库连接信息，需要根据实际情况修改
    ///private static final String DB_URL = "jdbc:dm://192.168.1.159:5236/DMSERVER";
    private static final String DB_URL = "jdbc:dm://192.168.1.159:5236";
    private static final String USER = "SYSDBA";
    private static final String PASS = "Dameng123!";

    // 连接池配置
    private static final int INITIAL_POOL_SIZE = 5;
    private static final int MAX_POOL_SIZE = 20;
    private static final ExecutorService executor = Executors.newFixedThreadPool(5);
    private static final List<Connection> connectionPool = new ArrayList<>();
    private static final List<Connection> usedConnections = new ArrayList<>();

    static {
        try {
            // 加载驱动类
            Class.forName(JDBC_DRIVER);
            // 初始化连接池
            for (int i = 0; i < INITIAL_POOL_SIZE; i++) {
                connectionPool.add(createConnection());
            }
            logger.info("达梦数据库连接池初始化完成，初始连接数: {}", INITIAL_POOL_SIZE);
        } catch (ClassNotFoundException e) {
            logger.error("加载达梦数据库驱动失败", e);
            throw new ExceptionInInitializerError("无法加载达梦数据库驱动: " + e.getMessage());
        } catch (SQLException e) {
            logger.error("初始化数据库连接失败", e);
            throw new ExceptionInInitializerError("初始化数据库连接失败: " + e.getMessage());
        }
    }

    /**
     * 创建新的数据库连接
     */
    private static Connection createConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, USER, PASS);
    }

    /**
     * 从连接池获取连接
     */
    public static synchronized Connection getConnection() throws SQLException {
        if (!connectionPool.isEmpty()) {
            Connection conn = connectionPool.remove(connectionPool.size() - 1);
            if (conn.isClosed()) {
                // 如果连接已关闭，递归获取新连接
                return getConnection();
            }
            usedConnections.add(conn);
            return conn;
        }

        // 连接池为空但未达到最大连接数，创建新连接
        if (usedConnections.size() < MAX_POOL_SIZE) {
            Connection conn = createConnection();
            usedConnections.add(conn);
            return conn;
        }

        // 已达到最大连接数
        throw new SQLException("无法获取连接：已达到最大连接数限制(" + MAX_POOL_SIZE + ")");
    }

    /**
     * 归还连接到连接池
     */
    public static synchronized void releaseConnection(Connection conn) {
        if (conn != null) {
            usedConnections.remove(conn);
            try {
                if (!conn.isClosed()) {
                    connectionPool.add(conn);
                }
            } catch (SQLException e) {
                logger.error("检查连接状态失败", e);
                closeConnection(conn);
            }
        }
    }

    /**
     * 关闭连接
     */
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
                logger.debug("数据库连接已关闭");
            } catch (SQLException e) {
                logger.error("关闭数据库连接失败", e);
            }
        }
    }

    /**
     * 关闭Statement
     */
    public static void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                logger.error("关闭Statement失败", e);
            }
        }
    }

    /**
     * 关闭ResultSet
     */
    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                logger.error("关闭ResultSet失败", e);
            }
        }
    }

    /**
     * 执行查询SQL，返回结果集
     */
    public static List<Map<String, Object>> executeQuery(String sql, Object... params) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);

            // 设置查询参数
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }

            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 处理结果集
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    row.put(metaData.getColumnName(i), rs.getObject(i));
                }
                resultList.add(row);
            }

            logger.debug("查询SQL执行成功: {}", sql);
        } catch (SQLException e) {
            logger.error("执行查询SQL失败: {}", sql, e);
        } finally {
            closeResultSet(rs);
            closeStatement(pstmt);
            releaseConnection(conn);
        }

        return resultList;
    }

    /**
     * 执行插入SQL，返回受影响的行数
     */
    public static int executeInsert(String sql, Object... params) {
        int rows = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);

            // 设置插入参数
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }

            rows = pstmt.executeUpdate();
            // 构建完整SQL（仅用于日志，不可直接执行！）
            String fullSql = buildCompleteSql(sql, params);
            logger.debug("插入SQL执行成功，受影响行数: {}, SQL: {}", rows, fullSql);
        } catch (SQLException e) {
            // 构建完整SQL（仅用于日志，不可直接执行！）
            String fullSql = buildCompleteSql(sql, params);
            logger.error("执行插入SQL失败: {}", fullSql, e);
        } finally {
            closeStatement(pstmt);
            releaseConnection(conn);
        }

        return rows;
    }

    /**
     * 异步执行插入操作
     */
    public static void executeInsertAsync(String sql, Object... params) {
        //executor.submit(() -> executeInsert(sql, params));
    }

    /**
     * 关闭连接池和线程池
     */
    public static void shutdown() {
        // 关闭所有连接
        for (Connection conn : connectionPool) {
            closeConnection(conn);
        }
        for (Connection conn : usedConnections) {
            closeConnection(conn);
        }
        connectionPool.clear();
        usedConnections.clear();

        // 关闭线程池
        executor.shutdown();
        logger.info("达梦数据库连接池和线程池已关闭");
    }

    // 手动拼接SQL（注意转义防止注入）
    private static String buildCompleteSql(String sql, Object[] params) {
        //System.out.println("buildCompleteSql 1 -------------------");
        if (params == null || params.length == 0) return sql;

        String[] parts = sql.split("\\?");
        StringBuilder fullSql = new StringBuilder();
        //System.out.println("buildCompleteSql 2 -------------------");
        for (int i = 0; i < parts.length; i++) {
            fullSql.append(parts[i]);
            if (i < params.length) {
                // 对字符串类型添加引号并转义
                fullSql.append(formatParam(params[i]));
            }
            //System.out.println("buildCompleteSql 3 --------------- " + i);
        }
        return fullSql.toString();
    }

    private static String formatParam(Object param) {
        if (param == null) return "NULL";
        if (param instanceof String) {
            return "'" + param.toString().replace("'", "''") + "'"; // 转义单引号
        }
        //if (param instanceof java.util.Date) {
        //    return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(param) + "'";
        //}
        return param.toString();
    }

}

dbCDR.java     CDR表的实体类

package com.DamengDB.dbDameng;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.UUID;
import com.alibaba.fastjson.JSONObject;

public class dbCDR {
    protected static final Logger logger = LoggerFactory.getLogger(dbCDR.class);

    //CDR 参数
    private String _eventJson;

    public dbCDR(String eventJson){
        _eventJson=eventJson;
    }

    //插入
    public int insert() {
        logger.debug("dbCDR insert() CDR 准备执行SQL" + this._eventJson);
        int result = 0;
        String sql = "";
        String callid ="";
        String callfrom ="";

        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"}
            String strId = UUID.randomUUID().toString();//唯一码
            JSONObject jsonObj = JSONObject.parseObject(this._eventJson);//

            String event = jsonObj.get("event").toString();
            callid = jsonObj.get("callid").toString();
            String timestart = jsonObj.get("timestart").toString();
            //主叫号码
            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();

            sql = "insert into CTI_CDR ";
            sql = sql + " ( ID,EVENT,CALLID,TIMESTART,CALLFROM ";
            sql = sql + " ,CALLTO,CALLDURACTION,TALKDURACTION,SRCTRUNKNAME,DSTTRCUNKNAME ";
            sql = sql + " ,PINCODE,STATUS,TYPE,RECORDING,DIDNUMBER ";
            sql = sql + " ,AGENTRINGTIME,SN,CALLPATH ) ";
            sql = sql + " values(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,? )";

            //参数
            Object[] params = {
                    strId,event, callid,timestart,callfrom
                    ,callto, callduraction,talkduraction,srctrunkname,dsttrcunkname
                    ,pincode, status,type, recording,didnumber
                    ,agentringtime, sn, callPath
            };

            //logger.debug("dbCDR insert() CDR 执行SQL！" + ps.toString()  );
            result = DamengUtil.executeInsert(sql, params);
            //logger.debug("dbCDR insert() CDR 执行SQL成功！" + result + " "+ callid+ " "+callfrom+ " " + sql);

        } catch (Exception e) {
            e.printStackTrace();
            logger.error("dbCDR insert() CDR 执行SQL出错！ " + result + " " + callid + " " + callfrom + " " + sql + " " + e.toString());
        }
        return result;
    }

}

App.java   主函数 调用示例

package com.DamengDB;

import com.DamengDB.dbDameng.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;
import java.util.Map;

/**
 * Hello world!
 */
public class App {
    protected static final Logger logger = LoggerFactory.getLogger(App.class);
    protected static dbCDR  objCDR;

    public static void main(String[] args) {
        System.out.println("Hello World!=================");

        //示例 查询服务名 ------------------------------------------------------------------
        List<Map<String, Object>> result = DamengUtil.executeQuery("SELECT INSTANCE_NAME FROM V$INSTANCE WHERE 1 = ?", 1);
        for (int i = 0; i < result.size(); i++) {
            Map<String, Object> row = result.get(i);
            for (Map.Entry<String, Object> entry : row.entrySet()) {
                String columnName = entry.getKey();
                Object value = entry.getValue();
                System.out.println(columnName + ": " + value);
            }
            System.out.println("-------------------");
        }
        //------------------------------------------------------------------------------
 
        //示例 向CDR表插入一条记录
        String strMsg="<dataBase>{\"callid\":\"1635822668.528\",\"callto\":\"6700(1008)\",\"pincode\":\"\",\"srctrunkname\":\"MNWG\",\"recording\":\"20211102111141-1635822668.528-13941128270-1008-Inbound.wav\",\"dsttrcunkname\":\"\",\"type\":\"Inbound\",\"callPath\":\"6700\",\"callduraction\":\"15\",\"callfrom\":\"13941128270\",\"didnumber\":\"123456\",\"agentringtime\":\"8\",\"sn\":\"369264842620\",\"event\":\"NewCdr\",\"timestart\":\"2021-11-02 11:11:41\",\"talkduraction\":\"7\",\"status\":\"ANSWERED\"}";
        String eventJson = strMsg.replace("<dataBase>", "");

        dbCDR objCDR = new dbCDR(eventJson);
        int intResult=objCDR.insert();

        logger.debug("写达梦数据库结果: "+intResult+" " + strMsg);

    }
}

pom.xml

      <!--  达梦数据库 -->
      <dependency>
        <groupId>com.dameng</groupId>
        <artifactId>DmJdbcDriver18</artifactId>
        <version>8.1.2.14</version>  
      </dependency>
      <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>1.7.21</version>
      </dependency>
      <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.47</version>
      </dependency>