tk.mybatis.mapper.entity.Example Java Examples

/**
 * 校验用户电话号码唯一性.
 *
 * @param checkUserPhoneDto the check user phone dto
 *
 * @return the wrapper
 */
@PostMapping(value = "/checkUserPhone")
@ApiOperation(httpMethod = "POST", value = "校验用户电话号码唯一性")
public Wrapper<Boolean> checkUserPhone(@ApiParam(name = "checkUserPhoneDto", value = "校验用户电话号码唯一性Dto") @RequestBody CheckUserPhoneDto checkUserPhoneDto) {
	logger.info(" 校验用户电话号码唯一性 checkUserPhoneDto={}", checkUserPhoneDto);
	Long id = checkUserPhoneDto.getUserId();
	String mobileNo = checkUserPhoneDto.getMobileNo();
	Example example = new Example(UacUser.class);
	Example.Criteria criteria = example.createCriteria();
	criteria.andEqualTo("mobileNo", mobileNo);

	if (id != null) {
		criteria.andNotEqualTo("id", id);
	}

	int result = uacUserService.selectCountByExample(example);
	return WrapMapper.ok(result < 1);
}
 

/**
 * 设置用户角色
 * @param userId  用户id
 * @return
 */
public boolean setUserRoles(String userId,List<String> roleIds){
    Example example=new Example(SysUserRole.class);
    example.createCriteria().andEqualTo("userId",userId);
    sysUserRoleMapper.deleteByExample(example);
    SysUserRole sysUserRole=new SysUserRole();
    int count=0;
    for(String roleId:roleIds){
        if(StringUtils.isNotEmpty(roleId)){
            sysUserRole.setRoleId(roleId);
            sysUserRole.setUserId(userId);
            sysUserRole.setId(IdGen.uuid());
            count+=sysUserRoleMapper.insert(sysUserRole);
        }
    }
    return count>0?true:false;
}
 

@Test
public void testIn() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = Example.builder(Country.class)
                .where(Sqls.custom().andIn("id", new ArrayList<Integer>(Arrays.asList(35, 183))))
                .build();
        List<Country> countries = mapper.selectByExample(example);
        Country country35 = countries.get(1);
        Assert.assertEquals(Integer.valueOf(35), country35.getId());
        Assert.assertEquals("China", country35.getCountryname());
        Assert.assertEquals("CN", country35.getCountrycode());

        Country country183 = countries.get(0);
        Assert.assertEquals(Integer.valueOf(183), country183.getId());
        Assert.assertEquals("Zambia", country183.getCountryname());
        Assert.assertEquals("ZM", country183.getCountrycode());

    } finally {
        sqlSession.close();
    }
}
 

@Test
public void testDistinct() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = Example.builder(Country.class)
                .distinct()
                .build();
        List<Country> countries = mapper.selectByExample(example);
        Assert.assertEquals(183, countries.size());

        // distinct和order by冲突问题
        Example example0 = Example.builder(Country.class)
                .selectDistinct("id", "countryname").build();
        List<Country> countries0 = mapper.selectByExample(example0);
        Assert.assertEquals(183, countries0.size());
    } finally {
        sqlSession.close();
    }
}
 

@RequestMapping(value = "/update", method = RequestMethod.POST)
@ResponseBody
public ResultBean<String> update(@Validated ErpUser item, String roleIds) {
    service.updateByPrimaryKeySelective(item);
    String[] split = roleIds.split(",");
    if (service.updateByPrimaryKeySelective(item) == 1) {
        List<ErpRoleUser> roleUsers = combineRoleUsers(split, item.getId());

        Example ruExample = new Example(ErpRoleUser.class);
        ruExample.createCriteria().andEqualTo("userId", item.getId());
        roleUserService.deleteByExample(ruExample);
        if (roleUserService.insertList(roleUsers) < 1) {
            throw new BusinessException("501", "修改失败");
        }
        return new ResultBean<String>("");
    }
    return new ResultBean<String>(ExceptionEnum.BUSINESS_ERROR, "修改失败", "", "");
}
 

/**
 * 通过spu_id删除tb_sku
 * 通过sku_id删除tb_stock
 *
 * @param spuId
 */
private void deleteSkuAndStock(Long spuId) {
    // 通过spu_id查询sku
    Sku querySku = new Sku();
    querySku.setSpuId(spuId);
    List<Sku> skus = this.skuMapper.select(querySku);
    // 删除sku
    if (!CollectionUtils.isEmpty(skus)) {
        // 获得sku_id集合
        List<Long> ids = skus.stream().map(sku -> sku.getId()).collect(Collectors.toList());
        // 通过sku_id删除tb_stock
        Example example = new Example(Stock.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andIn("skuId", ids);
        this.stockMapper.deleteByExample(example);
    }
    // 删除sku
    this.skuMapper.delete(querySku);
}
 

/**
 * @Description:递归遍历角色组下面的角色和角色组
 * @param listGroup
 * @author: Ryan
 * @date 2018年11月10日
 */
private List<GemRoleGroup> findGroupChildren(List<GemRoleGroup> listGroup){
	if(listGroup!=null && listGroup.size()>0) {
		for (GemRoleGroup roleGroup : listGroup) {
			Example example = new Example(GemRole.class);
			Criteria createCriteria = example.createCriteria();
			createCriteria.andEqualTo("groupId", roleGroup.getId());
			example.setOrderByClause("role_sort asc");
			List<GemRole> selectByExample = roleMapper.selectByExample(example);
			roleGroup.setRoles(selectByExample);

			Example example2 = new Example(GemRoleGroup.class);
			example2.createCriteria().andEqualTo("parentId", roleGroup.getId());
			example2.setOrderByClause("group_sort asc");
			List<GemRoleGroup> selectByExample2 = roleGroupMapper.selectByExample(example2);
			roleGroup.setChildren(selectByExample2);
			findGroupChildren(selectByExample2);
		}
	}
	return listGroup;
}
 

@Override
public AddressListResponse addressList(AddressListRequest request) {
    //TODO 地址信息要做缓存处理
    AddressListResponse response=new AddressListResponse();
    try{
        request.requestCheck();
        Example example=new Example(Address.class);

        example.createCriteria().andEqualTo("userId",request.getUserId());
        List<Address> addresses=addressMapper.selectByExample(example);
        response.setAddressDtos(converter.address2List(addresses));
        response.setCode(SysRetCodeConstants.SUCCESS.getCode());
        response.setMsg(SysRetCodeConstants.SUCCESS.getMessage());
    }catch (Exception e){
        log.error("AddressServiceImpl.addressList occur Exception :"+e);
        ExceptionProcessorUtils.wrapperHandlerException(response,e);
    }
    return response;
}
 

@Test
public void test(){

  SqlSession sqlSession = MybatisHelper.getSqlSession();
  CountryMapper countryMapper = sqlSession.getMapper(CountryMapper.class);

  Example example = new Example(Country.class);

  example.createCriteria().andGreaterThan("id",100);
  PageHelper.startPage(2,10);

  /*SELECT Id,countryname,countrycode FROM country WHERE ( Id > ? )*/
  List<Country> countries = countryMapper.selectByExample(example);
  PageInfo<Country> pageInfo = new PageInfo<Country>(countries);
  System.out.println(pageInfo.getTotal());

  countries = countryMapper.selectByExample(example);
  pageInfo = new PageInfo<Country>(countries);
  System.out.println(pageInfo.getTotal());

}
 

/**
 * 指定排除的查询字段为@Transient注释字段
 */
@Test
public void testExcludePropertisCheckTransient() {
    exception.expect(MapperException.class);
    exception.expectMessage("类 Country 不包含属性 'dynamicTableName123'，或该属性被@Transient注释！");
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = new Example(Country.class);
        example.excludeProperties(new String[]{"dynamicTableName123"});
        example.createCriteria().andEqualTo("id", 35);
        List<Country> country = mapper.selectByExample(example);
    } finally {
        sqlSession.close();
    }
}
 

@Test
public void testSelectByExampleInNotIn() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = new Example(Country.class);
        Set<Integer> set = new HashSet<Integer>();
        set.addAll(Arrays.asList(new Integer[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11}));
        example.createCriteria().andIn("id", set)
                .andNotIn("id", Arrays.asList(new Object[]{11}));
        List<Country> countries = mapper.selectByExample(example);
        //查询总数
        Assert.assertEquals(10, countries.size());
    } finally {
        sqlSession.close();
    }
}
 

@Override
@Transactional(rollbackFor = Exception.class)
public boolean removeByPrimaryKey(Long primaryKey) {
    boolean result = bizArticleMapper.deleteByPrimaryKey(primaryKey) > 0;
    // 删除标签记录
    Example tagsExample = new Example(BizArticleTags.class);
    Example.Criteria tagsCriteria = tagsExample.createCriteria();
    tagsCriteria.andEqualTo("articleId", primaryKey);
    bizArticleTagsMapper.deleteByExample(tagsExample);
    // 删除查看记录
    Example lookExample = new Example(BizArticleLook.class);
    Example.Criteria lookCriteria = lookExample.createCriteria();
    lookCriteria.andEqualTo("articleId", primaryKey);
    bizArticleLookMapper.deleteByExample(lookExample);
    // 删除赞记录
    Example loveExample = new Example(BizArticleLove.class);
    Example.Criteria loveCriteria = loveExample.createCriteria();
    loveCriteria.andEqualTo("articleId", primaryKey);
    bizArticleLoveMapper.deleteByExample(loveExample);
    return result;
}
 

@Test
public void testSelectByExampleForUpdate() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = new Example(Country.class);
        example.setForUpdate(true);
        example.createCriteria().andGreaterThan("id", 100).andLessThan("id",151);
        example.or().andLessThan("id", 41);
        List<Country> countries = mapper.selectByExample(example);
        //查询总数
        Assert.assertEquals(90, countries.size());
    } finally {
        sqlSession.close();
    }
}
 

/**
 * 修改时验证组织编码
 *
 * @param checkGroupCodeDto the check group code dto
 *
 * @return the wrapper
 */
@PostMapping(value = "/checkGroupCode")
@ApiOperation(httpMethod = "POST", value = "修改校验组织编码唯一性")
public Wrapper<Boolean> checkGroupCode(@ApiParam(name = "checkGroupCode", value = "组织相关信息") @RequestBody CheckGroupCodeDto checkGroupCodeDto) {
	logger.info("校验组织编码唯一性 checkGroupCodeDto={}", checkGroupCodeDto);

	Long id = checkGroupCodeDto.getGroupId();
	String groupCode = checkGroupCodeDto.getGroupCode();

	Example example = new Example(UacGroup.class);
	Example.Criteria criteria = example.createCriteria();

	if (id != null) {
		criteria.andNotEqualTo("id", id);
	}
	criteria.andEqualTo("groupCode", groupCode);

	int result = uacGroupService.selectCountByExample(example);
	return WrapMapper.ok(result < 1);
}
 

@Test
public void testSelectCountByExample2() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = new Example(Country.class);
        example.createCriteria().andLike("countryname", "A%");
        example.or().andGreaterThan("id", 100);
        example.setDistinct(true);
        int count = mapper.selectCountByExample(example);
        //查询总数
        Assert.assertEquals(true, count > 83);
    } finally {
        sqlSession.close();
    }
}
 

/**
 * 测试通用Mapper - 条件查询
 */
@Test
public void testQueryByCondition() {
    initData();
    Example example = new Example(User.class);
    // 过滤
    example.createCriteria().andLike("name", "%Save1%").orEqualTo("phoneNumber", "17300000001");
    // 排序
    example.setOrderByClause("id desc");
    int count = userMapper.selectCountByExample(example);
    // 分页
    PageHelper.startPage(1, 3);
    // 查询
    List<User> userList = userMapper.selectByExample(example);
    PageInfo<User> userPageInfo = new PageInfo<>(userList);
    Assert.assertEquals(3, userPageInfo.getSize());
    Assert.assertEquals(count, userPageInfo.getTotal());
    log.debug("【userPageInfo】= {}", userPageInfo);
}
 

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

    SqlSession sqlSession = MybatisHelper.getSqlSession();
    CountryMapper countryMapper = sqlSession.getMapper(CountryMapper.class);

    /*rowBounds*/
    RowBounds rowBounds = new RowBounds(2, 5);
    /*Example*/
    Example example = new Example(Country.class);
    Example.Criteria criteria = example.createCriteria();

//    criteria.andCountrycodeBetween("0", "ZZZZZZZZZZ");
//    criteria.andIdBetween(0, 20);

    List<Country> countries1 = countryMapper.selectByExample(example);
    log.debug("countries1" + countries1.size());
    List<Country> countries2 = countryMapper.selectByExampleAndRowBounds(example, rowBounds);
    log.debug("countries2" + countries2.size());


    PageInfo<Country> pageInfo = new PageInfo<>(countries1);
    System.out.println("PageHelperTest.main() pageInfo :" + pageInfo.getSize());

  }
 

@Test
public void testSelectColumnsByExample() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = new Example(Country.class);
        example.createCriteria().andGreaterThan("id", 100).andLessThan("id", 151);
        example.or().andLessThan("id", 41);
        example.selectProperties("id", "countryname", "hehe");
        List<Country> countries = mapper.selectByExample(example);
        //查询总数
        Assert.assertEquals(90, countries.size());
    } finally {
        sqlSession.close();
    }
}
 

@Test
public void testWhereAndWhereCompound() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = Example.builder(Country.class)
                .where(Sqls.custom()
                    .andEqualTo("countryname", "China")
                    .andEqualTo("id", 35)
                )
                .andWhere(Sqls.custom()
                    .andEqualTo("id", 183)
                )
                .build();
        List<Country> countries = mapper.selectByExample(example);
        Assert.assertEquals(0, countries.size());

    } finally {
        sqlSession.close();
    }
}
 

/**
 * @Description:条件查询用户
 * @param userVo 用户参数实体
 * @param pageNum 当前页
 * @param pageSize 每页显示的数据
 * @author: Ryan
 * @date 2018年11月10日
 */
@Override
public List<GemUser> findUserList(GemUserVo userVo, Integer pageNum, Integer pageSize) {
	PageHelper.startPage(pageNum, pageSize);
	Example example = new Example(GemUser.class);
	Criteria createCriteria = example.createCriteria();
	createCriteria.andIsNotNull("id");
	String userName = userVo.getUserName();
	if(GemFrameStringUtlis.isNotBlank(userName) && !userName.equalsIgnoreCase("null") && userName.length()>0) {
		createCriteria.andLike("userName","%"+userName+"%");
	}
	String memberName = userVo.getMemberName();
	if(GemFrameStringUtlis.isNotBlank(memberName) && !memberName.equalsIgnoreCase("null") && memberName.length()>0) {
		createCriteria.andLike("memberName","%"+memberName+"%");
	}
	String phone = userVo.getPhone();
	if(GemFrameStringUtlis.isNotBlank(phone) && !phone.equalsIgnoreCase("null") && phone.length()>0) {
		createCriteria.andEqualTo("phone", phone);
	}
	String email = userVo.getEmail();
	if(GemFrameStringUtlis.isNotBlank(email) && !email.equalsIgnoreCase("null") && email.length()>0) {
		createCriteria.andEqualTo("email", email);
	}
	return userMapper.selectByExample(example);
}
 

@Test
// Example中没有条件的非正常情况，where条件应只有逻辑删除注解的未删除条件
public void testExampleWithNoCriteria() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        TbUserLogicDeleteMapper logicDeleteMapper = sqlSession.getMapper(TbUserLogicDeleteMapper.class);

        Example example = new Example(TbUserLogicDelete.class);

        TbUserLogicDelete tbUserLogicDelete = new TbUserLogicDelete();
        tbUserLogicDelete.setUsername("123");

        Assert.assertEquals(5, logicDeleteMapper.updateByExample(tbUserLogicDelete, example));

        Assert.assertEquals(5, logicDeleteMapper.updateByExampleSelective(tbUserLogicDelete, example));

        List<TbUserLogicDelete> list = logicDeleteMapper.selectByExample(example);
        Assert.assertEquals(5, list.size());
    } finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}
 

@Test
public void testSelectByExampleForUpdate() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = new Example(Country.class);
        example.setForUpdate(true);
        example.createCriteria().andGreaterThan("id", 100).andLessThan("id",151);
        example.or().andLessThan("id", 41);
        List<Country> countries = mapper.selectByExample(example);
        //查询总数
        Assert.assertEquals(90, countries.size());
    } finally {
        sqlSession.close();
    }
}
 

/**
 * 测试通用Mapper - 条件查询
 */
@Test
public void testQueryByCondition() {
    initData();
    Example example = new Example(User.class);
    // 过滤
    example.createCriteria().andLike("name", "%Save1%").orEqualTo("phoneNumber", "17300000001");
    // 排序
    example.setOrderByClause("id desc");
    int count = userMapper.selectCountByExample(example);
    // 分页
    PageHelper.startPage(1, 3);
    // 查询
    List<User> userList = userMapper.selectByExample(example);
    PageInfo<User> userPageInfo = new PageInfo<>(userList);
    Assert.assertEquals(3, userPageInfo.getSize());
    Assert.assertEquals(count, userPageInfo.getTotal());
    log.debug("【userPageInfo】= {}", userPageInfo);
}
 

@Override
public UserDetails loadUserByUsername(String s) throws UsernameNotFoundException {
    Example example = new Example(Merchant.class);
    Example.Criteria criteria = example.createCriteria();
    criteria.andEqualTo("mobile", s);

    if(StringUtils.isEmpty(s)) {
        throw new UsernameNotFoundException("手机号码不能为空");
    }
    List<Merchant> merchants = merchantMapper.selectByCondition(example);

    if(CollectionUtils.isEmpty(merchants) || merchants.size()>1) {
        throw new UsernameNotFoundException("不存在该用户");
    }

    User user = new User(merchants.get(0).getMobile(), merchants.get(0).getPassword(),
            AuthorityUtils.commaSeparatedStringToAuthorityList("admin"));

    return user;
}
 

@Test
public void testWhereCompound1() {
    SqlSession sqlSession = MybatisHelper.getSqlSession();
    try {
        CountryMapper mapper = sqlSession.getMapper(CountryMapper.class);
        Example example = Example.builder(Country.class)
                .where(Sqls.custom()
                    .andBetween("id", 35, 50)
                    .orLessThan("id", 40)
                    .orIsNull("countryname")
                )
                .build();
        List<Country> countries = mapper.selectByExample(example);
        Assert.assertEquals(50, countries.size());
    } finally {
        sqlSession.close();
    }
}
 

/**
 * 检测数据字典名称是否已存在.
 *
 * @param mdcDictCheckNameDto the mdc dict check name dto
 *
 * @return the wrapper
 */
@PostMapping(value = "/checkDictName")
@ApiOperation(httpMethod = "POST", value = "检测数据字典名称是否已存在")
public Wrapper<Boolean> checkDictName(@ApiParam(name = "uacMenuCheckCodeDto", value = "id与url") @RequestBody MdcDictCheckNameDto mdcDictCheckNameDto) {
	logger.info("检测数据字典名称是否已存在 mdcDictCheckNameDto={}", mdcDictCheckNameDto);

	Long id = mdcDictCheckNameDto.getDictId();
	String dictName = mdcDictCheckNameDto.getDictName();

	Example example = new Example(MdcDict.class);
	Example.Criteria criteria = example.createCriteria();

	if (id != null) {
		criteria.andNotEqualTo("id", id);
	}
	criteria.andEqualTo("dictName", dictName);

	int result = mdcDictService.selectCountByExample(example);
	return WrapMapper.ok(result < 1);
}
 

/**
 * 校验登录名唯一性.
 *
 * @param checkEmailDto the check email dto
 *
 * @return the wrapper
 */
@PostMapping(value = "/checkEmail")
@ApiOperation(httpMethod = "POST", value = "校验登录名唯一性")
public Wrapper<Boolean> checkEmail(@RequestBody CheckEmailDto checkEmailDto) {
	logger.info("校验邮箱唯一性 checkEmailDto={}", checkEmailDto);

	Long id = checkEmailDto.getUserId();
	String email = checkEmailDto.getEmail();

	Example example = new Example(UacUser.class);
	Example.Criteria criteria = example.createCriteria();
	criteria.andEqualTo("email", email);
	if (id != null) {
		criteria.andNotEqualTo("id", id);
	}

	int result = uacUserService.selectCountByExample(example);
	return WrapMapper.ok(result < 1);
}
 

@Override
@Transactional
public void updatePassword(String password, String username) {
    Example example = new Example(MyUser.class);
    example.createCriteria().andCondition("username=", username);

    MyUser user = new MyUser();
    user.setPassword(password);
    this.userMapper.updateByExampleSelective(user, example);
}
 

@Override
public List<Menu> selectByPermission(String permission) {
    Assert.hasText(permission, "权限不能为空");
    Example menuExample = new Example(Menu.class);
    menuExample.createCriteria().andEqualTo(Menu.PERMISSION, permission);
    return super.selectByExample(menuExample);
}
 

@Override
@Transactional
public void updateRole(Role role, Long[] menuIds) {
    role.setModifyTime(new Date());
    this.updateNotNull(role);
    Example example = new Example(RoleMenu.class);
    example.createCriteria().andCondition("role_id=", role.getRoleId());
    this.roleMenuMapper.deleteByExample(example);
    setRoleMenus(role, menuIds);
}