产业数据三级联动，直接通过sql查询，开启二级缓存

产业数据三级联动，直接通过sql查询

产业字典表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="xxx.mapper.IndustryDictMapper">
    <cache/>
    <cache-ref namespace="xxx.mapper.IndustryValueMapper"/>

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="xxx.IndustryDict">
        <id column="id" property="id" />
        <result column="industry_name" property="industryName" />
        <result column="parent_id" property="parentId" />
    </resultMap>

    <resultMap id="AllIndustry" type="xxx.IndustryDict" extends="BaseResultMap">     
        <collection property="child" javaType="list" ofType="xxx.IndustryDict">
            <result column="b_id" property="id" />
            <result column="b_industry_name" property="industryName" />
            <result column="b_parent_id" property="parentId" />
            <collection property="child" javaType="list" ofType="xxx.IndustryDict" >
                <result column="c_id" property="id" />
                <result column="c_industry_name" property="industryName" />
                <result column="c_parent_id" property="parentId" />
　　　　　　　　　 <association property="outputValue" fetchType="eager" javaType="xxx.IndustryValue" select="xxx.mapper.IndustryValueMapper.outputValueUpRate"
                    column="{industryId=c_id}"/>
            </collection>
        </collection>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, industry_name, parent_id
    </sql>

    <select id="getAllIndustry" resultMap="AllIndustry">
        SELECT
            a.id,
            a.industry_name,
            a.parent_id,
            b.id AS b_id,
            b.industry_name b_industry_name,
            b.parent_id b_parent_id,
            c.id AS c_id,
            c.industry_name c_industry_name,
            c.parent_id c_parent_id
        FROM
            loc_industry_dict a
        INNER JOIN loc_industry_dict b ON a.id = b.parent_id
        LEFT JOIN loc_industry_dict c ON b.id = c.parent_id
        WHERE a.parent_id = 0
    </select>

</mapper>

 

产业实体类添加

@TableField(exist = false)
private IndustryValue outputValue;

@TableField(exist = false)
private List<IndustryDict> child;

产值表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="xxx.mapper.IndustryValueMapper">
    <cache/>

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="xxx.IndustryValue">
        <id column="id" property="id" />
        <result column="industry_id" property="industryId" />
        <result column="output_value" property="outputValue" />
        <result column="record_time" property="recordTime" />
        <result column="create_time" property="createTime" />
    </resultMap>

    <resultMap id="RateResultMap" type="xxx.IndustryValue" extends="BaseResultMap">
        <result column="change_rate" property="rate" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, industry_id, output_value, record_time, create_time
    </sql>

    <select id="outputValueUpRate" resultMap="RateResultMap">
        SELECT
            a.industry_id,
            ( a.output_value - b.output_value ) / b.output_value change_rate
        FROM
            loc_industry_value a
            INNER JOIN loc_industry_value b ON a.industry_id = b.industry_id
            AND b.record_time = ( YEAR ( CURRENT_DATE ) - 1 )
            AND a.record_time = YEAR ( CURRENT_DATE )
        WHERE
            a.record_time = YEAR ( CURRENT_DATE )
            AND a.industry_id = #{industryId}
    </select>

</mapper>

产值实体类添加

@TableField(exist = false)
private double rate;

后续查询日志不再打印sql语句，显示缓存命中率

Cache Hit Ratio [xxx.IndustryValueMapper]: 0.16666666666666666

备注：mybatis-plus

SpringBoot+Mybatis一级缓存和二级缓存详解