mybatis的collection 标签实现省市县三级联动和forEach实现性能比较
程序员文章站
2022-03-26 21:59:29
1.SQL:脚本参考如下链接https://blog.csdn.net/xubenxismile/article/details/107662209最终效果如下:或者:(下图是纯SQL实现,性能要比Java代码中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.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
本文地址:https://blog.csdn.net/qq_38807606/article/details/112645437
上一篇: 你的腰好细啊