1、selectOneByExample的使用
Example userExample = new Example(User.class); userExample.createCriteria().andEqualTo("userName",traceabilitySlice.getCreateBy()).andEqualTo("delFlag",0); User sendUser = userMapper.selectOneByExample(userExample);
2、selectByExample的使用
Example example = new Example(User.class); example.createCriteria().andEqualTo("userName", user.getUserName()); List<User> list = userMapper.selectByExample(example);
3、排序
Example添加查询条件时,除了使用andEqualTo外,还可设置字段降序和日期范围查询:
Example example = new Example(OfficialAccout.class); //设置排序,STAT_DATE字段为升序 example.setOrderByClause("STAT_DATE ASC"); example.createCriteria().andBetween("statDate", last, now); List<OfficialAccout> list = officialAccoutMapper.selectByExample(example);
当排序单个字段时的使用方法
example.setOrderByClause("user_type ASC");
当排序多个字段时的使用方法,中间用逗号隔开
example.setOrderByClause("user_type ASC, level DESC");
或者
example.setOrderByClause("user_type , level DESC");
设置字段升序或降序的另一种方法:
Example example = new Example(IntelDrugStore.class); example.orderBy("createTime").desc().orderBy("updateTime").desc();
4、andBetween的使用
Example example = new Example(PlantGrowIrrigation.class); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("plantGrowId", plantGrowId).andBetween("irrigationDate", DateUtil.parse(startDate), DateUtil.parse(endDate)); List<PlantGrowIrrigation> list = plantGrowIrrigationMapper.selectByExample(example);
5、andIn的使用
根据一个集合去数据库查询多条记录。
List<String> menuCodeList = new ArrayList<String>(); menuCodeList.add("index@index"); menuCodeList.add("backstage@table"); menuCodeList.add("backstage@role@index"); menuCodeList.add("backstage@department@list"); menuCodeList.add("backstage@user@index"); menuCodeList.add("admin@404"); Example example = new Example(Menu.class); Example.Criteria criteria = example.createCriteria(); criteria.andIn("menuCode", menuCodeList); List<Menu> mList = mMapper.selectByExample(example);
6、and或or的使用
and和or的使用主要是如下三种场景:
1)、where (条件1 and 条件2) or (条件3 and 条件4)
// 条件1 and 条件2 example.createCriteria() .andEqualTo("isDeleted",IsDeleted.NOT_DELETED) .andEqualTo("name", projectCatalogEntity.getName()); // or (条件3 and 条件4) example.or(example.createCriteria() .andEqualTo("isDeleted",IsDeleted.NOT_DELETED) .andEqualTo("code", projectCatalogEntity.getCode()));
即:WHERE ( is_deleted = ? and name = ? ) or ( is_deleted = ? and code = ? )
2)、where (条件1 and 条件2) and (条件3 or 条件4)
// 条件1 and 条件2 example.createCriteria() .andEqualTo("isDeleted",IsDeleted.NOT_DELETED)) .andEqualTo("parentId", projectCatalogEntity.getParentId()); // and (条件3 or 条件4) example.and(example.createCriteria() .andEqualTo("name", projectCatalogEntity.getName()) .orEqualTo("code", projectCatalogEntity.getCode()));
即:WHERE ( is_deleted = ? and parent_id = ? ) and ( name = ? or code = ? )
注意:条件3既可以用andEqualTo,也可以用orEqualTo。
案例:
Example example = new Example(Prepare.class); Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("supplyId", prepare.getSupplyId()); Example.Criteria c = example.createCriteria(); c.orEqualTo("status", "0"); c.orEqualTo("status", "1"); example.and(c); List<Prepare> list = prepareMapper.selectByExample(example);
即:WHERE ( supply_id = ? ) AND ( status = ? OR status = ? )
7、updateByExampleSelective的使用
int updateByExampleSelective(@Param("record") T var1, @Param("example") Object var2);
第一个参数存着要修改的值。第二个参数为条件.
示例:
CrudeDrugs crudeDrugs = new CrudeDrugs(); crudeDrugs.setMaterialName(medMaterial.getMaterialCommonName()); Example example = new Example(CrudeDrugs.class); example.createCriteria().andEqualTo("materialCode", medMaterial.getMaterialCode()); crudeDrugsMapper.updateByExampleSelective(crudeDrugs, example);