Shulou(Shulou.com)06/02 Report--

Editor to share with you how to use Mybatis Example, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Advanced usage of Mybatis Example

Recent projects have been using mybatis to query the database, during which many efficient and concise query methods are used, which are hereby recorded and shared.

one。 Function of function and method name in mapper interface int countByExample (UserExample example) by conditional count int deleteByPrimaryKey (Integer id) press primary key delete int deleteByExample (UserExample example) conditional query String/Integer insert (User record) insert data (return value is ID) User selectByPrimaryKey (Integer id) press primary key query ListselectByExample (UserExample example) query ListselectByExampleWithBLOGs (UserExample example) query by condition (including BLOB field) It occurs only if the field type in the data table is binary. Int updateByPrimaryKey (User record) updates int updateByPrimaryKeySelective (User record) updates fields whose value is not null by primary key int updateByExample (User record, UserExample example) updates int updateByExampleSelective (User record, UserExample example) conditionally updates field two whose value is not null. Example instance method

Example is used to add conditions, which is equivalent to the following part of where. Theoretically, any complex conditional query for a single table can be done using example.

The method explains example.setOrderByClause ("field name ASC"), adds ascending order arrangement condition, DESC is descending order example.setDistinct (false) to remove repetition, Boolean type, true is selected non-repetitive record. Example.and (Criteria criteria) adds criteria query conditions for example, and the relationship is to add criteria query conditions for example with example.or (Criteria criteria) Relation or criteria.andXxxIsNull add field xxx is null conditional criteria.andXxxIsNotNull add field xxx is not null conditional criteria.andXxxEqualTo (value) add xxx field is equal to value conditional criteria.andXxxNotEqualTo (value) add xxx field is not equal to value conditional criteria.andXxxGreaterThan (value) add xxx field greater than value conditional criteria.andXxxGreaterThanOrEqualTo (value) add xxx field greater than or equal to value conditional criteria.andXxxLessThan (value) add xxx field less than value conditional criteria.andXxxLessThanOrEqualTo (value) Add xxx field less than or equal to value condition criteria.andXxxIn (List) add xxx field value in List condition criteria.andXxxNotIn (List) add xxx field value not in List condition criteria.andXxxLike ("%" + value+ "%") add fuzzy query condition criteria.andXxxNotLike ("%" + value+ "%") where xxx field value is value) add fuzzy query condition criteria.andXxxBetween (value1) whose xxx field value is not value Value2) add xxx field value between value1 and value2 condition criteria.andXxxNotBetween (value1,value2) add xxx field value is not between value1 and value2 condition 3. Use case

1. Basic field query

/ / 1. Use criteria Example example = new Example (User.class); Criteria criteria = example.createCriteria (); criteria.andEqualTo ("name", name); criteria.andNotEqualTo ("id", id); criteria.andEqualTo ("userId", uid); List list = userMapper.selectByExample (example) / / if criteria is not used, the underlying layer of example.and () is still the returned criteria, which can be easily written. Example example = new Example (User.class); example.and () .andEqualTo ("name", name) .andEqualTo ("id", id) .andEqualTo ("userId", uid); List list = userMapper.selectByExample (example); equivalent to: select * from user where name = # {name} and id = # {id} and uid = # {uid}

2. And or query

Example example = new Example (User.getClass ()); / / where condition Criteria criteria = example.createCriteria (); Criteria criteria1 = example.createCriteria (); criteria.andIn ("id", ids); criteria1.orLike ("des", "%" + des + "%"); criteria1.orLike ("name", "%" + name + "%"); example.and (criteria1) Example.and () .andEqualTo ("status", staus) is equivalent to: where id in (# {ids}) and (name like concat ('%', # {name},'%') or des like concat ('%', # {des},'%')) and status = # {status}

Note: if you do not add example.and (criteria1);, the default example that only adds the first generated criteria,criteria1 will not be added to this condition

3. Conditional query of array parameters

Public Example test (List names, String sex) {Example example = new Example (User.getClass ()); example.and (). AndEqualTo ("sex", sex) Example.Criteria criteria = example.createCriteria (); for (String str: names) {criteria.orLike ("name", str);} example.and (criteria); List list = userMapper.selectByExample (example) Equivalent to: where sex = # {sex} and (name like concat ('%', # {name1},'%') or name like concat ('%', # {name2},'%'))} tell me about the common usage of Mybatis Example. Description

We will encounter some scenarios when we use mybatis example to add / delete / modify / query our business. Take notes.

two。 Sort query

When using mybatis example to make a query, businesses need to be sorted according to conditions, such as reverse order of creation time.

Example.setOrderByClause ("create_time desc")

2.1 example:

@ Override public UpgradeNotifyInfoDTO queryLatestNotify (Integer appType) {UpgradeNotifyInfoDTO notifyDTO=new UpgradeNotifyInfoDTO (); SportAppUpgradeNotifyExample example = new SportAppUpgradeNotifyExample (); example.setOrderByClause ("`create_ time` desc"); SportAppUpgradeNotifyExample.Criteria criteria = example.createCriteria (); criteria.andAppTypeEqualTo (appType); / / 0-disable 1-enable criteria.andStatusEqualTo (1); List list = upgradeNotifyMapper.selectByExample (example) If (! CollectionUtils.isEmpty (list)) {BeanUtils.copyProperties (list.get (0), notifyDTO);} return notifyDTO;}

Note: the multi-conditional sort is written as follows:

ReservationProductOrderDetailExample example = new ReservationProductOrderDetailExample (); example.setOrderByClause ("`reservate_ time` desc, `reservate_start_ time` desc, `create_ time` desc"); ReservationProductOrderDetailExample.Criteria criteria = example.createCriteria (); three. Query limit and return only the first 50 pieces of data

3.1 with the help of PageHelper

If we do paging queries through Pagehelper, then limit can also use Pagehelper. As follows, the query meets the first 50 items in the criteria. Total data count will not be returned here

PageHelper.startPage (1,50); public List queryShopList (String shopCityName,String shopCityId) {List shopCityList = new ArrayList (); ShopInfoExample example = new ShopInfoExample (); ShopInfoExample.Criteria criteria = example.createCriteria (); criteria.andStatusEqualTo ("0"); if (! StringUtils.isEmpty (shopCityId)) {criteria.andShopIdEqualTo (shopCityId) } if (! StringUtils.isEmpty (shopCityName)) {criteria.andShopNameLike ("%" + shopCityName + "%");} / / the query is limited to 50 pieces of data, but the total PageHelper.startPage (1,50) cannot be returned; List shopInfoList = shopInfoMapper.selectByExample (example); if (CollectionUtils.isEmpty (shopInfoList)) {return shopCityList } for (ShopInfo shopInfo: shopInfoList) {ShopCityInfoRespDTO respDTO = new ShopCityInfoRespDTO (); respDTO.setCompanyId (shopInfo.getCompanyId ()); respDTO.setShopCityId (shopInfo.getShopId ()); respDTO.setShopCityName (shopInfo.getShopName ()); respDTO.setShopCityCode (shopInfo.getShopCode ()); respDTO.setShopType (1); shopCityList.add (respDTO) } return shopCityList;} above is all the content of this article "how to use Mybatis Example". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.