对XML报文进行全动态解析并动态生成SQL语句

对XML报文进行全动态解析并动态生成SQL语句
/**
     * 2017-03-27 增加对直接传入数据的处理 张明伟
     * 如果是DOC,则每次只传入一个
     * 放入返回报文和原报文键值对
     * @param filename
     * @return
     */
    public HashMap<String,String> parserXml(String filename) {
        String returnValue = null;
        HashMap<String,String> hm=new HashMap<String,String>();//放入返回报文和原报文键值对
        String tb = null,dtb=null,msgid=null,md5str=null; //表,明细表,MSGID声明
        String xmlOrder = null;
        File file = null;
        String filepath = "";
        String longfilename = "";
        String tmpfilename = "",tmpresult="PR00";
        SqlEntityStatment sestmp0 = null,sestmp1=null;
        SqlEntityStatment[] sestmps1=null, sestmps2=null;
        String msgtype=null;
        String workdate=null;
        Document doc = null;
        String sql;
        String msghead=schemaPrefix1.trim() + "PUB_MSGHEAD";
        String preclassname="com.citic.msgutil.xmlback.Create";
        SAXReader sax = new SAXReader();
        fileFlag = Boolean.parseBoolean(ConfigFileUtil.getValue("fileFlag"));
        //修正
        fileFlag=filename.length()>200?false:true;
        CommFun.log(debuglevel, "fileFlag:" + fileFlag + ",filename:["
                + (fileFlag ? filename : filename.substring(0, 200)) + "]");
        try {
            if (fileFlag) {
                CommFun.log(filename);
                file = new File(filename);
                filepath = file.getParent() + File.separator;
                CommFun.log(INFO, "file:[" + file + "],path:[" + filepath + "]");
                // 因为MQ接收的文件前缘是SUPIS,所以为了和原报文核对,保留原来的名字,除去SUPIS
                // 请参见MQFileReceiver getGroupMessages方法
                tmpfilename = file.getName();
                int len = tmpfilename.length();
                if (len > 9 && !tmpfilename.startsWith("PSIS")) {
                    tmpfilename = tmpfilename.substring(5, len - 4);
                }
                try {
                    doc = sax.read(file);
                    CommFun.log(debuglevel, "文件解析成功装入DOC!");
                } catch (DocumentException e) {
                    CommFun.log(ERR, "解析文件失败:" + file+",尝试使用GBK编码再次解析!");
                    // e.printStackTrace();
                    try {
                        FileInputStream in=new FileInputStream(file);
                        doc = sax.read(new InputStreamReader(in, "GBK"));
                        CommFun.log(debuglevel, "文件以GBK编码解析成功装入DOC!");
                    } catch (DocumentException e1) {
                        CommFun.log(ERR, "解析文件尝试使用GBK编码再次解析失败:" + file+"!");
                        e.printStackTrace();
                        tmpresult = "PR21";
                        throw new RuntimeException(e1);
                    }
                }
            } else {
                try {
                    doc = DocumentHelper.parseText(filename);
                    filepath = ConfigFileUtil.getInstance().getPathName()
                            + "receive" + File.separator;
                    CommFun.log(debuglevel, "文件解析成功转换DOC!");
                } catch (DocumentException e) {
                    CommFun.log(ERR,"解析文件失败,文件前200字:"+filename.substring(0, 200));
                    e.printStackTrace();
                    tmpresult="PR21";
                    throw new RuntimeException(e);
                }
            }

            CommFun.log(debuglevel, "继续文件解析DOC!");
            
            // Document doc = sax.read(new File(filepth + filename));
            // sax.read(filename);
            Element rootElement = doc.getRootElement();
            String nameURI = rootElement.getNamespaceURI();
            HashMap nsMap = new HashMap();
            nsMap.put("ns", nameURI);
            
            msgid = rootElement.element(msgHeader).element("MsgID")
                    .getTextTrim();
            msgtype = rootElement.element(msgHeader).element("MsgType")
                    .getTextTrim();
            xmlOrder = msgtype.substring(4, 7);
            workdate = rootElement.element(msgHeader)
                    .element("Workdate").getTextTrim();
            tb = ConfigFileUtil.getValue("T" + xmlOrder);
            String[] strMsgtbs = { schemaPrefix1.trim() + "PUB_MSGHEAD",
                    schemaPrefix1.trim() + tb };
            dtb =  ConfigFileUtil.getValue("T" + xmlOrder + "D");
            dtb=(dtb==null || "".equals(dtb)?"":schemaPrefix1.trim()+dtb);
            CommFun.log(tb, "============","["+xmlOrder+"]", "============",dtb);

            // System.out.println("workdate:"+workdate);
            longfilename = filepath + "PSIS" + xmlOrder + "_";
            // 非回送报文,只获取报文头内容,否则还需要原报文类型
            if ("900".equals(xmlOrder)) {
                longfilename += rootElement.element(msgBody)
                        .element("OriMsgType").getTextTrim().substring(4, 7)
                        + "_";
            }
            // 通过String来的需要创建规则文件,以保持和原来的兼容
            // 如果传入文件解析,则保留原来的文件名后缀(原文件名为SUPIS.....)
            longfilename += workdate + "_"
                    + (fileFlag ? tmpfilename : CommFun.strNowRand())
                    + "_R.xml";
            //如果传入的是文件名,则需要判断是否是正常的文件名还是加工后的
            //,加工后的就不需要用新文件名了
            //IF.1
            if(tmpfilename.startsWith("PSIS")){
                longfilename=filename;
            }
            //如果数据重复,则不需要进行创建
            //IF.2 与IF.1对应,减少文件创建
            //2017-06-13修改,因为有些支付机构把MSGID做为非唯一值了
            //我们改为msgid和文件的md5值做唯一
            if(!tmpfilename.startsWith("PSIS")){
                XMLUtil.xmlCreate(doc, longfilename);
            }
            
            CommFun.log(debuglevel, longfilename);
            md5str=MD5Util.getMD5String(longfilename);
            //如果没有找到对应表,也需要进行解析,此步应该放到解析前
            sestmp0 = XMLUtil.dispElementshead(doc, nsMap, msgHeader);
            sql = "INSERT INTO " + strMsgtbs[0] + "(" + sestmp0.getSqlfileds()
                    + ",FILENAME,FILEMD5STR)" + " VALUES (" + sestmp0.getSqlstr() + ",‘"
                    + longfilename + "‘,‘"+md5str+"‘)";
            

            // 1.插入头
            CommFun.log(DATA,sql);
            DBOperation.executeSql(sql);
            
            if ("".equals(tb)) {
                String errormsg = "接收文件:[" +longfilename /*+ (fileFlag ? filename : filename.substring(0, 200))*/
                        + "],报文号为:[" + xmlOrder
                        + "]对应配置中对应的表不存在,请配置表名!";
                throw new RuntimeException(errormsg);
            }
            
            
            int xoder=Integer.parseInt(xmlOrder);
            String listtag="TxList";
            //解析中所遇到的List tag需要与上层数据合并操作
            switch (xoder) {
            case 503:
                listtag="DiffList"; // 503报文
                break;
            case 200:
                listtag="ApplyList";
                break;
            default:
                listtag="TxList";
                break;
            }
            sestmp1 = XMLUtil.dispElementshead(doc, nsMap, msgBody);
            sestmps1 = XMLUtil.dispElements1(doc, nsMap, listtag);
            //CommFun.log(ALL,doc.asXML()+"--->"+nsMap.toString()+"--->"+listtag);
            //CommFun.log(ALL,sestmps1.toString());
            String[] bodytables=null;
            //如果无明细数据表,则List和MsgBody混编成一组数据,否则分开存储(PSIS106)
            if("".equals(dtb)){
                //sestmps2中包含了merge后的混编数据,有N*1的关系
                sestmps2 = XMLUtil.mergedata(sestmp1, sestmps1);
                bodytables=new String[sestmps2.length];
                for(int i=0;i<bodytables.length;i++){
                    bodytables[i]=strMsgtbs[1];
                }
            }else{
                int seslen=0;
                if(sestmps1!=null){
                    seslen=sestmps1.length;    
                }
                
                CommFun.log(ALL, "解析出"+listtag+"个数为:"+seslen+"个!");
                //sestmps2中包含了merge后的混合组成数据,有N+1的关系
                sestmps2=new SqlEntityStatment[seslen+1];
                //System.arraycopy(sestmps1, 0, sestmps2, 0, sestmps1.length-1);  //明细表数据
                for(int i=0;i<seslen;i++){
                    sestmps2[i]=sestmps1[i].clone();    
                }
                
                CommFun.log(debuglevel,"sestmps2‘:"+sestmps2.length);
                //PSIS106报文需要在插入明细数据中添加当前包序号
                //20170502改为对所有的明细表都添加CurrentPkg字段
                //if ("106".equals(xmlOrder)) {
                    sestmps2 = XMLUtil.mergedata(new SqlEntityStatment(
                            "CurrentPkg", sestmp1.getValue("CurrentPkg")),
                            sestmps2);
                //}
                sestmps2[seslen]=XMLUtil.mergedata(sestmp1, null)[0]; //插入汇总表
                bodytables=new String[sestmps2.length];
                for(int i=0;i<bodytables.length;i++){
                    bodytables[i]=dtb;
                }
                bodytables[bodytables.length-1]=strMsgtbs[1];
            }

            StringBuffer[] stb = null;
            int sqllimit=30;
            // 2.插入内容,可能是多条,也可以是多条
            if (sestmps2 != null && sestmps2.length >= 1) {
                stb = new StringBuffer[sestmps2.length];
                // 多记录的msgbody
                // 多条记录防止频繁对数据库进行操作,插入条数定为30
                int cycle = (int) Math.ceil(sestmps2.length*1.0 / sqllimit);
                int icycle=0;
                int remainder = sestmps2.length % sqllimit;
                //对于正好整除的情况,最后一次是sqllimit的个数
                remainder = (remainder == 0 && cycle * sqllimit == sestmps2.length) ? sqllimit
                        : remainder;
                CommFun.log(INFO, "总数量为:"+sestmps2.length+",批量总执行次数为:"+cycle+"次,"+sqllimit+"/次"+",最后一次条数为:"+remainder);
                String[] sqlstr=null;
                int j=0;
                for (int i = 0; i < sestmps2.length && icycle<cycle; i++,j++) {
                    //分批执行,减少提交数据库的次数,一批SQL为30次
                    if(i%sqllimit==0){
                        sqlstr=null;
                        sqlstr=new String[(i>=(cycle-1)*sqllimit)?remainder:sqllimit];
                        j=0;
                    }
                    sqlstr[j]="INSERT INTO " + bodytables[i] + "(MSGID,DATADATE,"
                            + sestmps2[i].getSqlfileds() + ")" + " VALUES (‘"
                            + msgid + "‘,‘" + workdate + "‘,"
                            + sestmps2[i].getSqlstr() + ")";
                    //CommFun.log(DATA,"sqlstr["+sestmps2.length+":"+(icycle+1)+":"+(i+1)+":"+(j+1)+"]:"+sqlstr[j]);
                    //节约日志时间及空间,不再写,如果需要写,请直接把0改为DATA
                    CommFun.log(0,"sqlstr["+sestmps2.length+":"+(icycle+1)+":"+(i+1)+":"+(j+1)+"]:"+sqlstr[j]);
                    
                    //执行批量插入
                    //如果
                    /*if ((i + 1) % sqllimit == 0
                            || ((i >= (cycle - 1) * sqllimit) && (i + 1)
                                    % remainder == 0))*/
                    if ((j + 1) % sqllimit == 0
                            || ((i >= (cycle - 1) * sqllimit) && (j + 1)
                                    % remainder == 0)) {
                        CommFun.log(debuglevel, "调用批量处理SQL,i‘s value:["
                                + (i + 1) + "] and sqlstr.length:"
                                + sqlstr.length);
                        DBOperation.executeBatchSql(sqlstr);
                        icycle++;
                    }
                }
                
                /*
                for (int i = 0; i < sestmps2.length; i++) {
                    //分批执行,减少提交数据库的次数,一批SQL为30次
                    sql = "INSERT INTO " + strMsgtbs[1] + "(MSGID,DATADATE,"
                            + sestmps2[i].getSqlfileds() + ")" + " VALUES (‘"
                            + msgid + "‘,‘" + workdate + "‘,"
                            + sestmps2[i].getSqlstr() + ")";
                    CommFun.log(sql);
                    DBOperation.executeSql(sql);
                }
                 */
                
            }/* else {
                // 单记录的msgbody
                sql = "INSERT INTO " + strMsgtbs[1] + "(MSGID,DATADATE,"
                        + sestmp1.getSqlfileds() + ")" + " VALUES (‘" + msgid
                        + "‘,‘" + workdate + "‘," + sestmp1.getSqlstr() + ")";
                CommFun.log(sql);
                DBOperation.executeSql(sql);
            }*/

            // 如果是901日切报文,则对日切进行更新
            if ("901".equals(xmlOrder)) {
                CommFun.log(INFO, "处理日切!");
                DBOperation.setWorkdate();
            }
            /** 执行成功,需返回1 */
            tmpresult="PR00";
            sql="update "+msghead
                    +" set FILEDELSTAT = ‘"+"文件处理完毕"
                    +"‘ where msgid=‘"+msgid+"‘ and FILEMD5STR=‘"+md5str+"‘";
        } catch(SQLException se){
            CommFun.log(debuglevel);
            se.printStackTrace();
            CommFun.log(debuglevel,
                    "" + se.getErrorCode() + ",--" + se.getSQLState());
            if (se.getErrorCode() == -803) {
                CommFun.log(debuglevel, se.getMessage());
                tmpresult="MSGID:"+msgid+"的报文数据重复";
                tmpresult="PR40";
            } else {
                tmpresult=se.getMessage();
                tmpresult="PR41";
//                throw new RuntimeException(se);
            }
            sql="update "+msghead
                    +" set FILEDELSTAT = ‘文件处理出错:SQLCODE:"+se.getErrorCode() /*+",SQLMESSAGE:"+se.getMessage()*/
                    +"‘ where msgid=‘"+msgid+"‘ and FILEMD5STR=‘"+md5str+"‘";
        } catch (Exception e) {
            e.printStackTrace();
            CommFun.log(ERR, e.getStackTrace().toString());
            CommFun.log(ERR, e.getMessage());
            sql="update "+msghead
                    +" set FILEDELSTAT = ‘"+"文件处理其它错:"+e.getMessage()
                    +"‘ where msgid=‘"+msgid+"‘ and FILEMD5STR=‘"+md5str+"‘";
//            throw new RuntimeException(e);
        } finally{

        }
        CommFun.log(DATA, sql);
        try {
            DBOperation.executeSql(sql);
        } catch (SQLException e) {
            CommFun.log(ERR, e.getStackTrace().toString());
            e.printStackTrace();
        }
        //需要回复的报文,才会做前后及900报文处理
        //如100报文,则只需要入库后对入库数据进行校验,并根据数据是否存在返回900报文(100报文不需要事后处理)
        //如803报文,则只需要入库后根据操作类型或变更类型进行后续处理,并返回900报文(不需要事前处理)
        //20170425针对批量数据进行处理,可能在preexecute返回多条记录,生成900报文
        CommFun.log(debuglevel, "xmlOrder:"+xmlOrder+","+Errors.getvalue("PSIS"+xmlOrder));
        if (Errors.getvalue("PSIS"+xmlOrder).startsWith("Y") && !"".equals(tb)) {
            //0.对数据入库前进行处理,如果有返回且不为空,CODE和MESSAGE可以做依据
            HashMap<String,String[]> hm1=ReflectUtil.preexecute((Object)preclassname+xmlOrder+"xml",xmlOrder, msgid);
            String rettext[]=null;
            if (hm1 != null && hm1.size() > 0) {
                String t1=(String) hm1.keySet().toArray()[0];
                if(t1.startsWith("PR")){
                    tmpresult = t1;
                    CommFun.log(debuglevel, tmpresult);
                }
                rettext=hm1.get(t1);
            }
            CommFun.log(debuglevel, "hm1:"+hm1+",and tmpresult:"+tmpresult+",rettext:"+rettext);
            if(rettext!=null && rettext.length>1){
                for(String st:rettext){
                    returnValue = create900Msg(sestmp0, tmpresult, st);
                    hm.put(returnValue, longfilename);
                }
            }else{
                returnValue = create900Msg(sestmp0, tmpresult, null);
                hm.put(returnValue, longfilename);
            }
            
            //同时利用反射技术实现生成与本报文类型有关的业务回执
            hm1 = ReflectUtil.execute((Object) preclassname + xmlOrder + "xml",
                    xmlOrder, msgid);
            if(hm1!=null && hm1.size()>0){
                for (String str : hm1.keySet()) {
                    hm.put(str, hm1.get(str)[0]);
                }
            }
            CommFun.log(debuglevel, ""+hm);
        }else{
            CommFun.log(debuglevel,"因不需要回执或者没有对应配置表,不需要回执处理!");
        }
        return hm;
    }
View Code

 

对XML报文进行全动态解析并动态生成SQL语句

上一篇:JAVA之XML文件解析(根据头部和身体自动组合成SQL)


下一篇:数据库操作