What is the operation method of Mybatis Criteria's joint conditional query using and and or?

Today, I will talk to you about how Mybatis Criteria uses and and or to query joint conditions. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Before using the Mybatis framework reverse entity, and the entity inside the Example, just know that Example is put in the conditional query method, you can not know how to use, until today began to know how to use it. When we inquire at the front desk, there will be a lot of conditions coming: let's take a look at an example:

Public List 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 returnList = vcontactMapper.selectByExample (example); return returnList;}

This is a simple user login background code, there is a Criterria method in example, inside

AndUsernameEqualTo andPasswordEqualTo

Are generated when the example is generated. These two methods judge the single value.

Criterion

Criterion is the most basic and lowest-level Where condition for field-level filtering.

Criteria

Criteria contains a collection of Criterion, and the Criterion contained in each Criteria object is connected by AND, which is a logical relationship with.

Other

The distinct field of the Example class is used to specify the DISTINCT query.

The orderByClause field is used to specify the ORDER BY condition, which has no constructor and is specified directly by passing a string value.

Code example

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 fieldValues = new ArrayList (); fieldValues.add (8); fieldValues.add (11) FieldValues.add (14); fieldValues.add (22); itemsExample1.or (). AndIdIn (fieldValues); itemsExample1.or (). AndIdBetween (5,9); / * the relationship between criteria1 and criteria2 is 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); / / Mode 1 and Mode 2 are equivalent / / spring gets the mapper proxy object ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("classpath:applicationContext.xml"); ItemsMapper itemsMapper = (ItemsMapper) applicationContext.getBean ("itemsMapper"); itemsMapper.countByExample (itemsExample2) / / get SqlSessionFactory String resource = "SqlMapConfig.xml"; Reader reader = Resources.getResourceAsReader (resource); SqlSessionFactory sqlMapper = new SqlSessionFactoryBuilder () .build (reader); / / get SqlSession SqlSession sqlSession = sqlMapper.openSession ();}}

Code case 2:

Public void getQueryExampleByCondition (Example example, OrderQuery query) {Criteria criteria1 = example.createCriteria (); / / sets the association between criteria2 and criteria1 as 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 () + "%") } / / the following three will use OR to add the conditional if in criteria2 (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 ());}}

The relationship between criteria1 and criteria2 is or.

Depending on the parameters passed in, the effect of executing sql is similar:

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

The conditional combination in the first parenthesis after where load_type = 5 AND loan_day = 7 is the condition in criteria1

The condition order_No = 5 in the second parenthesis is the condition setting in criteria2.

And and or of Criteria are jointly queried

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)

Generate the following SQL

Select count (*) from demo WHERE (ID =? And STATUS =?) Or (ID =? And STATUS =?)

Limits exist in order to transcend them.

After reading the above, do you have any further understanding of Mybatis Criteria's operation of federated conditional queries using and and or? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.