All Downloads are FREE. Search and download functionalities are using the official Maven repository.

com.kukababy.plus.pager.BuildWhere Maven / Gradle / Ivy

Go to download 

package com.kukababy.plus.pager;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.kukababy.plus.exception.PlusRuntimeException;
import com.kukababy.plus.utils.Constant;

/**
 * 
 * 
描述:构建Where条件
1、基本支持数据库的所有查询条件
 * 

 * 
 * @author [email protected]
 * @date 2019年3月5日 下午11:30:02
 */
public class BuildWhere {

	public static final String LESS = "<";
	public static final String LESS_OR_EQUAL = "<=";
	public static final String EQUAL = "=";
	public static final String NOT_EQUAL = "!=";
	public static final String GREATER = ">";
	public static final String GREATER_OR_EQUAL = ">=";
	public static final String EXISTS = "exists";
	public static final String LIKE = "like";

	public static final String IN = "in";
	public static final String NOT_IN = "nin";

	public static final String OR = "or";
	public static final String AND = "and";

	public static final Map elMap = new HashMap();

	static {
		elMap.put(LESS, " < ");
		elMap.put(LESS_OR_EQUAL, " <= ");
		elMap.put(EQUAL, " = ");
		elMap.put(LIKE, " like ");
		elMap.put(NOT_EQUAL, " != ");
		elMap.put(GREATER, " > ");
		elMap.put(GREATER_OR_EQUAL, " >= ");
		elMap.put(IN, " in ");
		elMap.put(NOT_IN, " not in ");
		elMap.put(EXISTS, " is null ");
	}

	public static SqlRes getWheres(SqlFilter sqlFilter) {
		SqlRes sqlRes = new SqlRes();
		StringBuilder currSql = new StringBuilder();
		int i = 0;
		for (Filter filter : sqlFilter.getFilters()) {
			if (i > 0) {
				if (sqlFilter.isAnd()) {
					currSql.append(" and ");
				} else {
					currSql.append(" or ");
				}
			}
			oneFilter(filter, currSql, sqlRes.getWhereVals());
			i++;
		}
		if (currSql.length() > 2) {
			if (sqlFilter.isWherePre()) {// 有where前缀
				if (sqlFilter.isAnd()) {
					sqlRes.setWhereSql(" and (" + currSql + ")");
				} else {
					sqlRes.setWhereSql(" or (" + currSql + ")");
				}
			} else {
				sqlRes.setWhereSql(" where " + currSql);
			}
		}
		return sqlRes;
	}

	private static boolean hasEl(Filter filter) {
		if (StringUtils.isBlank(filter.getCol()) || StringUtils.isBlank(filter.getOp()) || filter.getVal() == null) {
			return false;
		}
		return true;
	}

	/**
	 * 
	 * 描述:
	 * 一行字段组合的条件表达式
	 * 

	 * 
	 * @param paramVals
	 * @param filter
	 * @return
	 */
	private static void oneFilter(Filter filter, StringBuilder whereSql, List whereVals) {
		StringBuilder currSql1 = new StringBuilder();
		if (hasEl(filter)) {
			rowFilter(filter, currSql1, whereVals);
		}

		StringBuilder currSql2 = new StringBuilder();
		if (filter.getFilters() != null && !filter.getFilters().isEmpty()) {
			groupFilters(filter.isAnd(), filter.getFilters(), currSql2, whereVals);
		}

		if (currSql1.length() > 2 && currSql2.length() > 2) {
			if (filter.isLogic()) {
				whereSql.append("(" + currSql1 + " and " + currSql2 + ")");
			} else {
				whereSql.append("(" + currSql1 + " or " + currSql2 + ")");
			}
		} else {
			if (currSql1.length() > 2) {
				whereSql.append(currSql1);
			}
			if (currSql2.length() > 2) {
				whereSql.append(currSql2);
			}
		}

	}

	public static void groupFilters(boolean isAnd, List filters, StringBuilder whereSql, List whereVals) {
		int pos = whereSql.length();
		int i = 0;
		for (Filter filter : filters) {
			if (hasEl(filter)) {
				if (i > 0) {
					if (isAnd) {
						whereSql.append(" and ");
					} else {
						whereSql.append(" or ");
					}
				}
				oneFilter(filter, whereSql, whereVals);
				i++;
			}
		}
		if (whereSql.length() > pos) {
			whereSql.insert(pos, "(");
			whereSql.append(")");
		}
	}

	private static void rowFilter(Filter filter, StringBuilder whereSql, List whereVals) {
		String col = filter.getCol();
		String op = filter.getOp();
		Object val = filter.getVal();
		String op2 = filter.getOp2();
		Object val2 = filter.getVal2();
		if (StringUtils.isBlank(col) || StringUtils.isBlank(op) || val == null) {
			return;
		}

		int pos = whereSql.length();
		if (StringUtils.isNotBlank(op2) && val2 != null && filter.isBetween()) {
			whereSql.append(col + " between ? and ?");
			whereVals.add(val);
			whereVals.add(val2);
		} else {
			cellFilter(true, filter, whereSql, whereVals);
			if (StringUtils.isBlank(op2) && val2 != null) {
				if (filter.isOpAnd()) {
					whereSql.append(" and ");
				} else {
					whereSql.append(" or ");
				}
				cellFilter(false, filter, whereSql, whereVals);
			}
		}
		if (whereSql.length() > pos) {
			whereSql.insert(pos, "(");
			whereSql.append(")");
		}
	}

	private static void cellFilter(boolean isop, Filter filter, StringBuilder whereSql, List whereVals) {
		String col = filter.getCol();
		String op = filter.getOp();
		Object val = filter.getVal();
		if (!isop) {
			op = filter.getOp2();
			val = filter.getVal2();
		}

		switch (op) {
		case LESS:
		case LESS_OR_EQUAL:
		case NOT_EQUAL:
		case GREATER:
		case GREATER_OR_EQUAL:
		case EQUAL:
			whereSql.append(col).append(elMap.get(op)).append("?");
			whereVals.add(val);
			break;
		case EXISTS:
			if (Boolean.valueOf(val.toString())) {
				whereSql.append(col).append(" is not null");
			} else {
				whereSql.append(col).append(" is null");
			}
			break;
		case LIKE:
			whereSql.append(col).append(elMap.get(op)).append("?");// 只能是字符型
			if (filter.getLikeMatch() == Constant.LikeBoth) {
				whereVals.add("%" + val + "%");
			} else if (filter.getLikeMatch() == Constant.LikeLeft) {
				whereVals.add("%" + val);
			} else {
				whereVals.add(val + "%");
			}
			break;
		case IN:
		case NOT_IN:
			if (val instanceof List) {
				List _vals = (List) val;
				if (_vals.isEmpty()) {
					break;
				}
				whereSql.append(col).append(elMap.get(op)).append("(");
				int i = 0;
				for (Object obj : _vals) {
					if (i > 0) {
						whereSql.append(",?");
					} else {
						whereSql.append("?");
					}
					whereVals.add(obj);
					i++;
				}
				whereSql.append(")");
			} else {
				whereSql.append(col).append(elMap.get(op)).append("(?)");
				whereVals.add(val);
			}
			break;
		default:
			throw new PlusRuntimeException("没有提供正确的关系操作符");
		}

	}

	public static String getSorts(List sorts) {

		if (sorts == null || sorts.isEmpty()) {
			return "";
		}
		StringBuilder sb = new StringBuilder();
		sb.append(" order by ");
		for (Sort sort : sorts) {
			if (sort.getOp().equals(Sort.ASC)) {
				sb.append(sort.getCol()).append(" asc");
			} else {
				sb.append(sort.getCol()).append(" desc");
			}
		}
		return sb.toString();
	}

	public static void main(String args[]) {
		SqlFilter sqlFilter = new SqlFilter();
		Filter filter = new Filter("name", "AAA");
		filter.setAnd(false);
		//filter.setLogic(false);
		sqlFilter.getFilters().add(filter);
		filter.getFilters().add(new Filter("name1", "AAA1"));
		filter.getFilters().add(new Filter("name2", "AAA2"));
		sqlFilter.getFilters().add(new Filter("age", GREATER_OR_EQUAL, 1, LESS_OR_EQUAL, 2));
		sqlFilter.getFilters().add(new Filter("addr", EXISTS, false));
		sqlFilter.getFilters().add(new Filter("city", LIKE, "北京"));

		List ips = new ArrayList();
		ips.add("aa");
		ips.add("bb");

		sqlFilter.getFilters().add(new Filter("ip", NOT_IN, ips));

		SqlRes sqlRes = BuildWhere.getWheres(sqlFilter);
		System.out.println(sqlRes.getWhereSql());
		System.out.println(sqlRes.getWhereVals());
	}

}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy