轻松玩转MyBatis-Example类（内容精简，干货齐全，附带各种高级联动查询）

前言：

因为项目开发需要很多时候需要做一些复杂的联动查询，那么当你不想用SQL语句实现的时候，那应该怎么办呢？本文教你充分利用Example类来做复杂联动查询。

复习简单查询

增加行为SQL:

insert into industrycategory ( id, categoryname, detailcategoryname ) values ( ?, ?, ? )
JAVA实际操作:

  public int insertSelective(Industrycategory record) {
        return IndustrycategoryMapper.insertSelective(record);
    }
;

删除行为SQL:

delete from industrycategory where id = ?
JAVA实际操作:

    @Override
    public int deleteByPrimaryKey(Integer id) {
        return IndustrycategoryMapper.deleteByPrimaryKey(id);
    }

更新行为SQL:

update industrycategory SET categoryname = ?, detailcategoryname = ? where id = ?
JAVA实际操作:

    @Override
    public int updateByPrimaryKeySelective(Industrycategory record) {
        return IndustrycategoryMapper.updateByPrimaryKeySelective(record);
    }

全查询行为SQL:

select 'true' as QUERYID, id, categoryname, detailcategoryname from industrycategory
JAVA实际操作:

    @Override
    public List<Industrycategory> selectAll() {
        IndustrycategoryExample IndustrycategoryExample = new IndustrycategoryExample();

        return IndustrycategoryMapper.selectByExample(IndustrycategoryExample);
    }

高级查询方案（where后字段）

复杂条件查

where a=? and b=? and .... and z=?：可能任意a~z中字段需要Equals类型处理。
JAVA实际操作:

    @Override
    public List<Usercompany> selectAll(Usercompany usercompany) {
        UsercompanyExample UsercompanyExample = new UsercompanyExample();
        UsercompanyExample.Criteria criteria = UsercompanyExample.createCriteria();

        String usercompanyname = usercompany.getUsercompanyname();
        String chinesename = usercompany.getChinesename();
        String enlighname = usercompany.getEnlighname();
        String address = usercompany.getAddress();
        String agencyarea = usercompany.getAgencyarea();
        String officialwebsite = usercompany.getOfficialwebsite();
        String authorizedchannel = usercompany.getAuthorizedchannel();
        String behaviorcategory = usercompany.getBehaviorcategory();
        String category = usercompany.getCategory();
        String instruction = usercompany.getInstruction();
        String description = usercompany.getDescription();
        String createdtime = usercompany.getCreatedtime();

        String usercompanystatus = usercompany.getUsercompanystatus();

        if (usercompanyname != null ) {
            criteria.andUsercompanynameEqualTo(usercompanyname);
        }
        if (chinesename != null  ) {
            criteria.andChinesenameEqualTo(chinesename);
        }
        if (enlighname != null) {
            criteria.andEnlighnameEqualTo(enlighname);
        }
        if (address != null) {
            criteria.andAddressEqualTo(address);
        }
        if (agencyarea != null) {
            criteria.andAgencyareaEqualTo(agencyarea);
        }
        if (officialwebsite != null) {
            criteria.andOfficialwebsiteEqualTo(officialwebsite);
        }

        if (authorizedchannel != null) {
            criteria.andAuthorizedchannelEqualTo(authorizedchannel);
        }
        if (behaviorcategory != null) {
            criteria.andBehaviorcategoryEqualTo(behaviorcategory);
        }
        if (category != null) {
            criteria.andCategoryEqualTo(category);
        }
        if (instruction != null) {
            criteria.andInstructionEqualTo(instruction);
        }
        if (description != null) {
            criteria.andDescriptionEqualTo(description);
        }
        if (createdtime != null) {
            criteria.andCreatedtimeEqualTo(createdtime);
        }

        if (usercompanystatus != null) {
            criteria.andUsercompanystatusEqualTo(usercompanystatus);
        }


        return UsercompanyMapper.selectByExample(UsercompanyExample);
    }

全域搜索（暂时SQL）

where a like %?% or b like %?% or .... z like %?%：需要a~z每个字段进行模糊搜索
JAVA实际操作:

• 防止SQL注入

    @Override
    public List<Usercompany> selectAll(String index) {
        if (index==null) {
            index = "%";
        }else if (index.contains("%")) {
            return null;
        }else {
            index = '%' + index + '%';
        }

        System.out.println(index);
        return UsercompanyMapper.selectAll(index);
    }

• SQL

    <select id="selectAll" parameterType="string" resultType="cn.lantian.springboot.model.Usercompany">
      select * from usercompany WHERE (
  usercompanyname like #{index} or
  chinesename like #{index} or
  enlighname like #{index} or
  address like #{index} or
  agencyarea like #{index} or
  officialwebsite like #{index} or
  authorizedchannel like #{index} or
  behaviorcategory like #{index} or
  category like #{index} or
  instruction like #{index} or
  description like #{index} or
  createdtime like  #{index}

      )
    </select>

单字段多条件搜索查

where (a=? or a=?? or a=???) and (b=? or b=?? or b=???):可能需要筛选 广东省or河南省 And 年龄28or年龄29
JAVA实际操作:

    @Override
    public List<Usercompany> selectComplexAllByAgencyareaAndAuthorizedchannel(String[] agencyarea, String[] authorizedchannel) {
        UsercompanyExample UsercompanyExample = new UsercompanyExample();
        
        UsercompanyExample.Criteria criteria1 = UsercompanyExample.or();
        criteria1.andAgencyareaIn(Arrays.asList(agencyarea));
        criteria1.andAuthorizedchannelIn(Arrays.asList(authorizedchannel));
        
        return UsercompanyMapper.selectByExample(UsercompanyExample);
    }

多字段多条件查1

where (a = ? and b=?) or (c = ? and d=?):可能需要筛选 男生 and 体力好 or 经验丰富 and 没有受伤
JAVA实际操作:

多字段多条件查2

where (a = ? or b=?) and (c = ? or d=?):可能需要筛选 男生 or 体力好 And 善于沟通 or 懂的技巧
JAVA实际操作:·

• 代码

Example example = new xample();

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) 

全域搜索+复杂条件查

where (a like %?% or b like %?% or .... z like %?%) and (a=? and b=? and .... and z=?):需要a~z每个字段进行模糊搜索 And 可能任意a~z中字段需要Equals类型处理。
JAVA实际操作: