java操作Oracle 方式三 ( 全局一个连接，当操作时发现连接断开了，则再次连接，单线程 )
全局一个连接，当操作时发现连接断开了，则再次连接，单线程
这种方式好处是，全局一个连接，不会每次都发启连接，适用于某一时刻，频繁操作数据库，如：每晚同步数据
OracleUtil.java 基础类代码 详见：https://www.cnblogs.com/hailexuexi/p/18302732
完整代码
dbCdrOneConnect.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 java.sql.Statement; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class dbCdrOneConnect { protected static final Logger logger = LoggerFactory.getLogger(dbCDR.class); //连接对象 Connection connection=null; //创建预编译对象 PreparedStatement ps=null; //创建结果集 ResultSet rs = null; //CDR 参数 private String _eventJson; public dbCdrOneConnect(){ if (OracleUtil.oracle_open.equals("true") == false) { logger.warn("dbCDR insert() CDR 没有启动连接数据库 " + OracleUtil.oracle_open + " " + _eventJson); return; } connection = OracleUtil.getCon();//连接对象 } //插入 public int insert(String eventJson) { _eventJson=eventJson; 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(); //工号 String agentname = ""; if(jsonObj.containsKey("agentname")==true){ agentname=jsonObj.get("agentname").toString(); } //唯一码 String sessionid = "";// if(jsonObj.containsKey("sessionid")==true){ sessionid=jsonObj.get("sessionid").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,agentname,sessionid ) "; sql = sql + " values(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,? )"; //加上若已关闭了连接，则重新连接-------------------------- if(connection.isClosed()==true){ connection.commit(); //java.sql.SQLRecoverableException: ORA-01089: 正在执行立即关闭 - 不允许进行任何操作 logger.info("dbCdrOneConnect insert() 若已关闭了连接，则重新连接！ " + result + " "+ callid+ " "+callfrom+ " " + 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("dbCdrOneConnect insert() CDR 执行SQL出错！ " + result + " "+ callid+ " "+callfrom+ " " + sql + " " + e.toString()); //加入 若执行出错 则关闭 连接------------- try{ OracleUtil.closeCon(connection); } catch (SQLException exSql) { logger.error("dbCdrOneConnect insert() CDR 执行SQL后 关闭 OracleUtil.closeCon 时出错！A " + result + " "+ callid+ " "+callfrom+ " " + sql + " " + exSql.toString()); } //------------------------------------------------- return result; } finally { try { if (ps != null) { ps.close();//ps需要关闭，不然会出现 java.sql.SQLException: ORA-01000: 超出打开游标的最大数 } } catch (SQLException ex) { logger.error("dbCdrOneConnect insert() CDR 执行SQL后 关闭ps时出错！ " + result + " "+ callid+ " "+callfrom+ " " + sql + " " + ex.toString()); //加入 若执行出错 则关闭 连接---------------- try{ OracleUtil.closeCon(connection);//gx230519 加入 若执行出错 则关闭 连接 } catch (SQLException exSql) { logger.error("dbCdrOneConnect insert() CDR 执行SQL后 关闭 OracleUtil.closeCon 时出错！ B " + result + " "+ callid+ " "+callfrom+ " " + sql + " " + exSql.toString()); } //--------------------------------------------------- return result; } } logger.debug("dbCdrOneConnect insert() CDR 执行SQL成功！" + result + " "+ callid+ " "+callfrom+ " " + sql); return result; } //插入 public int updateHX(String eventJson) { int result=0; Statement stmt=null; String updateSql = ""; try { //解析json参数 JSONObject jsonObj = JSONObject.parseObject(this._eventJson);// String type = "";// if(jsonObj.containsKey("type")==true){ type=jsonObj.get("type").toString(); }else{ logger.error("dbCdrOneConnect updateHX() eventJson中 不存在type键值 " + " " + " " +eventJson); } String recordFile = "";// if(jsonObj.containsKey("recording")==true){ recordFile=jsonObj.get("recording").toString(); }else{ logger.error("dbCdrOneConnect updateHX() eventJson中 不存在recording键值 " + " " + " " +eventJson); } String sessionId = "";// if(jsonObj.containsKey("sessionid")==true){ sessionId=jsonObj.get("sessionid").toString(); }else{ logger.error("dbCdrOneConnect updateHX() eventJson中 不存在sessionid键值 " + " " + " " +eventJson); } // 执行更新操作 if(type.equals("Outbound")==true){ //outbound 呼出 updateSql = "UPDATE CTI_CDR SET recordfile = '"+recordFile+"' WHERE sessionid='"+sessionId+"'"; }else if(type.equals("Inbound")==true){ //inbound 呼入 updateSql = "UPDATE CTI_CDR SET recordfile = '"+recordFile+"' WHERE sessionid='"+sessionId+"'"; }else{ logger.error("dbCdrOneConnect updateHX() type不是 Outbound 也不是 Inbound 则不做任何处理 " + " " + " " +eventJson); return -2; } //加上若已关闭了连接，则重新连接----------------------- if(connection.isClosed()==true){ connection.commit(); //java.sql.SQLRecoverableException: ORA-01089: 正在执行立即关闭 - 不允许进行任何操作 logger.info("dbCdrOneConnect updateHX() 若已关闭了连接，则重新连接！ " + " " + updateSql + " " +eventJson); } //------------------------------------------------------ // 创建Statement对象来执行SQL语句 stmt = connection.createStatement(); result = stmt.executeUpdate(updateSql); } catch (SQLException e) { logger.error("dbCdrOneConnect updateHX() 执行SQL出错！" + " " + updateSql + " " +eventJson); } finally { // 关闭Statement和Connection try { if (stmt != null) stmt.close(); } catch (SQLException se) { logger.error("dbCdrOneConnect updateHX() CDR 执行SQL后 关闭 stmt 时出错！A " + updateSql + " "+ eventJson + " " + se.toString()); // 加入 若执行出错 则关闭 连接---------------- try{ OracleUtil.closeCon(connection);// 加入 若执行出错 则关闭 连接 } catch (SQLException exSql) { logger.error("dbCdrOneConnect updateHX() CDR 执行SQL后 关闭 OracleUtil.closeCon 时出错！ B " + updateSql + " "+ eventJson + " " + exSql.toString()); } //--------------------------------------------------- } } return result; } }
调用方法
全局定义
protected static dbCdrOneConnect objCDR2;
在 main函数中 执行
//Oracle 参数 OracleUtil.oracle_open = prop.getProperty("oracle_open"); OracleUtil.oracle_url = prop.getProperty("oracle_url"); OracleUtil.oracle_username = prop.getProperty("oracle_username"); OracleUtil.oracle_password = prop.getProperty("oracle_password"); OracleUtil.oracle_jdbcName = prop.getProperty("oracle_jdbcName"); logger.info("Oracle 参数： " + prop.getProperty("oracle_url") + " -- " + prop.getProperty("oracle_username") + " -- " + prop.getProperty("oracle_password") + " -- " + prop.getProperty("oracle_jdbcName")); if (OracleUtil.oracle_open.equals("true") == false) { logger.warn("没有启动连接 Oracle 数据库 " + OracleUtil.oracle_open + " " ); }else { try { String str = OracleUtil.connectionTest();//连接测试 logger.info("连接测试结果： " + str); logger.info("==========================连接 Oracle 成功！==========================="); } catch (Exception e) { logger.info("连接 Oracle 失败 " + e.toString() + " ==========================="); } } objCDR2 = new dbCdrOneConnect();
当想操作 oracle 时写
int intResult=objCDR2.insert(eventJson); logger.debug("写Oracle结果: "+intResult+" " + strMsg);
