mybatis中crud操作范例

  1 <?xml version="1.0" encoding="UTF-8"?>
  2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  3         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  4 
  5 <!--提供基本版，依据业务情况，酌情添加-->
  6 <!--author XiJun.Gong-->
  7 
  8 <mapper namespace="com.qunar.qexam2.course.dao.CourseDao">
  9 
 10     <insert id="insertCourse" parameterType="com.qunar.qexam2.course.model.CourseDomain"
 11             useGeneratedKeys="true" keyProperty="id">
 12         INSERT INTO
 13             course
 14             (
 15                 classification_id,
 16                 course_name,
 17                 create_time,
 18                 create_user,
 19                 update_time,
 20                 update_user,
 21                 is_delete
 22             )
 23         VALUES
 24             (
 25                 #{classificationId},
 26                 #{name},
 27                 #{createTime},
 28                 #{creatorTalkId},
 29                 #{modifyTime},
 30                 #{menderTalkId},
 31                 #{isDelete}
 32             )
 33     </insert>
 34     <!--批量插入数据-->
 35     <insert id="insertCourseBatch" parameterType="com.qunar.qexam2.course.model.CourseDomain">
 36         INSERT INTO
 37         course
 38         (
 39         classification_id ,
 40         course_name ,
 41         create_time ,
 42         create_user,
 43         update_time,
 44         update_user,
 45         is_delete
 46         )
 47         VALUES
 48         <foreach collection="list" item="item" index="index" separator=",">
 49             (
 50             #{item.classificationId},
 51             #{item.name} ,
 52             #{item.createTime} ,
 53             #{item.creatorTalkId},
 54             #{item.modifyTime},
 55             #{item.menderTalkId},
 56             #{item.isDelete}
 57             )
 58         </foreach>
 59 
 60     </insert>
 61 
 62     <!--物理删除-->
 63     <delete id="deleteCourse">
 64 
 65         DELETE course1 , question1
 66         FROM course course1
 67         JOIN question question1
 68         ON course1.id = question1.course_id
 69         AND question1.use_count = 0
 70 
 71         WHERE
 72         course1.id = #{CourseId};
 73 
 74     </delete>
 75 
 76 
 77     <update id="updateCourse" parameterType="com.qunar.qexam2.course.model.CourseDomain">
 78 
 79         UPDATE course
 80         <set>
 81 
 82             <if test="classificationId != null and classificationId !='' ">
 83                 course.classification_id = #{classificationId} ,
 84             </if>
 85 
 86             <if test=" name != null and name != '' ">
 87                 course.course_name = #{name} ,
 88             </if>
 89 
 90             <if test="createTime != null and createTime !='' ">
 91                 course.create_time = #{createTime} ,
 92             </if>
 93 
 94             <if test="creatorTalkId != null and creatorTalkId!='' ">
 95                 course.create_user = #{creatorTalkId} ,
 96             </if>
 97 
 98             <if test="modifyTime != null and modifyTime != '' ">
 99                 course.update_time = #{modifyTime} ,
100             </if>
101 
102             <if test="menderTalkId != null and menderTalkId !='' ">
103                 course.update_user = #{menderTalkId} ,
104             </if>
105 
106             <if test="isDelete != null and isDelete != '' ">
107                 course.is_delete = #{isDelete}
108             </if>
109 
110         </set>
111         WHERE course.id =#{id}
112 
113     </update>
114 
115     <!--逻辑删除-->
116     <update id="updateCourseStatus">
117         UPDATE course AS course1
118          JOIN question AS  question1
119         ON course1.id = question1.course_id
120         AND question1.use_count = 0
121         SET
122         course1.is_delete = 1,
123         question1.is_delete = 1
124         WHERE
125         course1.id = #{CourseId}
126 
127     </update>
128 
129     <!--分类逻辑删除-->
130     <!--逻辑删除-->
131     <update id="updateBatchCourseStatus">
132 
133         UPDATE course  As course1
134             JOIN question As question1
135         ON
136         course1.id = question1.course_id
137         AND question1.use_count = 0
138         SET course1.is_delete = 1,
139         question1.is_delete = 1
140         WHERE
141         course.classification_id = #{classificationId}
142     </update>
143 
144     <!--逻辑添加-->
145     <update id="updateCourseOnline">
146         UPDATE course
147         SET course.is_delete = 0
148         WHERE
149             course.id = #{CourseId}
150     </update>
151 
152     <select id="selectCourses" resultType="com.qunar.qexam2.course.model.CourseDomain">
153 
154         SELECT
155         course.id as id ,
156         course.classification_id as classificationId ,
157         course.course_name as name ,
158         course.create_time as createTime ,
159         course.create_user as creatorTalkId ,
160         course.update_time as modifyTime ,
161         course.update_user as menderTalkId ,
162         course.is_delete as isDelete
163 
164         FROM course
165 
166         <where>
167 
168             <if test="classificationId != null and classificationId != '' ">
169                 AND
170                 course.classification_id = #{classificationId}
171             </if>
172             <if test="isDelete != null and isDelete != '' ">
173                 AND
174                 course.is_delete = #{isDelete}
175             </if>
176 
177         </where>
178         order by course.create_time desc
179     </select>
180 
181     <select id="selectCourseVoAll" resultType="com.qunar.qexam2.course.vo.CourseVo">
182         SELECT
183             course.id          AS id,
184             course.course_name AS name
185         FROM course
186         WHERE
187             course.is_delete = 0
188         ORDER BY course.create_time DESC
189     </select>
190 
191 
192     <select id="CountCourses" resultType="com.qunar.qexam2.course.model.CourseDomain">
193         SELECT COUNT(*)
194         FROM course
195         <where>
196             <if test="classificationId != null  and classificationId != '' ">
197                 AND
198                 course.classification_id = #{classificationId}
199             </if>
200             AND
201             course.is_delete = 0
202         </where>
203     </select>
204 
205     <!--统计未进行逻辑删除的课程-->
206     <select id="CountCoursesByCourseId" resultType="java.lang.Integer">
207         SELECT count(*)
208         FROM course
209         WHERE course.is_delete = 0
210     </select>
211 
212     <!--统计多个分类下的课程数目-->
213     <select id="CountCoursesByCategoryId" resultType="Integer">
214         SELECT COUNT(*)
215         FROM course
216         WHERE
217         course.is_delete = 0
218         AND course.classification_id IN
219         <foreach item="classificationId" index="index" collection="classificationIdList"
220                  open="(" separator="," close=")">
221             #{classificationId}
222         </foreach>
223     </select>
224 
225 
226     <select id="selectCourseByName" resultType="com.qunar.qexam2.course.model.CourseDomain">
227 
228         SELECT
229         course.id as id ,
230         course.classification_id as classificationId ,
231         course.course_name as name ,
232         course.create_time as createTime ,
233         course.create_user as creatorTalkId ,
234         course.update_time as modifyTime ,
235         course.update_user as menderTalkId ,
236         course.is_delete as isDelete
237 
238         FROM course
239         <where>
240             <if test="CourseName != null and CourseName != '' ">
241                 AND
242                 course.course_name like #{CourseName}
243             </if>
244             <if test="isDelete != null  and isDelete != '' ">
245                 AND
246                 course.is_delete = #{isDelete}
247             </if>
248         </where>
249 
250         order by course.create_time desc
251     </select>
252 
253     <select id="selectCourse" resultType="com.qunar.qexam2.course.model.CourseDomain">
254 
255         SELECT
256         course.id as id ,
257         course.classification_id as classificationId ,
258         course.course_name as name ,
259         course.create_time as createTime ,
260         course.create_user as creatorTalkId ,
261         course.update_time as modifyTime ,
262         course.update_user as menderTalkId ,
263         course.is_delete as isDelete
264 
265         FROM course
266         <where>
267             <if test="CourseId != null and CourseId != '' ">
268                 AND
269                 course.id = #{CourseId}
270             </if>
271             <if test="isDelete != null and isDelete !='' ">
272                 AND
273                 course.is_delete = #{isDelete}
274             </if>
275         </where>
276         order by course.create_time desc
277     </select>
278 
279 
280     <select id="selectCourseWithoutLimit"
281             resultType="com.qunar.qexam2.course.model.CourseDomain">
282 
283         SELECT
284             course.id                AS id,
285             course.classification_id AS classificationId,
286             course.course_name       AS name,
287             course.create_time       AS createTime,
288             course.create_user       AS creatorTalkId,
289             course.update_time       AS modifyTime,
290             course.update_user       AS menderTalkId,
291             course.is_delete         AS isDelete
292         FROM course
293 
294         WHERE course.is_delete = 0
295         ORDER BY course.create_time DESC
296     </select>
297 
298     <select id="selectCourseByAuthor" resultType="com.qunar.qexam2.course.model.CourseDomain">
299 
300         SELECT
301         course.id as id ,
302         course.classification_id as classificationId ,
303         course.course_name as name ,
304         course.create_time as createTime ,
305         course.create_user as creatorTalkId ,
306         course.update_time as modifyTime ,
307         course.update_user as menderTalkId ,
308         course.is_delete as isDelete
309 
310         FROM course
311         <where>
312             <if test="creatorTalkId != null  and creatorTalkId != '' ">
313                 AND
314                 course.create_user = #{creatorTalkId}
315             </if>
316 
317             <if test="isDelete != null and isDelete != '' ">
318                 AND
319                 course.is_delete = #{isDelete}
320             </if>
321         </where>
322         order by course.create_time desc
323     </select>
324 
325     <select id="selectClassificationName" resultType="java.lang.String">
326         SELECT classification.tag_name
327         FROM course
328         LEFT JOIN classification
329         ON course.classification_id = classification.id
330         <where>
331             <if test="courseId != null and courseId !='' ">
332                 AND
333                 course.id = courseId
334             </if>
335         </where>
336     </select>
337 
338     <select id="queryCourseVoByCategoryId" resultType="com.qunar.qexam2.course.vo.CourseVo">
339         SELECT
340         course.id AS id ,
341         course.course_name AS name,
342         FROM course
343         <where>
344             <if test="classificationId != null and classificationId != '' ">
345                 AND
346                 course.classification_id = #{classificationId}
347             </if>
348         </where>
349     </select>
350 
351 
352     <!--依照分类来返回课程列表信息-->
353     <select id="queryCourseInfVoByCategoryId" resultType="com.qunar.qexam2.course.vo.CourseInfoVo">
354 
355         SELECT
356         course.id AS id ,
357         course.course_name AS courseName,
358         course.create_user AS createUser,
359         course.create_time As createTime
360         FROM course
361 
362         <where>
363             <if test="classificationId != null and classificationId != '' ">
364                 AND
365                 course.classification_id = #{classificationId}
366             </if>
367         </where>
368 
369     </select>
370 
371     <!--依照分类来返回课程列表信息-->
372     <select id="queryCourseInfVoByCategoryIdList" resultType="com.qunar.qexam2.course.vo.CourseInfoVo">
373         SELECT
374         course.id AS id ,
375         course.course_name AS courseName,
376         course.create_user AS createUser,
377         course.create_time As createTime
378         FROM course
379         WHERE course.classification_id IN
380         <foreach item="classificationId" index="index" collection="classificationIdList"
381                  open="(" separator="," close=")">
382             #{classificationId}
383         </foreach>
384     </select>
385 
386     <!--
387         &lt;!&ndash;给予课程Id查询课程所属的一二级部门部门&ndash;&gt;
388         <select id="queryCourseAffiliation" resultType="com.qunar.qexam2.course.vo.CourseAffiliation">
389 
390           SELECT
391             course1.course_name AS courseName ,
392             category1.tag_name  As firstDepart ,
393             category2.tag_name  AS secondDepart
394 
395           FROM
396               course course1
397 
398            JOIN classification category2 ON
399              category2.id  =  course1.classification_id
400 
401            JOIN  classification category1 ON
402             category1.id = category2.parent_id
403 
404           WHERE course1.id = #{courseId}
405 
406         </select>
407     -->
408 
409     <!--依照分类来返回课程列表信息-->
410     <select id="queryCategoryIdByCourseId" resultType="java.lang.Integer">
411         SELECT
412         course.classification_id AS classificationId
413         FROM course
414         WHERE course.id IN
415         <foreach item="courseId" index="index" collection="courseIdList"
416                  open="(" separator="," close=")">
417             #{courseId}
418         </foreach>
419     </select>
420 
421 
422     <!--依照分类Id来返回课程Id列表信息-->
423     <select id="queryCourseIdByCategoryId" resultType="java.lang.Integer">
424         SELECT
425         course.id
426         FROM course
427         WHERE course.classification_id IN
428         <foreach item="classificationId" index="index" collection="categoryIdList"
429                  open="(" separator="," close=")">
430             #{classificationId}
431         </foreach>
432     </select>
433 
434 
435 </mapper>