mysql中有一个字段是字符串类型的,category字段值有多个用逗号分割的,例如:娱乐,时尚美妆,美食 。现在想实现这么一个功能, 前端传参 字符串,美食,娱乐。现在想在mybatis的xml中实现,查询,能查到category只要包含美食或者娱乐的数据行。
数据如下示例,category字段中有多个值,country字段中只有一个值
Mybatis xml 查询如下:
<where><if test="sex!= null and sex!=''">and sex= #{sex}</if><if test="country != null and country != ''">AND country IN<foreach item="item" index="index" collection="country.split(',')" open="(" separator="," close=")">#{item}</foreach></if><if test="category != null and category != ''">and<foreach item="item" index="index" collection="category.split(',')" open="(" separator="OR" close=")">FIND_IN_SET(#{item}, category) > 0</foreach></if></where>
Mybatis-plus中实现:
QueryWrapper<对象> lqw = new QueryWrapper<>();if (StringUtils.isNotEmpty(paramObj.getCountry())) {String[] split = paramObj.getCountry().split(",");List<String> countryList = Arrays.asList(split);lqw.in("country", countryList);}if (StringUtils.isNotEmpty(paramObj.getCategory())) {String[] split = paramObj.getCategory().split(",");StringBuilder sb = new StringBuilder();sb.append("(");for(int i = 0; i< split.length; i++){String catgory = split[i];if(i == split.length -1){sb.append("FIND_IN_SET('" + catgory + "', category) > 0").append(")");}else{sb.append("FIND_IN_SET('" + catgory + "', category) > 0").append(" or ");}}lqw.apply(sb.toString());}