- 在mybatis中如何形成动态条件,我们可以使用 “实体类Exmple” 类对条件进行封装。我们可以借助idea的插件进行自动生成 “实体类对应Exmple” 类(举例代码在最后)。
在创建实体类Exmple类时有两种方法:createcriteria和or方法,他们有什么区别呢?
通过源码,能很清楚的看出差别。
createcriteria,当没有规则时,则加入到现有规则,但有规则时,不再加入到现有规则,只是返回创建的规则
public Criteria createCriteria() {
Criteria criteria = createCriteriaInternal();
if (oredCriteria.size() == 0) {
oredCriteria.add(criteria);
}
return criteria;
}
or,创建的规则,加入到规则集中,并且是or的关系。
public Criteria or() {
Criteria criteria = createCriteriaInternal();
oredCriteria.add(criteria);
return criteria;
}
例如:
@Test
public void test2() {
Test1Example example=new Test1Example();
Test1Example.Criteria criteria=example.createCriteria();
criteria.andIdBetween(11,14);
example.createCriteria().andScoreBetween(70,80);
Test1Example.Criteria criteriaOr1=example.or();
criteriaOr1.andIdEqualTo(15);
Test1Example.Criteria criteriaOr2=example.or();
criteriaOr2.andIdGreaterThanOrEqualTo(17);
List<Test1> list=test1ExmpleDao.selectByExample(example);
System.out.println(Arrays.toString(list.toArray()));
}
最后执行的sql:
日志:
Preparing: select id, name
, course, score from test1 WHERE ( id between ? and ? ) or( id = ? ) or( id >= ? )
select distinct id, `name`, course, score FROM test1
WHERE ( id between 11 and 14 ) or( id = 15 ) or( id >= 17 );
从sql执行的语句来看,example.createCriteria().andScoreBetween(70,80);创建的规则并没有添加到sql中
那么如果需要将此条件加入规则且用AND进行连接的话怎么办了?
则需要使用第一次创建的criteria进行添加规则,如:
@Test
public void test2() {
Test1Example example=new Test1Example();
Test1Example.Criteria criteria=example.createCriteria();
criteria.andIdBetween(11,14);
criteria.andScoreBetween(70,80);
Test1Example.Criteria criteriaOr1=example.or();
criteriaOr1.andIdEqualTo(15);
Test1Example.Criteria criteriaOr2=example.or();
criteriaOr2.andIdGreaterThanOrEqualTo(17);
List<Test1> list=test1ExmpleDao.selectByExample(example);
System.out.println(Arrays.toString(list.toArray()));
}
最后执行的sql:
日志:
Preparing: select id, name
, course, score from test1 WHERE ( id between ? and ? and score between ? and ? ) or( id = ? ) or( id >= ? )
select id, `name`, course, score FROM test1
WHERE ( id between 11 and 14 and score between 70 and 80 ) or( id = 15 ) or( id >= 17 );
如果需要用and 连接or中的规则?
则使用criteriaOr2(criteriaOr1也行,具体的看实际的业务情况,此处以criteriaOr2举例)
@Test
public void test2() {
Test1Example example=new Test1Example();
Test1Example.Criteria criteria=example.createCriteria();
criteria.andIdBetween(11,14);
criteria.andScoreBetween(70,80);
Test1Example.Criteria criteriaOr1=example.or();
criteriaOr1.andIdEqualTo(15);
Test1Example.Criteria criteriaOr2=example.or();
criteriaOr2.andIdGreaterThanOrEqualTo(17);
criteriaOr2.andScoreGreaterThan(60);
List<Test1> list=test1ExmpleDao.selectByExample(example);
System.out.println(Arrays.toString(list.toArray()));
}
日志:
Preparing: select id, name
, course, score from test1 WHERE ( id between ? and ? and score between ? and ? ) or( id = ? ) or( id >= ? and score > ? )
select id, `name`, course, score FROM test1
WHERE ( id between 11 and 14 and score between 70 and 80 ) or( id = 15 ) or( id >= 17 and score > 60 );
下面贴出实体类以及“实体类Exmple”
- 实体类:
@ToString
@Data
public class Test1 implements Serializable {
private static final long serialVersionUID = -31102224517137006L;
private Integer id;
private String name;
private String course;
private Integer score;
}
2. 实体类Exmple:
public class Test1Example {
protected String orderByClause;
protected boolean distinct;
protected List<Criteria> oredCriteria;
public Test1Example() {
oredCriteria = new ArrayList<Criteria>();
}
public void setOrderByClause(String orderByClause) {
this.orderByClause = orderByClause;
}
public String getOrderByClause() {
return orderByClause;
}
public void setDistinct(boolean distinct) {
this.distinct = distinct;
}
public boolean isDistinct() {
return distinct;
}
public List<Criteria> getOredCriteria() {
return oredCriteria;
}
public void or(Criteria criteria) {
oredCriteria.add(criteria);
}
public Criteria or() {
Criteria criteria = createCriteriaInternal();
oredCriteria.add(criteria);
return criteria;
}
public Criteria createCriteria() {
Criteria criteria = createCriteriaInternal();
if (oredCriteria.size() == 0) {
oredCriteria.add(criteria);
}
return criteria;
}
protected Criteria createCriteriaInternal() {
Criteria criteria = new Criteria();
return criteria;
}
public void clear() {
oredCriteria.clear();
orderByClause = null;
distinct = false;
}
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) {
if (condition == null) {
throw new RuntimeException("Value for condition cannot be null");
}
criteria.add(new Criterion(condition));
}
protected void addCriterion(String condition, Object value, String property) {
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 (value1 == null || value2 == null) {
throw new RuntimeException("Between values for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value1, value2));
}
public Criteria andIdIsNull() {
addCriterion("id is null");
return (Criteria) this;
}
public Criteria andIdIsNotNull() {
addCriterion("id is not null");
return (Criteria) this;
}
public Criteria andIdEqualTo(Integer value) {
addCriterion("id =", value, "id");
return (Criteria) this;
}
public Criteria andIdNotEqualTo(Integer value) {
addCriterion("id <>", value, "id");
return (Criteria) this;
}
public Criteria andIdGreaterThan(Integer value) {
addCriterion("id >", value, "id");
return (Criteria) this;
}
public Criteria andIdGreaterThanOrEqualTo(Integer value) {
addCriterion("id >=", value, "id");
return (Criteria) this;
}
public Criteria andIdLessThan(Integer value) {
addCriterion("id <", value, "id");
return (Criteria) this;
}
public Criteria andIdLessThanOrEqualTo(Integer value) {
addCriterion("id <=", value, "id");
return (Criteria) this;
}
public Criteria andIdIn(List<Integer> values) {
addCriterion("id in", values, "id");
return (Criteria) this;
}
public Criteria andIdNotIn(List<Integer> values) {
addCriterion("id not in", values, "id");
return (Criteria) this;
}
public Criteria andIdBetween(Integer value1, Integer value2) {
addCriterion("id between", value1, value2, "id");
return (Criteria) this;
}
public Criteria andIdNotBetween(Integer value1, Integer value2) {
addCriterion("id not between", value1, value2, "id");
return (Criteria) this;
}
public Criteria andNameIsNull() {
addCriterion("`name` is null");
return (Criteria) this;
}
public Criteria andNameIsNotNull() {
addCriterion("`name` is not null");
return (Criteria) this;
}
public Criteria andNameEqualTo(String value) {
addCriterion("`name` =", value, "name");
return (Criteria) this;
}
public Criteria andNameNotEqualTo(String value) {
addCriterion("`name` <>", value, "name");
return (Criteria) this;
}
public Criteria andNameGreaterThan(String value) {
addCriterion("`name` >", value, "name");
return (Criteria) this;
}
public Criteria andNameGreaterThanOrEqualTo(String value) {
addCriterion("`name` >=", value, "name");
return (Criteria) this;
}
public Criteria andNameLessThan(String value) {
addCriterion("`name` <", value, "name");
return (Criteria) this;
}
public Criteria andNameLessThanOrEqualTo(String value) {
addCriterion("`name` <=", value, "name");
return (Criteria) this;
}
public Criteria andNameLike(String value) {
addCriterion("`name` like", value, "name");
return (Criteria) this;
}
public Criteria andNameNotLike(String value) {
addCriterion("`name` not like", value, "name");
return (Criteria) this;
}
public Criteria andNameIn(List<String> values) {
addCriterion("`name` in", values, "name");
return (Criteria) this;
}
public Criteria andNameNotIn(List<String> values) {
addCriterion("`name` not in", values, "name");
return (Criteria) this;
}
public Criteria andNameBetween(String value1, String value2) {
addCriterion("`name` between", value1, value2, "name");
return (Criteria) this;
}
public Criteria andNameNotBetween(String value1, String value2) {
addCriterion("`name` not between", value1, value2, "name");
return (Criteria) this;
}
public Criteria andCourseIsNull() {
addCriterion("course is null");
return (Criteria) this;
}
public Criteria andCourseIsNotNull() {
addCriterion("course is not null");
return (Criteria) this;
}
public Criteria andCourseEqualTo(String value) {
addCriterion("course =", value, "course");
return (Criteria) this;
}
public Criteria andCourseNotEqualTo(String value) {
addCriterion("course <>", value, "course");
return (Criteria) this;
}
public Criteria andCourseGreaterThan(String value) {
addCriterion("course >", value, "course");
return (Criteria) this;
}
public Criteria andCourseGreaterThanOrEqualTo(String value) {
addCriterion("course >=", value, "course");
return (Criteria) this;
}
public Criteria andCourseLessThan(String value) {
addCriterion("course <", value, "course");
return (Criteria) this;
}
public Criteria andCourseLessThanOrEqualTo(String value) {
addCriterion("course <=", value, "course");
return (Criteria) this;
}
public Criteria andCourseLike(String value) {
addCriterion("course like", value, "course");
return (Criteria) this;
}
public Criteria andCourseNotLike(String value) {
addCriterion("course not like", value, "course");
return (Criteria) this;
}
public Criteria andCourseIn(List<String> values) {
addCriterion("course in", values, "course");
return (Criteria) this;
}
public Criteria andCourseNotIn(List<String> values) {
addCriterion("course not in", values, "course");
return (Criteria) this;
}
public Criteria andCourseBetween(String value1, String value2) {
addCriterion("course between", value1, value2, "course");
return (Criteria) this;
}
public Criteria andCourseNotBetween(String value1, String value2) {
addCriterion("course not between", value1, value2, "course");
return (Criteria) this;
}
public Criteria andScoreIsNull() {
addCriterion("score is null");
return (Criteria) this;
}
public Criteria andScoreIsNotNull() {
addCriterion("score is not null");
return (Criteria) this;
}
public Criteria andScoreEqualTo(Integer value) {
addCriterion("score =", value, "score");
return (Criteria) this;
}
public Criteria andScoreNotEqualTo(Integer value) {
addCriterion("score <>", value, "score");
return (Criteria) this;
}
public Criteria andScoreGreaterThan(Integer value) {
addCriterion("score >", value, "score");
return (Criteria) this;
}
public Criteria andScoreGreaterThanOrEqualTo(Integer value) {
addCriterion("score >=", value, "score");
return (Criteria) this;
}
public Criteria andScoreLessThan(Integer value) {
addCriterion("score <", value, "score");
return (Criteria) this;
}
public Criteria andScoreLessThanOrEqualTo(Integer value) {
addCriterion("score <=", value, "score");
return (Criteria) this;
}
public Criteria andScoreIn(List<Integer> values) {
addCriterion("score in", values, "score");
return (Criteria) this;
}
public Criteria andScoreNotIn(List<Integer> values) {
addCriterion("score not in", values, "score");
return (Criteria) this;
}
public Criteria andScoreBetween(Integer value1, Integer value2) {
addCriterion("score between", value1, value2, "score");
return (Criteria) this;
}
public Criteria andScoreNotBetween(Integer value1, Integer value2) {
addCriterion("score not between", value1, value2, "score");
return (Criteria) this;
}
}
/**
*/
public static class Criteria extends GeneratedCriteria {
protected Criteria() {
super();
}
}
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);
}
}
}
3. XML(此处只是贴出条件查询的Mapper.xml):
<select id="selectByExample" parameterType="com.hydata.studyMybatisPlus.entity.Test1Example" resultMap="BaseResultMap">
select
<if test="distinct">
distinct
</if>
<include refid="Base_Column_List" />
from test1
<if test="_parameter != null">
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null">
order by ${orderByClause}
</if>
</select>