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 = ? )

 

再例如：查询客户冻结流水，银行号是100，且客户机构是1000或者操作机构是1000，这样写：

public static MktCifFrozenJnlExample buildMergeCondition(Long bankSeq, Long deptSeq, Long createDeptSeq) {
    MktCifFrozenJnlExample example = new MktCifFrozenJnlExample();
    MktCifFrozenJnlExample.Criteria criteria = example.createCriteria();
    criteria.andBankSeqEqualTo(bankSeq);
    criteria.andDeptSeqEqualTo(deptSeq);
    
    example.or().andBankSeqEqualTo(bankSeq).andCreateDeptSeqEqualTo(createDeptSeq);
    return example;
}

生成的sql：

List<MktCifFrozenJnl> jnlList = MktCifFrozenJnlExampleBuild.buildMergeCondition(1, 1000, 1000);

生成的sql：
select * from MKT_CIF_FROZEN_JNL where (bank_seq = 1 and dept_seq = 1000) or (bank_seq = 1 and create_dept_seq = 1000);

 

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相当于一个括号，把里面的内容当成一个整体