Mybatis中的Criteria中or方法的使用
参考博客:mybatis createCriteria和or的区别
1、example.or()
TestTableExample example = new TestTableExample();
example.or()
.andField1EqualTo(5)
.andField2IsNull();
example.or()
.andField3NotEqualTo(9)
.andField4IsNotNull();
List field5Values = new ArrayList();
field5Values.add(8);
field5Values.add(11);
field5Values.add(14);
field5Values.add(22);
example.or()
.andField5In(field5Values);
example.or()
.andField6Between(3, 7);
生成的where句子是:
where (field1 = 5 and field2 is null)
or (field3 <> 9 and field4 is not null)
or (field5 in (8, 11, 14, 22))
or (field6 between 3 and 7)
将会返回满足这些条件的记录结果.
@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()));
}
生成的sql:
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 );
2、example.or(Criteria criteria)
直接将查询条件加入当前的oredCriteria中,并不会新建新查询条件
pubic void or(Criteria criteria){
oredCriteria.add(criteria);
}
Example example = new Example(User.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("gender", "女");
Example.Criteria anotherCriteria = example.createCriteria();
anotherCriteria.andLike("name", "张%");
example.or(anotherCriteria);
List<User> users = userMapper.selectByExample(example);
生成的sql大概是:
select * from user where gender = '女' or name like '张%'
3、想使用where id = xx and (status = xx or status =xx)
想使用这种形式的sql拼接,example来构造条件,网上找了很久没有很好的解决方案。但是还是办法是有的:
3.1 将查询条件分开,放在两个or的条件中
Criteria的and和or进行联合查询
DemoExample example=new DemoExample ();
DemoExample.Criteria criteria=example.createCriteria();
criteria.andidEqualTo(id);
criteria.andStatusEqualTo(“0”);
DemoExample.Criteria criteria2=example.createCriteria();
criteria2.andidEqualTo(id);
criteria2.andstatusEqualTo(“1”);
example.or(criteria2);
dao.countByExample(example);
生成的sql:
select count(*) from demo WHERE ( ID = ? and STATUS = ? ) or( ID = ? and STATUS = ? )
3.2 在example中自定义Criteria:
public Criteria andActivityStateEnd(String date){
addCriterion("((ACTIVITY_STATE = 'run' AND EXPIRE_TIME is not null AND EXPIRE_TIME < '"+date+"') or (ACTIVITY_STATE = 'end'))");
return (Criteria) this;
}
再比如:
public Criteria andStatusEqualTo(String value1, String value2) {
addCriterion("(status = '"+value1+"' or status = '"+value2+"')");
return (Criteria) this;
}
tips:
在Example中的每一个Criteria相当于一个括号,把里面的内容当成一个整体