Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.kukababy.plus.pager.SqlWhere Maven / Gradle / Ivy
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 SqlWhere {
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 NULL = "null";
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(NULL, " null ");
}
public static SqlInfo getWheres(SqlFilter sqlFilter) {
SqlInfo sqlRes = new SqlInfo();
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) {
sqlRes.setWhereSql(" where " + currSql);
}
return sqlRes;
}
private static boolean hasOneEl(Filter filter) {
String col = filter.getCol(), op = filter.getOp();
Object val = filter.getVal();
if (StringUtils.isBlank(col) || StringUtils.isBlank(op) || val == null) {
return false;
}
if (!elMap.containsKey(op)) {
return false;
}
return true;
}
private static boolean hasTwoEl(Filter filter) {
String col = filter.getCol(), op2 = filter.getOp2();
Object val2 = filter.getVal2();
if (StringUtils.isBlank(col) || StringUtils.isBlank(op2) || val2 == null) {
return false;
}
if (!elMap.containsKey(op2)) {
return false;
}
return true;
}
private static boolean hasBetweenEl(Filter filter) {
String col = filter.getCol();
Object val = filter.getVal2(), val2 = filter.getVal2();
if (!filter.isBetween()) {
return false;
}
if (StringUtils.isBlank(col)) {
return false;
}
if (val != null && val2 != null) {
return true;
}
return false;
}
/**
*
* 描述:
* 一行字段组合的条件表达式
*
*
* @param paramVals
* @param filter
* @return
*/
private static void oneFilter(Filter filter, StringBuilder whereSql, List whereVals) {
StringBuilder currSql1 = new StringBuilder();
if (hasOneEl(filter)) {
rowFilter(filter, currSql1, whereVals);
}
StringBuilder currSql2 = new StringBuilder();
List filters = filter.getFilters();
if (filters != null && !filters.isEmpty()) {
groupFilters(filter.isAnd(), filters, currSql2, whereVals);
}
if (currSql1.length() > 2 && currSql2.length() > 2) {
if (filter.isChildAnd()) {
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 (hasOneEl(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(), op = filter.getOp(), op2 = filter.getOp2();
Object val = filter.getVal(), val2 = filter.getVal2();
int pos = whereSql.length();
if (hasBetweenEl(filter)) {
whereSql.append(col + " between ? and ?");
whereVals.add(val);
whereVals.add(val2);
} else {
if (!hasOneEl(filter)) {
return;
}
cellFilter(true, filter, whereSql, whereVals);
if (hasTwoEl(filter)) {
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:
case LIKE:
whereSql.append(col).append(elMap.get(op)).append("?");
whereVals.add(val);
break;
case NULL:
if (Boolean.valueOf(val.toString())) {
whereSql.append(col).append(" is null");
} else {
whereSql.append(col).append(" is not null");
}
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 getOrderby(List orderbys) {
if (orderbys == null || orderbys.isEmpty()) {
return "";
}
StringBuilder sb = new StringBuilder();
sb.append(" order by ");
for (Orderby orderby : orderbys) {
if (orderby.getOp().equals(Orderby.ASC)) {
sb.append(orderby.getCol()).append(" asc");
} else {
sb.append(orderby.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", 1, 2, true));
sqlFilter.getFilters().add(new Filter("addr", NULL, 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));
SqlInfo sqlRes = SqlWhere.getWheres(sqlFilter);
System.out.println(sqlRes.getWhereSql());
System.out.println(sqlRes.getWhereVals());
}
}
