【Mybatis】mybatis使用示例

时间：2023-03-10 01:08:46
【Mybatis】mybatis使用示例

BusinessAnalysisMapper.java

import com.chinamobile.epic.dao.model.PerformanceMetricAnalysis;

import com.chinamobile.epic.dao.model.PerformanceMetricAnalysisModel;

import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Param;

import java.util.Date;

import java.util.List;

@Mapper

public interface BusinessAnalysisMapper {

    // 参考： BusinessStatisticMapper

    int deleteByPrimaryKey(PerformanceMetricAnalysisModel key);

    int insert(PerformanceMetricAnalysisModel record);

    /**

     * 批量插入数据（根据“表名后缀”插入）

     *

     * @param record

     * @param tableNameSuffix 表名后缀

     * @return

     */

    int insertBatch(@Param("list") List<PerformanceMetricAnalysisModel> record, @Param("tableNameSuffix") String tableNameSuffix);

    int insertSelective(PerformanceMetricAnalysisModel record);

    PerformanceMetricAnalysis selectByPrimaryKey(PerformanceMetricAnalysisModel key);

    int updateByPrimaryKeySelective(PerformanceMetricAnalysisModel record);

    int updateByPrimaryKey(PerformanceMetricAnalysisModel record);

    /**

     * 删除 startTime 之前的数据

     *

     * @param startTime

     * @param tableNameSuffix

     * @return

     */

    int deleteBeforeTime(@Param("createAt") Date startTime, @Param("tableNameSuffix") String tableNameSuffix);

    /**

     * 聚合查询

     *

     * @param tableName

     * @param funcToUse 对value求值时，使用的函数，如：avg，sum

     * @param startTime 查询的开始时间

     * @param endTime   查询的结束时间

     * @return

     */

    List<PerformanceMetricAnalysis> groupByDimensionIDAndIndicatorID(@Param("srcTableName") String tableName, @Param("func") String

            funcToUse, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    /**

     * 聚合查询：指标项为 {@code MetricDataType.Counter} 类型

     *

     * @param tableName

     * @param startTime

     * @param endTime

     * @param indicatorKeysOfCounter

     * @return

     */

    List<PerformanceMetricAnalysis> groupCounterDataByDimensionIndicator(@Param("srcTableName") String tableName, @Param("startTime")

            Date startTime, @Param("endTime") Date endTime, @Param("list") List<String> indicatorKeysOfCounter);

    /**

     * 聚合查询：指标项为 {@code MetricDataType.Gauge} 类型

     *

     * @param tableName

     * @param startTime

     * @param endTime

     * @param indicatorKeysOfGauge

     * @return

     */

    List<PerformanceMetricAnalysis> groupGaugeDataByDimensionIndicator(@Param("srcTableName") String tableName, @Param("startTime")

            Date startTime, @Param("endTime") Date endTime, @Param("list") List<String> indicatorKeysOfGauge);

}

BusinessAnalysisMapper.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.chinamobile.epic.dao.mapper.BusinessAnalysisMapper">

    <resultMap id="BaseResultMap" type="com.chinamobile.epic.dao.model.PerformanceMetricAnalysis">

        <id column="dimension_id" property="dimensionId" jdbcType="CHAR"/>

        <id column="indicator_key" property="indicatorKey" jdbcType="VARCHAR"/>

        <id column="create_at" property="createAt" jdbcType="TIMESTAMP"/>

        <result column="pool_id" property="poolId" jdbcType="CHAR"/>

        <result column="value" property="value" jdbcType="DOUBLE"/>

        <result column="resource_type" property="resourceType" jdbcType="VARCHAR"/>

    </resultMap>

    <sql id="Base_Column_List">

        pool_id, dimension_id, indicator_key, value, resource_type, create_at

    </sql>

    <sql id="Base_Column_List_sumValue">

        pool_id, dimension_id, indicator_key, sum(value) as value, resource_type, create_at

    </sql>

    <sql id="Base_Column_List_avgValue">

        pool_id, dimension_id, indicator_key, avg(value) as value, resource_type, create_at

    </sql>

    <sql id="Base_Column_List_maxValue">

        pool_id, dimension_id, indicator_key,  max(value) as value, resource_type, create_at

    </sql>

    <sql id="Aggregation_Group_By">

        dimension_id, indicator_key

    </sql>

    <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="com.chinamobile.epic.dao.model.PerformanceMetricAnalysisModel">

        select

        <include refid="Base_Column_List"/>

        from performance_metirc_business_${tableNameSuffix}

        where dimension_id = #{dimensionId,jdbcType=CHAR}

        and indicator_key = #{indicatorKey,jdbcType=VARCHAR}

        and create_at = #{createAt,jdbcType=TIMESTAMP}

    </select>

    <select id="groupByDimensionIDAndIndicatorID" resultMap="BaseResultMap">

        select

        <if test='func=="avg"'>

            <include refid="Base_Column_List_avgValue"/>

        </if>

        <if test='func=="sum"'>

            <include refid="Base_Column_List_sumValue"/>

        </if>

        from ${srcTableName}

        where create_at &gt;= #{startTime,jdbcType=TIMESTAMP}

        and create_at &lt;= #{endTime,jdbcType=TIMESTAMP}

        GROUP BY

        <include refid="Aggregation_Group_By"/>

    </select>

    <!-- 这种方式可能不准确，应该使用求last的方式： 见下面-->

    <!--<select id="groupCounterDataByDimensionIndicator" resultMap="BaseResultMap">-->

    <!--select-->

    <!--<include refid="Base_Column_List_maxValue"/>-->

    <!--from ${srcTableName}-->

    <!--where create_at &gt;= #{startTime,jdbcType=TIMESTAMP}-->

    <!--and create_at &lt;= #{endTime,jdbcType=TIMESTAMP}-->

    <!--<choose>-->

    <!--<when test="list != null and list.size()>0">-->

    <!--and indicator_key in-->

    <!--<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">-->

    <!--#{item,jdbcType=CHAR}-->

    <!--</foreach>-->

    <!--</when>-->

    <!--<otherwise>-->

    <!--and indicator_key in('Unknown')-->

    <!--</otherwise>-->

    <!--</choose>-->

    <!--GROUP BY-->

    <!--<include refid="Aggregation_Group_By"/>-->

    <!--</select>-->

    <select id="groupCounterDataByDimensionIndicator" resultMap="BaseResultMap">

        select

        <include refid="Base_Column_List"/>

        from

        (

        SELECT * FROM ${srcTableName}

        WHERE create_at &gt;= #{startTime,jdbcType=TIMESTAMP}

        and create_at &lt;= #{endTime,jdbcType=TIMESTAMP}

        <choose>

            <when test="list != null and list.size()>0">

                and indicator_key in

                <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">

                    #{item,jdbcType=CHAR}

                </foreach>

            </when>

            <otherwise>

                and indicator_key in('Unknown')

            </otherwise>

        </choose>

        ORDER BY create_at DESC

        ) AS t

        GROUP BY

        <include refid="Aggregation_Group_By"/>

    </select>

    <select id="groupGaugeDataByDimensionIndicator" resultMap="BaseResultMap">

        select

        <include refid="Base_Column_List_avgValue"/>

        from ${srcTableName}

        where create_at &gt;= #{startTime,jdbcType=TIMESTAMP}

        and create_at &lt;= #{endTime,jdbcType=TIMESTAMP}

        <choose>

            <when test="list != null and list.size()>0">

                and indicator_key in

                <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">

                    #{item,jdbcType=CHAR}

                </foreach>

            </when>

            <otherwise>

                and indicator_key in('Unknown')

            </otherwise>

        </choose>

        GROUP BY

        <include refid="Aggregation_Group_By"/>

    </select>

    <delete id="deleteByPrimaryKey" parameterType="com.chinamobile.epic.dao.model.PerformanceMetricAnalysisModel">

      delete from performance_metirc_business_${tableNameSuffix}

      where dimension_id = #{dimensionId,jdbcType=CHAR}

          and indicator_key = #{indicatorKey,jdbcType=VARCHAR}

          and create_at = #{createAt,jdbcType=TIMESTAMP}

  </delete>

    <delete id="deleteBeforeTime">

        delete from performance_metirc_business_${tableNameSuffix}

        where create_at &lt;= #{createAt,jdbcType=TIMESTAMP}

    </delete>

    <insert id="insert" parameterType="com.chinamobile.epic.dao.model.PerformanceMetricAnalysisModel">

       insert into performance_metirc_business_${tableNameSuffix} (dimension_id, indicator_key, create_at, pool_id, value, resource_type )

      values (#{dimensionId,jdbcType=CHAR}, #{indicatorKey,jdbcType=VARCHAR}, #{createAt,jdbcType=TIMESTAMP},

      #{poolId,jdbcType=CHAR}, #{value,jdbcType=DOUBLE}, #{resourceType,jdbcType=VARCHAR}

      )

  </insert>

    <insert id="insertBatch">

        insert into performance_metirc_business_${tableNameSuffix} (dimension_id, indicator_key, create_at, pool_id, value, resource_type )

        values

        <foreach collection="list" item="item" index="index" separator=",">

            (#{item.dimensionId,jdbcType=CHAR}, #{item.indicatorKey,jdbcType=VARCHAR}, #{item.createAt,jdbcType=TIMESTAMP},

            #{item.poolId,jdbcType=CHAR}, #{item.value,jdbcType=DOUBLE}, #{item.resourceType,jdbcType=VARCHAR} )

        </foreach>

    </insert>

    <insert id="insertSelective" parameterType="com.chinamobile.epic.dao.model.PerformanceMetricAnalysisModel">

        insert into performance_metirc_business_${tableNameSuffix}

        <trim prefix="(" suffix=")" suffixOverrides=",">

            <if test="poolId != null">

                pool_id,

            </if>

            <if test="dimensionId != null">

                dimension_id,

            </if>

            <if test="indicatorKey != null">

                indicator_key,

            </if>

            <if test="value != null">

                value,

            </if>

            <if test="resourceType != null">

                resource_type,

            </if>

            <if test="createAt != null">

                create_at,

            </if>

        </trim>

        <trim prefix="values (" suffix=")" suffixOverrides=",">

            <if test="poolId != null">

                #{poolId,jdbcType=CHAR},

            </if>

            <if test="dimensionId != null">

                #{dimensionId,jdbcType=CHAR},

            </if>

            <if test="indicatorKey != null">

                #{indicatorKey,jdbcType=VARCHAR},

            </if>

            <if test="value != null">

                #{value,jdbcType=DOUBLE},

            </if>

            <if test="resourceType != null">

                #{resourceType,jdbcType=VARCHAR},

            </if>

            <if test="createAt != null">

                #{createAt,jdbcType=TIMESTAMP},

            </if>

        </trim>

    </insert>

    <update id="updateByPrimaryKeySelective" parameterType="com.chinamobile.epic.dao.model.PerformanceMetricAnalysisModel">

        update performance_metirc_business_${tableNameSuffix}

        <set>

            <if test="poolId != null">

                pool_id = #{poolId,jdbcType=CHAR},

            </if>

            <if test="value != null">

                value = #{value,jdbcType=DOUBLE},

            </if>

            <if test="resourceType != null">

                resource_type = #{resourceType,jdbcType=VARCHAR},

            </if>

        </set>

        where dimension_id = #{dimensionId,jdbcType=CHAR}

        and indicator_key = #{indicatorKey,jdbcType=VARCHAR}

        and create_at = #{createAt,jdbcType=TIMESTAMP}

    </update>

    <update id="updateByPrimaryKey" parameterType="com.chinamobile.epic.dao.model.PerformanceMetricAnalysisModel">

    update performance_metirc_business_${tableNameSuffix}

    set pool_id = #{poolId,jdbcType=CHAR},

      value = #{value,jdbcType=DOUBLE},

      resource_type = #{resourceType,jdbcType=VARCHAR}

    where dimension_id = #{dimensionId,jdbcType=CHAR}

      and indicator_key = #{indicatorKey,jdbcType=VARCHAR}

      and create_at = #{createAt,jdbcType=TIMESTAMP}

  </update>

</mapper>

相关文章