<?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">
<!--提供基本版，依据业务情况，酌情添加-->
<!--author XiJun.Gong-->
<mapper namespace="com.qunar.qexam2.course.dao.CourseDao">
<insert id="insertCourse" parameterType="com.qunar.qexam2.course.model.CourseDomain"
useGeneratedKeys="true" keyProperty="id">
INSERT INTO
course
(
classification_id,
course_name,
create_time,
create_user,
update_time,
update_user,
is_delete
)
VALUES
(
#{classificationId},
#{name},
#{createTime},
#{creatorTalkId},
#{modifyTime},
#{menderTalkId},
#{isDelete}
)
</insert>
<!--批量插入数据-->
<insert id="insertCourseBatch" parameterType="com.qunar.qexam2.course.model.CourseDomain">
INSERT INTO
course
(
classification_id ,
course_name ,
create_time ,
create_user,
update_time,
update_user,
is_delete
)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.classificationId},
#{item.name} ,
#{item.createTime} ,
#{item.creatorTalkId},
#{item.modifyTime},
#{item.menderTalkId},
#{item.isDelete}
)
</foreach>
</insert>
<!--物理删除-->
<delete id="deleteCourse">
DELETE course1 , question1
FROM course course1
JOIN question question1
ON course1.id = question1.course_id
AND question1.use_count = 0
WHERE
course1.id = #{CourseId};
</delete>
<update id="updateCourse" parameterType="com.qunar.qexam2.course.model.CourseDomain">
UPDATE course
<set>
<if test="classificationId != null and classificationId !='' ">
course.classification_id = #{classificationId} ,
</if>
<if test=" name != null and name != '' ">
course.course_name = #{name} ,
</if>
<if test="createTime != null and createTime !='' ">
course.create_time = #{createTime} ,
</if>
<if test="creatorTalkId != null and creatorTalkId!='' ">
course.create_user = #{creatorTalkId} ,
</if>
<if test="modifyTime != null and modifyTime != '' ">
course.update_time = #{modifyTime} ,
</if>
<if test="menderTalkId != null and menderTalkId !='' ">
course.update_user = #{menderTalkId} ,
</if>
<if test="isDelete != null and isDelete != '' ">
course.is_delete = #{isDelete}
</if>
</set>
WHERE course.id =#{id}
</update>
<!--逻辑删除-->
<update id="updateCourseStatus">
UPDATE course AS course1
JOIN question AS question1
ON course1.id = question1.course_id
AND question1.use_count = 0
SET
course1.is_delete = 1,
question1.is_delete = 1
WHERE
course1.id = #{CourseId}
</update>
<!--分类逻辑删除-->
<!--逻辑删除-->
<update id="updateBatchCourseStatus">
UPDATE course As course1
JOIN question As question1
ON
course1.id = question1.course_id
AND question1.use_count = 0
SET course1.is_delete = 1,
question1.is_delete = 1
WHERE
course.classification_id = #{classificationId}
</update>
<!--逻辑添加-->
<update id="updateCourseOnline">
UPDATE course
SET course.is_delete = 0
WHERE
course.id = #{CourseId}
</update>
<select id="selectCourses" resultType="com.qunar.qexam2.course.model.CourseDomain">
SELECT
course.id as id ,
course.classification_id as classificationId ,
course.course_name as name ,
course.create_time as createTime ,
course.create_user as creatorTalkId ,
course.update_time as modifyTime ,
course.update_user as menderTalkId ,
course.is_delete as isDelete
FROM course
<where>
<if test="classificationId != null and classificationId != '' ">
AND
course.classification_id = #{classificationId}
</if>
<if test="isDelete != null and isDelete != '' ">
AND
course.is_delete = #{isDelete}
</if>
</where>
order by course.create_time desc
</select>
<select id="selectCourseVoAll" resultType="com.qunar.qexam2.course.vo.CourseVo">
SELECT
course.id AS id,
course.course_name AS name
FROM course
WHERE
course.is_delete = 0
ORDER BY course.create_time DESC
</select>
<select id="CountCourses" resultType="com.qunar.qexam2.course.model.CourseDomain">
SELECT COUNT(*)
FROM course
<where>
<if test="classificationId != null and classificationId != '' ">
AND
course.classification_id = #{classificationId}
</if>
AND
course.is_delete = 0
</where>
</select>
<!--统计未进行逻辑删除的课程-->
<select id="CountCoursesByCourseId" resultType="java.lang.Integer">
SELECT count(*)
FROM course
WHERE course.is_delete = 0
</select>
<!--统计多个分类下的课程数目-->
<select id="CountCoursesByCategoryId" resultType="Integer">
SELECT COUNT(*)
FROM course
WHERE
course.is_delete = 0
AND course.classification_id IN
<foreach item="classificationId" index="index" collection="classificationIdList"
open="(" separator="," close=")">
#{classificationId}
</foreach>
</select>
<select id="selectCourseByName" resultType="com.qunar.qexam2.course.model.CourseDomain">
SELECT
course.id as id ,
course.classification_id as classificationId ,
course.course_name as name ,
course.create_time as createTime ,
course.create_user as creatorTalkId ,
course.update_time as modifyTime ,
course.update_user as menderTalkId ,
course.is_delete as isDelete
FROM course
<where>
<if test="CourseName != null and CourseName != '' ">
AND
course.course_name like #{CourseName}
</if>
<if test="isDelete != null and isDelete != '' ">
AND
course.is_delete = #{isDelete}
</if>
</where>
order by course.create_time desc
</select>
<select id="selectCourse" resultType="com.qunar.qexam2.course.model.CourseDomain">
SELECT
course.id as id ,
course.classification_id as classificationId ,
course.course_name as name ,
course.create_time as createTime ,
course.create_user as creatorTalkId ,
course.update_time as modifyTime ,
course.update_user as menderTalkId ,
course.is_delete as isDelete
FROM course
<where>
<if test="CourseId != null and CourseId != '' ">
AND
course.id = #{CourseId}
</if>
<if test="isDelete != null and isDelete !='' ">
AND
course.is_delete = #{isDelete}
</if>
</where>
order by course.create_time desc
</select>
<select id="selectCourseWithoutLimit"
resultType="com.qunar.qexam2.course.model.CourseDomain">
SELECT
course.id AS id,
course.classification_id AS classificationId,
course.course_name AS name,
course.create_time AS createTime,
course.create_user AS creatorTalkId,
course.update_time AS modifyTime,
course.update_user AS menderTalkId,
course.is_delete AS isDelete
FROM course
WHERE course.is_delete = 0
ORDER BY course.create_time DESC
</select>
<select id="selectCourseByAuthor" resultType="com.qunar.qexam2.course.model.CourseDomain">
SELECT
course.id as id ,
course.classification_id as classificationId ,
course.course_name as name ,
course.create_time as createTime ,
course.create_user as creatorTalkId ,
course.update_time as modifyTime ,
course.update_user as menderTalkId ,
course.is_delete as isDelete
FROM course
<where>
<if test="creatorTalkId != null and creatorTalkId != '' ">
AND
course.create_user = #{creatorTalkId}
</if>
<if test="isDelete != null and isDelete != '' ">
AND
course.is_delete = #{isDelete}
</if>
</where>
order by course.create_time desc
</select>
<select id="selectClassificationName" resultType="java.lang.String">
SELECT classification.tag_name
FROM course
LEFT JOIN classification
ON course.classification_id = classification.id
<where>
<if test="courseId != null and courseId !='' ">
AND
course.id = courseId
</if>
</where>
</select>
<select id="queryCourseVoByCategoryId" resultType="com.qunar.qexam2.course.vo.CourseVo">
SELECT
course.id AS id ,
course.course_name AS name,
FROM course
<where>
<if test="classificationId != null and classificationId != '' ">
AND
course.classification_id = #{classificationId}
</if>
</where>
</select>
<!--依照分类来返回课程列表信息-->
<select id="queryCourseInfVoByCategoryId" resultType="com.qunar.qexam2.course.vo.CourseInfoVo">
SELECT
course.id AS id ,
course.course_name AS courseName,
course.create_user AS createUser,
course.create_time As createTime
FROM course
<where>
<if test="classificationId != null and classificationId != '' ">
AND
course.classification_id = #{classificationId}
</if>
</where>
</select>
<!--依照分类来返回课程列表信息-->
<select id="queryCourseInfVoByCategoryIdList" resultType="com.qunar.qexam2.course.vo.CourseInfoVo">
SELECT
course.id AS id ,
course.course_name AS courseName,
course.create_user AS createUser,
course.create_time As createTime
FROM course
WHERE course.classification_id IN
<foreach item="classificationId" index="index" collection="classificationIdList"
open="(" separator="," close=")">
#{classificationId}
</foreach>
</select>
<!--
<!–给予课程Id查询课程所属的一二级部门部门–>
<select id="queryCourseAffiliation" resultType="com.qunar.qexam2.course.vo.CourseAffiliation">
SELECT
course1.course_name AS courseName ,
category1.tag_name As firstDepart ,
category2.tag_name AS secondDepart
FROM
course course1
JOIN classification category2 ON
category2.id = course1.classification_id
JOIN classification category1 ON
category1.id = category2.parent_id
WHERE course1.id = #{courseId}
</select>
-->
<!--依照分类来返回课程列表信息-->
<select id="queryCategoryIdByCourseId" resultType="java.lang.Integer">
SELECT
course.classification_id AS classificationId
FROM course
WHERE course.id IN
<foreach item="courseId" index="index" collection="courseIdList"
open="(" separator="," close=")">
#{courseId}
</foreach>
</select>
<!--依照分类Id来返回课程Id列表信息-->
<select id="queryCourseIdByCategoryId" resultType="java.lang.Integer">
SELECT
course.id
FROM course
WHERE course.classification_id IN
<foreach item="classificationId" index="index" collection="categoryIdList"
open="(" separator="," close=")">
#{classificationId}
</foreach>
</select>
</mapper>