DutyMapper.xml备忘
来源：互联网 发布：winpcap是什么软件 编辑：程序博客网 时间：2024/06/07 02:18
xml:
Mapper:
<?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.sunny.dao.duty.DutyMapper" > <resultMap id="BaseResultMap" type="com.sunny.model.duty.Duty" > <id column="DUTY_ID" property="dutyId" jdbcType="INTEGER" /> <result column="DSCD" property="dscd" jdbcType="CHAR" /> <result column="UNIT_ID" property="unitId" jdbcType="INTEGER" /> <result column="YEAR" property="year" jdbcType="CHAR" /> <result column="MONTH" property="month" jdbcType="CHAR" /> <result column="DAY" property="day" jdbcType="CHAR" /> <result column="DAY0" property="day0" jdbcType="CHAR" /> <result column="WEEK_DAY" property="weekDay" jdbcType="CHAR" /> <result column="MORNING_PEOPLE_IDS" property="morningPeopleIds" jdbcType="VARCHAR" /> <result column="MORNING_PEOPLE_NAMES" property="morningPeopleNames" jdbcType="VARCHAR" /> <result column="AFTERNOON_PEOPLE_IDS" property="afternoonPeopleIds" jdbcType="VARCHAR" /> <result column="AFTERNOON_PEOPLE_NAMES" property="afternoonPeopleNames" jdbcType="VARCHAR" /> <result column="EVENING_PEOPLE_IDS" property="eveningPeopleIds" jdbcType="VARCHAR" /> <result column="EVENING_PEOPLE_NAMES" property="eveningPeopleNames" jdbcType="VARCHAR" /> <result column="LEADER_IDS" property="leaderIds" jdbcType="VARCHAR" /> <result column="LEADER_NAMES" property="leaderNames" jdbcType="VARCHAR" /> <result column="FLAG" property="flag" jdbcType="INTEGER" /> <result column="REMARK" property="remark" jdbcType="VARCHAR" /> </resultMap> <resultMap id="onHashMap_1" type="java.util.HashMap"> <result column="DSCD" property="dscd" jdbcType="CHAR" /> <result column="UNIT_ID" property="unitId" jdbcType="INTEGER" /> <result column="YEAR" property="year" jdbcType="CHAR" /> <result column="MONTH" property="month" jdbcType="CHAR" /> <result column="FLAG" property="flag" jdbcType="INTEGER" /> <result column="FLAGNAME" property="flagName" jdbcType="VARCHAR" /> <result column="DSNM" property="dsnm" jdbcType="CHAR" /> <result column="UNIT_NAME" property="unitName" jdbcType="VARCHAR" /> </resultMap> <resultMap id="onHashMap_2" type="java.util.HashMap"> <result column="mName" property="mName" jdbcType="VARCHAR" /> <result column="mContact" property="mContact" jdbcType="VARCHAR" /> <result column="mEmail" property="mEmail" jdbcType="VARCHAR" /> <result column="m2Name" property="m2Name" jdbcType="VARCHAR" /> <result column="m2Contact" property="m2Contact" jdbcType="VARCHAR" /> <result column="m2Email" property="m2Email" jdbcType="VARCHAR" /> <result column="aName" property="aName" jdbcType="VARCHAR" /> <result column="aContact" property="aContact" jdbcType="VARCHAR" /> <result column="aEmail" property="aEmail" jdbcType="VARCHAR" /> <result column="a2Name" property="a2Name" jdbcType="VARCHAR" /> <result column="a2Contact" property="a2Contact" jdbcType="VARCHAR" /> <result column="a2Email" property="a2Email" jdbcType="VARCHAR" /> <result column="eName" property="eName" jdbcType="VARCHAR" /> <result column="eContact" property="eContact" jdbcType="VARCHAR" /> <result column="eEmail" property="eEmail" jdbcType="VARCHAR" /> <result column="e2Name" property="e2Name" jdbcType="VARCHAR" /> <result column="e2Contact" property="e2Contact" jdbcType="VARCHAR" /> <result column="e2Email" property="e2Email" jdbcType="VARCHAR" /> <result column="lName" property="lName" jdbcType="VARCHAR" /> <result column="lContact" property="lContact" jdbcType="VARCHAR" /> <result column="lEmail" property="lEmail" jdbcType="VARCHAR" /> <result column="l2Name" property="l2Name" jdbcType="VARCHAR" /> <result column="l2Contact" property="l2Contact" jdbcType="VARCHAR" /> <result column="l2Email" property="l2Email" jdbcType="VARCHAR" /> <result column="WEEK_DAY" property="weekDay" jdbcType="CHAR" /> <result column="FLAG" property="flag" jdbcType="INTEGER" /> <result column="DAY0" property="day0" jdbcType="CHAR" /> <result column="DAY" property="day" jdbcType="CHAR" /> <result column="MORNING_PEOPLE_NAMES" property="morningPeopleNames" jdbcType="VARCHAR" /> <result column="AFTERNOON_PEOPLE_NAMES" property="afternoonPeopleNames" jdbcType="VARCHAR" /> <result column="EVENING_PEOPLE_NAMES" property="eveningPeopleNames" jdbcType="VARCHAR" /> <result column="LEADER_NAMES" property="leaderNames" jdbcType="VARCHAR" /> </resultMap> <resultMap id="onHashMap_3" type="java.util.HashMap"> <result column="RN" property="rn" jdbcType="INTEGER" /> <result column="WEEK_DAY" property="weekDay" jdbcType="CHAR" /> <result column="FLAG" property="flag" jdbcType="INTEGER" /> <result column="FLAG" property="flagName" jdbcType="VARCHAR" /> <result column="DAY0" property="day0" jdbcType="CHAR" /> <result column="DAY" property="day" jdbcType="CHAR" /> <result column="MORNING_PEOPLE_NAMES" property="morningPeopleNames" jdbcType="VARCHAR" /> <result column="AFTERNOON_PEOPLE_NAMES" property="afternoonPeopleNames" jdbcType="VARCHAR" /> <result column="EVENING_PEOPLE_NAMES" property="eveningPeopleNames" jdbcType="VARCHAR" /> <result column="LEADER_NAMES" property="leaderNames" jdbcType="VARCHAR" /> </resultMap> <resultMap id="onHashMap_4" type="java.util.HashMap"> <result column="PERSON_NAME" property="personName" jdbcType="VARCHAR" /> <result column="DAY0" property="day0" jdbcType="CHAR" /> <result column="WEEK_DAY" property="weekDay" jdbcType="CHAR" /> <result column="MORNING_PEOPLE_IDS" property="morningPeopleIds" jdbcType="VARCHAR" /> <result column="MORNING_PEOPLE_NAMES" property="morningPeopleNames" jdbcType="VARCHAR" /> <result column="AFTERNOON_PEOPLE_IDS" property="afternoonPeopleIds" jdbcType="VARCHAR" /> <result column="AFTERNOON_PEOPLE_NAMES" property="afternoonPeopleNames" jdbcType="VARCHAR" /> <result column="EVENING_PEOPLE_IDS" property="eveningPeopleIds" jdbcType="VARCHAR" /> <result column="EVENING_PEOPLE_NAMES" property="eveningPeopleNames" jdbcType="VARCHAR" /> <result column="LEADER_IDS" property="leaderIds" jdbcType="VARCHAR" /> <result column="LEADER_NAMES" property="leaderNames" jdbcType="VARCHAR" /> <result column="flagName" property="flagName" jdbcType="VARCHAR" /> </resultMap> <resultMap id="onHashMap_5" type="java.util.HashMap"> <result column="mName" property="mName" jdbcType="VARCHAR" /> <result column="mContact" property="mContact" jdbcType="VARCHAR" /> <result column="mEmail" property="mEmail" jdbcType="VARCHAR" /> <result column="m2Name" property="m2Name" jdbcType="VARCHAR" /> <result column="m2Contact" property="m2Contact" jdbcType="VARCHAR" /> <result column="m2Email" property="m2Email" jdbcType="VARCHAR" /> <result column="aName" property="aName" jdbcType="VARCHAR" /> <result column="aContact" property="aContact" jdbcType="VARCHAR" /> <result column="aEmail" property="aEmail" jdbcType="VARCHAR" /> <result column="a2Name" property="a2Name" jdbcType="VARCHAR" /> <result column="a2Contact" property="a2Contact" jdbcType="VARCHAR" /> <result column="a2Email" property="a2Email" jdbcType="VARCHAR" /> <result column="eName" property="eName" jdbcType="VARCHAR" /> <result column="eContact" property="eContact" jdbcType="VARCHAR" /> <result column="eEmail" property="eEmail" jdbcType="VARCHAR" /> <result column="e2Name" property="e2Name" jdbcType="VARCHAR" /> <result column="e2Contact" property="e2Contact" jdbcType="VARCHAR" /> <result column="e2Email" property="e2Email" jdbcType="VARCHAR" /> <result column="lName" property="lName" jdbcType="VARCHAR" /> <result column="lContact" property="lContact" jdbcType="VARCHAR" /> <result column="lEmail" property="lEmail" jdbcType="VARCHAR" /> <result column="l2Name" property="l2Name" jdbcType="VARCHAR" /> <result column="l2Contact" property="l2Contact" jdbcType="VARCHAR" /> <result column="l2Email" property="l2Email" jdbcType="VARCHAR" /> <result column="MORNING_PEOPLE_IDS" property="morningPeopleIds" jdbcType="VARCHAR" /> <result column="AFTERNOON_PEOPLE_IDS" property="afternoonPeopleIds" jdbcType="VARCHAR" /> <result column="EVENING_PEOPLE_IDS" property="eveningPeopleIds" jdbcType="VARCHAR" /> <result column="LEADER_IDS" property="leaderIds" jdbcType="VARCHAR" /> <result column="WEEK_DAY" property="weekDay" jdbcType="CHAR" /> <result column="FLAG" property="flag" jdbcType="INTEGER" /> <result column="DAY0" property="day0" jdbcType="CHAR" /> <result column="DAY" property="day" jdbcType="CHAR" /> <result column="MORNING_PEOPLE_NAMES" property="morningPeopleNames" jdbcType="VARCHAR" /> <result column="AFTERNOON_PEOPLE_NAMES" property="afternoonPeopleNames" jdbcType="VARCHAR" /> <result column="EVENING_PEOPLE_NAMES" property="eveningPeopleNames" jdbcType="VARCHAR" /> <result column="LEADER_NAMES" property="leaderNames" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List" > DUTY_ID, DSCD, UNIT_ID, YEAR, MONTH, DAY,DAY0, WEEK_DAY, MORNING_PEOPLE_IDS, MORNING_PEOPLE_NAMES, AFTERNOON_PEOPLE_IDS, AFTERNOON_PEOPLE_NAMES, EVENING_PEOPLE_IDS, EVENING_PEOPLE_NAMES, LEADER_IDS, LEADER_NAMES, FLAG, REMARK </sql> <!-- 按条件查找某天的值班信息 --><select id="getTodayDutyInfo" resultMap="onHashMap_5">select m1.person_name as mName,m1.contact as mContact,m1.email as mEmail,m2.person_name as m2Name,m2.contact as m2Contact,m2.email as m2Email,a1.person_name as aName,a1.contact as aContact,a1.email as aEmail,a2.person_name as a2Name,a2.contact as a2Contact,a2.email as a2Email,e1.person_name as eName,e1.contact as eContact,e1.email as eEmail,e2.person_name as e2Name,e2.contact as e2Contact,e2.email as e2Email,l1.person_name as lName,l1.contact as lContact,l1.email as lEmail,l2.person_name as l2Name,l2.contact as l2Contact,l2.email as l2Email,t.morning_people_ids,t.afternoon_people_ids,t.evening_people_ids,t.leader_ids,t.week_day,t.flag,case when t.flag=1 then '普通班' when t.flag=2 then '加强班' else '' end flagName,t.day0,t.day,t.morning_people_names,t.afternoon_people_names,t.evening_people_names,t.leader_names from(selectsubstr(a.morning_people_ids,0,instr(a.morning_people_ids,',',1,1)-1) as mId,substr(a.morning_people_ids,instr(a.morning_people_ids,',',1,1)+1) as m2Id,substr(a.afternoon_people_ids,0,instr(a.afternoon_people_ids,',',1,1)-1) as aId,substr(a.afternoon_people_ids,instr(a.afternoon_people_ids,',',1,1)+1) as a2Id,substr(a.evening_people_ids,0,instr(a.evening_people_ids,',',1,1)-1) as eId,substr(a.evening_people_ids,instr(a.evening_people_ids,',',1,1)+1) as e2Id,substr(a.leader_ids,0,instr(a.leader_ids,',',1,1)-1) as lId,substr(a.leader_ids,instr(a.leader_ids,',',1,1)+1) as l2Id,a.*from tb_duty a ) t,tb_duty_person_info m1,tb_duty_person_info m2,tb_duty_person_info a1,tb_duty_person_info a2,tb_duty_person_info e1,tb_duty_person_info e2,tb_duty_person_info l1,tb_duty_person_info l2where 1=1 andnvl2(t.mId,t.mId,t.m2Id)=m1.person_id and t.m2Id=m2.person_id andnvl2(t.aId,t.aId,t.a2Id)=a1.person_id and t.a2Id=a2.person_id andnvl2(t.eId,t.eId,t.e2Id)=e1.person_id and t.e2Id=e2.person_id andnvl2(t.lId,t.lId,t.l2Id)=l1.person_id and t.l2Id=l2.person_id <if test="dscdStr!='' and dscdStr!=null" > and t.DSCD=#{dscdStr,jdbcType=CHAR}</if><if test="unitIdInt!=null" > and t.UNIT_ID=#{unitIdInt,jdbcType=INTEGER}</if><if test="day0Str!='' and day0Str!=null" > and t.DAY0=#{day0Str,jdbcType=CHAR}</if><if test="flagInt != null " >and t.FLAG=#{flagInt,jdbcType=INTEGER}</if>order by t.day0</select> <!-- 值班个人查询 --> <select id="selectPersonalDutyList" resultMap="onHashMap_4"> select b.person_name,a.day0,a.week_day,a.morning_people_ids,a.morning_people_names,a.afternoon_people_ids,a.afternoon_people_names,a.evening_people_ids,a.evening_people_names,a.leader_ids,a.leader_names,case when a.flag=1 then '普通班' when a.flag=2 then '加强班' else '' end flagName from tb_duty a,tb_duty_person_info bwhere 1=1<if test="dscd !='' and dscd != null " > and a.dscd=#{dscd,jdbcType=CHAR}</if><if test="unitId != null " > and a.unit_id=#{unitId,jdbcType=INTEGER}</if><if test="year !='' and year != null " > and a.year=#{year,jdbcType=CHAR}</if><if test="month !='' and month != null " > and a.month=#{month,jdbcType=CHAR}</if>and (substr(a.morning_people_ids,0,instr(a.morning_people_ids,',',1,1)-1)=#{personId,jdbcType=CHAR} or substr(a.morning_people_ids,instr(a.morning_people_ids,',',1,1)+1)=#{personId,jdbcType=CHAR} orsubstr(a.afternoon_people_ids,0,instr(a.afternoon_people_ids,',',1,1)-1)=#{personId,jdbcType=CHAR} or substr(a.afternoon_people_ids,instr(a.afternoon_people_ids,',',1,1)+1)=#{personId,jdbcType=CHAR} orsubstr(a.evening_people_ids,0,instr(a.evening_people_ids,',',1,1)-1)=#{personId,jdbcType=CHAR} or substr(a.evening_people_ids,instr(a.evening_people_ids,',',1,1)+1)=#{personId,jdbcType=CHAR} orsubstr(a.leader_ids,0,instr(a.leader_ids,',',1,1)-1)=#{personId,jdbcType=CHAR} or substr(a.leader_ids,instr(a.leader_ids,',',1,1)+1)=#{personId,jdbcType=CHAR}) and b.person_id=#{personId,jdbcType=CHAR}order by day0 asc </select> <!-- 前一个月的后10条值班记录 --> <select id="selectPreDutyList" resultMap="onHashMap_3">select s.* from(selectrownum rn, t.week_day,t.flag,case when t.flag=1 then '普通班' when t.flag=2 then '加强班' else '' end flagName,t.day0,t.day,t.morning_people_names,t.afternoon_people_names,t.evening_people_names,t.leader_namesfrom tb_duty twhere 1=1 <if test="dscd !='' and dscd != null " > and t.dscd=#{dscd,jdbcType=CHAR}</if><if test="unitId != null " > and t.unit_id=#{unitId,jdbcType=INTEGER}</if><if test="day0 !='' and day0 != null " > and to_char(to_date(t.day0,'yyyy-mm-dd'),'yyyy-mm')=#{day0,jdbcType=CHAR}</if><if test="flag != null " > and t.flag=#{flag,jdbcType=INTEGER}</if>order by t.duty_id) s <![CDATA[where s.rn>((]]>select count(*) from tb_duty pwhere 1=1 <if test="dscd !='' and dscd != null " > and p.dscd=#{dscd,jdbcType=CHAR}</if><if test="unitId != null " > and p.unit_id=#{unitId,jdbcType=INTEGER}</if><if test="day0 !='' and day0 != null " > and to_char(to_date(p.day0,'yyyy-mm-dd'),'yyyy-mm')=#{day0,jdbcType=CHAR}</if><if test="flag != null " > and p.flag=#{flag,jdbcType=INTEGER}</if>)-10) </select> <!-- 后一个月的前10条值班记录 --> <select id="selectNextDutyList" resultMap="onHashMap_3">select s.* from(selectrownum rn, t.week_day,t.flag,case when t.flag=1 then '普通班' when t.flag=2 then '加强班' else '' end flagName,t.day0,t.day,t.morning_people_names,t.afternoon_people_names,t.evening_people_names,t.leader_namesfrom tb_duty twhere 1=1 <if test="dscd !='' and dscd != null " > and t.dscd=#{dscd,jdbcType=CHAR}</if><if test="unitId != null " > and t.unit_id=#{unitId,jdbcType=INTEGER}</if><if test="day0 !='' and day0 != null " > and to_char(to_date(t.day0,'yyyy-mm-dd'),'yyyy-mm')=#{day0,jdbcType=CHAR}</if><if test="flag != null " > and t.flag=#{flag,jdbcType=INTEGER}</if><![CDATA[and rownum<11order by t.duty_id) s where s.rn>0 ]]> </select> <!-- 按条件查找今日值班 --> <select id="selectTodayDutyList" resultMap="onHashMap_2">select m1.person_name as mName,m1.contact as mContact,m1.email as mEmail,m2.person_name as m2Name,m2.contact as m2Contact,m2.email as m2Email,a1.person_name as aName,a1.contact as aContact,a1.email as aEmail,a2.person_name as a2Name,a2.contact as a2Contact,a2.email as a2Email,e1.person_name as eName,e1.contact as eContact,e1.email as eEmail,e2.person_name as e2Name,e2.contact as e2Contact,e2.email as e2Email,l1.person_name as lName,l1.contact as lContact,l1.email as lEmail,l2.person_name as l2Name,l2.contact as l2Contact,l2.email as l2Email,t.week_day,t.flag,case when t.flag=1 then '普通班' when t.flag=2 then '加强班' else '' end flagName,t.day0,t.day,t.morning_people_names,t.afternoon_people_names,t.evening_people_names,t.leader_names from(selectsubstr(a.morning_people_ids,0,instr(a.morning_people_ids,',',1,1)-1) as mId,substr(a.morning_people_ids,instr(a.morning_people_ids,',',1,1)+1) as m2Id,substr(a.afternoon_people_ids,0,instr(a.afternoon_people_ids,',',1,1)-1) as aId,substr(a.afternoon_people_ids,instr(a.afternoon_people_ids,',',1,1)+1) as a2Id,substr(a.evening_people_ids,0,instr(a.evening_people_ids,',',1,1)-1) as eId,substr(a.evening_people_ids,instr(a.evening_people_ids,',',1,1)+1) as e2Id,substr(a.leader_ids,0,instr(a.leader_ids,',',1,1)-1) as lId,substr(a.leader_ids,instr(a.leader_ids,',',1,1)+1) as l2Id,a.*from tb_duty a ) t,tb_duty_person_info m1,tb_duty_person_info m2,tb_duty_person_info a1,tb_duty_person_info a2,tb_duty_person_info e1,tb_duty_person_info e2,tb_duty_person_info l1,tb_duty_person_info l2where 1=1 andnvl2(t.mId,t.mId,t.m2Id)=m1.person_id and t.m2Id=m2.person_id andnvl2(t.aId,t.aId,t.a2Id)=a1.person_id and t.a2Id=a2.person_id andnvl2(t.eId,t.eId,t.e2Id)=e1.person_id and t.e2Id=e2.person_id andnvl2(t.lId,t.lId,t.l2Id)=l1.person_id and t.l2Id=l2.person_id <if test="dscd !='' and dscd != null " > and t.dscd=#{dscd,jdbcType=CHAR}</if><if test="unitId != null " > and t.unit_id=#{unitId,jdbcType=INTEGER}</if><if test="day0 !='' and day0 != null " > and to_char(to_date(t.day0,'yyyy-mm-dd'),'yyyy-mm')=#{day0,jdbcType=CHAR}</if><if test="flag != null " > and t.flag=#{flag,jdbcType=INTEGER}</if>order by t.day0 </select> <select id="selectByUnitIdAndYear" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from TB_DUTY where 1=1 <if test="unitId != null " > and unit_id=#{unitId,jdbcType=INTEGER} </if> <if test="year != null " > and year=#{year,jdbcType=INTEGER} </if> </select> <!-- 根据unitId和year查找记录集 --> <select id="selectListByConditions" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from TB_DUTY where 1=1 <if test="dscd!='' and dscd!=null" > and dscd=${dscd}</if><if test="unitId!=null" > and unit_id=${unitId}</if><if test="year!='' and year!=null" > and year=${year}</if><if test="month!='' and month!=null" > and month=${month}</if><if test="flag!=null" > and flag=${flag}</if> </select> <!-- 批量新增 --> <insert id="addMonthDutyIntoDB" parameterType="java.util.List"> insert into TB_DUTY select SEQ_TB_DUTY.nextval,A.* from( <foreach collection="list" item="item" index="index" separator="union"> SELECT #{item.dscd}, #{item.unitId},#{item.year},#{item.month},#{item.day},trim(#{item.weekDay}), #{item.morningPeopleIds}, #{item.morningPeopleNames},#{item.afternoonPeopleIds},#{item.afternoonPeopleNames},#{item.eveningPeopleIds}, #{item.eveningPeopleNames},#{item.leaderIds},#{item.leaderNames},#{item.flag},#{item.remark},#{item.day0} FROM DUAL </foreach> ) A </insert> <!-- 按条件统计月度排班 --> <select id="getDutyByCondition" resultMap="onHashMap_1">select a.dscd,a.unit_id, a.year,a.month,a.flag, case when a.flag=1 then '普通班' when a.flag=2 then '加强班' else '' end flagName,ltrim(rtrim(b.dsnm)) as dsnm,c.unit_namefrom tb_duty aleft join tb_code_area_info bon a.dscd=b.dscdleft join tb_unit_info con a.unit_id=c.unit_idwhere 1=1 <if test="dscd !='' and dscd != null " > and a.dscd=#{dscd,jdbcType=CHAR}</if><if test="unitId != null " > and a.unit_id=#{unitId,jdbcType=INTEGER}</if><if test="year != null " > and a.year=#{year,jdbcType=INTEGER}</if><if test="month != null " > and a.month=#{month,jdbcType=INTEGER}</if><if test="flag != null " > and a.flag=#{flag,jdbcType=INTEGER}</if>group by a.dscd,b.dsnm,a.unit_id,c.unit_name,a.year,a.month,a.flagorder by a.year desc,to_number(a.month) desc </select> <!-- 重复保存月度排班检查 --> <select id="selectRecordExists" resultType="java.lang.Integer" >select count(*) from tb_duty where 1=1<if test="dscd!='' and dscd!=null" > and dscd=${dscd}</if><if test="unitId!=null" > and unit_id=${unitId}</if><if test="year!='' and year!=null" > and year=${year}</if><if test="month!='' and month!=null" > and month=${month}</if><if test="flag!=null" > and flag=${flag}</if> </select> <!-- 单个删除值班表 --> <delete id="delById"> delete from tb_duty where 1=1<if test="dscd !='' and dscd != null " > and dscd=#{dscd,jdbcType=CHAR}</if><if test="unitId != null " > and unit_id=#{unitId,jdbcType=INTEGER}</if><if test="year != null " > and year=#{year,jdbcType=INTEGER}</if><if test="month != null " > and month=#{month,jdbcType=INTEGER}</if> <if test="flag != null " >and flag=#{flag,jdbcType=INTEGER} </if> </delete> <!-- 批量删除值班表(多条件) --> <delete id="delMultiByIds2" parameterType="java.util.List"> delete from tb_duty A where exists ( select 1 from( <foreach collection="list" item="item" index="index" separator="union all"> select B.* from tb_duty B where 1=1 and B.dscd=${item.dscd} and B.unit_id=${item.unitId} and B.year=${item.year} and B.month=${item.month} and B.flag=${item.flag} </foreach> )S where A.duty_id=S.duty_id )</delete> <!--批量修改值班表(多条件) --> <update id="updateByMultiConditions" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="begin" close="; end;" separator=";"> update TB_DUTY <set> <if test="item.morningPeopleIds != null and item.morningPeopleIds != '' " > MORNING_PEOPLE_IDS=#{item.morningPeopleIds,jdbcType=VARCHAR}, </if> <if test="item.morningPeopleNames != null and item.morningPeopleNames != '' " > MORNING_PEOPLE_NAMES=#{item.morningPeopleNames,jdbcType=VARCHAR}, </if> <if test="item.afternoonPeopleIds != null and item.afternoonPeopleIds != '' " > AFTERNOON_PEOPLE_IDS=#{item.afternoonPeopleIds,jdbcType=VARCHAR}, </if> <if test="item.afternoonPeopleNames != null and item.afternoonPeopleNames != '' " > AFTERNOON_PEOPLE_NAMES=#{item.afternoonPeopleNames,jdbcType=VARCHAR}, </if> <if test="item.eveningPeopleIds != null and item.eveningPeopleIds != '' " > EVENING_PEOPLE_IDS=#{item.eveningPeopleIds,jdbcType=VARCHAR}, </if> <if test="item.eveningPeopleNames != null and item.eveningPeopleNames != '' " > EVENING_PEOPLE_NAMES=#{item.eveningPeopleNames,jdbcType=VARCHAR}, </if> <if test="item.leaderIds != null and item.leaderIds != '' " > LEADER_IDS=#{item.leaderIds,jdbcType=VARCHAR}, </if> <if test="item.leaderNames != null and item.leaderNames != '' " > LEADER_NAMES=#{item.leaderNames,jdbcType=VARCHAR}, </if> </set> where DUTY_ID=#{item.dutyId,jdbcType=INTEGER} </foreach></update> <!-- 以下是自动生成的sql --> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from TB_DUTY where DUTY_ID = #{dutyId,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from TB_DUTY where DUTY_ID = #{dutyId,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.sunny.model.duty.Duty" > insert into TB_DUTY (DUTY_ID, DSCD, UNIT_ID, YEAR, MONTH, DAY,DAY0, WEEK_DAY, MORNING_PEOPLE_IDS, MORNING_PEOPLE_NAMES, AFTERNOON_PEOPLE_IDS, AFTERNOON_PEOPLE_NAMES, EVENING_PEOPLE_IDS, EVENING_PEOPLE_NAMES, LEADER_IDS, LEADER_NAMES, FLAG, REMARK) values (SEQ_TB_DUTY.nextval, #{dscd,jdbcType=CHAR}, #{unitId,jdbcType=INTEGER}, #{year,jdbcType=CHAR}, #{month,jdbcType=CHAR}, #{day,jdbcType=CHAR}, #{day0,jdbcType=CHAR},#{weekDay,jdbcType=CHAR}, #{morningPeopleIds,jdbcType=VARCHAR}, #{morningPeopleNames,jdbcType=VARCHAR}, #{afternoonPeopleIds,jdbcType=VARCHAR}, #{afternoonPeopleNames,jdbcType=VARCHAR}, #{eveningPeopleIds,jdbcType=VARCHAR}, #{eveningPeopleNames,jdbcType=VARCHAR}, #{leaderIds,jdbcType=VARCHAR}, #{leaderNames,jdbcType=VARCHAR}, #{flag,jdbcType=INTEGER}, #{remark,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" parameterType="com.sunny.model.duty.Duty" > insert into TB_DUTY <trim prefix="(" suffix=")" suffixOverrides="," > <if test="dutyId != null" > DUTY_ID, </if> <if test="dscd != null" > DSCD, </if> <if test="unitId != null" > UNIT_ID, </if> <if test="year != null" > YEAR, </if> <if test="month != null" > MONTH, </if> <if test="day != null" > DAY, </if> <if test="day0 != null" > DAY0, </if> <if test="weekDay != null" > WEEK_DAY, </if> <if test="morningPeopleIds != null" > MORNING_PEOPLE_IDS, </if> <if test="morningPeopleNames != null" > MORNING_PEOPLE_NAMES, </if> <if test="afternoonPeopleIds != null" > AFTERNOON_PEOPLE_IDS, </if> <if test="afternoonPeopleNames != null" > AFTERNOON_PEOPLE_NAMES, </if> <if test="eveningPeopleIds != null" > EVENING_PEOPLE_IDS, </if> <if test="eveningPeopleNames != null" > EVENING_PEOPLE_NAMES, </if> <if test="leaderIds != null" > LEADER_IDS, </if> <if test="leaderNames != null" > LEADER_NAMES, </if> <if test="flag != null" > FLAG, </if> <if test="remark != null" > REMARK, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="dutyId != null" > SEQ_TB_DUTY.nextval, </if> <if test="dscd != null" > #{dscd,jdbcType=CHAR}, </if> <if test="unitId != null" > #{unitId,jdbcType=INTEGER}, </if> <if test="year != null" > #{year,jdbcType=CHAR}, </if> <if test="month != null" > #{month,jdbcType=CHAR}, </if> <if test="day != null" > #{day,jdbcType=CHAR}, </if> <if test="day0 != null" > #{day0,jdbcType=CHAR}, </if> <if test="weekDay != null" > #{weekDay,jdbcType=CHAR}, </if> <if test="morningPeopleIds != null" > #{morningPeopleIds,jdbcType=VARCHAR}, </if> <if test="morningPeopleNames != null" > #{morningPeopleNames,jdbcType=VARCHAR}, </if> <if test="afternoonPeopleIds != null" > #{afternoonPeopleIds,jdbcType=VARCHAR}, </if> <if test="afternoonPeopleNames != null" > #{afternoonPeopleNames,jdbcType=VARCHAR}, </if> <if test="eveningPeopleIds != null" > #{eveningPeopleIds,jdbcType=VARCHAR}, </if> <if test="eveningPeopleNames != null" > #{eveningPeopleNames,jdbcType=VARCHAR}, </if> <if test="leaderIds != null" > #{leaderIds,jdbcType=VARCHAR}, </if> <if test="leaderNames != null" > #{leaderNames,jdbcType=VARCHAR}, </if> <if test="flag != null" > #{flag,jdbcType=INTEGER}, </if> <if test="remark != null" > #{remark,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.sunny.model.duty.Duty" > update TB_DUTY <set > <if test="dscd != null" > DSCD = #{dscd,jdbcType=CHAR}, </if> <if test="unitId != null" > UNIT_ID = #{unitId,jdbcType=INTEGER}, </if> <if test="year != null" > YEAR = #{year,jdbcType=CHAR}, </if> <if test="month != null" > MONTH = #{month,jdbcType=CHAR}, </if> <if test="day != null" > DAY = #{day,jdbcType=CHAR}, </if> <if test="day0 != null" > DAY0 = #{day0,jdbcType=CHAR}, </if> <if test="weekDay != null" > WEEK_DAY = #{weekDay,jdbcType=CHAR}, </if> <if test="morningPeopleIds != null" > MORNING_PEOPLE_IDS = #{morningPeopleIds,jdbcType=VARCHAR}, </if> <if test="morningPeopleNames != null" > MORNING_PEOPLE_NAMES = #{morningPeopleNames,jdbcType=VARCHAR}, </if> <if test="afternoonPeopleIds != null" > AFTERNOON_PEOPLE_IDS = #{afternoonPeopleIds,jdbcType=VARCHAR}, </if> <if test="afternoonPeopleNames != null" > AFTERNOON_PEOPLE_NAMES = #{afternoonPeopleNames,jdbcType=VARCHAR}, </if> <if test="eveningPeopleIds != null" > EVENING_PEOPLE_IDS = #{eveningPeopleIds,jdbcType=VARCHAR}, </if> <if test="eveningPeopleNames != null" > EVENING_PEOPLE_NAMES = #{eveningPeopleNames,jdbcType=VARCHAR}, </if> <if test="leaderIds != null" > LEADER_IDS = #{leaderIds,jdbcType=VARCHAR}, </if> <if test="leaderNames != null" > LEADER_NAMES = #{leaderNames,jdbcType=VARCHAR}, </if> <if test="flag != null" > FLAG = #{flag,jdbcType=INTEGER}, </if> <if test="remark != null" > REMARK = #{remark,jdbcType=VARCHAR}, </if> </set> where DUTY_ID = #{dutyId,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.sunny.model.duty.Duty" > update TB_DUTY set DSCD = #{dscd,jdbcType=CHAR}, UNIT_ID = #{unitId,jdbcType=INTEGER}, YEAR = #{year,jdbcType=CHAR}, MONTH = #{month,jdbcType=CHAR}, DAY = #{day,jdbcType=CHAR}, DAY0 = #{day0,jdbcType=CHAR}, WEEK_DAY = #{weekDay,jdbcType=CHAR}, MORNING_PEOPLE_IDS = #{morningPeopleIds,jdbcType=VARCHAR}, MORNING_PEOPLE_NAMES = #{morningPeopleNames,jdbcType=VARCHAR}, AFTERNOON_PEOPLE_IDS = #{afternoonPeopleIds,jdbcType=VARCHAR}, AFTERNOON_PEOPLE_NAMES = #{afternoonPeopleNames,jdbcType=VARCHAR}, EVENING_PEOPLE_IDS = #{eveningPeopleIds,jdbcType=VARCHAR}, EVENING_PEOPLE_NAMES = #{eveningPeopleNames,jdbcType=VARCHAR}, LEADER_IDS = #{leaderIds,jdbcType=VARCHAR}, LEADER_NAMES = #{leaderNames,jdbcType=VARCHAR}, FLAG = #{flag,jdbcType=INTEGER}, REMARK = #{remark,jdbcType=VARCHAR} where DUTY_ID = #{dutyId,jdbcType=INTEGER} </update></mapper>Mapper:
package com.sunny.dao.duty;import java.util.List;import java.util.Map;import org.apache.ibatis.annotations.Param;import com.sunny.model.duty.Duty;public interface DutyMapper { int deleteByPrimaryKey(@Param(value="dutyId")Integer dutyId); int insert(Duty record); int insertSelective(Duty record); Duty selectByPrimaryKey(@Param(value="dutyId")Integer dutyId); int updateByPrimaryKeySelective(Duty record); int updateByPrimaryKey(Duty record); //以下是自定义的 // List<Duty> selectByUnitIdAndYear(@Param(value="unitId")Integer unitId,@Param(value="year")Integer year); // List<Duty> selectListByConditions(@Param(value="dscd")String dscd,@Param(value="unitId")Integer unitId, @Param(value="year")String year,@Param(value="month")String month,@Param(value="flag")Integer flag); // int addMonthDutyIntoDB(@Param(value="list")List<Duty> list); // List<Map<String, Object>> getDutyByCondition(@Param(value="dscd")String dscd,@Param(value="unitId")Integer unitId, @Param(value="year")Integer year,@Param(value="month")Integer month,@Param(value="flag")Integer flag); // int delById(@Param(value="dscd")String dscd,@Param(value="unitId")Integer unitId, @Param(value="year")String year,@Param(value="month")String month,@Param(value="flag")Integer flag); // int delMultiByIds2(List<Duty> list); // int selectRecordExists(@Param(value="dscd")String dscd,@Param(value="unitId")Integer unitId, @Param(value="year")String year,@Param(value="month")String month,@Param(value="flag")Integer flag); // int updateByMultiConditions(@Param(value="list")List<Duty> list); // List<Map<String, Object>> selectTodayDutyList(@Param(value="dscd")String dscd,@Param(value="unitId")Integer unitId, @Param(value="day0")String day0,@Param(value="flag")Integer flag); // List<Map<String, Object>> getTodayDutyInfo(@Param(value="dscdStr")String dscd,@Param(value="unitIdInt")Integer unitId, @Param(value="day0Str")String day0,@Param(value="flagInt")Integer flag); // List<Map<String, Object>> selectPreDutyList(@Param(value="dscd")String dscd,@Param(value="unitId")Integer unitId, @Param(value="day0")String day0,@Param(value="flag")Integer flag); // List<Map<String, Object>> selectNextDutyList(@Param(value="dscd")String dscd,@Param(value="unitId")Integer unitId, @Param(value="day0")String day0,@Param(value="flag")Integer flag); // List<Map<String, Object>> selectPersonalDutyList(@Param(value="dscd")String dscd,@Param(value="unitId")Integer unitId, @Param(value="year")String year,@Param(value="month")String month,@Param(value="personId")String personId); } 0 0
- DutyMapper.xml备忘
- XML HTTP备忘
- web.xml顺序备忘
- XML DTD备忘
- XML Scheme备忘
- Xml Xsl 备忘
- Log4j.xml备忘
- Dom解析xml<备忘>
- openfire.xml文件备忘
- tibco "Render XML" 使用备忘
- Delphi XE2 XML编程备忘
- XML和XPath使用方法备忘
- SSH Maven pom.xml 备忘
- SSH Maven pom.xml 备忘
- DOM解析XML简单示例(备忘)
- web.xml的一份配置(备忘)
- ant的build.xml文件详解备忘
- kvm虚拟机 xml文件配置（备忘）
- python-如何根据用户选择实现不同函数功能
- soj 4558 Coupon
- AngularJS 学习示例
- angularjs ul li radio span ng-class ng-checked demo
- PyTorch中文文档
- DutyMapper.xml备忘
- angularjs select multiple="multiple" ng-repeat
- 链表通讯录demo
- java socket通信编程
- socket通信例子_服务端写客户端读多线程写法
- 简单的MyBatis使用DEMO。
- Spring AOP
- Boost库编译后命名方式
- HDU4726
