数据重构与动态CRUD(下)

OLAP分析应用(十一)-动态生成sq

动态插入语句：

拼前半段：前半段语句在布局表当中，已经存在了（设计界面的时候，就生成好了）。

拼插入sql语句后半段：关键是顺序还要和插入前半段字段顺序一致，其实，我们在comm_doc_layout中也有定义：

根据comm_doc_layout表中maincols中定义的顺序，循环取得相关的值，代码如下：

            DocLayout layout = (DocLayout) cacheHelper.getObjectInCache(Constants.CACHE_LAYOUT,
                    new DocLayout_Key(true), Integer.parseInt(layoutId));
            StringBuffer sql = new StringBuffer(layout.getMainsql() + "(");//插入语句的前半段
            String[] cols = layout.getMaincols().split(",");//界面已保存的所有字段
            List<BillArrObject> objs = this.exchange2Codes(list);//转换得到所有数据元code
            //保存主表
            for (String col : cols) {//循环界面已保存的所有字段与传来的list拼凑后半段sql
                boolean f = true;
                for (BillArrObject obj : objs) {
                    if (col.equals(obj.getName())) {
                        sql.append(this.exchange2str(user, obj, newGuid) + ",");//转换成值
                        f = false;
                    }
                }
                if (col.equals("GUID") && f) {
                    String s = CreateGUID.GenerateGUID();
                    sql.append("'" + s + "',");
                    newGuid = s;
                }
            }
            StringBuffer sql2 = new StringBuffer(sql.substring(0, sql.length() - 1));//去掉最后的逗号
            sql2.append(")");
            Query query = this.getCurrentSession().createSQLQuery(sql2.toString());
            query.executeUpdate();

        

不过，取得值根据类型要进行一些转换，如对日期进行格式化、数字进行必要的检查判断、字符串进行必要的转换（主要是转自然键值）、字符类型插入要加上前后引号等，代码如下：

    /***
     * 判断数据元类型并返回相应的字符串
     * @param ecode
     * 字符串返回'XX',数字返回XX，日期返回to_date('xx','yyyy-mm-dd hh24:mi:ss')
     */
    private String exchange2str(UserInfo user, BillArrObject obj, String newGuid) throws Exception {
        String str = "";
        //try {
        DataElement de = (DataElement) cacheHelper.getObjectInCache(
                Constants.CACHE_DATA_ELEMENT, new DataElement_Code_Key(true), obj.getName());
        if (de != null) {
            Dictionary dic = (Dictionary) cacheHelper.getObjectInCache(Constants.CACHE_DIC,
                    new Dictionary_Key(true), de.getEtype());
            String type = dic.getContent();
            //String value = new String(obj.getValue().getBytes("ISO-8859-1"), "UTF-8");
            String value = obj.getValue();
            if (BaseUtil.isNotNull(value)) {
                value = value.trim();
            }
            if ("VARCHAR2".equals(type)) {//字符串类型
                if ("GUID".equals(de.getEcode()) && BaseUtil.isNull(value)) {//sys_guid()
                    str = "'" + newGuid + "'";
                } else if ("DOC_BILLID".equals(de.getEcode()) && BaseUtil.isNotNull(value)) {
                    str = "'" + BaseUtil.guid2Nid(EBasBillInfo.class, value) + "'";//前端传来的票种GUID转换成NID
                } else if ("ChargeProjectID1".equals(de.getEcode()) && BaseUtil.isNotNull(value)) {
                    str = "'" + BaseUtil.guid2Nid(EBasChargePrjAll.class, value) + "'";//前端传来的项目GUID转换成NID
                } else if ("dwmc".equals(de.getEcode()) && BaseUtil.isNotNull(value)) {
                    //str = "'"+BaseUtil.guid2Nid(EBasChargeAgency.class,value)+"'";//前端传来的单位GUID转换成NID
                    str = "'" + user.getDept() + "'";
                } else if ("CollectingBank".equals(de.getEcode()) && BaseUtil.isNotNull(value)) {
                    str = "'" + BaseUtil.guid2Nid(EBasRemittedBank.class, value) + "'";
                } else {
                    str = "'" + value + "'";
                }
            } else if ("NUMBER".equals(type) || "number".equals(type)
                    || type.contains("number") || type.contains("NUMBER")) {//数字类型
                if ("DOC_ID".equals(de.getEcode()) && (BaseUtil.isNull(value) || "0".equals(value))) {
                    str = "SEQUENCE_DOC_ID.NEXTVAL";
                } else if ("DOC_TOTALAMOUNT".equals(de.getEcode()) && BaseUtil.isNull(value)) {//总金额为空的话
                    str = "0.00";
                    //return "";
                } else if ("num".equals(de.getEcode()) && BaseUtil.isNull(value)) {
                    str = "1";
                } else if ("num".equals(de.getEcode()) && BaseUtil.isNotNull(value)) {
                    Double sl = Double.parseDouble(value);
                    if (sl.doubleValue() == 0) {
                        str = "1";
                    } else {
                        str = "" + value + "";
                    }
                } else if ("amount".equals(de.getEcode()) && BaseUtil.isNull(value)) {
                    throw new BusinessException("明细金额有误，请重新开票保存！");
                } else if ("amount".equals(de.getEcode()) && BaseUtil.isNotNull(value)) {
                    Double amt = Double.parseDouble(value);
                    if (amt.doubleValue() == 0) {
                        throw new BusinessException("明细金额有误，请重新开票保存！");
                    } else {
                        str = "" + value + "";
                    }
                } else if (!"amount".equals(de.getEcode()) && !"DOC_ID".equals(de.getEcode()) && !"DOC_TOTALAMOUNT".equals(de.getEcode()) && BaseUtil.isNull(value)) {
                    str = "0.00";
                } else {
                    str = "" + value + "";
                }
            } else if ("DATE".equals(type)) {//时间类型
                str = "to_date('" + value + "','yyyy-MM-dd HH24:mi:ss')";
            } else if ("CHAR".equals(type)) {//字符类型
                str = "'" + value + "'";
            }
        }
        //} catch (CacheException e) {
        //	log.error(e.getMessage(), e);
        //	e.printStackTrace();
        //}
        return str;
    }

至于更新，和插入是一样的，不过得先删除原来的记录。

动态查询列表：

目前的写法有点...,主要搞了个doc_index表，做为所有动态业务表的入口

这里，无非是查总记录调试、记录集合而已。

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        StringBuffer account = new StringBuffer("select count(*) from DOC_INDEX t ");
        StringBuffer hql = new StringBuffer("select t.id \"id\",t.doc_id \"docid\",t.doc_zg \"doczg\",t.doc_num \"docnum\"," +
                "t.create_date \"createdate\",t.table_name \"tablename\",t.doc_state \"docstate\"," +
                "t.bill_def_id \"billdefid\",t.login_code \"logincode\",t.doc_totalamount \"totalamount\"," +
                "t.billdate \"billdate\",t.agencyid \"agencyid\",t.verifistate \"verifistate\",t.invalidstate \"invalidstate\","
                + "t.orgid \"orgid\",t.verid \"verid\",t.dkagency \"dkagency\",t.dkagencyid \"dkagencyid\",t.jm \"jm\",x.payer \"payer\",t.xjagencyid \"xjagencyid\" from DOC_INDEX t ");
        StringBuffer joinStr = new StringBuffer(" inner join ");
        StringBuffer str = new StringBuffer(" where 1=1 ");
        String sdate = (String) map.get("sbilldate");
        String edate = (String) map.get("ebilldate");
        String docnum = (String) map.get("docnum");
        String creator = (String) map.get("creator");
        String orgID = (String) map.get("orgID");
        String ignorelogincode = (String) map.get("ignorelogincode");
        String payer = (String) map.get("payer");
        EBasBill2DataSet b2d = null;
        DataSet ds = null;
        String tabName = "";

        int docstate = -1;

        String localyear = Constants.BUSI_YEAR;

        if (BaseUtil.isNotNull(map.get("docstate"))) {
            docstate = (Integer) map.get("docstate");   //如果docstate是0，查询可删除与可审核的单据
            if (docstate == 4) {//核销状态
                str.append(" and t.doc_state>0 ");
                str.append(" and t.verifistate>0 ");
            }
            if (docstate >= 0 && docstate <= 2) {
                str.append(" and t.verifistate=0 ");
            }
        }
        String userRole = this.checkUserRole(user);

        str.append(" and t.bill_def_id = '" + billid + "'");//票种id
        if (docstate > -1 && docstate < 4) {//3是收款状态
            str.append(" and t.doc_state =" + docstate + "");
        }

        if (userRole.equals(Constants.SYS_ROLE_AGENCY) || userRole.equals(Constants.SYS_ROLE_BANK)) {
            //单位自己和自己的下级
            if (docstate > -1) {
                str.append(" and t.agencyid in (select distinct nid from BAS_CHARGEAGENCY where guid in "
                        + "(select agencyid from BAS_CHGAGENCY2CHGAGENCY where parentagencyid='" + BaseUtil.nid2Guid(EBasChargeAgency.class, user.getDept(), localyear) + "')"
                        + " union  select '" + user.getDept() + "' from dual ) ");
                if (docstate == 4) {
                    str.append(" and t.doc_state>0 ");
                    str.append(" and t.verifistate>0 ");
                } else {
                    str.append(" and t.doc_state =" + docstate + " ");
                }
                //str.append(" ) ");
            } else {
                str.append(" and t.agencyid in (select distinct nid from BAS_CHARGEAGENCY where guid in "
                        + "(select agencyid from BAS_CHGAGENCY2CHGAGENCY where parentagencyid='" + BaseUtil.nid2Guid(EBasChargeAgency.class, user.getDept(), localyear) + "')"
                        + " union  select '" + user.getDept() + "' from dual )");
            }
        }

        if (BaseUtil.isNotNull(sdate)) {
            str.append(" and to_date(to_char(t.billdate,'yyyy-mm-dd'),'yyyy-mm-dd') >= to_date('" + sdate + "','yyyy-mm-dd')");
        }
        if (BaseUtil.isNotNull(edate)) {
            str.append(" and to_date(to_char(t.billdate,'yyyy-mm-dd'),'yyyy-mm-dd') <= to_date('" + edate + "','yyyy-mm-dd')");
        }
        if (BaseUtil.isNotNull(docnum)) {
            str.append(" and t.doc_num like '%" + docnum + "%'");
        }
        if (BaseUtil.isNotNull(creator)/* && "0".equals(ignorelogincode)*/) {
            try {
                creator = new String(creator.getBytes("ISO-8859-1"), "UTF-8");
            } catch (UnsupportedEncodingException e) {
                log.error(e.getMessage(), e);
                e.printStackTrace();
            }
            str.append(" and t.login_code like '%" + creator + "%'");
        }


        //如果是二级单位则只能看见自己机构开的票，不能看到上级单位开的票
        boolean isSecOrg = userDao.checkUserIsSecOrg(user);
        if (isSecOrg) {
            str.append(" and orgid=" + user.getOrgid() + " ");
        } else {
            //TODO NID?
            if (orgID != null && !orgID.trim().equals("")) {
                BaseUtil.nid2Guid(EBasChargeAgency.class, user.getDept(), localyear);
                if (!BaseUtil.nid2Guid(EBasChargeAgency.class, user.getDept(), localyear).equals(orgID)) {
                    str.append(" and t.xjagencyid='" + orgID + "'");
                } else {
                    str.append(" and (t.xjagencyid is null or t.xjagencyid='')");
                }
            }
        }


        if (Constants.BUSI_YBJKSNID.equals(billid)) {//如果是一般缴款书
				......
        } else {//专票
            try {
                b2d = (EBasBill2DataSet) cacheHelper.getObjectInCache(Constants.CACHE_BILL2SET, new Bill2DataSet_billid_Key(true), billid);
                if (b2d != null) {
                    ds = (DataSet) cacheHelper.getObjectInCache(Constants.CACHE_DATA_SET, new DataSet_Key(true), b2d.getDatasetid());
                }
                if (ds != null) tabName = ds.getScode();
            } catch (CacheException e) {
                e.printStackTrace();
            }

            joinStr.append(tabName).append(" x on t.doc_id=x.guid ");
            if (BaseUtil.isNotNull(payer)) {//如果缴款人条件是空的则先分页查询
                str.append(" and x.payer like '%" + payer + "%' ");
            }
            List<EDocIndex> list = null;
            //算总条数
            String totalSql = account.toString() + joinStr.toString() + str.toString();
            Query query = this.getCurrentSession().createSQLQuery(totalSql);
            Integer totalCount = Integer.parseInt(query.uniqueResult().toString());
            str.append(" order by t.create_date desc");   // where 语句
            String hqlSql = hql.toString() + joinStr.toString() + str.toString();
            query = this.getCurrentSession().createSQLQuery(hqlSql);
            if (pageIndex > -1) {
                query.setFirstResult((pageIndex - 1) * GlobalConfig.pageSize);
                query.setMaxResults(GlobalConfig.pageSize);
            }
            query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            List<Map> listmap = query.list();   //查询记录
            if (listmap != null && listmap.size() > 0) {
                list = new ArrayList<EDocIndex>();
                for (Map map2 : listmap) {
                    try {
                        EDocIndex dc = (EDocIndex) BaseUtil.map2Bean(map2, EDocIndex.class.newInstance());
                        if (dc != null) list.add(dc);
                    } catch (InstantiationException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
            BigDecimal o = null;
            try {
                String countSql = "select SUM(t.DOC_TOTALAMOUNT) from DOC_INDEX t" + joinStr.toString() + str.toString();
                Query sqlQuery = this.getCurrentSession().createSQLQuery(countSql);
                o = (BigDecimal) sqlQuery.uniqueResult();
            } catch (Exception ignored) {
            }

            if (list != null) {
            	
            	//这里写的有点小问题，把公共属性都放入的EDocIndex 属性当中了,如pager属性...
                for (EDocIndex eDocIndex : list) {
                    EBasChargeAgency agency = null;
                    EBasBillInfo bill = null;
                    CommOrg org = null;
                    try {
                        agency = (EBasChargeAgency) cacheHelper.getObjectInCache(Constants.CACHE_AGENCY,
                                new BasChgAgency_Key(true), BaseUtil.nid2Guid(EBasChargeAgency.class, eDocIndex.getAgencyid(), localyear));
                        bill = (EBasBillInfo) cacheHelper.getObjectInCache(Constants.CACHE_BILLINFO,
                                new BasBillInfo_Key(true), BaseUtil.nid2Guid(EBasBillInfo.class, eDocIndex.getBilldefid(), localyear));
                        org = (CommOrg) cacheHelper.getObjectInCache(
                                Constants.CACHE_ORG, new Org_Key(true), eDocIndex.getOrgid());
                        try {
                            if (eDocIndex.getXjagencyid() != null && !eDocIndex.getXjagencyid().equals("")) {
                                EBasChargeAgency objectInCache = (EBasChargeAgency) cacheHelper.getObjectInCache(Constants.CACHE_AGENCY,
                                        new BasChgAgency_Key(true), BaseUtil.nid2Guid(EBasChargeAgency.class, eDocIndex.getXjagencyid(), localyear));
                                if (objectInCache != null) {
                                    agency = objectInCache;
                                }
                            }
                        } catch (Exception ignored) {
                        }
                        if (org != null) {
                            eDocIndex.setOrgname(org.getName());
                        }
                        if (agency != null) {
                            eDocIndex.setAgencyname(agency.getAgencyname());
                        }
                        eDocIndex.setBillname(bill.getBillname());
                        eDocIndex.setPager(PublicUtil.getPager(totalCount, GlobalConfig.pageSize, pageIndex));
                        if (o != null) {
                            eDocIndex.setTablename(o.toString());
                        }

                    } catch (CacheException e) {
                        e.printStackTrace();
                    }

                }
                return list;
            }
        }
        return null;
    

动态删除

一个要点是找到要删除的表：路径为：布局对象-->数据集--->要删除的表  拼接到sql当中

要删除细表，要找到这个细表，路径为  主表数据集对象--->主细表关系对象-->关联数据元

   
//根据布局表找到数据集id
String setid = layout.getBillsetid();
//根据数据集id从缓存当中取得数据集对象 
ds = (DataSet) cacheHelper.getObjectInCache(Constants.CACHE_DATA_SET, new DataSet_Key(true), setid);

//删除主表 
 sql = "delete from " + ds.getScode() + " where " + FieldEnum.GUID.getFieldCode() + "='" + obj.getValue() + "'";
....
//找到主细关系表  进一步删除细表当中数据
 DataSet2DataSet s2s = (DataSet2DataSet) cacheHelper.getObjectInCache(
                            Constants.CACHE_DATA_SET2SET, new DataSet2Set_RelationId_Key(true), setid);

 DataSet dds = (DataSet) cacheHelper.getObjectInCache(Constants.CACHE_DATA_SET,
                            new DataSet_Key(true), s2s.getSetid());//细表
DataElement de = (DataElement) cacheHelper.getObjectInCache(Constants.CACHE_DATA_ELEMENT,
                            new DataElement_Key(true), s2s.getElementid());//关联数据元
 String sql2 = "delete from " + dds.getScode() + " where " + de.getEcode() + "= '" + oldGuid + "'";//删除细表
 query = this.getCurrentSession().createSQLQuery(sql2);
query.executeUpdate();//执行删除细表

sql 题外话：

PLSQL性能优化技巧

上一节：OLAP分析应用(十一)-动态生成sql（上）

下一节：