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<String> 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<roleNames.length;i++){
|
rolesSb.append(roleNames[i]);
|
rolesSb.append(",");
|
}
|
if(roleNames.length > 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<OrderInfo> 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;
|
}
|
}
|
