package com.vci.server.query.parsers; import java.util.Collections; import java.util.List; import com.vci.common.qt.object.ComparatorOrderInfo; import com.vci.common.qt.object.Condition; import com.vci.common.qt.object.Connector; import com.vci.common.qt.object.Operator; import com.vci.common.qt.object.OrderInfo; import com.vci.common.qt.object.PageInfo; import com.vci.common.qt.object.QTConstants; import com.vci.common.qt.object.QueryTemplate; import com.vci.common.qt.object.Symbol; import com.vci.common.qt.object.Version; import com.vci.corba.framework.data.CheckValue; import com.vci.corba.common.VCIError; import com.vci.server.base.persistence.dao.HibernateSessionFactory; import com.vci.server.base.utility.OQueryHelper; import com.vci.server.base.utility.OmdHelper; import com.vci.server.query.util.QTSqlUtil; import com.vci.server.query.util.SecretUtil; /** * * 将QueryTemplate定义的OQL解析为SQL并返回 * * @author Administrator * */ public class ParserForOneLevel { private boolean addGrandRightFlag = true; /** * 是否增加授权SQL * @return */ public boolean isAddGrandRight() { return addGrandRightFlag; } /** * 是否增加授权SQL * @param addGrandRightFlag */ public void setAddGrandRightFlag(boolean addGrandRightFlag) { this.addGrandRightFlag = addGrandRightFlag; } private QueryTemplate queryTemplate = null; public ParserForOneLevel(QueryTemplate queryTemplate){ this.queryTemplate = queryTemplate; } /** * 解析OQL为不同数据库对应的SQL * 数据库: Oracle, SQLServer * @return * @throws Throwable */ public String parseToSql(){ return parseToSqlForLink(); } public String parseToSubSql(){ return parseToSubSqlForLink(); } private String parseToSqlForLink(){ StringBuilder sqlBu = new StringBuilder(""); sqlBu.append(Symbol.SELECT); sqlBu.append(Symbol.SPACE); sqlBu.append(getClauses()); sqlBu.append(Symbol.SPACE); sqlBu.append(Symbol.FROM); sqlBu.append(Symbol.SPACE); sqlBu.append(getQueryType()); sqlBu.append(Symbol.SPACE); sqlBu.append(Symbol.WHERE); sqlBu.append(Symbol.SPACE); sqlBu.append("1 = 1"); Condition condi = queryTemplate.getCondition(); if(condi != null && condi.getCIMap().size() > 0){ sqlBu.append(Symbol.SPACE); sqlBu.append(Connector.AND); sqlBu.append(Symbol.SPACE); //String conditionSql = queryTemplate.getCondition().getSql(queryTemplate, addGrandRightFlag); String conditionSql = QTSqlUtil.getSql(queryTemplate, addGrandRightFlag); sqlBu.append(conditionSql); } sqlBu.append(getOrderSQL(queryTemplate.getOrderInfoList())); PageInfo pageInfo = queryTemplate.getPageInfo(); if(pageInfo != null){ String sql = page(sqlBu.toString(), queryTemplate.getPageInfo()); return sql; }else { return sqlBu.toString(); } } private String parseToSubSqlForLink(){ StringBuilder sqlBu = new StringBuilder(""); sqlBu.append(Symbol.SELECT); sqlBu.append(Symbol.SPACE); sqlBu.append(getClauses()); sqlBu.append(Symbol.SPACE); sqlBu.append(Symbol.FROM); sqlBu.append(Symbol.SPACE); sqlBu.append(getQueryType()); sqlBu.append(Symbol.SPACE); sqlBu.append(Symbol.WHERE); sqlBu.append(Symbol.SPACE); sqlBu.append("1 = 1"); Condition condi = queryTemplate.getCondition(); if(condi != null && condi.getCIMap().size() > 0){ sqlBu.append(Symbol.SPACE); sqlBu.append(Connector.AND); sqlBu.append(Symbol.SPACE); //String conditionSql = queryTemplate.getCondition().getSql(queryTemplate, addGrandRightFlag); String conditionSql = QTSqlUtil.getSql(queryTemplate, addGrandRightFlag); sqlBu.append(conditionSql); } //sqlBu.append(getOrderSQL(queryTemplate.getOrderInfoList())); PageInfo pageInfo = queryTemplate.getPageInfo(); if(pageInfo != null){ String sql = page(sqlBu.toString(), queryTemplate.getPageInfo()); return sql; }else { return sqlBu.toString(); } } /** * start with 查询有没有下一级 * @return */ public String parseToSqlForLinkStartWith(){ String ltTable = OmdHelper.getLTTableName(queryTemplate.getLinkType()); StringBuilder sqlBu = new StringBuilder(""); sqlBu.append(Symbol.SELECT); sqlBu.append(Symbol.SPACE); sqlBu.append("OID, CONNECT_BY_ISLEAF"); sqlBu.append(Symbol.SPACE); sqlBu.append(Symbol.FROM); sqlBu.append(Symbol.SPACE); sqlBu.append(ltTable); sqlBu.append(Symbol.SPACE); sqlBu.append(Symbol.WHERE); sqlBu.append(Symbol.SPACE); sqlBu.append("level <= 1"); sqlBu.append(Symbol.SPACE); sqlBu.append("start with 1 = 1"); Condition con = queryTemplate.getCondition(); String f_oid = OQueryHelper.getConditionItemVlaue(con, "F_OID"); if(f_oid != null && !f_oid.equals("")){ sqlBu.append(" and f_oid = '"); sqlBu.append(f_oid); sqlBu.append("'"); } sqlBu.append(" connect by nocycle prior T_OID = F_OID and level <= 2"); return sqlBu.toString(); } /** * 获取查询列 * 到查询列中包含*时, 覆盖其他查询列 * @return */ private String getClauses(){ String countOverAlias = QTConstants.COUNT_OVER_ALIAS; StringBuilder clausesBu = new StringBuilder(""); List clauseList = queryTemplate.getClauseList(); for (int i = 0; i < clauseList.size(); i++) { String val = clauseList.get(i); // add by xchao 2017.09.04 BEGIN // 如果查询的列名是查询总数的特殊标记，则使用特殊的查询查总数 // add by xchao 2017.09.04 END if(countOverAlias.equals(val)){ val = QTConstants.COUNT_OVER_QUERY; } clausesBu.append(val); if (i < clauseList.size() - 1) { clausesBu.append(","); } } return clausesBu.toString(); } /** * 获取版本约束 * @return */ public String getRevConstraint(){ int version = queryTemplate.getVersion(); StringBuilder strb = new StringBuilder(""); switch(version){ case Version.allVer: case Version.currentRevCurrentVer: case Version.currentRevLastVer: case Version.currentVer: case Version.currentRev: case Version.currentName: break; case Version.lastRevLastVer: strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append("ISLastR"); strb.append(Symbol.SPACE); strb.append(Operator.EQUAL); strb.append(Symbol.SPACE); strb.append("'1'"); break; default: break; } return strb.toString(); } /** * 获取版次约束 * @return */ public String getVerConstraint(){ int version = queryTemplate.getVersion(); StringBuilder strb = new StringBuilder(""); switch(version){ case Version.allVer: case Version.currentRevCurrentVer: case Version.currentVer: case Version.currentRev: case Version.currentName: break; case Version.currentRevLastVer: case Version.lastRevLastVer: strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append("ISLastV"); strb.append(Symbol.SPACE); strb.append(Operator.EQUAL); strb.append(Symbol.SPACE); strb.append("'1'"); break; default: break; } return strb.toString(); } /** * 处理查询类型 * @return */ public String getQueryType(){ String type = queryTemplate.getType(); int version = queryTemplate.getVersion(); String linkType = queryTemplate.getLinkType(); String btmType = queryTemplate.getBtmType(); StringBuilder strb = new StringBuilder(""); //业务类型查询 if(type.equals(QTConstants.TYPE_BTM)){ String btmTable = OmdHelper.getBTTableName(btmType); strb.append(btmTable); //链接查询, 只查链接对象 }else if(type.equals(QTConstants.TYPE_LINK) && (btmType == null || btmType.equals(""))){ String ltTable = OmdHelper.getLTTableName(linkType); strb.append(ltTable); //链接查询, 既查链接对象又查链接的业务对象, 根据版本版次信息链接这两个表 }else{ //正向查询 boolean positiveFlag = true; if(queryTemplate.getDirection().equals(QTConstants.DIRECTION_OPPOSITE)){ positiveFlag = false; } String ltTable = OmdHelper.getLTTableName(linkType); String viewName = null; if (btmType != null && !btmType.equals("*")) { viewName = OmdHelper.getBTTableName(btmType); } else { if(positiveFlag){ viewName = OmdHelper.getToViewName(linkType); }else{ viewName = OmdHelper.getFromViewName(linkType); } } //当查询的业务类型不是全部（*）时，连接加上过滤条件 //形如：linkTable join viewName on ... //and viewName.btmName in ('btmName1', 'btmName2') String btmCons_ = null; StringBuilder btmCons = new StringBuilder("in ("); if(!btmType.equals("*")){ String[] btmNames = btmType.split(","); for(String btmName : btmNames){ btmCons.append("'" + btmName + "'"); btmCons.append(","); } btmCons_ = btmCons.substring(0, btmCons.lastIndexOf(",")) + ")"; } switch(version){ case Version.currentRevCurrentVer: strb.append(ltTable); strb.append(Symbol.SPACE); strb.append(Symbol.JOIN); strb.append(Symbol.SPACE); strb.append(viewName); strb.append(Symbol.SPACE); strb.append(Symbol.ON); strb.append(Symbol.SPACE); if(positiveFlag){ strb.append(ltTable + ".t_oid"); }else{ strb.append(ltTable + ".f_oid"); } strb.append(Symbol.SPACE); strb.append(Operator.EQUAL); strb.append(Symbol.SPACE); strb.append(viewName + ".oid"); if(!btmType.equals("*")){ strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append(viewName + ".btmName"); strb.append(Symbol.SPACE); strb.append(btmCons_); } break; case Version.currentRevLastVer: strb.append(ltTable); strb.append(Symbol.SPACE); strb.append(Symbol.JOIN); strb.append(Symbol.SPACE); strb.append(viewName); strb.append(Symbol.SPACE); strb.append(Symbol.ON); strb.append(Symbol.SPACE); if(positiveFlag){ strb.append(ltTable + ".t_revisionoid"); }else{ strb.append(ltTable + ".f_revisionoid"); } strb.append(Symbol.SPACE); strb.append(Operator.EQUAL); strb.append(Symbol.SPACE); strb.append(viewName + ".revisionoid"); strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append(viewName + ".ISLASTV = 1"); if(!btmType.equals("*")){ strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append(viewName + ".btmName"); strb.append(Symbol.SPACE); strb.append(btmCons_); } break; case Version.allVer: case Version.lastRevLastVer: strb.append(ltTable); strb.append(Symbol.SPACE); strb.append(Symbol.JOIN); strb.append(Symbol.SPACE); strb.append(viewName); strb.append(Symbol.SPACE); strb.append(Symbol.ON); strb.append(Symbol.SPACE); if(positiveFlag){ strb.append(ltTable + ".t_nameoid"); }else{ strb.append(ltTable + ".f_nameoid"); } strb.append(Symbol.SPACE); strb.append(Operator.EQUAL); strb.append(Symbol.SPACE); strb.append(viewName + ".nameoid"); strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append(viewName + ".ISLASTV = 1"); strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append(viewName + ".ISLASTR = 1"); if(!btmType.equals("*")){ strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append(viewName + ".btmName"); strb.append(Symbol.SPACE); strb.append(btmCons_); } break; case Version.currentVer: strb.append(ltTable); strb.append(Symbol.SPACE); strb.append(Symbol.JOIN); strb.append(Symbol.SPACE); strb.append(viewName); strb.append(Symbol.SPACE); strb.append(Symbol.ON); strb.append(Symbol.SPACE); strb.append(ltTable + ".f_oid"); strb.append(Symbol.SPACE); strb.append(Operator.EQUAL); strb.append(Symbol.SPACE); strb.append(viewName + ".oid"); if(!btmType.equals("*")){ strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append(viewName + ".btmName"); strb.append(Symbol.SPACE); strb.append(btmCons_); } break; case Version.currentRev: strb.append(ltTable); strb.append(Symbol.SPACE); strb.append(Symbol.JOIN); strb.append(Symbol.SPACE); strb.append(viewName); strb.append(Symbol.SPACE); strb.append(Symbol.ON); strb.append(Symbol.SPACE); strb.append(ltTable + ".f_oid"); strb.append(Symbol.SPACE); strb.append(Operator.EQUAL); strb.append(Symbol.SPACE); strb.append(viewName + ".oid"); if(!btmType.equals("*")){ strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append(viewName + ".btmName"); strb.append(Symbol.SPACE); strb.append(btmCons_); } break; case Version.currentName: strb.append(ltTable); strb.append(Symbol.SPACE); strb.append(Symbol.JOIN); strb.append(Symbol.SPACE); strb.append(viewName); strb.append(Symbol.SPACE); strb.append(Symbol.ON); strb.append(Symbol.SPACE); strb.append(ltTable + ".f_oid"); strb.append(Symbol.SPACE); strb.append(Operator.EQUAL); strb.append(Symbol.SPACE); strb.append(viewName + ".oid"); if(!btmType.equals("*")){ strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append(viewName + ".btmName"); strb.append(Symbol.SPACE); strb.append(btmCons_); } break; case Version.lastReleasedRev: strb.append(ltTable); strb.append(Symbol.SPACE); strb.append(Symbol.JOIN); strb.append(Symbol.SPACE); strb.append(QTConstants.PLRELEASEDOBJ); strb.append(Symbol.SPACE); strb.append(Symbol.ON); strb.append(Symbol.SPACE); if(positiveFlag){ strb.append(ltTable + ".t_nameoid"); }else{ strb.append(ltTable + ".f_nameoid"); } strb.append(Symbol.SPACE); strb.append(Operator.EQUAL); strb.append(Symbol.SPACE); strb.append(QTConstants.PLRELEASEDOBJ + ".nameoid"); strb.append(Symbol.SPACE); strb.append(Symbol.JOIN); strb.append(Symbol.SPACE); strb.append(viewName); strb.append(Symbol.SPACE); strb.append(Symbol.ON); strb.append(Symbol.SPACE); strb.append(QTConstants.PLRELEASEDOBJ + ".OID"); strb.append(Symbol.SPACE); strb.append(Operator.EQUAL); strb.append(Symbol.SPACE); strb.append(viewName + ".OID"); if(!btmType.equals("*")){ strb.append(Symbol.SPACE); strb.append(Connector.AND); strb.append(Symbol.SPACE); strb.append(viewName + ".btmName"); strb.append(Symbol.SPACE); strb.append(btmCons_); } break; default: break; } } return strb.toString(); } /** * 将SQL包装成分页SQL * select * from (select A.*, rownum RN from ( sql ) A where rownum < endRow ) where RN >= startRow * @param sql * @param pageInfo * @return */ private String page(String sql, PageInfo pageInfo){ int startRow = (pageInfo.getPageNO() - 1) * pageInfo.getRowCount() + 1; int endRow = startRow + pageInfo.getRowCount(); StringBuilder strb = new StringBuilder(); strb.append("select * from (select A.* , rownum RN from ("); strb.append(sql); strb.append(") A where rownum < "); strb.append(endRow); strb.append(") where RN >= "); strb.append(startRow); return strb.toString(); } /** * 获取授权SQL * : 权限 * @param btmType * @return * @throws Throwable */ public String getGrandRightSQL(){ String where = ""; CheckValue value = new CheckValue(); value.businesstype = queryTemplate.getBtmType(); value.opname = "query"; //TODO value.users = HibernateSessionFactory.getVciSessionInfo().userName; String[] roleNames = HibernateSessionFactory.getVciSessionInfo().roleNames; StringBuilder rolesSb = new StringBuilder(); for(int i=0;i 0){ value.roles = rolesSb.toString().substring(0, rolesSb.toString().lastIndexOf(",")); } String[] groupNames = HibernateSessionFactory.getVciSessionInfo().groupNames; if(groupNames.length > 0){ value.userGroups=groupNames[0]; } String[] params = HibernateSessionFactory.getVciSessionInfo().extAttribs; StringBuilder strb = new StringBuilder(); for(int i = 0; i < params.length; i++){ strb.append(params[i]); strb.append(","); } if(params.length > 0){ value.paramValues = strb.substring(0, strb.length() - 1); }else{ value.paramValues = ""; } try { //where = ServerServiceProvider.getAuth2Service().checkRight(value); where = new SecretUtil().checkRight(value); } catch (VCIError e) { e.printStackTrace(); } return where; } /** * 获取排序条件 * @param orderInfoList * @return */ private Object getOrderSQL(List orderInfoList) { StringBuilder strb = new StringBuilder(); if(orderInfoList == null || orderInfoList.size() < 1){ return strb.toString(); } //根据orderInfo.level降排序 Collections.sort(orderInfoList, new ComparatorOrderInfo()); String linkType = queryTemplate.getLinkType(); String btmType = queryTemplate.getBtmType(); String viewName = null; if (btmType != null && !btmType.equals("*")) { viewName = OmdHelper.getBTTableName(btmType); } else { if(queryTemplate.getDirection().equals(QTConstants.DIRECTION_POSITIVE)){ viewName = OmdHelper.getToViewName(linkType); }else{ viewName = OmdHelper.getFromViewName(linkType); } } for(int i = 0; i < orderInfoList.size(); i++){ OrderInfo orderInfo = orderInfoList.get(i); if(orderInfo == null){ continue; } strb.append(" "); String field = orderInfo.getOrderField(); if(queryTemplate.getType().equals(QTConstants.TYPE_LINK)){ if(field.startsWith("T_OID.") || field.startsWith("F_OID.")){ field = field.substring(6); field = viewName + "." + field; } } strb.append(field); strb.append(" "); strb.append(orderInfo.getOrderMode()); strb.append(","); } //当strb不等于"" 时, 为其加上前缀ORDER BY String str = strb.toString(); if(!str.equals("")){ str = str.substring(0, str.length() - 1); return " order by" + str; } return str; } public QueryTemplate getQueryTemplate() { return queryTemplate; } public void setQueryTemplate(QueryTemplate queryTemplate) { this.queryTemplate = queryTemplate; } }