Mybatis之SQL映射

TeacherMapper.xml:

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.baidu.sem.teacher.persistence.TeacherMapper"><sql id="teacher_fields_All">id,user_id,level,main_field,main_course,seniority,information,create_time,end_time</sql><sql id="teacher_fields_noId">user_id,level,main_field,main_course,seniority,information,create_time,end_time</sql><resultMap type="Teacher" id="teacher_ResultMap_ALL"><id property="id" column="T_ID" /><result property="userId" column="T_USERID" /><result property="name" column="U_NAME" /><result property="realName" column="U_REALNAME" /><result property="email" column="U_EMAIL" /><result property="idKind" column="U_IDKIND" /><result property="idCardNumber" column="U_IDCARDNUMBER" /><result property="cerName" column="CER_NAME" /><result property="cerNumber" column="CER_NUMBER" /><result property="cerCreate" column="CER_CREATE" /><result property="cerEnd" column="CER_END" /><result property="telMobile" column="U_TELMOBILE" /><result property="gender" column="U_GENDER" /><result property="province" column="U_PROVINCE" /><result property="city" column="U_CITY" /><result property="add" column="U_ADD" /><result property="status" column="TA_STATUS" /><result property="level" column="T_LEVEL" /><result property="mainField" column="T_MAINFIELD" /><result property="mainCourse" column="T_MAINCOURSE" /><result property="seniority" column="T_SENIORITY" /><result property="information" column="T_INFORMATION" /><result property="createTime" column="T_CREATE" /><result property="endTime" column="T_END" /></resultMap><select id="listByCondition" parameterType="map"resultMap="teacher_ResultMap_ALL">selectt.id AS T_ID,t.user_id AS T_USERID,u.name AS U_NAME,u.real_nameAS U_REALNAME,u.email AS U_EMAIL,u.id_kind AS U_IDKIND,u.id_card_number AS U_IDCARDNUMBER,u.tel_mobile AS U_TELMOBILE,u.gender AS U_GENDER,t.level AS T_LEVEL,t.main_field AS T_MAINFIELD,t.main_course AS T_MAINCOURSE,t.seniorityAS T_SENIORITY,t.informationAS T_INFORMATION,t.create_time AST_CREATE,t.end_time AS T_ENDFROMteacher tLEFT OUTER JOIN USER uON t.user_id=u.id<where><if test="levels!=null">t.level in<foreach collection="levels" item="item" index="index" open="("separator="," close=")">#{item}</foreach></if><if test="genders!=null">and u.gender in<foreach collection="genders" item="item" index="index"open="(" separator="," close=")">#{item}</foreach></if><if test="later_create_time!=null"> and t.create_time >#{later_create_time}</if><if test="earlier_create_time!=null">and t.create_time <#{earlier_create_time}</if><if test="keyWord!=null&keyWord!=''"> and (u.name like #{keyWord}or u.real_name like#{keyWord})</if></where><if test="orderBy!=null">order by ${orderBy}<if test="order!=null">${order}</if></if><if test="size!=null">limit<choose><when test="start !=null">#{start}</when><otherwise>0</otherwise></choose>,#{size}</if></select><select id="totalCount" parameterType="map" resultType="long">select count(*)FROMteacher tLEFT OUTER JOIN USER uON t.user_id=u.id<where><if test="levels!=null">t.level in<foreach collection="levels" item="item" index="index" open="("separator="," close=")">#{item}</foreach></if><if test="genders!=null">and u.gender in<foreach collection="genders" item="item" index="index"open="(" separator="," close=")">#{item}</foreach></if><if test="later_create_time!=null"> and t.create_time >#{later_create_time}</if><if test="earlier_create_time!=null">and t.create_time <#{earlier_create_time}</if><if test="keyWord!=null&keyWord!=''"> and (u.name like #{keyWord}or u.real_name like#{keyWord})</if></where></select><select id="findByApplyId" parameterType="java.lang.Long"resultMap="teacher_ResultMap_ALL">selectt.id AS T_ID,t.user_id AS T_USERID,u.name AS U_NAME,u.real_nameAS U_REALNAME,u.email AS U_EMAIL,u.id_kind AS U_IDKIND,u.id_card_number AS U_IDCARDNUMBER,u.tel_mobile AS U_TELMOBILE,u.gender AS U_GENDER,ta.status AS TA_STATUS,t.level AS T_LEVEL,t.main_field AS T_MAINFIELD,t.main_course AS T_MAINCOURSE,t.seniorityAS T_SENIORITY,t.information AS T_INFORMATION,t.create_time AST_CREATE,t.end_time AS T_ENDFROMteacher_apply taLEFT OUTER JOIN teacher tON ta.teacher_id = t.idLEFT OUTER JOIN USER uON t.user_id=u.idwhere ta.id=#{applyId}</select><select id="findByUserId" parameterType="java.lang.Long"resultMap="teacher_ResultMap_ALL">selectt.id AS T_ID,t.user_id AS T_USERID,u.name AS U_NAME,u.real_nameAS U_REALNAME,u.email AS U_EMAIL,u.id_kind AS U_IDKIND,u.id_card_number AS U_IDCARDNUMBER,u.tel_mobile AS U_TELMOBILE,u.gender AS U_GENDER,t.level AS T_LEVEL,t.main_field AS T_MAINFIELD,t.main_course AS T_MAINCOURSE,t.seniorityAS T_SENIORITY,t.information AS T_INFORMATION,t.create_time AST_CREATE,t.end_time AS T_ENDFROMteacher tLEFT OUTER JOIN USER uON t.user_id=u.idwhere u.id=#{userId} ;</select><select id="findByTeacherId" parameterType="java.lang.Long"resultMap="teacher_ResultMap_ALL">selectt.id AS T_ID,t.user_id AS T_USERID,u.name AS U_NAME,u.real_nameAS U_REALNAME,u.email AS U_EMAIL,u.id_kind AS U_IDKIND,u.id_card_number AS U_IDCARDNUMBER,u.tel_mobile AS U_TELMOBILE,u.gender AS U_GENDER,t.level AS T_LEVEL,t.main_field AS T_MAINFIELD,t.main_course AS T_MAINCOURSE,t.seniorityAS T_SENIORITY,t.information AS T_INFORMATION,t.create_time AST_CREATE,t.end_time AS T_ENDFROMteacher tLEFT OUTER JOIN USER uON t.user_id=u.idwhere t.id=#{teacherId} limit 0,1;</select><insert id="insert" parameterType="Teacher" useGeneratedKeys="true"keyProperty="id">INSERT INTO teacher (<include refid="teacher_fields_noId" />)VALUES(#{userId},#{level},#{mainField},#{mainCourse},#{seniority},#{information},#{createTime},#{endTime})</insert><update id="update" parameterType="Teacher">UPDATE teacher SETlevel =#{level},main_field = #{mainField},main_course = #{mainCourse},seniority = #{seniority},information = #{information},create_time =#{createTime},end_time = #{endTime}where user_id=#{userId}</update><update id="updateUser" parameterType="Teacher">UPDATE user SETreal_name =#{realName},tel_mobile = #{telMobile}where id=#{userId}</update><delete id="delete" parameterType="Teacher">delete fromteacherwhere id=#{id} </delete></mapper>

TeacherApplyMapper.xml:

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.baidu.sem.teacher.persistence.TeacherApplyMapper"><sql id="teacherApply_fields_All">id,teacher_id,level,main_field,main_course,seniority,information,file_path,create_time,update_time,status,refuse_reason</sql><sql id="teacherApply_fields_insert">teacher_id,main_field,main_course,seniority,information,file_path,create_time,status</sql><resultMap type="TeacherApply" id="teacherApply_ResultMap"><id property="id" column="id" /><result property="teacherId" column="teacher_id" /><result property="name" column="U_NAME" /><result property="realName" column="U_REAL_NAME" /><result property="level" column="level" /><result property="mainField" column="main_field" /><result property="mainCourse" column="main_course" /><result property="seniority" column="seniority" /><result property="information" column="information" /><result property="filePath" column="file_path" /><result property="createTime" column="create_time" /><result property="updateTime" column="update_time" /><result property="status" column="status" /><result property="refuseReason" column="refuse_reason" /></resultMap><resultMap type="TeacherApply" id="teacherApply_ResultMap11111111"><id property="id" column="TA_ID" /><result property="teacherId" column="TA_TEACHERID" /><result property="name" column="U_NAME" /><result property="realName" column="U_REAL_NAME" /><result property="level" column="TA_LEVEL" /><result property="mainField" column="TA_MAINFIELD" /><result property="mainCourse" column="TA_MAINCOURSE" /><result property="seniority" column="TA_SENIORITY" /><result property="information" column="TA_INFORMATION" /><result property="filePath" column="TA_FILEPATH" /><result property="createTime" column="TA_CREATE" /><result property="updateTime" column="TA_UPDATE" /><result property="status" column="TA_STATUS" /><result property="refuseReason" column="TA_REFULEREASON" /></resultMap><select id="listByCondition" parameterType="map"resultMap="teacherApply_ResultMap11111111">selectta.id AS TA_ID,ta.teacher_id AS TA_TEACHERID,u.name AS U_NAME,u.real_name AS U_REAL_NAME,ta.level AS TA_LEVEL,ta.main_field AS TA_MAINFIELD,ta.main_course AS TA_MAINCOURSE,ta.seniority AS TA_SENIORITY,ta.information AS TA_INFORMATION,ta.file_path AS TA_FILEPATH,ta.create_time AS TA_CREATE,ta.update_time AS TA_UPDATE,ta.refuse_reason AS TA_REFULEREASON,ta.status AS STATUSfromteacher_apply taleft outer join teacher teon ta.teacher_id=te.idleft outer join user uon te.user_id=u.id<where><if test="status!=null"> ta.status=#{status}</if><if test="keyWord!=null&keyWord!=''">and (u.name like #{keyWord} or u.real_name like#{keyWord})</if></where><if test="orderBy!=null">order by ${orderBy}<if test="order!=null">${order}</if></if><if test="size!=null">limit<choose><when test="start !=null">#{start}</when><otherwise>0</otherwise></choose>,#{size}</if></select><select id="totalCount" parameterType="map" resultType="long">select count(*)from teacher_apply ta left outer join teacher teonta.teacher_id=te.id left outer join user uon te.user_id=u.id<where><if test="status!=null"> ta.status=#{status}</if><if test="keyWord!=null&keyWord!=''">and (u.name like #{keyWord} or u.real_name like#{keyWord})</if></where></select><select id="listByTeacherId" parameterType="java.lang.Long"resultMap="teacherApply_ResultMap">select<include refid="teacherApply_fields_All" />from teacher_applywhere teacher_id=#{teacherId}order by create_time desc ;</select><select id="findByApplyId" parameterType="java.lang.Long"resultMap="teacherApply_ResultMap">select<include refid="teacherApply_fields_All" />from teacher_applywhere id=#{id} limit 0,1;</select><insert id="insert" parameterType="TeacherApply"useGeneratedKeys="true" keyProperty="id">INSERT INTO teacher_apply (<include refid="teacherApply_fields_insert" />)VALUES(#{teacherId},#{mainField},#{mainCourse},#{seniority},#{information},#{filePath},#{createTime},#{status})</insert><update id="applySucceed" parameterType="TeacherApply">UPDATE teacher_apply appSETapp.update_time= #{updateTime},app.status = #{status}WHEREapp.id =#{id}</update><update id="applyFailed" parameterType="TeacherApply">UPDATE teacher_apply appSETapp.update_time= #{updateTime},app.status = #{status},app.refuse_reason =#{refuseReason}WHEREapp.id =#{id}</update><delete id="deleteByTeaId" parameterType="java.lang.Long">delete fromteacher_applywhere teacher_id=#{teacherId} </delete></mapper>