Mybatis Criteria使用and和or进行联合条件查询的操作方法

public List<Contact> searchByExample(Contact contact) {

         System.out.println( "searchByExampleContact" );

         ContactExample example = new ContactExample();

         ContactExample.Criteria cri = example.createCriteria();

         if ( this .objectAttrNullCheck(contact, "username" ))

             cri.andUsernameEqualTo(contact.getUsername());

         if ( this .objectAttrNullCheck(contact, "password" ))

             cri.andPasswordEqualTo(contact.getPassword());

         ContactMapper vcontactMapper = sqlSession

                 .getMapper(ContactMapper. class );

         List<Contact> returnList = vcontactMapper.selectByExample(example);

         return returnList;

}

andUsernameEqualTo

andPasswordEqualTo

import java.io.IOException;

import java.io.Reader;

import java.util.ArrayList;

import java.util.List;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.apache.log4j.pattern.ClassNamePatternConverter;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import cn.itcast.ssm.mapper.ItemsMapper;

import cn.itcast.ssm.po.ItemsExample;

public class Student {

     public static void main(String[] args) throws IOException {

         /*方式一  */

         ItemsExample itemsExample1 = new ItemsExample();

         itemsExample1.or().andIdEqualTo( 5 ).andNameIsNotNull();

         itemsExample1.or().andPicEqualTo( "xxx" ).andPicIsNull();

         List<Integer> fieldValues = new ArrayList<Integer>();

         fieldValues.add( 8 );

         fieldValues.add( 11 );

         fieldValues.add( 14 );

         fieldValues.add( 22 );

         itemsExample1.or().andIdIn(fieldValues);

         itemsExample1.or().andIdBetween( 5 , 9 );

         /*  方式二 criteria1与criteria2是or的关系 */

         ItemsExample itemsExample2 = new ItemsExample();

         ItemsExample.Criteria criteria1 = itemsExample2.createCriteria();

         criteria1.andIdIsNull();

         criteria1.andPriceEqualTo(( float ) 3 );

         ItemsExample.Criteria criteria2 = itemsExample2.createCriteria();

         criteria2.andNameIsNull();

         criteria2.andIdGreaterThanOrEqualTo( 5 );

         itemsExample2.or(criteria2);

         //方式一和方式二是等价的

         // spring获取mapper代理对象

         ApplicationContext applicationContext = new ClassPathXmlApplicationContext( "classpath:applicationContext.xml" );

         ItemsMapper itemsMapper = (ItemsMapper) applicationContext.getBean( "itemsMapper" );

         itemsMapper.countByExample(itemsExample2);

         // 获取SqlSessionFactory

         String resource = "SqlMapConfig.xml" ;

         Reader reader = Resources.getResourceAsReader(resource);

         SqlSessionFactory sqlMapper = new SqlSessionFactoryBuilder().build(reader);

         // 获取SqlSession

         SqlSession sqlSession = sqlMapper.openSession();

     }

}

public void getQueryExampleByCondition(Example example, OrderQuery query) {

         Criteria criteria1 = example.createCriteria();

         // 设置 criteria2 与 criteria1 为 or 方式关联

         Criteria criteria2 = example.or();

         if (query != null ) {

             if (query.getLoadSrc() != null && query.getLoadSrc() != - 1 ) {

                 criteria1.andEqualTo( "loadSrc" , query.getLoadSrc());

             }

             if (!StringUtil.isEmpty(query.getOrderNo())) {

                 criteria1.andLike( "orderNo" , "%" + query.getOrderNo() + "%" );

             }

             // 以下 3 个会用 OR 方式加上 criteria2 中的条件

             if (query.getStatus() != null && query.getStatus() != - 1 ) {

                 criteria1.andEqualTo( "status" , query.getStatus());

                 criteria2.andEqualTo( "status" , query.getStatus());

             }

             if (!StringUtil.isEmpty(query.getRepayDateEnd())) {

                 criteria1.andLessThanOrEqualTo( "repayDate" , query.getRepayDateEnd());

                 criteria2.andLessThanOrEqualTo( "repayDate" , query.getRepayDateEnd());

             }

             if (!StringUtil.isEmpty(query.getLoanDay())) {

                 criteria1.andEqualTo( "loanDay" , query.getLoanDay());

                 criteria1.andEqualTo( "loanDay" , query.getLoanDay());

             }

         }

     }

SELECT * FROM xxxx WHERE (load_type = 5 AND loan_day = 7) OR (order_No = 5)

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);

select count (*) from demo WHERE ( ID = ? and STATUS = ? ) or ( ID = ? and STATUS = ? )