`
Franciswmf
  • 浏览: 796860 次
  • 性别: Icon_minigender_1
  • 来自: 上海
文章分类
社区版块
存档分类
最新评论

Mybatis配置SQL的Mapper.xml

 
阅读更多
引用参考：
--MyBatis中jdbcType和javaType的映射关系
https://www.cnblogs.com/yucongblog/p/7388648.html
https://www.cnblogs.com/hapday/p/5734210.html
--Mybatis中的trim标签 介绍
https://blog.csdn.net/u011118321/article/details/68946027
--设置mybatis打印sql日志
https://blog.csdn.net/gao36951/article/details/53641432
--mapper.xml里面choose when otherwis一直执行otherwise解决
https://www.cnblogs.com/xu-xiang/p/5883580.html

--mybatis insert之后返回主键id
https://www.cnblogs.com/zhuzhen/p/6894995.html 
<insert id="addStudent" parameterType="com.test.StudentEntity" useGeneratedKeys="true" keyProperty="id">  
...
</insert>
代码中：
studentMapper.addStudent(entity);
long stuId=entity.getId();

--choose when otherwise
http://www.cnblogs.com/caoyc/p/5574948.html


xml: 
<?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(
	select
	substr(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 l2
	where 1=1 
	and
	nvl2(t.mId,t.mId,t.m2Id)=m1.person_id 
	and t.m2Id=m2.person_id 
	and
	nvl2(t.aId,t.aId,t.a2Id)=a1.person_id 
	and t.a2Id=a2.person_id 
	and
	nvl2(t.eId,t.eId,t.e2Id)=e1.person_id 
	and t.e2Id=e2.person_id 
	and
	nvl2(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 b
where 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} or
substr(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} or
substr(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} or
substr(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(
select
rownum 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_names
from tb_duty t
where 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 p
where 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(
select
rownum 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_names
from tb_duty t
where 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<11			
order 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(
select
substr(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 l2
where 1=1 
and
nvl2(t.mId,t.mId,t.m2Id)=m1.person_id 
and t.m2Id=m2.person_id 
and
nvl2(t.aId,t.aId,t.a2Id)=a1.person_id 
and t.a2Id=a2.person_id 
and
nvl2(t.eId,t.eId,t.e2Id)=e1.person_id 
and t.e2Id=e2.person_id 
and
nvl2(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_name
			from tb_duty a
			left join tb_code_area_info b
			on a.dscd=b.dscd
			left join tb_unit_info c
			on a.unit_id=c.unit_id
			where 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.flag
			order 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);
    
}


创建、释放、查找、提交、撤销登记、关闭 sqlsession

  • 大小: 48.3 KB
分享到：
| angularjs ul li radio span ng-class ng-c ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    springboot mybatis mapper.xml 配置

    在Spring Boot集成MyBatis的过程中，`mapper.xml`配置文件起着至关重要的作用。它用于定义SQL语句，实现数据库的CRUD操作。本示例将深入探讨如何配置`mapper.xml`，并提供新增、修改、删除、查询及分页查询的实践...

    用java程序生成mybatis的mapper.xml和mapper.java文件

    3. **配置Mybatis**：在项目的Mybatis配置文件（mybatis-config.xml）中，需要引入刚刚生成的mapper.xml文件，使得Mybatis能够找到并解析它们。 4. **编写mapper接口**：虽然逆向工具会生成基础的mapper接口和实现...

    spring boot + mybatis +mapper.xml 项目

    本项目利用Spring Boot的自动配置特性，配合MyBatis作为持久层框架，以及Mapper.xml文件进行SQL映射，实现了数据访问的便捷性。下面将详细阐述这个项目中的关键知识点。 1. **Spring Boot**: Spring Boot是Spring...

    mybatis自动生成mapper.xml文件

    MyBatis会根据Mapper.xml中的SQL语句动态生成SQL并执行。 总的来说，SpringBoot整合MyBatis并自动生成Mapper.xml文件，大大提高了开发效率，降低了手动编写SQL和映射代码的工作量。同时，通过MBG的定制化功能，可以...

    mybatis联表查询mapper.xml

    总结来说，"mybatis联表查询mapper.xml"涉及到的主要知识点有：MyBatis的Mapper接口和XML配置，一对多关联查询的实现，以及实体类和DAO层的设计。理解并熟练掌握这些内容，对于进行复杂的数据库操作和优化数据访问...

    mybatis-3-config.dtd mybatis-3-mapper.dtd

    `mapper.xml`文件是MyBatis中定义SQL语句的地方，它可以包含增删查改的各种SQL语句，以及结果映射。在每个`mapper.xml`文件中，你可以声明`select`、`insert`、`update`、`delete`元素，每个元素对应一个SQL操作。...

    mybatis-3-mapper.rar

    MyBatis的XML映射文件是配置Mapper接口方法与SQL语句关联的关键部分。每个XML映射文件通常对应一个Mapper接口，其中包含`select`, `insert`, `update`, `delete`等元素，这些元素定义了对应的SQL操作。例如： ```...

    自动生成Mybatis的 model mapper 和mapper.xml文件

    总结来说，Mybatis Generator是提高开发效率的重要工具，通过合理配置`generatorConfig.xml`文件，可以自动化地生成Mybatis所需的Model、Mapper接口和mapper.xml文件。在SSM项目中，这种自动化代码生成极大地降低了...

    mybatis自动生成Mapper.xml,entity,dao

    接下来是`Mapper.xml`，这是MyBatis的核心组件之一，用于存放SQL语句和结果映射。MBG可以自动生成这些映射文件，包括插入、更新、删除和查询等各种操作，开发者只需根据需求进行适当修改。每个Mapper.xml文件通常与...

    mybatis Mapper.xml中传参多选 字符串形式逗号分隔 AND中拼接OR.rar

    Mapper.xml文件是Mybatis的核心组件，它包含了数据库操作的SQL语句和映射规则。 在处理字符串形式的多选参数时，我们通常会先在服务层将这些参数转换为Java集合，如List或Set。例如，如果用户选择的标签以逗号分隔...

    mybatis-3-mapper.dtd文件下载

    在 Mybatis 中，`mybatis-3-mapper.dtd` 文件扮演着至关重要的角色，它是 Mybatis 映射器接口的定义文件，用于验证 XML 映射文件的语法正确性。 `mybatis-3-mapper.dtd` 文件是基于 DTD（Document Type Definition...

    idea工具中直接从mapper.Java文件中跳转到mapper.xml文件的插件,挺不错的

    Mapper接口是MyBatis的核心组成部分，它定义了数据库操作的方法，而Mapper XML文件则包含了这些方法对应的SQL语句和结果映射。在传统的开发流程中，当需要查看或修改SQL时，开发者需要手动在众多的XML文件中寻找对应...

    mybatis-3-mapper.dtd.zip

    你可以将这个压缩包中的`mybatis-3-mapper.dtd`文件复制到本地，并在MyBatis的配置文件中指定本地DTD文件的位置，这样即使没有网络，MyBatis也能正确解析XML映射文件。 为了做到这一点，你需要在MyBatis的配置文件...

    mybatis修改了mapper xml不用重启tomcat，热发布热更新.zip_mybatis的xml模糊查询

    mybatis修改了mapper xml不用重启tomcat，热发布热更新.zip

Magicbox
Global site tag (gtag.js) - Google Analytics