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

com.bixuebihui.db.SqlHelper Maven / Gradle / Ivy

Go to download

a fast small database connection pool and a active record flavor mini framework

There is a newer version: 1.15.3.3
Show newest version
package com.bixuebihui.db;

import com.bixuebihui.DbException;
import com.bixuebihui.jdbc.ISqlConditionType;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;

import java.sql.SQLException;
import java.util.*;
import java.util.Map.Entry;

import static com.bixuebihui.db.Dialect.MYSQL;

/**
 * 
SqlHelper class.

 *
 * @author xingwx
 * @version $Id: $Id
 */
public class SqlHelper {

	public static final String WHERE = " where ";
	public static final String HAVING = " having ";

	List filters;
	List orCond = null;
	List notCond = null;
	private int databaseType = MYSQL;
	private boolean useNullAsCondition = false;
	private boolean acceptEmptyStringAsNullObjectInCondition = false;
	private boolean conditionTypeIsWhere = true;

	/**
	 * 
Constructor for SqlHelper.

	 *
	 * @param src a {@link SqlHelper} object.
	 */
	public SqlHelper(SqlHelper src) {
		this.filters = new ArrayList<>();
		this.filters.addAll(src.filters);
		if(src.orCond!=null) {
			this.orCond = new ArrayList<>();
			this.orCond.addAll(src.orCond);
		}
		if(src.notCond!=null) {
			this.notCond = new ArrayList<>();
			this.notCond.addAll(src.notCond);
		}
		this.databaseType = src.databaseType;
		this.useNullAsCondition = src.useNullAsCondition;
		this.acceptEmptyStringAsNullObjectInCondition = src.acceptEmptyStringAsNullObjectInCondition;
	}

	/**
	 * 
Constructor for SqlHelper.

	 */
	public SqlHelper() {
		filters = new ArrayList<>();
	}
	public SqlHelper(boolean isHaving) {
		filters = new ArrayList<>();
		this.conditionTypeIsWhere = !isHaving;
	}

	public boolean isAcceptEmptyStringAsNullObjectInCondition() {
		return acceptEmptyStringAsNullObjectInCondition;
	}

	public void setAcceptEmptyStringAsNullObjectInCondition(boolean acceptEmptyStringAsNullObjectInCondition) {
		this.acceptEmptyStringAsNullObjectInCondition = acceptEmptyStringAsNullObjectInCondition;
	}

	/**
	 * 
toCondition.

	 *
	 * @return a {@link SqlPocket} object.
	 */
    public SqlPocket toCondition() {
		SqlPocket criteria = new SqlPocket();
		if (filters == null || filters.size() <= 0) {
			return criteria;
		}
		for (BaseFilter filter : filters) {
			buildCriteria(criteria, filter);
		}

		StringBuffer condition = criteria.getCondition();
		removeLeadAnd(condition);

		if (orCond != null) {
			condition.insert(0, "(").append(")");
			for (SqlHelper it : orCond) {
				SqlPocket cond = it.toCondition();
				condition.append(" or (")
						.append(trimWhere(cond.getCondition())).append(")");
				criteria.getParams().addAll(cond.getParams());
			}
		}
		if (notCond != null) {
			condition.insert(0, "(").append(")");
			for (SqlHelper it : notCond) {
				SqlPocket cond = it.toCondition();

				condition.append(" and not (")
						.append(trimWhere(cond.getCondition())).append(")");
				criteria.getParams().addAll(cond.getParams());
			}
		}
		if (condition.length() > 0) {
			removeLeadAnd(condition);
			condition.insert(0, conditionTypeIsWhere? WHERE: HAVING);
		}
		return criteria;
	}

	private void removeLeadAnd(StringBuffer condition) {
		if (condition.length() > 0 && condition.indexOf(" and ") == 0) {
			condition.delete(0, 5);
		}
	}

	/**
	 * 
trimWhere.

	 *
	 * @param trimCond a {@link java.lang.StringBuffer} object.
	 * @return a {@link java.lang.StringBuffer} object.
	 */
	protected StringBuffer trimWhere(StringBuffer trimCond) {
		if(trimCond.indexOf(conditionTypeIsWhere? WHERE : HAVING)==0) {
            trimCond.delete(0, 7);
        }
		return trimCond;
	}


	/**
	 * 
build.

	 *
	 * @return a {@link SqlPocket} object.
	 */
	public SqlPocket build() {
		return toCondition();
	}


	private boolean valueIsNotNull(Object v){
		if(v==null){
			return false;
		}
		if(!acceptEmptyStringAsNullObjectInCondition){
			return true;
		}
		return !"".equals(v);
	}
	private void buildCriteria(SqlPocket criteria, BaseFilter f)
	{
		if (valueIsNotNull(f.value) || useNullAsCondition || f instanceof NullFilter || f instanceof NotNullFilter ) {
			f.build(criteria);
		}
	}


	/**
	 * 增加
	 *
	 * @param condition 条件
	 * @param ignoreNulls 忽略null参数
	 * @return 当前实例
	 */
	public SqlHelper eq(Map condition, boolean ignoreNulls) {
		Set> e = condition.entrySet();
		for (Entry entry:e) {
			String key = entry.getKey();
			Object value = entry.getValue();
			if (ignoreNulls && value == null) {
                continue;
            }
			eq(key, value);
		}
		return this;
	}

	/**
	 * 
eq.

	 *
	 * @param condition a {@link java.util.Map} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper eq(Map condition) {
		return eq(condition, true);
	}

	/**
	 * 
Getter for the field databaseType.

	 *
	 * @return a int.
	 */
	public int getDatabaseType() {
		return databaseType;
	}

	/**
	 * 设置数据库类型
	 *
	 * @param databaseType
	 *            采用BaseDao里的数据类型定义
	 */
	public void setDatabaseType(int databaseType) {
		this.databaseType = databaseType;
	}

	/**
	 * 
or.

	 *
	 * @param cond a {@link SqlHelper} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper or(SqlHelper cond) {
		if (orCond == null) {
            orCond = new ArrayList<>();
        }
		this.orCond.add(cond);
		return this;
	}

	/**
	 * 
not.

	 *
	 * @param cond a {@link SqlHelper} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper not(SqlHelper cond) {
		if (notCond == null) {
            notCond = new ArrayList<>();
        }
		this.notCond.add(cond);
		return this;
	}

	/**
	 * 
in.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param value a {@link java.util.Collection} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper in(String field, Collection value) {
		filters.add(new InFilter(field, value));
		return this;
	}

	/**
	 * 
in.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param value a {@link SqlString} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper in(String field, SqlString value) {
		filters.add(new InFilter(field, value));
		return this;
	}


	/**
	 * 
isNull.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper isNull(String field) {
		filters.add(new NullFilter(field));
		return this;
	}

	public SqlHelper isNotNull(String field) {
		filters.add(new NotNullFilter(field));
		return this;
	}

	/**
	 * 
at.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param value a {@link ISqlConditionType} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper at(String field, ISqlConditionType value) {
		filters.add(new CondFilter(field, value, databaseType));
		return this;
	}

	/**
	 * 
like.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param value a {@link java.lang.String} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper like(String field, String value) {
		filters.add(new LikeFilter(field, value));
		return this;
	}

	/**
	 * 
startWith.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param value a {@link java.lang.String} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper startWith(String field, String value) {
		filters.add(new StartWithFilter(field, value));
		return this;
	}

	/**
	 * 
eq.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param value a {@link java.lang.Object} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper eq(String field, Object value) {
		filters.add(new EqualsFilter(field, value));
		return this;
	}

	/**
	 * 
between.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param leftValue a {@link java.lang.Object} object.
	 * @param rightValue a {@link java.lang.Object} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper between(String field, Object leftValue, Object rightValue) {
		filters.add(new BetweenFilter(field, leftValue, rightValue));
		return this;
	}

	/**
	 * 
 not between.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param leftValue a {@link java.lang.Object} object.
	 * @param rightValue a {@link java.lang.Object} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper notBetween(String field, Object leftValue, Object rightValue) {
		filters.add(new NotBetweenFilter(field, leftValue, rightValue));
		return this;
	}


	/**
	 * 
ne.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param value a {@link java.lang.Object} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper ne(String field, Object value) {
		filters.add(new NotEqualsFilter(field, value));
		return this;
	}

	/**
	 * 
eq.

	 *
	 * @param fields an array of {@link java.lang.String} objects.
	 * @param value an array of {@link java.lang.Object} objects.
	 * @return a {@link SqlHelper} object.
	 * @throws DbException if any.
	 */
	public SqlHelper eq(String[] fields, Object[] value) {
		if (fields.length != value.length) {
            throw new DbException("fields.length must equals value.length:"
                    + StringUtils.join(fields,",") + "->" + StringUtils.join(value,","));
        }
		int i = 0;
		for (String field : fields) {
			filters.add(new EqualsFilter(field, value[i++]));
		}
		return this;
	}

	/**
	 * 
greaterThan.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param value a {@link java.lang.Object} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper greaterThan(String field, Object value) {
		filters.add(new GtFilter(field, value));
		return this;
	}

	public SqlHelper greaterOrEqualThan(String field, Object value) {
		filters.add(new GeFilter(field, value));
		return this;
	}

	/**
	 * 
smallerThan.

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param value a {@link java.lang.Object} object.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper smallerThan(String field, Object value) {
		filters.add(new LtFilter(field, value));
		return this;
	}

	public SqlHelper smallerOrEqualThan(String field, Object value) {
		filters.add(new LeFilter(field, value));
		return this;
	}

	/**
	 * 
isUseNullAsCondition.

	 *
	 * @return a boolean.
	 */
	public boolean isUseNullAsCondition() {
		return useNullAsCondition;
	}

	/**
	 * 
Setter for the field useNullAsCondition.

	 *
	 * @param useNullAsCondition a boolean.
	 */
	public void setUseNullAsCondition(boolean useNullAsCondition) {
		this.useNullAsCondition = useNullAsCondition;
	}

	/**
	 * 
clear.

	 */
	public void clear() {
		if(filters!=null) {
            this.filters.clear();
        }
		if(orCond!=null) {
            this.orCond.clear();
        }
		if(notCond!=null) {
            this.notCond.clear();
        }
	}

	/**
	 * 
in 

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param value an array of {@link java.lang.Object} objects.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper in(String field, Object[] value) {
		filters.add(new InFilter(field, Arrays.asList(value)));
		return this;
	}

	/**
	 * 
 not in

	 *
	 * @param field a {@link java.lang.String} object.
	 * @param values an array of {@link java.lang.Object} objects.
	 * @return a {@link SqlHelper} object.
	 */
	public SqlHelper notIn(String field, Object[] values) {
		filters.add(new NotInFilter(field, Arrays.asList(values)));
		return this;
	}


	protected abstract static class BaseFilter {
		protected final String property;
		protected final Object value;

		public BaseFilter(String property, Object value) {
			this.property = property;
			this.value = value;
		}

		/**
		 * extends input condition with current filter's condition
		 * @param pkt SqlPocket to extend
		 * @return result SqlPocket
		 */
		abstract public SqlPocket build(SqlPocket pkt) ;

	}

	protected static class NotInFilter extends InFilter {
		public NotInFilter(String property, Collection value) {
			super(property, new HashSet<>(value)); //use Hashset to reduce duplicated values
			op = "not in";
		}

		public NotInFilter(String property, SqlString value) {
			super(property, value); //use Hashset to reduce duplicated values
			op = "not in";
		}

	}

	protected static class InFilter extends BaseFilter {
		String op ="in";
		public InFilter(String property, Collection value) {
			super(property, new HashSet<>(value)); //use Hashset to reduce duplicated values
		}

		public InFilter(String property, SqlString value) {
			super(property, value); //use Hashset to reduce duplicated values
		}

		@Override
		public SqlPocket build(SqlPocket sb) {
			if (value instanceof SqlString) {
				sb.getCondition().append(" and ").append(property).append(" "+op+" (").append(value).append(")");

			} else if (!(value instanceof Collection)) {
				throw new DbException("InFilter must have a Collection value:"
						+ value + "->" + value.getClass());
			} else {
				int size = ((Collection) value).size();
				sb.addFilter(" and " +
						property + " "+op+" (" + StringUtils.repeat("?", ",", size)
						+ ")", value);
			}
			return sb;
		}
	}

	protected static abstract class AbstractBiFilter extends BaseFilter {
		String sign;

		public AbstractBiFilter(String property, Object value, String sign) {
			super(property, value);
			this.sign = sign;
		}

		@Override
		public SqlPocket build(SqlPocket sb) {
			if (value instanceof SqlString) {
				sb.getCondition().append(" and ").append(property).append(sign)
						.append(((SqlString) value).getContent());
			} else if (value != null) {
				sb.getCondition().append(" and ").append(property).append(sign)
						.append("?");
				sb.getParams().add(value);
			} else {
				sb.getCondition().append(" and ").append(property).append(sign)
						.append("null");
			}
			return sb;
		}
	}

	protected static class GtFilter extends AbstractBiFilter {
		public GtFilter(String property, Object value) {
			super(property, value, " > ");
		}
	}

	protected static class LtFilter extends AbstractBiFilter {
		public LtFilter(String property, Object value) {
			super(property, value, " < ");
		}
	}

	protected static class GeFilter extends AbstractBiFilter {
		public GeFilter(String property, Object value) {
			super(property, value, " >= ");
		}
	}

	protected static class LeFilter extends AbstractBiFilter {
		public LeFilter(String property, Object value) {
			super(property, value, " <= ");
		}
	}

	protected static class EqualsFilter extends AbstractBiFilter {
		public EqualsFilter(String property, Object value) {
			super(property, value, " = ");
		}
	}

	protected static class NotEqualsFilter extends AbstractBiFilter {
		public NotEqualsFilter(String property, Object value) {
			super(property, value, " != ");
		}
	}

	protected static class NullFilter extends AbstractBiFilter {
		public NullFilter(String property) {
			super(property, null, " is ");
		}
	}

	protected static class NotNullFilter extends AbstractBiFilter {
		public NotNullFilter(String property) {
			super(property, null, " is not ");
		}
	}
	protected static class CondFilter extends BaseFilter {
		int databaseType;

		public CondFilter(String property, Object value, int databaseType) {
			super(property, value);
			this.databaseType = databaseType;
		}

		@Override
		public SqlPocket build(SqlPocket sb) {
			if (value != null && value instanceof ISqlConditionType) {
				sb.getCondition().append(
						((ISqlConditionType) value).getConditionSql(property,
								databaseType));
			} else {
				throw new DbException(
						"CondFilter constructor only accept value of type ISqlConditionType:"
								+ value + "->" + (value == null ? "null" : value.getClass()));
			}
			return sb;
		}
	}

	protected static abstract class BaseLikeFilter extends BaseFilter {
		public BaseLikeFilter(String property, Object value){
			super(property, value);
		}

		public abstract String getConcat();

		@Override
		public SqlPocket build(SqlPocket sb) {
			if (value != null && value instanceof String) {
				sb.getCondition().append(" and ").append(property)
						.append(" like ").append(getConcat());
				sb.getParams().add(value);
			} else {
				throw new DbException(
						"CondFilter constructor only accept value of type ISqlConditionType:"
								+ value + "->" + (value == null ? "null" : value.getClass()));
			}
			return sb;
		}

	}

	protected static class LikeFilter extends BaseLikeFilter {
		public LikeFilter(String property, Object value){
			super(property, value);
		}

		@Override
		public String getConcat(){ return "concat('%',?,'%')"; }
	}

	protected static class StartWithFilter extends BaseLikeFilter {
		public StartWithFilter(String property, Object value){
			super(property, value);
		}

		@Override
		public String getConcat(){ return "concat(?,'%')";
		}

	}

	protected static class BetweenFilter extends BaseFilter {
		protected String operator = " between ";
		public BetweenFilter(String property, Object valueLeft, Object valueRight) {
			super(property, Pair.of(valueLeft, valueRight));
		}

		@Override
		public SqlPocket build(SqlPocket sb) {
			if(value instanceof Pair) {
				Pair p = (Pair) value;
				if (p.getLeft() instanceof SqlString) {
					sb.getCondition().append(" and ").append(property).append(operator).append(p.getLeft());
				} else{
					sb.getCondition().append(" and ").append(property).append(operator).append("?");
					sb.getParams().add(p.getLeft());
				}

				if (p.getRight() instanceof SqlString) {
					sb.getCondition().append(" and ").append(p.getRight());
				} else{
					sb.getCondition().append(" and ").append("?");
					sb.getParams().add(p.getRight());
				}

			} else {
				throw new DbException(
						"BetweenFilter constructor only accept value of type SqlString or Pair object:"
								+ value + "->" + (value == null ? "null" : value.getClass()));
			}
			return sb;
		}
	}

	protected static class NotBetweenFilter extends BetweenFilter {
		public NotBetweenFilter(String property, Object valueLeft, Object valueRight) {
			super(property, valueLeft, valueRight);
			operator = " not between ";
		}
	}

}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy