实战Sql语句

1. 添加：

    @Insert({
               "INSERT INTO `banner`(`type`，.......)",
               "VALUES (#{type}, ......)"})
       @SelectKey(statement = "SELECT LAST_INSERT_ID()", keyProperty = "id", 
       before = false, resultType = Integer.class)
       int insertRecord(Advertisement record);

2. 统计数量：

    @Select("select count(1) from banner where deleted = 0 and type = 2")
    int countType2();

3. 查询：

      @Results({
               @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
               @Result(column = "type", property = "type", jdbcType = JdbcType.INTEGER),
              
               @Result(column = "description", property = "description", jdbcType = JdbcType.VARCHAR),
             
               @Result(column = "start_time", property = "startTime", jdbcType = JdbcType.TIMESTAMP),
             
               @Result(column = "deleted", property = "deleted", jdbcType = JdbcType.BIT)
       })
       @Select({
               "<script> ",
               "select * from banner where deleted = false ",
               " <if test = 'descriptionKey !=null' > ",
               "   and `description` like '%${descriptionKey}%' ",
               " </if>",
               " <if test = 'startTime !=null' > ",
               "   and `start_time` &gt;= #{startTime} ",
               " </if>",
               " <if test = 'endTime !=null' > ",
               "   and `end_time` &lt;= #{endTime} ",
               " </if>",
               " <if test = 'type !=null' > ",
               "   and `type` = #{type} ",
               " </if>",
               " <if test = 'status == 0' > ",
               "   and `start_time` &gt; now() ",
               " </if>",
               " <if test = 'status == 1' > ",
               "   and `start_time` &lt;= now() and `end_time` &gt;= now() ",
               " </if>",
               " <if test = 'status == 2' > ",
               "   and `end_time` &lt; now() ",
               " </if>",
               "order by `type` asc, `start_time` desc",
               "</script>"
       })
       List<Advertisement> selectByKeys(@Param("descriptionKey") String descriptionKey,
                                        @Param("startTime") String startTime,
                                        @Param("endTime") String endTime,
                                        @Param("type") Integer type,
                                        @Param("status") Integer status);

4.时间段查询：

  @Select({
            "select * from banner where deleted = false and type = #{type}",
            "and `start_time` >= now() and `end_time` <= now() "
    })
    List<Advertisement> selectActiveRecords(@Param("type") int type);

5.更新：

 @Update({
            "update `banner_index` set `type` = #{type}, 
            `index_list` = #{indexList} where id = #{id}"
    })
    int updateAdvertisementIndex(AdvertisementIndex record);

6.XXX.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.qf.dao.QfClassifyDao">

    <resultMap id="BaseResultMap" type="qfClassify">
        <!--@Table qf_classify-->
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="courseClassifyName" column="course_classify_name" jdbcType="VARCHAR"/>
        <result property="classifyStatus" column="classify_status" jdbcType="INTEGER"/>
        <result property="createtime" column="createtime" jdbcType="TIMESTAMP"/>
        <result property="updatetime" column="updatetime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <!--查询单个-->
    <select id="queryById" resultMap="BaseResultMap">
        select
        id, course_classify_name, classify_status, createtime, updatetime
        from qf_classify
        where id = #{id}
    </select>

    <!--查询指定行数据-->
    <select id="queryAllByLimit" resultMap="BaseResultMap">
        select
        id, course_classify_name, classify_status, createtime, updatetime
        from qf_classify

    </select>

    <!--通过实体作为筛选条件查询-->
    <select id="queryAll" resultMap="BaseResultMap">
        select
        id, course_classify_name, classify_status, createtime, updatetime
        from qf_classify
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="courseClassifyName != null and courseClassifyName != ''">
                and course_classify_name = #{courseClassifyName}
            </if>
            <if test="classifyStatus != null">
                and classify_status = #{classifyStatus}
            </if>
            <if test="createtime != null">
                and createtime = #{createtime}
            </if>
            <if test="updatetime != null">
                and updatetime = #{updatetime}
            </if>
        </where>
    </select>

    <!--新增所有列-->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into qf_classify(course_classify_name, classify_status, createtime, updatetime)
        values (#{courseClassifyName}, #{classifyStatus}, #{createtime}, #{updatetime})
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        update qf_classify
        <set>
            <if test="courseClassifyName != null and courseClassifyName != ''">
                course_classify_name = #{courseClassifyName},
            </if>
            <if test="classifyStatus != null">
                classify_status = #{classifyStatus},
            </if>
            <if test="createtime != null">
                createtime = #{createtime},
            </if>
            <if test="updatetime != null">
                updatetime = #{updatetime},
            </if>
        </set>
        where id = #{id}
    </update>

    <!--通过主键删除-->
    <delete id="deleteById">
        delete from qf_classify where id = #{id}
    </delete>

</mapper>