📄 crbtscandb.java
字号:
package com.wireless.crbt.gwif.function;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Vector;
import org.apache.commons.dbcp.SQLNestedException;
import com.wireless.crbt.gwif.global.CRBT;
import com.wireless.crbt.gwif.global.CrbtGWIFGlobal;
import com.wireless.crbt.gwif.global.GetCRBTGlobalForUsual;
import com.wireless.crbt.gwif.global.LoggerConstant;
import com.wireless.crbt.gwif.global.Util;
/**
* 该类包含两种功能:扫描数据库以检查是否有未发送的CRBT数据；扫描RespQueue队列大小,进行Resp入库操作。
* @author Administrator
*
*/
public class CrbtScanDB {
/**_TABLENAME = "temp_crbt_send"*/
private static final String _TABLENAME = "temp_crbt_send";
private static final int QUEUEMAXSIZE = 5;//队列大小限制
private static Timer timerCrbt = null;
private static Timer timerResp = null;
private static boolean hasInitFlag=false;
/**stopFlag用于停止saveResp()中可能的不能及时退出while循环*/
private static boolean stopFlag = false;
private static CrbtScanDB _instance = null;
private String startTime="",stopTime="";
public static Vector timeManage=new Vector();
static Connection conn = null;
static Connection connResp = null;
static CrbtDBCommon datacommon=CrbtDBCommon.getInstance();
/**CRBT字段(共23个)**/
private static String remark1; //备注1
private static String remark2; //备注2
private static int sendNum; //发送次数
private static String sendTime;
// private static String respTime;//回复时间
private static String type = "un";//运营商类型
private static int sendtype; //发送类型<!--1 Crbt_Add_Ring/2 Crbt_Close /3 Crbt_Del_Ring/4 Crbt_Get_Password/5 Crbt_Login/
//6 Crbt_Name_Query/7 Crbt_On_Off /8 Crbt_Open/9 Crbt_Password/10 Crbt_Present/11 Crbt_Query2-->
//PDU 头部分 sou_addr_6和des_addr_6目前默认由config绑定,数据库中读出的无效
private static String sou_addr_6;//此域为消息源地址，表示消息发起的原始发起方地址描述字串。/*目前该字段默认由config绑定
private static String des_addr_6;//此域为消息目的地址，表示消息希望到达的终端地址描述字串。/*目前该字段默认由config绑定
private static String seq_no_10;//此域表示一个序列号，使用在异步通信模式下，由消息发起者设定，应答者对应给回此序列号。
//序列号范围：0000000000－9999999999，循环使用。 同步方式下该域保留
//PDU 体部分
private static String craccount_20;
private static String crid_20;
private static String ret1_10;
private static String srcflag_1;
private static String srcIpaddr_20;
private static String srcPrice_4;
private static String ringtype_4;
private static String old_pass_8;
private static String new_pass_8;
private static String receiver_20;
private static String mobile_number_20;
private static String password_8;
private static String oper_id_1;
private static String user_type_2;
private static String ringName_40;
/**
* 返回一个CrbtScanDB的静态实例
* @return
*/
public static CrbtScanDB getInstance(){
if(_instance==null)
_instance = new CrbtScanDB();
return _instance;
}
/**扫描数据库，检查是否有未发送的CRBT**/
public static void scan(){
Statement stmt = null;
ResultSet rst = null;
String sql;
int numID = 0;
try {
if(conn==null)
conn = datacommon.POOL.getConnection();
sql = "select * from "+_TABLENAME+" e where e.NUMSendFlag = 1" ;
stmt = conn.createStatement();
rst = stmt.executeQuery(sql);
while(rst.next()){
while(GetCRBTGlobalForUsual.crbtqueue.size() >= QUEUEMAXSIZE){
try {
Thread.sleep(5000);
} catch (InterruptedException e) {}
}
LoggerConstant.log.info("----从数据库中读出CRBT数据...");
remark1 = rst.getString("VC2remark1");
remark2 = rst.getString("VC2remark2");
sendNum = rst.getInt("NUMSendNum");
sendTime = rst.getString("DATsendTime");
type = rst.getString("VC2type");
sendtype = rst.getInt("NUMsendtype");
// sou_addr_6 = rst.getString("VC2sou_addr");
// des_addr_6 = rst.getString("VC2des_addr");
sou_addr_6 = CrbtGWIFGlobal.sou_addr;//目前默认由config绑定,数据库中读出的无效
des_addr_6 = CrbtGWIFGlobal.des_addr;//目前默认由config绑定,数据库中读出的无效
seq_no_10 = rst.getString("VC2seq_no");
craccount_20 = rst.getString("VC2craccount");
crid_20 = rst.getString("VC2crid");
ret1_10 = rst.getString("VC2ret1");
srcflag_1 = rst.getString("VC2srcflag");
srcIpaddr_20 = rst.getString("VC2srcIpaddr");
srcPrice_4 = rst.getString("VC2srcPrice");
ringtype_4 = rst.getString("VC2ringtype");
old_pass_8 = rst.getString("VC2old_pass");
new_pass_8 = rst.getString("VC2new_pass");
receiver_20 = rst.getString("VC2receiver");
mobile_number_20 = rst.getString("VC2mobile_num");
password_8 = rst.getString("VC2password");
oper_id_1 = rst.getString("VC2oper_id");
user_type_2 = rst.getString("VC2user_type");
ringName_40 = rst.getString("VC2ringName");
numID = rst.getInt("NUMID");
seq_no_10 = seq_no_10==null?Util.getSeq_no():
(seq_no_10.length()!=10?Util.getSeq_no(seq_no_10):seq_no_10);
//读取成功后更新数据库
sql = "update "+_TABLENAME+" e set e.NUMSendFlag=0,e.VC2seq_no='"+seq_no_10+
"' where e.NUMID="+numID ;
Statement s = null;
s = conn.createStatement();
s.executeQuery(sql);
LoggerConstant.sqllog.info("SQL - "+sql);
LoggerConstant.log.info("------数据库更新成功");
structCRBT();
LoggerConstant.log.info("------构造CRBT数据成功,插入CRBTQueue");
if(s!=null){
s.close();
s = null;
}
}
}catch(SQLNestedException sq){
if(sq.getMessage().indexOf("pool exhausted")>0)
LoggerConstant.log.error("------无法获得连接池");
else
LoggerConstant.log.error("------数据库更新失败");
sq.printStackTrace();
}catch(SQLException cause){
LoggerConstant.log.info("------数据库更新失败");
if(cause.getMessage().indexOf("unique constraint")>0){//如果出现数据冲突的现象
LoggerConstant.log.error("------Seq_no冲突,将试着生成新的Seq_no");
sql = "update "+_TABLENAME+" e set e.NUMSendFlag=1,e.VC2seq_no=temp_crbt_send_seqno.nextval" +
" where e.NUMID="+numID ;
try {
conn.createStatement().executeQuery(sql);
LoggerConstant.log.info("------生成新的Seq_no完毕！");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}catch (Exception e) {
LoggerConstant.log.error("------数据库更新失败");
e.printStackTrace();
} finally {
if( rst != null ){
try {
rst.close();
rst = null;
}
catch (SQLException ex2) {
}
}
if (stmt != null) {
try {
stmt.close();
stmt = null;
} catch (SQLException ex) {
}
}
if (conn != null) {
try {
conn.close();
conn = null;
}catch (SQLException ex1) {
}
}
}
// return result;
}
/**检测RespQueue，是否有未入库的Resp**/
public static void saveResp(){
// new Thread(){
// public void run(){
Statement stmt = null;
ResultSet rst = null;
String sql;
CRBT temp = null;
try {
if(connResp==null)
connResp = datacommon.POOL.getConnection();
while(GetCRBTGlobalForUsual.respqueue.size()>0&&!stopFlag){
CRBT crbt = (CRBT)GetCRBTGlobalForUsual.respqueue.removeNoWait();
temp = crbt;
LoggerConstant.log.info("------从RespQueue队列获取CRBT_Resp成功");
/** 以下判断该CRBT是否是由Socket接收到的Test_CRBT,
* 如果是由Socket接收到的Test_CRBT则进行入库,同时在NUMSENDFLAG字段置1,准备由Scan扫描发送*/
String remark1 = crbt.getRemark1();//判断是否是由Socket接收的Test信息
if (remark1.equalsIgnoreCase("TEST")) {
LoggerConstant.sqllog.info("INFO - 由Socket接收到的CRBT数据先执行入库操作");
sql = "insert into " +_TABLENAME+
" (NUMSENDFLAG,VC2TYPE,NUMSENDTYPE,NUMSENDNUM,DATSENDTIME,VC2SOU_ADDR,"+
"VC2DES_ADDR,VC2SEQ_NO,VC2CRACCOUNT,VC2PASSWORD,VC2CRID,VC2SRCFLAG," +
"VC2SRCIPADDR,VC2RINGTYPE,VC2OLD_PASS,VC2NEW_PASS,VC2RECEIVER,VC2MOBILE_NUM," +
"VC2OPER_ID,VC2USER_TYPE,VC2RINGNAME,VC2REMARK1,NUMID) " +
"values "
+ "(1,'"+crbt.getType()+"',"+crbt.getSendtype()+","+crbt.getSendNum()+
",to_date('"+crbt.getSendTime()+"','yyyy-mm-dd hh24:mi:ss'),'"+
crbt.getSou_addr_6()+"','"+crbt.getDes_addr_6()+"',temp_crbt_send_seqno.nextval,'"
+crbt.getCraccount_20()+"','"+crbt.getPassword_8()+"','"+crbt.getCrid_20()+
"','"+crbt.getSrcflag_1()+"','"+crbt.getSrcIpaddr_20()+"','"+crbt.getRingtype_4()+
"','"+crbt.getOld_pass_8()+"','"+crbt.getNew_pass_8()+"','"+crbt.getReceiver_20()+
"','"+crbt.getMobile_number_20()+"','"+crbt.getOper_id_1()+"','"
+crbt.getUser_type_2()+"','"+crbt.getRingName_40()+"','" +
crbt.getRemark1()+"',temp_crbt_send_seq.nextval) ";
LoggerConstant.sqllog.info("SQL - "+sql);
connResp.createStatement().executeQuery(sql);
LoggerConstant.sqllog.info("INFO - 接收到的CRBT数据入库成功！");
break;
}
String seq_no_10_resp = crbt.getSeq_no_10();
String command_status_resp_4 = crbt.getCommand_status_resp_4()==null?"":
crbt.getCommand_status_resp_4();
String Status_resp_2 = crbt.getStatus_resp_2()==null?"":
crbt.getStatus_resp_2();
String Craccount_resp_20 = crbt.getCraccount_resp_20()==null?"":
crbt.getCraccount_resp_20();
String Password_resp_8 = crbt.getPassword_resp_8()==null?"":
crbt.getPassword_resp_8();
String Totalnum_resp_4 = crbt.getTotalnum_resp_4()==null?"":
crbt.getTotalnum_resp_4();
String Crid_resp_200 = crbt.getCrid_resp_200()==null?"":
crbt.getCrid_resp_200();
String respTime = crbt.getRespTime()==null||crbt.getRespTime()==""?
Util.getCurrentTime():crbt.getRespTime();
/** 以下判断数据库中是否有相应的Seq_no,有则执行update操作,无则insert*/
boolean hasSeqFlag = false;
sql = "select e.VC2seq_no from "+_TABLENAME+" e";
stmt = connResp.createStatement();
rst = stmt.executeQuery(sql);
while(rst.next()){
String seq_no = rst.getString("VC2seq_no");
if(seq_no.equalsIgnoreCase(seq_no_10_resp)){//判断该seq_no_10_resp是否在数据库中有对应项
hasSeqFlag = true;//有则执行update，无则执行insert
rst.close();
rst = null;
break;
}
}
if(hasSeqFlag){
LoggerConstant.sqllog.info("INFO - 找到匹配的seq_no,将执行update操作");
sql = "update "
+ _TABLENAME
+ " e set"
+ " e.VC2cmd_stu_resp='"
+ command_status_resp_4
+ "', e.VC2Status_resp='"
+ Status_resp_2
+ "', e.VC2Crac_resp='"
+ Craccount_resp_20
+ "', e.VC2Pass_resp='"
+ Password_resp_8
+ "', e.NUMTotal_resp="
+ Totalnum_resp_4
+ " ,e.VC2Crid_resp='"
+ Crid_resp_200
+ "', e.DATrespTime=to_date('"
+ respTime
+ "','yyyy-mm-dd HH24:mi:ss') where e.VC2seq_no="
+ seq_no_10_resp;
}else{
LoggerConstant.sqllog.info("INFO - 没有找到匹配的seq_no,将执行insert操作");
sql = "insert into "
+ _TABLENAME
+ " (VC2cmd_stu_resp,VC2Status_resp,VC2Crac_resp,"
+ "VC2Pass_resp,NUMTotal_resp,VC2Crid_resp,DATrespTime,VC2seq_no,NUMID) "
+ "values ('" + command_status_resp_4
+ "','" + Status_resp_2 + "','"
+ Craccount_resp_20 + "','"
+ Password_resp_8 + "','"
+ Totalnum_resp_4 + "','"
+ Crid_resp_200 + "',to_date('"
+ respTime
+ "','yyyy-mm-dd HH24:mi:ss'),'"
+ seq_no_10_resp
+ "',temp_crbt_send_seq.nextval) ";
}
LoggerConstant.sqllog.info("SQL - "+sql);
stmt = connResp.createStatement();
rst = stmt.executeQuery(sql);
rst.close();
rst = null;
LoggerConstant.log.info("------Resp插入数据库成功");
}
//数据冲突的可能在这里可能有,比如Scan更新数据的时候发生数据冲突将试着变更Seq_no,而同时这里也insert操作就有可能引起数据冲突的现象！
//但是这里的重新插入的功能能够实现最终成功插入
}catch (Exception e) {
LoggerConstant.log.info("------Resp插入数据库失败");
if(temp!=null)
GetCRBTGlobalForUsual.getInstance().addResp(temp);
e.printStackTrace();
} finally {
if( rst != null ){
try {
rst.close();
rst = null;
}
catch (SQLException ex2) {
}
}
if (stmt != null) {
try {
stmt.close();
stmt = null;
} catch (SQLException ex) {
}
}
if (connResp != null) {
try {
connResp.close();
connResp = null;
}catch (SQLException ex1) {
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -