java操作Oracle 方式一 ( 連線-》操作-》斷開連線 )

海乐学习發表於2024-07-15
JavaOracle

連線-》操作-》斷開連線

這種方式的特點是 每次都是新的連線，單執行緒，缺點是 網路環境不好時連線oracle比較費時。

OracleUtil.java 基礎類程式碼 詳見：https://www.cnblogs.com/hailexuexi/p/18302732

完整程式碼

dbCDR.java

package com.JavaRabbitMQToDataBase.dbOracle;

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

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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

public class dbCDR  {
    protected static final Logger logger = LoggerFactory.getLogger(dbCDR.class);
    //連線物件
    Connection connection=null;
    //建立預編譯物件
    PreparedStatement ps=null;
    //建立結果集
    ResultSet rs = null;
    //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 ="";
        if (OracleUtil.oracle_open.equals("true") == false) {
            logger.warn("dbCDR insert() CDR 沒有啟動連線資料庫 " + OracleUtil.oracle_open + " " + _eventJson);
            return -2;
        }
        try {
            connection = OracleUtil.getCon();//連線物件

            //{"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();

            String sessionid = "";// 
            if(jsonObj.containsKey("sessionid")==true){
                sessionid=jsonObj.get("sessionid").toString();
            }
            String agentname = "";
            if(jsonObj.containsKey("agentname")==true){
                agentname=jsonObj.get("agentname").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 ,agent_name,sessionid) ";
            sql = sql + " values(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?  )";

            logger.error("dbCDR insert() CDR 準備執行SQL  " + sql + " " );

            ps = connection.prepareStatement(sql);
            ps.setString(1, strId);
            ps.setString(2, event);
            ps.setString(3, callid);
            ps.setString(4, timestart);
            ps.setString(5, callfrom);

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

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

            ps.setString(16, agentringtime);
            ps.setString(17, sn);
            ps.setString(18, callPath);

            ps.setString(19, agentname);
            ps.setString(20, sessionid);
            //logger.debug("dbCDR insert() CDR 執行SQL！" + ps.toString()  );

            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("dbCDR insert() CDR 執行SQL出錯！ " + result + " "+ callid+ " "+callfrom+  " " + sql + " " + e.toString());
        } finally {
            try {
                if (ps != null) { // 
                    ps.close();////ps需要關閉，不然會出現 java.sql.SQLException: ORA-01000: 超出開啟遊標的最大數
                }
                OracleUtil.closeCon(connection);
            } catch (SQLException e) {
                e.printStackTrace();
                logger.error("dbCDR insert() CDR 關閉連線出錯！" + result + " "+ callid+ " "+callfrom+ " " + " " + sql + " " + e.toString());
            }
        }
        logger.debug("dbCDR insert() CDR 執行SQL成功！" + result + " "+ callid+ " "+callfrom+ " " + sql);
        return result;
    }
    
    //查詢
    public void select(){
        connection = OracleUtil.getCon();
        String sql = "select ID,AGENT_NAME,DATE_TIME,STATUS from CTI_AGENT_WORK_REST";
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt(1);
                String AGENT_NAME = rs.getString(2);
                String DATE_TIME = rs.getString(3);
                String STATUS= rs.getString(4);
                System.out.println("ID:"+id + " AGENT_NAME:"+AGENT_NAME+" DATE_TIME:"+DATE_TIME+" STATUS:"+STATUS);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                OracleUtil.closeCon(connection);
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //修改
    public int update(){
        connection = OracleUtil.getCon();
        String sql = "update student set name = ?,gender = ? where id = ?";
        int result = 0;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,"老張");
            ps.setString(2,"男");
            ps.setInt(3,2);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                OracleUtil.closeCon(connection);
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return result;

    }

    //刪除
    public int delete(){
        int result = 0;
        connection = OracleUtil.getCon();
        String sql = "delete from student where id = ?";
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1,1);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                OracleUtil.closeCon(connection);
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }
 
}

呼叫方法

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

相關文章