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. 字段查询

2.1 数据源格式

2.2 映射文件 OrderMapper.xml

        select * from boot_order
    
        select
        
        from boot_order
        
                AND code = #{code}
            
                AND code in
                
                    #{item}
                
                    AND ( city in
                    
                        #{item}
                    
                    OR area in
                    
                        #{item}
                    
                    )
                
                    AND city in
                    
                        #{item}
                    
                    AND area in
                    
                        #{item}
                    
        id, code, user_name, quantity, gmt_create, gmt_modify, attributes
    

打印结果：

==>  Preparing: select id, code, user_name, quantity, gmt_create, gmt_modify, attributes from boot_order WHERE code = ? AND ( city in ( ? ) OR area in ( ? , ? ) )
==> Parameters: 1001(String), 武汉市(String), 余杭区(String), 西湖区(String)

2.2 属性 like 匹配

    AND (
    
        attributes LIKE CONCAT('%;city:', #{item}, ';%')
    
    OR
    
        attributes LIKE CONCAT('%;area:', #{item}, ';%')
    
    )

    AND
    
        attributes LIKE CONCAT('%;city:', #{item}, ';%')
    
    AND
    
        attributes LIKE CONCAT('%;area:', #{item}, ';%')
    

打印结果：

==>  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)

2.3 调用示例

@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 codeList;
    private String userName;
    private Date gmtCreate;
    private Date gmtModify;
    private Integer quantity;
    private String attributes;
    private List multiCity;
    private List multiArea;
    private List multiCityList;
    private List multiAreaList;
}

Mapper 接口

public interface OrderMapper {

    List findAll();

    List 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;
}

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 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 areaConfig = JSON.parseArray(source, MultiCascadeDTO.class);

    List areaList = new ArrayList();
    Map secondMap = new HashMap();
    for (MultiCascadeDTO cascadeDTO : areaConfig) {
        if (Boolean.FALSE.equals(cascadeDTO.getIsLeaf())) {
            List 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 subtract = CollectionUtils.subtract(selects, areaList);
    System.out.println("selects - areaList = subtract: " + subtract);

    // 2. 移除多余的一级 (即 已有二级的一级)
    System.out.println("city: " + secondMap.values());
    Collection 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 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]

【信息由网络或者个人提供，如有涉及版权请联系COOY资源网邮箱处理】