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]
网友评论