mybatis的collection 标签实现省市县三级联动和forEach实现性能比较

最新推荐文章于 2023-08-16 16:03:40 发布

guoyiguang2

最新推荐文章于 2023-08-16 16:03:40 发布

阅读量509

点赞数 2

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_38807606/article/details/112645437

版权

mysql 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

SQL：脚本参考如下链接

https://blog.csdn.net/xubenxismile/article/details/107662209

最终效果如下：

或者：（下图是纯SQL实现，性能要比Java代码中forEach 效果好）

性能比较：

如果<collection> 标签里传多个参数

demo

column="{sourceTarget=id,sourceType=cmdb_type}"

定义 xxxMap 继承 zzzzMap 类型为 xxxx.bean.xxx 实体。
然后通过 association 标签（一对一查询，collection 一对多 查询）。

select 子查询标签，值为查询的dao 层的方法。

column 为 传递到select 查询里的参数，sourceTarget=id （sourceTarget 为子查询方法的参数，id 为父查询的记录id 用于传递到子查询中作为sourceTarget 的值）

property=”destName” 将查询的结果映射到父查询的 resultMap 的 destName 属性

javaType=”java.lang.String” 查询结果的类型，通常是一个bean实体

关键代码如下：

<?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.springboot.demo.mapper.RegionMapper">
    <resultMap id="BaseResultMap" type="com.springboot.demo.model.Region">
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="VARCHAR"/>
    </resultMap>

    <resultMap id="ProvinceResultMap" type="com.springboot.demo.model.Vo.Province">
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="VARCHAR"/>
        <!--通过 code  这一列和 表发生关联-->
        <collection property="cityList" column="code" javaType="ArrayList"
                    ofType="com.springboot.demo.model.Vo.City" select="getCityByParentCode"/>
    </resultMap>
    <resultMap id="CityResultMap" type="com.springboot.demo.model.Vo.City">
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="VARCHAR"/>
        <!--通过 code  这一列和 表发生关联-->
        <collection property="counties" column="code" javaType="ArrayList"
                    ofType="com.springboot.demo.model.Vo.County" select="getCountiesByParentCode"/>
    </resultMap>

    <resultMap id="CountyResultMap" type="com.springboot.demo.model.Vo.County">
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="VARCHAR"/>
        <!--通过 code  这一列和 表发生关联-->
        <collection property="streets" column="code" javaType="ArrayList"
                    ofType="com.springboot.demo.model.Vo.Street" select="getStreetsByParentCode"/>
    </resultMap>

    <resultMap id="StreetsResultMap" type="com.springboot.demo.model.Vo.Street">
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="VARCHAR"/>
    </resultMap>

    <!-- 对这个语句useCache="true"默认是true，可以不写 -->
    <select id="getList" resultType="com.springboot.demo.model.Region"  useCache="true">
        select
           name , code , parent_code as parentCode,level
        from sys_region
    </select>

    <select id="getProvices" resultMap="ProvinceResultMap" >
        select
           name , code , parent_code as parentCode,level
        from sys_region where parent_code is null
    </select>

    <!--根据parentcode获得市-->
    <select id="getCityByParentCode" parameterType="java.lang.String" resultMap="CityResultMap" >
        select
           name , code , parent_code as parentCode,level
        from sys_region where parent_code  = #{parentCode}
    </select>
    <!--根据parentcode获得县-->
    <select id="getCountiesByParentCode" parameterType="java.lang.String" resultMap="CountyResultMap" >
        select
           name , code , parent_code as parentCode,level
        from sys_region where parent_code  = #{parentCode}
    </select>

    <!--根据parentcode获得街-->
    <select id="getStreetsByParentCode" parameterType="java.lang.String" resultMap="StreetsResultMap" >
        select
           name , code , parent_code as parentCode,level
        from sys_region where parent_code  = #{parentCode}
    </select>

</mapper>

package com.springboot.demo.service.imp;


import com.mysql.cj.util.StringUtils;
import com.springboot.demo.mapper.RegionMapper;
import com.springboot.demo.model.Region;
import com.springboot.demo.model.Vo.Province;
import com.springboot.demo.service.RegionService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
public class RegionServiceImp implements RegionService {

    @Autowired
    public RegionMapper regionMapper;

    @Override
    public List<Region> getList() {
        long startTime = System.currentTimeMillis();
        System.out.println(startTime);
        List<Region> provinceList0 = regionMapper.getList();
        // 省份
        List<Region> provices = provinceList0.stream()
                .filter(e -> StringUtils.isNullOrEmpty(e.getParentCode()))
                .collect(Collectors.toList());
        System.out.println(provices);

        // 根据level分成四个区域(分区的话，只有 true和false 两个区域，)
        Map<String, List<Region>> levelProvince = provinceList0.stream()
                .collect(Collectors.groupingBy(Region::getLevel));
        List<Region> cities = levelProvince.get("2");
        List<Region> counties = levelProvince.get("3");
        List<Region> streets = levelProvince.get("4");
        // void accept(T t, U u);
        // levelProvince.forEach((k,v) -> {});
        // void accept(T t);
        provices.forEach(e -> {
            // 省份下的城市
            List<Region> citiesList = cities.stream()
                    .filter(city ->city.getParentCode().equals(e.getCode()) )
                    .collect(Collectors.toList());
            // 城市下的县
            citiesList.forEach(city ->{
                List<Region> countiesList =    counties.stream()
                        .filter(coun -> coun.getParentCode().equals(city.getCode()))
                        .collect(Collectors.toList());
                // 赋值给城市
                city.setCounties(countiesList);
                // 县下的街道
                countiesList.forEach(county -> {
                    List<Region> streetsList =     streets.stream()
                            .filter(street -> street.getParentCode().equals(county.getCode()) )
                            .collect(Collectors.toList());
                    // 给县赋值
                    county.setStreets(streetsList);
                });
            });
            // 设值
            e.setCities(citiesList);
        });
        long endTime = System.currentTimeMillis();
        long cost = endTime-startTime;
        // 732 ms 所以还是在SQL中要快
        System.out.println(" forEach   总耗时为------------------------------------------------------------------------------------------------->"+cost+ " ms  ");
        return provices;
    }

    @Override
    public List<Province> getProvices() {
        long startTime = System.currentTimeMillis();
        List<Province> provices = regionMapper.getProvices();
        long endTime = System.currentTimeMillis();
        long cost = endTime - startTime;
        // 179 ms
        System.out.println(" sql  总耗时为------------------------------------------------------------------------------------------------->"+cost+ " ms  ");
        return provices;
    }


}

完整代码如下：

目录结果：

package com.springboot.demo.mapper;

import com.springboot.demo.model.Region;
import com.springboot.demo.model.Users;
import com.springboot.demo.model.Vo.City;
import com.springboot.demo.model.Vo.County;
import com.springboot.demo.model.Vo.Province;
import com.springboot.demo.model.Vo.Street;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository
public interface RegionMapper {

    public List<Region> getList() ;

    public List<Province> getProvices() ;

    public List<City> getCityByParentCode(@Param("parentCode") String parentCode) ;

    public List<County> getCountiesByParentCode(@Param("parentCode") String parentCode) ;

    public List<Street> getStreetsByParentCode(@Param("parentCode") String parentCode) ;

}

<?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.springboot.demo.mapper.RegionMapper">
    <resultMap id="BaseResultMap" type="com.springboot.demo.model.Region">
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="VARCHAR"/>
    </resultMap>

    <resultMap id="ProvinceResultMap" type="com.springboot.demo.model.Vo.Province">
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="VARCHAR"/>
        <!--通过 code  这一列和 表发生关联-->
        <collection property="cityList" column="code" javaType="ArrayList"
                    ofType="com.springboot.demo.model.Vo.City" select="getCityByParentCode"/>
    </resultMap>
    <resultMap id="CityResultMap" type="com.springboot.demo.model.Vo.City">
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="VARCHAR"/>
        <!--通过 code  这一列和 表发生关联-->
        <collection property="counties" column="code" javaType="ArrayList"
                    ofType="com.springboot.demo.model.Vo.County" select="getCountiesByParentCode"/>
    </resultMap>

    <resultMap id="CountyResultMap" type="com.springboot.demo.model.Vo.County">
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="VARCHAR"/>
        <!--通过 code  这一列和 表发生关联-->
        <collection property="streets" column="code" javaType="ArrayList"
                    ofType="com.springboot.demo.model.Vo.Street" select="getStreetsByParentCode"/>
    </resultMap>

    <resultMap id="StreetsResultMap" type="com.springboot.demo.model.Vo.Street">
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="code" property="code" jdbcType="VARCHAR"/>
        <result column="parent_code" property="parentCode" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="VARCHAR"/>
    </resultMap>

    <!-- 对这个语句useCache="true"默认是true，可以不写 -->
    <select id="getList" resultType="com.springboot.demo.model.Region"  useCache="true">
        select
           name , code , parent_code as parentCode,level
        from sys_region
    </select>

    <select id="getProvices" resultMap="ProvinceResultMap" >
        select
           name , code , parent_code as parentCode,level
        from sys_region where parent_code is null
    </select>

    <!--根据parentcode获得市-->
    <select id="getCityByParentCode" parameterType="java.lang.String" resultMap="CityResultMap" >
        select
           name , code , parent_code as parentCode,level
        from sys_region where parent_code  = #{parentCode}
    </select>
    <!--根据parentcode获得县-->
    <select id="getCountiesByParentCode" parameterType="java.lang.String" resultMap="CountyResultMap" >
        select
           name , code , parent_code as parentCode,level
        from sys_region where parent_code  = #{parentCode}
    </select>

    <!--根据parentcode获得街-->
    <select id="getStreetsByParentCode" parameterType="java.lang.String" resultMap="StreetsResultMap" >
        select
           name , code , parent_code as parentCode,level
        from sys_region where parent_code  = #{parentCode}
    </select>

</mapper>

package com.springboot.demo.service;


import com.springboot.demo.model.Region;
import com.springboot.demo.model.Vo.Province;

import java.util.List;

public interface RegionService {

    List<Region> getList();

    List<Province> getProvices();

}

package com.springboot.demo.service.imp;


import com.mysql.cj.util.StringUtils;
import com.springboot.demo.mapper.RegionMapper;
import com.springboot.demo.model.Region;
import com.springboot.demo.model.Vo.Province;
import com.springboot.demo.service.RegionService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
public class RegionServiceImp implements RegionService {

    @Autowired
    public RegionMapper regionMapper;

    @Override
    public List<Region> getList() {
        long startTime = System.currentTimeMillis();
        System.out.println(startTime);
        List<Region> provinceList0 = regionMapper.getList();
        // 省份
        List<Region> provices = provinceList0.stream()
                .filter(e -> StringUtils.isNullOrEmpty(e.getParentCode()))
                .collect(Collectors.toList());
        System.out.println(provices);

        // 根据level分成四个区域(分区的话，只有 true和false 两个区域，)
        Map<String, List<Region>> levelProvince = provinceList0.stream()
                .collect(Collectors.groupingBy(Region::getLevel));
        List<Region> cities = levelProvince.get("2");
        List<Region> counties = levelProvince.get("3");
        List<Region> streets = levelProvince.get("4");
        // void accept(T t, U u);
        // levelProvince.forEach((k,v) -> {});
        // void accept(T t);
        provices.forEach(e -> {
            // 省份下的城市
            List<Region> citiesList = cities.stream()
                    .filter(city ->city.getParentCode().equals(e.getCode()) )
                    .collect(Collectors.toList());
            // 城市下的县
            citiesList.forEach(city ->{
                List<Region> countiesList =    counties.stream()
                        .filter(coun -> coun.getParentCode().equals(city.getCode()))
                        .collect(Collectors.toList());
                // 赋值给城市
                city.setCounties(countiesList);
                // 县下的街道
                countiesList.forEach(county -> {
                    List<Region> streetsList =     streets.stream()
                            .filter(street -> street.getParentCode().equals(county.getCode()) )
                            .collect(Collectors.toList());
                    // 给县赋值
                    county.setStreets(streetsList);
                });
            });
            // 设值
            e.setCities(citiesList);
        });
        long endTime = System.currentTimeMillis();
        long cost = endTime-startTime;
        // 732 ms 所以还是在SQL中要快
        System.out.println(" forEach   总耗时为------------------------------------------------------------------------------------------------->"+cost+ " ms  ");
        return provices;
    }

    @Override
    public List<Province> getProvices() {
        long startTime = System.currentTimeMillis();
        List<Province> provices = regionMapper.getProvices();
        long endTime = System.currentTimeMillis();
        long cost = endTime - startTime;
        // 179 ms
        System.out.println(" sql  总耗时为------------------------------------------------------------------------------------------------->"+cost+ " ms  ");
        return provices;
    }


}

启动类：

package com.springboot.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.springboot.demo.mapper")
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}

配置文件：


server.port=8080
#redis
spring.redis.host=localhost
spring.redis.port=6379
#spring.redis.password=bigdata123
spring.redis.database=0
#spring.redis.timeout=0

spring.redis.pool.maxTotal=8
spring.redis.pool.maxWaitMillis=1000
#spring.redis.pool.max-idle=8 # pool settings ...
#spring.redis.pool.min-idle=0
#spring.redis.pool.max-active=8
#spring.redis.pool.max-wait=-1
#spring.redis.sentinel.master= # name of Redis server
#spring.redis.sentinel.nodes= # comma-separated list of host:port pairs



# 数据源配置
spring.datasource.url=jdbc:mysql://localhost:3306/45_sql_practice?serverTimezone=UTC&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
#连接池配置
#spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource

#mybatis
#entity扫描的包名
mybatis.type-aliases-package=com.springboot.demo.model
#Mapper.xml所在的位置
mybatis.mapper-locations=classpath*:/mapper/*Mapper.xml
#开启MyBatis的二级缓存
mybatis.configuration.cache-enabled=true

#pagehelper
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql

#日志配置
logging.level.com.xiaolyuh=debug
logging.level.org.springframework.web=debug
logging.level.org.springframework.transaction=debug
logging.level.org.mybatis=debug

# mybatis plus  log
mybatis.configuration.log-impl =org.apache.ibatis.logging.stdout.StdOutImpl

guoyiguang2

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mybatis的collection 标签实现省市县三级联动和forEach实现性能比较

1.SQL：脚本参考如下链接https://blog.csdn.net/xubenxismile/article/details/107662209最终效果如下：或者：（下图是纯SQL实现，性能要比Java代码中forEach 效果好）性能比较：关键代码如下：<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.
复制链接

扫一扫