2018 11-14 (Mybatis)

<?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.youxuepai.train.mapper.TrainCourseExtMapper">

     <resultMap id="BaseResultMap_course"

           type="com.youxuepai.train.dto.TrainCourseDTO">

           <id column="id_course" jdbcType="INTEGER" property="idCourse" />

           <result column="course_name" jdbcType="VARCHAR"

                property="courseName" />

           <result column="course_introduce" jdbcType="VARCHAR"

                property="courseIntroduce" />

           <result column="category_id" jdbcType="INTEGER"

                property="categoryId" />

           <result column="display_dict_id" jdbcType="INTEGER"

                property="displayDictId" />

           <result column="cover_url" jdbcType="VARCHAR"

                property="coverUrl" />

           <result column="course_file_name" jdbcType="VARCHAR"

                property="courseFileName" />

           <result column="course_url" jdbcType="VARCHAR"

                property="courseUrl" />

           <result column="is_top" jdbcType="TINYINT" property="isTop" />

           <result column="is_show" jdbcType="TINYINT" property="isShow" />

           <result column="is_has_work" jdbcType="TINYINT"

                property="isHasWork" />

           <result column="work_question" jdbcType="VARCHAR"

                property="workQuestion" />

           <result column="is_video" jdbcType="TINYINT" property="isVideo" />

           <result column="create_time" jdbcType="TIMESTAMP"

                property="createTime" />

           <result column="update_time" jdbcType="TIMESTAMP"

                property="updateTime" />

           <result column="stars_number" jdbcType="INTEGER"

                property="starsNumber" />

           <result column="pv_number" jdbcType="INTEGER"

                property="pvNumber" />

           <result column="is_collected" jdbcType="TINYINT"

                property="isCollected" />

           <result column="is_like" jdbcType="TINYINT" property="isLike" />

     </resultMap>

     <resultMap id="BaseResultMap_work"

           type="com.youxuepai.train.dto.TrainCourseWorkDTO"

           extends="BaseResultMap_course">

           <result column="id_work" jdbcType="INTEGER" property="idWork" />

           <result column="work_url" jdbcType="VARCHAR" property="workUrl" />

           <result column="cur_time" jdbcType="DOUBLE" property="curTime" />

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

           <result column="progress" jdbcType="TINYINT"

                property="progress" />

           <result column="start_time" jdbcType="TIMESTAMP"

                property="startTime" />

           <result column="end_time" jdbcType="TIMESTAMP"

                property="endTime" />

           <result column="already_done" jdbcType="TINYINT"

                property="alreadyDone" />

     </resultMap>

     <select id="queryIndexCourse" resultMap="BaseResultMap_course">

           select t.id_course,

           t.course_name,

           t.course_introduce,

           t.category_id,

           t.display_dict_id,

           t.cover_url,

           t.course_file_name,

           t.course_url,

           t.is_top,

           t.is_show,

           t.is_has_work,

           t.work_question,

           t.is_video,

           t.create_time,

           t.update_time,

           (select IFNULL(sum(IFNULL(x.is_like,0)),0) from train_course2user x

           where

           x.course_id=t.id_course) stars_number,

           t.pv_number ,

           b.is_collected,

           b.is_like

           from train_course t left join

           train_course2user b

           on

           t.id_course=b.course_id

           and b.user_id=

           #{userId,jdbcType=INTEGER}

           where

           t.category_id =

           #{categoryId,jdbcType=INTEGER}

           and t.is_show=1

           and

           t.is_top=1

           order by

           t.update_time desc

     </select>

     <select id="queryIndexCourseNoUser"

           resultMap="BaseResultMap_course">

           select t.* from ( select t.id_course,

           t.course_name,

           t.course_introduce,

           t.category_id,

           t.display_dict_id,

           t.cover_url,

           t.course_file_name,

           t.course_url,

           t.is_top,

           t.is_show,

           t.is_has_work,

           t.work_question,

           t.is_video,

           t.create_time,

           t.update_time,

           (select

           IFNULL(sum(IFNULL(x.is_like,0)),0) from train_course2user x where

           x.course_id=t.id_course) stars_number,

           t.pv_number ,

           null is_collected,

           null is_like

           from train_course t

           where t.category_id =

           #{categoryId,jdbcType=INTEGER}

           and t.is_show=1

           and

           t.is_top=1

           order by

           t.update_time desc ) t limit 2

     </select>

     <select id="queryCourseWork" resultMap="BaseResultMap_work">

           select a.id_course,

           a.course_name,

           a.course_introduce,

           a.category_id,

           a.display_dict_id,

           a.cover_url,

           a.course_file_name,

           a.course_url,

           a.is_top,

           a.is_show,

           a.is_has_work,

           a.work_question,

           a.is_video,

           a.create_time,

           a.update_time,

           a.stars_number,

           b.id_work,

           b.work_url,

           c.is_collected,

           c.is_like,

           c.cur_time,

           c.duration,

           c.progress,

           c.start_time,

           c.end_time,

           c.already_done

           from train_course a left join

           train_work b

           on a.id_course=b.course_id

           left join train_course2user c

           on

           a.id_course=c.course_id

           <if test="userId != null">

                and c.user_id= #{userId,jdbcType=INTEGER}

           </if>

           where a.id_course=#{idCourse,jdbcType=INTEGER}

           order by b.update_time

           desc limit 1

     </select>

     <update id="increasePvNumber" parameterType="Integer">

           update train_course

           t

           set t.pv_number=IFNULL(t.pv_number,0)+1,

           t.update_time=now()

           where

           t.id_course=#{idCourse,jdbcType=INTEGER}

     </update>

     <update id="increaseStarsNumber" parameterType="Integer">

           update

           train_course t

           set t.stars_number=IFNULL(t.stars_number,0)+1,

           t.update_time=now()

           where t.id_course=#{idCourse,jdbcType=INTEGER}

     </update>

</mapper>

运用知识点：

resultMap

IFNULL

SELECT   需要被赋于的值 字段名   FROM……

order by  * desc 排序

join