mybatis 通用查询实现

package com.oceano.modity.entity;

import java.util.ArrayList;
import java.util.List;

public class CriteriaExample {
    protected List<OrderByCase> orderByList;

    protected boolean distinct;

    protected List<Criteria> criteriaList;

    public List<OrderByCase> getOrderByList() {
	return orderByList;
    }

    public void setOrderByList(List<OrderByCase> orderByList) {
	this.orderByList = orderByList;
    }

    public boolean isDistinct() {
	return distinct;
    }

    public void setDistinct(boolean distinct) {
	this.distinct = distinct;
    }

    public List<Criteria> getCriteriaList() {
	return criteriaList;
    }

    public void setCriteriaList(List<Criteria> criteriaList) {
	this.criteriaList = criteriaList;
    }

    @Override
    public String toString() {
	return "CriteriaExample [orderByList=" + orderByList + ", distinct=" + distinct + ", criteriaList="
		+ criteriaList + ", getOrderByList()=" + getOrderByList() + ", isDistinct()=" + isDistinct()
		+ ", getCriteriaList()=" + getCriteriaList() + ", getClass()=" + getClass() + ", hashCode()="
		+ hashCode() + ", toString()=" + super.toString() + "]";
    }

    public CriteriaExample addOrderByCase(String field, String direction) {
	if (this.orderByList == null) {
	    this.orderByList = new ArrayList<>();
	}
	this.orderByList.add(new OrderByCase(field, direction));
	return (CriteriaExample) this;
    }

    public CriteriaExample addCriteria(Criteria criteria) {
	if (this.criteriaList == null) {
	    this.criteriaList = new ArrayList<>();
	}
	this.criteriaList.add(criteria);
	return (CriteriaExample) this;
    }

    public static class OrderByCase {
	public static String ASC = "ASC";
	public static String DESC = "DESC";
	private String condition;
	private String direction; // ASC,DESC

	public String getCondition() {
	    return condition;
	}

	public void setCondition(String condition) {
	    this.condition = condition;
	}

	public String getDirection() {
	    return direction;
	}

	public void setDirection(String direction) {
	    this.direction = direction;
	}

	public OrderByCase(String condition, String direction) {
	    super();
	    this.condition = condition;
	    this.direction = direction;
	}

	@Override
	public String toString() {
	    return "OrderByCase [condition=" + condition + ", direction=" + direction + "]";
	}
    }

    protected abstract static class GeneratedCriteria {
	protected List<Criterion> criteria;

	protected GeneratedCriteria() {
	    super();
	    criteria = new ArrayList<Criterion>();
	}

	public boolean isValid() {
	    return criteria.size() > 0;
	}

	public List<Criterion> getAllCriteria() {
	    return criteria;
	}

	public List<Criterion> getCriteria() {
	    return criteria;
	}

	protected void addCriterion(String condition, String property) {
	    if (condition == null) {
		throw new RuntimeException("condition for " + property + " cannot be null");
	    }

	    criteria.add(new Criterion(condition));
	}

	protected void addCriterion(String condition, Object value, String property) {
	    if (condition == null) {
		throw new RuntimeException("condition for " + property + " cannot be null");
	    }
	    if (value == null) {
		throw new RuntimeException("Value for " + property + " cannot be null");
	    }
	    criteria.add(new Criterion(condition, value));
	}

	protected void addCriterion(String condition, Object value1, Object value2, String property) {
	    if (condition == null) {
		throw new RuntimeException("condition for " + property + " cannot be null");
	    }
	    if (value1 == null || value2 == null) {
		throw new RuntimeException("Between values for " + property + " cannot be null");
	    }
	    criteria.add(new Criterion(condition, value1, value2));
	}

	public Criteria equal(String field, Object value) {
	    addCriterion(field + " =", value, field);
	    return (Criteria) this;
	}

	public Criteria notEqual(String field, Object value) {
	    addCriterion(field + " <> ", value, field);
	    return (Criteria) this;
	}

	public Criteria greaterThan(String field, Object value) {
	    addCriterion(field + " > ", value, field);
	    return (Criteria) this;
	}

	public Criteria lessThan(String field, Object value) {
	    addCriterion(field + " < ", value, field);
	    return (Criteria) this;
	}

	public Criteria lessThanOrEqual(String field, Object value) {
	    addCriterion(field + " <= ", value, field);
	    return (Criteria) this;
	}

	public <T> Criteria in(String field, List<T> valueList) {
	    addCriterion(field + " in ", valueList, field);
	    return (Criteria) this;
	}

	public <T> Criteria notIn(String field, List<T> valueList) {
	    addCriterion(field + " not in ", valueList, field);
	    return (Criteria) this;
	}

	public Criteria between(String field, Object value1, Object value2) {
	    addCriterion(field + " between ", value1, value2, field);
	    return (Criteria) this;
	}

	public Criteria notBetween(String field, Object value1, Object value2) {
	    addCriterion(field + " not between ", value1, value2, field);
	    return (Criteria) this;
	}

	public Criteria isNull(String field) {
	    addCriterion(field + " is null ", field);
	    return (Criteria) this;
	}

	public Criteria isNotNull(String field) {
	    addCriterion(field + " is not null ", field);
	    return (Criteria) this;
	}

	public Criteria like(String field, Object value) {
	    addCriterion(field + " like concat(concat('%',\"" + value + "\",'%'))", field);
	    return (Criteria) this;
	}

	public Criteria notLike(String field, Object value) {
	    addCriterion(field + " not like concat(concat('%',\"" + value + "\",'%'))", field);
	    // addCriterion(field + " not like ", value, field);
	    return (Criteria) this;
	}
    }

    public static class Criteria extends GeneratedCriteria {
	public Criteria() {
	    super();
	}

	@Override
	public String toString() {
	    return "Criteria [criteria=" + criteria + ", isValid()=" + isValid() + ", getAllCriteria()="
		    + getAllCriteria() + ", getCriteria()=" + getCriteria() + ", getClass()=" + getClass()
		    + ", hashCode()=" + hashCode() + ", toString()=" + super.toString() + "]";
	}

    }

    public static class Criterion {
	private String condition;

	private Object value;

	private Object secondValue;

	private boolean noValue;

	private boolean singleValue;

	private boolean betweenValue;

	private boolean listValue;

	private String typeHandler;

	public String getCondition() {
	    return condition;
	}

	public Object getValue() {
	    return value;
	}

	public Object getSecondValue() {
	    return secondValue;
	}

	public boolean isNoValue() {
	    return noValue;
	}

	public boolean isSingleValue() {
	    return singleValue;
	}

	public boolean isBetweenValue() {
	    return betweenValue;
	}

	public boolean isListValue() {
	    return listValue;
	}

	public String getTypeHandler() {
	    return typeHandler;
	}

	protected Criterion(String condition) {
	    super();
	    this.condition = condition;
	    this.typeHandler = null;
	    this.noValue = true;
	}

	protected Criterion(String condition, Object value, String typeHandler) {
	    super();
	    this.condition = condition;
	    this.value = value;
	    this.typeHandler = typeHandler;
	    if (value instanceof List<?>) {
		this.listValue = true;
	    } else {
		this.singleValue = true;
	    }
	}

	protected Criterion(String condition, Object value) {
	    this(condition, value, null);
	}

	protected Criterion(String condition, Object value, Object secondValue, String typeHandler) {
	    super();
	    this.condition = condition;
	    this.value = value;
	    this.secondValue = secondValue;
	    this.typeHandler = typeHandler;
	    this.betweenValue = true;
	}

	protected Criterion(String condition, Object value, Object secondValue) {
	    this(condition, value, secondValue, null);
	}

	@Override
	public String toString() {
	    return "Criterion [condition=" + condition + ", value=" + value + ", secondValue=" + secondValue
		    + ", noValue=" + noValue + ", singleValue=" + singleValue + ", betweenValue=" + betweenValue
		    + ", listValue=" + listValue + ", typeHandler=" + typeHandler + "]";
	}

    }

}

<!-- <trim prefix="(" suffix=")" prefixOverrides="OR"> -->
	<sql id="Example_Where_Clause">
		<if test="criteriaList != null">
		<foreach collection="criteriaList" item="criteria" separator="AND">
				<trim prefix="(" suffix=")">
					<foreach collection="criteria.criteria" item="criterion"
						separator="OR">
						<choose>
							<when test="criterion.noValue">
								${criterion.condition}
							</when>
							<when test="criterion.singleValue">
								${criterion.condition} #{criterion.value}
							</when>
							<when test="criterion.betweenValue">
								${criterion.condition} #{criterion.value} AND
								#{criterion.secondValue}
							</when>
							<when test="criterion.listValue">
								${criterion.condition}
								<foreach collection="criterion.value" item="listItem"
									open="(" close=")" separator=",">
									#{listItem}
								</foreach>
							</when>
						</choose>
					</foreach>
				</trim>
		</foreach>
		</if>
	</sql>

	<sql id="orderBy_case">
		ORDER BY
		<if test="orderByList !=null">
			<foreach collection="orderByList" item="orderBy" separator=",">
				${orderBy.condition} ${orderBy.direction}	
			</foreach>
		</if>
	</sql>




	<select id="findByTest" parameterType="com.oceano.modity.entity.CriteriaExample"
		resultMap="modityPriceList">
		select * from t_modity_price
		<where>
			<include refid="Example_Where_Clause" />
		</where>
		<include refid="orderBy_case" />
	</select>

@RequestMapping("/sqlTest")
	@ResponseBody
	public String sqlTest() {


		CriteriaExample criteriaExample = new CriteriaExample();

		criteriaExample.addOrderByCase("colour_id", OrderByCase.ASC);
		criteriaExample.addOrderByCase("colour_code", OrderByCase.DESC);

		criteriaExample.setDistinct(true);


		Criteria criteria1 =new Criteria();
		criteria1.equal("colour_id", "Yellow");

		criteriaExample.addCriteria(criteria1);


//		Criteria criteria2 =new Criteria();
//		criteria2.notEqual("colour_code","test");
//		
//		criteriaExample.addCriteria(criteria2);

		Criteria criteria2 =new Criteria();
		criteria2.between("colour_id", 4, 5);

		criteriaExample.addCriteria(criteria2);
//		
		Criteria criteria3 =new Criteria();
		criteria3.like("colour_id", "yellow");
		criteria3.like("colour_code", "黄色");

		criteriaExample.addCriteria(criteria3);

		Criteria criteria4 =new Criteria();
		List<String> list = new ArrayList<>();
		list.add("123");
		list.add("456");

		criteria4.in("colour_id", list);
		criteria4.like("colour_code", "黄色");

		criteriaExample.addCriteria(criteria4);

//		Criteria criteria4 =new Criteria();
//		List<String> list = new ArrayList<>();
//		list.add("123");
//		list.add("456");
//		
//		criteria3.in("colour_id", list);
//		criteria3.like("colour_code", "黄色");
//		
//		criteriaExample.addCriteria(criteria4);



		List<T_ModityPrice> modityPriceList = modityPriceService.findByTest(criteriaExample);




		return "OK";

	}