欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

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

程序员文章站 2022-06-28 20:18:28
1.SQL：脚本参考如下链接https://blog.csdn.net/xubenxismile/article/details/107662209最终效果如下：或者：（下图是纯SQL实现，性能要比Java代码中forEach 效果好）性能比较：关键代码如下：

1.

SQL：脚本参考如下链接

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

最终效果如下：

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

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

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

性能比较：

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

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

关键代码如下：

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


}

 

完整代码如下：

目录结果：

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

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

 

本文地址：https://blog.csdn.net/qq_38807606/article/details/112645437

相关标签： mysql

上一篇： .net 操作excel

下一篇： 千万不能照黑白的

推荐阅读