Sql mybatis

2018-06-04  本文已影响0人  bigfish1129

SQLmap

<?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.*.Mapper">
    <resultMap id="BaseResultMap" type="OrderDO">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="gmt_create" property="gmtCreate"/>
        <result column="gmt_modified" property="gmtModified"/>
        <result column="version" property="version"/>
        <result column="user_id" property="userId"/>
        <result column="user_name" property="userName"/>
    </resultMap>

    <sql id="columns">
        id, gmt_create, gmt_modified, version, user_id, user_name
    </sql>

    <insert id="save" parameterType="OrderDO">
        INSERT INTO order(
        user_id, user_name
        ) VALUES (
        #{userId},
        #{userName}
        )
        <selectKey keyProperty="id" resultType="long" order="AFTER">
            SELECT LAST_INSERT_ID();
        </selectKey>
    </insert>

    <update id="update" parameterType="OrderDO">
        UPDATE
        order
        SET
        gmt_modified = now(), version = version + 1
        <if test="status != null">
            , status = #{status}
        </if>
        WHERE
        id = #{Id}
        <if test="userId != null">
            and user_id = #{userId}
        </if>
    </update>

    <select id="query" parameterType="com.*.Param"
            resultMap="BaseResultMap">
        SELECT
        a.id,
        a.gmt_create,
        a.gmt_modified,
        a.version,
        a.user_id,
        a.user_name,
        FROM
        order a
        <if test="task != null">
            LEFT JOIN task b ON b.id = a.instance_id and b.status = 5
        </if>
        <where>
            <if test="userId != null">
                a.user_id = #{userId}
            </if>
        </where>
        order by a.id desc
    </select>

    <select id="queryFor" parameterType="OrderDO" resultMap="BaseResultMap">
        SELECT
        <include refid="columns"/>
        FROM
        order
        <where>
            <if test="userId != null">
                user_id = #{userId}
            </if>
            <if test="statusSet != null">
                and status in
                <foreach collection="statusSet" item="e" open="(" separator="," close=")">
                    #{e}
                </foreach>
            </if>
        </where>
        order by id desc
    </select>

    <delete id="deleteOrderId" parameterType="map">
        DELETE FROM order WHERE user_id = #{userId} AND order_id = #{OrderId}
    </delete>

    <select id="summary" parameterType="map"
            resultType="com.*.DTO">
        SELECT
          count(1) as "count",
          status
        FROM
          order
        WHERE
          user_id = ${userId}
        <if test="statusSet != null">
            and status in
            <foreach collection="statusSet" item="e" open="(" separator="," close=")">
                #{e}
            </foreach>
        </if>
        GROUP BY status order by status
    </select>
</mapper>

2、mapper

/**
     * 数量统计
     */
    List<DTO> summary(@Param("Id") long Id, @Param("statusSet") Set<Integer> statusSet);
上一篇下一篇

猜你喜欢

热点阅读