MyBatis 多字段条件查询

1. MyBatis 配置

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.concrete.dao.entity
  configuration:
    map-underscore-to-camel-case: true
    # 打印sql
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

数据库配置：
在线数据库 http://www.sqlpub.com:8080

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://mysql.sqlpub.com:3306/spotlive
    username: xxx
    password: xxxxxx

2. 查询

@RestController
@RequestMapping("/order")
public class OrderMapperController {

    @Resource
    private OrderMapper orderMapper;

    @RequestMapping("/findAll")
    public String findAll() {
        return JSON.toJSONString(orderMapper.findAll());
    }

    @RequestMapping("/condition")
    public String condition() {
        OrderRequest request = new OrderRequest();
        request.setCode("1001");
        request.setMultiCity(Lists.newArrayList("武汉市"));
        request.setMultiArea(Lists.newArrayList("余杭区", "西湖区"));
        return JSON.toJSONString(orderMapper.findByCondition(request));
    }
}

@Data
public class OrderRequest implements Serializable {
    private static final long serialVersionUID = -420434203394339360L;

    private Integer id;
    private String code;
    private List<String> codeList;
    private String userName;
    private Date gmtCreate;
    private Date gmtModify;
    private Integer quantity;
    private String attributes;
    private List<String> multiCity;
    private List<String> multiArea;
}

打印结果：

==>  Preparing: select id, code, user_name, quantity, gmt_create, gmt_modify, attributes from boot_order WHERE code = ? AND ( attributes LIKE CONCAT('%;city:', ?, ';%') OR attributes LIKE CONCAT('%;area:', ?, ';%') or attributes LIKE CONCAT('%;area:', ?, ';%') )
==> Parameters: 1001(String), 武汉市(String), 余杭区(String), 西湖区(String)
<==    Columns: id, code, user_name, quantity, gmt_create, gmt_modify, attributes
<==        Row: 1, 1001, Tinyspot, 1, 2022-11-20 21:44:28, 2022-11-20 21:44:28, ;province:浙江省;city:杭州市;area:余杭区;
<==      Total: 1

2.1 Mapper 接口

public interface OrderMapper {

    List<Order> findAll();

    List<Order> findByCondition(OrderRequest request);
}

@Data
public class Order implements Serializable {
    private static final long serialVersionUID = -6401401493086809426L;

    private Integer id;
    private String code;
    private String userName;
    private Date gmtCreate;
    private Date gmtModify;
    private Integer quantity;
    private String attributes;
}

2.2 映射文件 OrderMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.concrete.dao.mapper.OrderMapper">

    <select id="findAll" resultType="Order">
        select * from boot_order
    </select>

    <select id="findByCondition" parameterType="OrderRequest" resultType="Order">
        select
        <include refid="columns"/>
        from boot_order
        <where>
            <if test="code != null and code != '' ">
                AND code = #{code}
            </if>
            <if test="codeList != null and codeList.size() > 0">
                AND code in
                <foreach collection="codeList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
<!--            <if test="multiCity != null and multiCity.size() > 0">-->
<!--                AND-->
<!--                <foreach collection="multiCity" item="item" open="(" separator=" or " close=")">-->
<!--                    attributes LIKE CONCAT('%;city:',#{item},';%')-->
<!--                </foreach>-->
<!--            </if>-->
            <choose>
                <when test="multiCity != null and multiCity.size() > 0 and multiArea != null and multiArea.size() > 0">
                    AND (
                    <foreach collection="multiCity" item="item" open=" " separator=" or " close=" ">
                        attributes LIKE CONCAT('%;city:', #{item}, ';%')
                    </foreach>
                    OR
                    <foreach collection="multiArea" item="item" open=" " separator=" or " close=" ">
                        attributes LIKE CONCAT('%;area:', #{item}, ';%')
                    </foreach>
                    )
                </when>
                <when test="multiCity != null and multiCity.size() > 0">
                    AND
                    <foreach collection="multiCity" item="item" open="(" separator=" or " close=")">
                        attributes LIKE CONCAT('%;city:', #{item}, ';%')
                    </foreach>
                </when>
                <when test="multiArea != null and multiArea.size() > 0">
                    AND
                    <foreach collection="multiArea" item="item" open="(" separator=" or " close=")">
                        attributes LIKE CONCAT('%;area:', #{item}, ';%')
                    </foreach>
                </when>
                <otherwise>

                </otherwise>
            </choose>
        </where>
    </select>

    <sql id="columns">
        id, code, user_name, quantity, gmt_create, gmt_modify, attributes
    </sql>
</mapper>

3. 级联多选

示例数据：

[{"name":"杭州市","code":"HangZhou","parentCode":"0","level":1,"isLeaf":false,"children":[{"name":"余杭区","code":"YuHangQu","parentCode":"HangZhou","level":2,"isLeaf":true},{"name":"西湖区","code":"XiHuQu","parentCode":"HangZhou","level":2,"isLeaf":true}]},
{"name":"武汉市","code":"WuHan","parentCode":"0","level":1,"isLeaf":false,"children":[{"name":"武昌区","code":"WuChang","parentCode":"WuHan","level":2,"isLeaf":true},{"name":"江汉区","code":"JiangHanQu","parentCode":"WuHan","level":2,"isLeaf":true}]}]

问题：选择"杭州市" 和 "武汉市 > 江汉区"
参数为: 杭州市, 武汉市, 江汉区
处理：

@Test
public void demo() {
    List<String> selects = Lists.newArrayList("HangZhou", "YuHangQu", "XiHuQu", "WuHan");

    String source = "[{\"name\":\"杭州市\",\"code\":\"HangZhou\",\"parentCode\":\"0\",\"level\":1,\"isLeaf\":false,\"children\":[{\"name\":\"余杭区\",\"code\":\"YuHangQu\",\"parentCode\":\"HangZhou\",\"level\":2,\"isLeaf\":true},{\"name\":\"西湖区\",\"code\":\"XiHuQu\",\"parentCode\":\"HangZhou\",\"level\":2,\"isLeaf\":true}]},{\"name\":\"武汉市\",\"code\":\"WuHan\",\"parentCode\":\"0\",\"level\":1,\"isLeaf\":false,\"children\":[{\"name\":\"武昌区\",\"code\":\"WuChang\",\"parentCode\":\"WuHan\",\"level\":2,\"isLeaf\":true},{\"name\":\"江汉区\",\"code\":\"JiangHanQu\",\"parentCode\":\"WuHan\",\"level\":2,\"isLeaf\":true}]}]";
    List<MultiCascadeDTO> areaConfig = JSON.parseArray(source, MultiCascadeDTO.class);

    List<String> areaList = new ArrayList<>();
    Map<String, String> secondMap = new HashMap<>();
    for (MultiCascadeDTO cascadeDTO : areaConfig) {
        if (Boolean.FALSE.equals(cascadeDTO.getIsLeaf())) {
            List<MultiCascadeDTO> children = cascadeDTO.getChildren();
            for (MultiCascadeDTO child : children) {
                if (selects.contains(child.getCode())) {
                    areaList.add(child.getCode());
                    secondMap.put(child.getCode(), child.getParentCode());
                }
            }
        }
    }

    System.out.println("selects: " + selects);
    System.out.println("secondMap: " + secondMap);
    System.out.println("areaList: " + areaList + "\n");

    // 1. 移除二级
    Collection<String> subtract = CollectionUtils.subtract(selects, areaList);
    System.out.println("selects - areaList = subtract: " + subtract);

    // 2. 移除多余的一级 (即 已有二级的一级)
    System.out.println("city: " + secondMap.values());
    Collection<String> cityList = CollectionUtils.subtract(subtract, secondMap.values());
    System.out.println("subtract - city = cityList: " + cityList);
}

@Data
public class MultiCascadeDTO implements Serializable {
    private static final long serialVersionUID = 753845213037215717L;
    
    private String name;
    private String code;
    private String parentCode;
    private Integer level;
    private Boolean isLeaf;
    private List<MultiCascadeDTO> children;
}

打印结果：

selects: [HangZhou, YuHangQu, XiHuQu, WuHan]
secondMap: {XiHuQu=HangZhou, YuHangQu=HangZhou}
areaList: [YuHangQu, XiHuQu]

selects - areaList = subtract: [HangZhou, WuHan]
city: [HangZhou, HangZhou]
subtract - city = cityList: [WuHan]