package com.vci.server.query.util;
|
|
import java.util.Map;
|
|
import org.apache.commons.lang3.StringUtils;
|
|
import com.vci.common.qt.object.Condition;
|
import com.vci.common.qt.object.ConditionItem;
|
import com.vci.common.qt.object.Connector;
|
import com.vci.common.qt.object.LeafInfo;
|
import com.vci.common.qt.object.LeafValue;
|
import com.vci.common.qt.object.Operator;
|
import com.vci.common.qt.object.QTConstants;
|
import com.vci.common.qt.object.QueryTemplate;
|
import com.vci.common.qt.object.Symbol;
|
import com.vci.server.base.utility.AttributeHelper;
|
import com.vci.server.base.utility.OmdHelper;
|
|
public final class QTSqlUtil {
|
|
|
/**
|
* 将查询条件解析成sql
|
* @param qt
|
* @param addGrandRightFlag 是否加上权限的条件, 默认应为true
|
* 当权限开关开启(right.switch=on), 并且addGrandRightFlag=true时才会加权限条件
|
* @return
|
*/
|
public static String getSql(QueryTemplate qt, boolean addGrandRightFlag){
|
StringBuilder strb = new StringBuilder("");
|
|
Condition condition = qt.getCondition();
|
|
Map<String, ConditionItem> ciMap = condition.getCIMap();
|
ConditionItem rootCI = ciMap.get(condition.getRootCIName());
|
if(rootCI.isLeaf()){
|
strb.append(getConditionItemSql(qt, rootCI, addGrandRightFlag));
|
}else{
|
ConditionItem root = rootCI;
|
ConditionItem left = ciMap.get(root.getChildrenInfo().getLeftCIName());
|
ConditionItem right = ciMap.get(root.getChildrenInfo().getRightCIName());
|
String treeSql = parseTree(ciMap, left, root, right, qt, addGrandRightFlag);
|
strb.append(treeSql);
|
}
|
|
return strb.toString();
|
}
|
|
/**
|
* 将查询条件中的link条件解析成sql
|
* @param qt
|
* @return
|
*/
|
public static String getLinkConditionSql(QueryTemplate qt, boolean addGrandRightFlag){
|
StringBuilder stb = new StringBuilder("");
|
|
Condition condition = qt.getCondition();
|
|
Map<String, ConditionItem> ciMap = condition.getCIMap();
|
|
ConditionItem rootCI = ciMap.get(condition.getRootCIName());
|
if(rootCI.isLeaf()){
|
if(!isBtmContidionItem(ciMap, rootCI)){
|
return getConditionItemSql(qt, rootCI, addGrandRightFlag);
|
}else{
|
return stb.toString();
|
}
|
}else{
|
ConditionItem root = rootCI;
|
ConditionItem left = ciMap.get(root.getChildrenInfo().getLeftCIName());
|
ConditionItem right = ciMap.get(root.getChildrenInfo().getRightCIName());
|
if(!isBtmContidionItem(ciMap, left) && !isBtmContidionItem(ciMap, right)){
|
String treeSql = parseTree(ciMap, left, root, right, qt, addGrandRightFlag);
|
stb.append(treeSql);
|
}else if(!isBtmContidionItem(ciMap, left)){
|
String rootCIName = condition.getRootCIName();
|
condition.setRootCIName(left.getId());
|
String sql = getSql(qt, addGrandRightFlag);
|
condition.setRootCIName(rootCIName);
|
return sql;
|
}else if(!isBtmContidionItem(ciMap, right)){
|
String rootCIName = condition.getRootCIName();
|
condition.setRootCIName(right.getId());
|
String sql = getSql(qt, addGrandRightFlag);
|
condition.setRootCIName(rootCIName);
|
return sql;
|
}else{
|
return stb.toString();
|
}
|
}
|
|
return stb.toString();
|
}
|
|
/**
|
* 将查询条件中的btm条件解析成sql
|
* @param qt
|
* @return
|
*/
|
public static String getBtmConditionSql(QueryTemplate qt, boolean addGrandRightFlag){
|
Condition condition = qt.getCondition();
|
Map<String, ConditionItem> ciMap = condition.getCIMap();
|
|
StringBuilder stb = new StringBuilder("");
|
ConditionItem rootCI = ciMap.get(condition.getRootCIName());
|
if(rootCI.isLeaf()){
|
if(isBtmContidionItem(ciMap, rootCI)){
|
return getConditionItemSql(qt, rootCI, addGrandRightFlag);
|
}else{
|
return stb.toString();
|
}
|
}else{
|
ConditionItem root = rootCI;
|
ConditionItem left = ciMap.get(root.getChildrenInfo().getLeftCIName());
|
ConditionItem right = ciMap.get(root.getChildrenInfo().getRightCIName());
|
if(isBtmContidionItem(ciMap, left) && isBtmContidionItem(ciMap, right)){
|
String treeSql = parseTree(ciMap, left, root, right, qt, addGrandRightFlag);
|
stb.append(treeSql);
|
}else if(isBtmContidionItem(ciMap, left)){
|
String rootCIName = condition.getRootCIName();
|
condition.setRootCIName(left.getId());
|
String sql = getSql(qt, addGrandRightFlag);
|
condition.setRootCIName(rootCIName);
|
return sql;
|
}else if(isBtmContidionItem(ciMap, right)){
|
String rootCIName = condition.getRootCIName();
|
condition.setRootCIName(right.getId());
|
String sql = getSql(qt, addGrandRightFlag);
|
condition.setRootCIName(rootCIName);
|
return sql;
|
}else{
|
return stb.toString();
|
}
|
}
|
|
return stb.toString();
|
}
|
|
/**
|
* 递归解析ConditionItem树结构
|
* @param left
|
* @param root
|
* @param right
|
* @return
|
*/
|
private static String parseTree(Map<String, ConditionItem> ciMap, ConditionItem left, ConditionItem root, ConditionItem right, QueryTemplate qt, boolean addGrandRightFlag){
|
StringBuilder strb = new StringBuilder("");
|
strb.append(Symbol.LEFT_PAREN);
|
if(left.isLeaf()){
|
strb.append(getConditionItemSql(qt, left, addGrandRightFlag));
|
}else{
|
ConditionItem left_ = ciMap.get(left.getChildrenInfo().getLeftCIName());
|
ConditionItem root_ = left;
|
ConditionItem right_ = ciMap.get(left.getChildrenInfo().getRightCIName());
|
String treeSql = parseTree(ciMap, left_, root_, right_, qt, addGrandRightFlag);
|
strb.append(treeSql);
|
}
|
strb.append(Symbol.SPACE);
|
String connector = root.getChildrenInfo().getConnector();
|
if(connector.equals(Connector.ANDCN)){
|
connector = Connector.AND;
|
}
|
if(connector.equals(Connector.ORCN)){
|
connector = Connector.OR;
|
}
|
strb.append(connector);
|
strb.append(Symbol.SPACE);
|
if(right.isLeaf()){
|
strb.append(getConditionItemSql(qt, right, addGrandRightFlag));
|
}else{
|
ConditionItem left_ = ciMap.get(right.getChildrenInfo().getLeftCIName());
|
ConditionItem root_ = right;
|
ConditionItem right_ = ciMap.get(right.getChildrenInfo().getRightCIName());
|
String treeSql = parseTree(ciMap, left_, root_, right_, qt, addGrandRightFlag);
|
strb.append(treeSql);
|
}
|
strb.append(Symbol.RIGHT_PAREN);
|
return strb.toString();
|
}
|
|
/**
|
* 在链接查询的查询条件中
|
* 判断查询条件项是否是约束业务对象的
|
* @return
|
*/
|
private static boolean isBtmContidionItem(Map<String, ConditionItem> ciMap, ConditionItem ci){
|
while(!ci.isLeaf()){
|
ci = ciMap.get(ci.getChildrenInfo().getRightCIName());
|
}
|
String clause = ci.getLeafInfo().getClause();
|
if(clause.startsWith("T_OID.") || clause.startsWith("F_OID.")){
|
return true;
|
}
|
return false;
|
}
|
|
|
|
/**
|
* 将叶子节点解析为sql
|
* @return
|
*/
|
private static String getConditionItemSql(QueryTemplate qt, ConditionItem ci, boolean addGrandRightFlag){
|
try{
|
StringBuilder strb = new StringBuilder("");
|
strb.append(getLeafSql(qt, ci.getLeafInfo(),addGrandRightFlag));
|
return strb.toString();
|
}catch(Exception e){
|
e.printStackTrace();
|
}
|
return "";
|
|
}
|
|
/**
|
* 叶子节点sql
|
*/
|
private static String getLeafSql(QueryTemplate qt, LeafInfo leaf, boolean addGrandRightFlag){
|
StringBuilder strb = new StringBuilder("");
|
String clause = leaf.getClause();
|
if(qt != null && qt.getType().equals(QTConstants.TYPE_LINK)){
|
String btmType = qt.getBtmType();
|
if(btmType != null && !btmType.equals("")){
|
String linkType = qt.getLinkType();
|
String viewName = null;
|
if (btmType.equals("*")) {
|
if(qt.getDirection().equals(QTConstants.DIRECTION_POSITIVE)){
|
viewName = OmdHelper.getToViewName(linkType);
|
}else{
|
viewName = OmdHelper.getFromViewName(linkType);
|
}
|
} else {
|
viewName = OmdHelper.getBTTableName(btmType);
|
}
|
|
if(clause.toUpperCase().contains("T_OID.") || clause.toUpperCase().contains("F_OID.")){
|
clause = clause.substring(6);
|
strb.append(viewName + "." + clause);
|
}else{
|
String tableName = OmdHelper.getLTTableName(linkType);
|
strb.append(tableName + "." + clause);
|
}
|
}else{
|
strb.append(clause);
|
}
|
}else{
|
if(clause.toUpperCase().contains("T_OID.")){
|
clause = clause.substring(6);
|
strb.append("PLRELEASEDOBJ" + "." + clause);
|
}else{
|
//addGrandRightFlag为false
|
//表示此解析为授权时要保存sql片段(where之后), 因链接类型也走此逻辑, 故不能加表名.
|
// if(!addGrandRightFlag){
|
// strb.append(clause);
|
// }else{
|
String btmType = qt.getBtmType();
|
strb.append(OmdHelper.getBTTableName(btmType) + "." + clause);
|
//}
|
}
|
}
|
|
String dataType = AttributeHelper.getAbItemDataType(clause);
|
strb.append(Symbol.SPACE);
|
String operator = leaf.getOperator();
|
if(operator.equals(Operator.CONTAINS)){
|
operator = Operator.LIKE;
|
}
|
strb.append(operator);
|
strb.append(Symbol.SPACE);
|
LeafValue value = leaf.getValue();
|
|
String valueSQL = value.getSql(addGrandRightFlag);
|
|
if(!(operator.equals(Operator.IN) || operator.equals(Operator.NOTIN)) && valueSQL.contains("'")){
|
//add by caill start 2016.3.25在拼凑sql语句时，对于查询模板中的查询条件中含有单引号的，在sql语句中要变成双引号
|
valueSQL = valueSQL.replaceAll("\'", "\"");
|
//valueSQL = valueSQL.replaceAll("'", "");
|
//add by caill end
|
}
|
|
if(value.getQueryTemplate() == null){
|
if ((operator.equals(Operator.IN) || operator.equals(Operator.NOTIN)) && !StringUtils.isEmpty(valueSQL)) {
|
strb.append(valueSQL);
|
} else if(dataType.equalsIgnoreCase("VTSTRING")){
|
if(operator.equals(Operator.LIKE) || operator.equals(Operator.NOTLIKE)){
|
if(valueSQL.equals("*")){
|
strb.append("'%'");
|
}else if(valueSQL.startsWith("*") && valueSQL.endsWith("*")){
|
valueSQL = valueSQL.substring(1, valueSQL.length() - 1);
|
strb.append("'%" + valueSQL + "%'");
|
}else if(valueSQL.startsWith("*")){
|
valueSQL = valueSQL.substring(1, valueSQL.length());
|
strb.append("'%" + valueSQL + "'");
|
}else if(valueSQL.endsWith("*")){
|
valueSQL = valueSQL.substring(0, valueSQL.length() - 1);
|
strb.append("'" + valueSQL + "%'");
|
}else{
|
strb.append("'%" + valueSQL + "%'");
|
}
|
} else {
|
if (valueSQL.equals("") || valueSQL.equalsIgnoreCase("null")) {
|
if (operator.equals(Operator.EQUAL)) {
|
strb.deleteCharAt(strb.indexOf("="));
|
strb.append("is null ");
|
} else if (operator.equals(Operator.UNEQUAL)) {
|
strb.delete(strb.indexOf("!="), strb.length());
|
strb.append("is not null ");
|
}
|
//操作符为in时 不对value加''
|
}else if(operator.equals(Operator.IN) || operator.equals(Operator.NOTIN)){
|
strb.append(valueSQL);
|
} else {
|
strb.append("'" + valueSQL + "'");
|
}
|
}
|
//boolean 在oracle中存为varchar2(8), 解析成bool = 'false'
|
}else if(dataType.equalsIgnoreCase("VTBOOLEAN")){
|
if(valueSQL.equalsIgnoreCase("false")){
|
valueSQL = "false";
|
}else if(valueSQL.equalsIgnoreCase("true")){
|
valueSQL = "true";
|
}
|
strb.append("'" + valueSQL + "'");
|
}else if(dataType.equalsIgnoreCase("VTDATETIME")
|
|| dataType.toUpperCase().equals("VTTIME")){
|
if (valueSQL.equals("") || valueSQL.equalsIgnoreCase("null")) {
|
if (operator.equals(Operator.EQUAL)) {
|
strb.deleteCharAt(strb.indexOf("="));
|
strb.append("is null ");
|
} else if (operator.equals(Operator.UNEQUAL)) {
|
strb.delete(strb.indexOf("!="), strb.length());
|
strb.append("is not null ");
|
}
|
} else {
|
valueSQL = "to_timestamp('" + valueSQL + "', 'syyyy-mm-dd hh24:mi:ss.ff')";
|
strb.append(valueSQL);
|
}
|
}else if (dataType.equalsIgnoreCase("VTDATE")){
|
if (valueSQL.equals("") || valueSQL.equalsIgnoreCase("null")) {
|
if (operator.equals(Operator.EQUAL)) {
|
strb.deleteCharAt(strb.indexOf("="));
|
strb.append("is null ");
|
} else if (operator.equals(Operator.UNEQUAL)) {
|
strb.delete(strb.indexOf("!="), strb.length());
|
strb.append("is not null ");
|
}
|
} else {
|
valueSQL = "to_date('" + valueSQL + "', 'syyyy-mm-dd hh24:mi:ss')";
|
strb.append(valueSQL);
|
}
|
}else{
|
if(operator.equals(Operator.LIKE)){
|
if(valueSQL.startsWith("*") && valueSQL.endsWith("*")){
|
valueSQL = valueSQL.substring(1, valueSQL.length() - 1);
|
strb.append("'%" + valueSQL + "%'");
|
}else if(valueSQL.startsWith("*")){
|
valueSQL = valueSQL.substring(1, valueSQL.length());
|
strb.append("'%" + valueSQL + "'");
|
}else if(valueSQL.endsWith("*")){
|
valueSQL = valueSQL.substring(0, valueSQL.length() - 1);
|
strb.append("'" + valueSQL + "%'");
|
}else{
|
strb.append("'%" + valueSQL + "%'");
|
}
|
}else{
|
if(valueSQL.equals("") || valueSQL.equalsIgnoreCase("null")){
|
if (operator.equals(Operator.EQUAL)) {
|
strb.deleteCharAt(strb.indexOf("="));
|
strb.append("is null ");
|
} else if (operator.equals(Operator.UNEQUAL)) {
|
strb.deleteCharAt(strb.indexOf("!="));
|
strb.append("is not null ");
|
}
|
}else{
|
strb.append("'" + valueSQL + "'");
|
}
|
}
|
}
|
}else{
|
strb.append(valueSQL);
|
}
|
|
|
return strb.toString();
|
|
}
|
}
|
