dynamicCondition后台拼接sql

基于layui的动态添加条件查询ui插件

码云：插件主页   高级查询插件下载

有很多人问后台拼接sql的代码怎么写。我这里分享一下java后台拼接sql代码给大家参考下。

支持嵌套条件查询：

/***
	 * 构造对应的查询条件语句以及添加动态参数到list列表
	 * @param listQueryCondition
	 * @param list
	 * @return
	 */
	public String superBuildSqlWhereNew(List<QueryCondition> listQueryCondition,List<String> list){
		String sqlWhere = " ";
		String blankStr = " ";
		if(listQueryCondition == null || listQueryCondition.size() == 0){
			return sqlWhere;
		}
		int preLevel = 0;
		for (int i=0;i<listQueryCondition.size();i++) {
			QueryCondition queryCondition = listQueryCondition.get(i);
			String logicalOperator = queryCondition.getLogicalOperator();
			int curLevel = queryCondition.getRowlevel();
			//拼接右括号
			if(curLevel < preLevel){
				sqlWhere += blankStr + "))))))))))))))))))))))))))))".substring(0, preLevel-curLevel);
			}
			//拼接逻辑运算符
			if(!" ".equals(sqlWhere)
				&& sqlWhere.charAt( sqlWhere.length() - 1 ) != '(' ){
				sqlWhere += blankStr + logicalOperator;
			}
			//拼接左括号
			if(i + 1 < listQueryCondition.size() ){
				String groupname = listQueryCondition.get(i+1).getGroupname();
				if(groupname != null && groupname.equals(queryCondition.getSubgroupname()) ){
					sqlWhere += blankStr + "(";
				}
			}
			String op = queryCondition.getConditionOption();
			QueryConditionOption qcop = QueryConditionOption.valueOf(QueryConditionOption.class,op);
			//考虑验证field的值，避免sql注入。
			String field = queryCondition.getConditionField();
			field = transactSQLInjection(field);
			String val = queryCondition.getConditionValue();
			val = "".equals(val) ? null : val;
			String valLeft = queryCondition.getConditionValueLeft();
			String valRight = queryCondition.getConditionValueRight();
			preLevel = curLevel;
			//拼接查询条件
			if(QueryConditionOption.empty != qcop && QueryConditionOption.notempty != qcop){
				if(QueryConditionOption.between != qcop && val == null){
					//不用添加条件
					continue;
				}
				if(QueryConditionOption.between == qcop && valLeft == null && valRight == null){
					//不用添加条件
					continue;
				}
			}
			if("oracle".equals(getDbTypeByConfigName(getConfigName()))  ){
				field = OracleUtil.attrToColumnName(field);
			}
			if(QueryConditionOption.equal == qcop){
				if(val != null){
					sqlWhere += field +" = ? ";
					list.add(val);
				}
			}
			else if(QueryConditionOption.like == qcop){
				if(val != null){
					sqlWhere += field +" like ? ";
					list.add("%"+val+"%");
				}
			}
			else if(QueryConditionOption.in == qcop){
				if(val != null){
					String [] vals = val.split(",");
					if(vals.length > 0){
						for (String string : vals) {
							list.add(string);
						}
						sqlWhere += field +" in ("+getInParams(vals.length)+") ";
					}
				}
			}
			else if(QueryConditionOption.between == qcop){
				if(valLeft != null && valRight != null){
					sqlWhere += "( " +field +" >= ? and "+field +" <= ? )";
					list.add(valLeft);
					list.add(valRight);
				}else{
					if(valLeft != null){
						sqlWhere += field +" >= ? ";
						list.add(valLeft);
					}
					if(valRight != null){
						sqlWhere += field +" <= ? ";
						list.add(valRight);
					}
				}
			}
			else if(QueryConditionOption.start == qcop){
				if(val != null){
					sqlWhere += field +" like ? ";
					list.add(val+"%");
				}
			}
			else if(QueryConditionOption.end == qcop){
				if(val != null){
					sqlWhere +=  field +" like ? ";
					list.add("%"+val);
				}
			}
			else if(QueryConditionOption.unequal == qcop){
				if(val == null){
					val = "";
				}
				sqlWhere +=  field +" <> ? ";
				list.add(val);
			}
			else if(QueryConditionOption.empty == qcop){
				sqlWhere += field +" is null ";
			}
			else if(QueryConditionOption.notempty == qcop){
				sqlWhere += field +" is not null ";
			}
		}
		sqlWhere += blankStr + "))))))))))))))))))))))))))))".substring(0,preLevel);
		sqlWhere = RegexUtil.replaceAll(sqlWhere, "\\(\\s*\\)", "");
		if("".equals(sqlWhere.trim())){
			return sqlWhere;
		}
		return " and " + sqlWhere;
	}

QueryCondition.java

package com.xpl.util;

/**
 * 动态查询条件类
 * @author Pelin
 */
public class QueryCondition {

	String conditionField;
	String conditionOption;
	String conditionValue;
	String conditionValueLeft;
	String conditionValueRight;

	//嵌套条件查询新增属性
	int rowlevel = 0;
	String logicalOperator = "and";
	String groupname = "groupname2";
	String subgroupname = (Math.random()+"").substring(2);

	public QueryCondition(){

	}
	public QueryCondition(String conditionField,String conditionOption,String conditionValue
			,String conditionValueLeft,String conditionValueRight){
		this.conditionField = conditionField;
		this.conditionOption = conditionOption;
		this.conditionValue = conditionValue;
		this.conditionValueLeft = conditionValueLeft;
		this.conditionValueRight = conditionValueRight;
	}
	public String getConditionField() {
		return conditionField;
	}
	public void setConditionField(String conditionField) {
		this.conditionField = conditionField;
	}
	public String getConditionOption() {
		return conditionOption;
	}
	public void setConditionOption(String conditionOption) {
		this.conditionOption = conditionOption;
	}
	public String getConditionValue() {
		return conditionValue;
	}
	public void setConditionValue(String conditionValue) {
		this.conditionValue = conditionValue;
	}
	public String getConditionValueLeft() {
		return conditionValueLeft;
	}
	public void setConditionValueLeft(String conditionValueLeft) {
		this.conditionValueLeft = conditionValueLeft;
	}
	public String getConditionValueRight() {
		return conditionValueRight;
	}
	public void setConditionValueRight(String conditionValueRight) {
		this.conditionValueRight = conditionValueRight;
	}

	public int getRowlevel() {
		return rowlevel;
	}
	public void setRowlevel(int rowlevel) {
		this.rowlevel = rowlevel;
	}
	public String getLogicalOperator() {
		if("and".equals(logicalOperator)){
			return " AND ";
		}else if("or".equals(logicalOperator)){
			return " OR ";
		}
		return " AND ";
	}
	public void setLogicalOperator(String logicalOperator) {
		this.logicalOperator = logicalOperator;
	}
	public String getGroupname() {
		return groupname;
	}
	public void setGroupname(String groupname) {
		this.groupname = groupname;
	}
	public String getSubgroupname() {
		return subgroupname;
	}
	public void setSubgroupname(String subgroupname) {
		this.subgroupname = subgroupname;
	}

	public enum QueryConditionOption {
		equal,like,between,start,end,unequal,empty,notempty,in;
	}

}