Mybitas XML映射文件案例备份

Mybitas XML映射文件标签含义详解：

http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html

用车日报的xml文件案例备份：

/wxapp/src/main/resources/mybatis1/cms/DriverInfoMapper.xml

    <!-- 通过ID获取数据 -->
    <select id="findById" parameterType="pd" resultType="pd">
        select 
        <include refid="Field"></include>
        from 
        <include refid="tableName"></include> 
        where 
            DRIVERINFO_ID = #{DRIVERINFO_ID}
    </select>

注意：

返回格式

xml文件返回数据格式 resultType="pd"，单条数据返回PageData，多条数据返回List<PageData>，

举例：一条数据{id:9527, name:'唐伯虎'}，多条数据[{id:9527, name:'唐伯虎'}, {id:9528, name:'唐伯虎2'}]，

传入参数格式

传入参数只有一个值，

pd.put('DRIVERINFO_ID', '201601220A')，xml文件设置 parameterType="pd"，取数据#{DRIVERINFO_ID}，

传入参数是多条数据，

DriverInfoService层：

    public void deleteAll(String[] ArrayDATA_IDS) throws Exception {
        dao.delete("DriverInfoMapper.deleteAll", ArrayDATA_IDS);
    }

DriverInfoMapper.xml层：

<!-- 批量删除CMS_DRIVERINFO(司机信息表)中信息 -->
<delete id="deleteAll" parameterType="String">
	delete from
	<include refid="tableName"></include>
	where 
		DRIVERINFO_ID in
	<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
                #{item}
	</foreach>
</delete>

传入参数为一条String，取数据如下：

Service层：

	public String findByUserId(String userId) throws Exception {
		return (String) dao.findForObject("BirthdayBlessingAllMapper.findByUserId", userId);
	}

xml层：

	<!-- 根据用户ID查找姓名 -->
	<select id="getSysDate" parameterType="String" resultType="string">
		SELECT NAME FROM OA_STAFF WHERE STAFF_ID = #{userId}
	</select>

 

DriverInfoMapper.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="DriverInfoMapper">
	
	<!--表名 -->
	<sql id="tableName">
		CMS_DRIVERINFO
	</sql>
	
	<sql id="departmentTableName">
		OA_DEPARTMENT
	</sql>
	<sql id="vehicleTableName">
		CMS_VEHICLEINFO
	</sql>
	<sql id="scheduleDriverMx">
		CMS_SCHEDULEDRIVERMX
	</sql>
	
	<!-- 字段 -->
	<sql id="Field">
		STAFF_ID,	
		DRIVER_NAME,
		DRIVEL_TEL,	
		DRIVER_SEX,	
		DEPARTMENT_ID,	
		BIRTH_DATE,	
		WORK_DATE,	
		VEHICLEINFO_ID,	
		CREATION_DATE,	
		CREATION_BY,	
		LAST_UPDATE_DATE,	
		LAST_UPDATE_BY,	
		ORGANIZATION_ID,
		DRIVERINFO_ID
	</sql>
	
	<!-- 字段值 -->
	<sql id="FieldValue">
		#{STAFF_ID},	
		#{DRIVER_NAME},	
		#{DRIVEL_TEL},	
		#{DRIVER_SEX},	
		#{DEPARTMENT_ID},	
		#{BIRTH_DATE},	
		#{WORK_DATE},	
		#{VEHICLEINFO_ID},	
		#{CREATION_DATE},	
		#{CREATION_BY},	
		#{LAST_UPDATE_DATE},	
		#{LAST_UPDATE_BY},	
		#{ORGANIZATION_ID},
		#{DRIVERINFO_ID}
	</sql>
	
	<!-- 新增-->
	<insert id="save" parameterType="pd">
		insert into 
	<include refid="tableName"></include>
		(
	<include refid="Field"></include>
		) values (
	<include refid="FieldValue"></include>
		)
	</insert>
	
	<!-- 删除CMS_DRIVERINFO中对应信息-->
	<delete id="delete" parameterType="pd">
		delete from
		<include refid="tableName"></include>
		where 
			DRIVERINFO_ID = #{DRIVERINFO_ID}
	</delete>
	
	<!-- 删除CMS_SCHEDULEDRIVERMX中对应信息-->
	<delete id="deleteScheduleDriverMx" parameterType="pd">
		delete from
		<include refid="scheduleDriverMx"></include>
		where 
			STAFF_ID = #{DRIVERINFO_ID}
	</delete>
	
	<!-- 修改 -->
	<update id="edit" parameterType="pd">
		update
		<include refid="tableName"></include>
		set 
			STAFF_ID = #{STAFF_ID},
			DRIVER_NAME = #{DRIVER_NAME},
			DRIVEL_TEL = #{DRIVEL_TEL},
			DRIVER_SEX = #{DRIVER_SEX},
			DEPARTMENT_ID = #{DEPARTMENT_ID},
			BIRTH_DATE = #{BIRTH_DATE},
			WORK_DATE = #{WORK_DATE},
			VEHICLEINFO_ID = #{VEHICLEINFO_ID},
			ORGANIZATION_ID =#{ORGANIZATION_ID},
			LAST_UPDATE_DATE = #{LAST_UPDATE_DATE},
			LAST_UPDATE_BY = #{LAST_UPDATE_BY},
		DRIVERINFO_ID = DRIVERINFO_ID
		where 
		DRIVERINFO_ID = #{DRIVERINFO_ID}
	</update>
	
	<!-- 通过ID获取数据 -->
	<select id="findById" parameterType="pd" resultType="pd">
		select 
		<include refid="Field"></include>
		from 
		<include refid="tableName"></include> 
		where 
			DRIVERINFO_ID = #{DRIVERINFO_ID}
	</select>
	
	<!-- 列表 -->
	<select id="datalistPage" parameterType="page" resultType="pd">
		select
		dr.DRIVERINFO_ID,
		dr.STAFF_ID,	
		dr.DRIVER_NAME,	
		dr.DRIVEL_TEL,
		dr.DRIVER_SEX,	
		dr.DEPARTMENT_ID,	
		dr.BIRTH_DATE,	
		dr.WORK_DATE,
		<!-- de.DEPARTMENT_ID, -->
		de.NAME
		<!-- <include refid="Field"></include> -->
		from 
		<include refid="tableName"></include>dr,<include refid="departmentTableName"></include>de
		where dr.DEPARTMENT_ID=de.DEPARTMENT_ID
		<if test="pd.orgId!= null and pd.orgId != ''">
		and  dr.ORGANIZATION_ID =#{pd.orgId}
		</if>
		<if test="pd.keywords!= null and pd.keywords != ''"><!-- 关键词检索 -->
			and
				(
				
					dr.DRIVER_NAME LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
					 or 
					dr.DRIVEL_TEL LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%') 
					 or 
					de.NAME LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%') 
				
				)
		</if>
	</select>
	
	<!-- 列表(全部) -->
	<select id="listAll" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from 
		<include refid="tableName"></include>
	</select>
	
		<!-- 列出所有空闲的司机-->
	<select id="listAllFreeDriverInfo" parameterType="page" resultType="pd">
		select
			di.VEHICLEINFO_ID,
			di.DRIVERINFO_ID,
			vi.VEHICLE_TYPE,
			vi.VEHICLE_BRAND,
			vi.VEHICLE_COLOR,
			vi.VEHICLE_NO,
			di.STAFF_ID DSTAFF_ID,
			di.DRIVER_NAME,
			di.DRIVEL_TEL
		from
		<include refid="tableName"></include> di,	<include refid="vehicleTableName"></include> vi
		where  di.VEHICLEINFO_ID = vi.VEHICLEINFO_ID
	<!-- 	and di.STAFF_ID  in(
		select DISTINCT  DSTAFF_ID  from cms_scheduledrivermx
				where SCHEDULEMANAGEMENT_ID in(SELECT
					DISTINCT SCHEDULEMANAGEMENT_ID
				FROM
					cms_schedulemanagement
				where SCH_USER_ID=#{SCH_USER_ID})
		) -->
	</select>
	
	
			<!-- 列出所有空闲的司机-->
	<select id="listAllFreeschDriverInfo" parameterType="page" resultType="pd">
		select
			di.VEHICLEINFO_ID,
			di.DRIVERINFO_ID,
			vi.VEHICLE_TYPE,
			vi.VEHICLE_BRAND,
			vi.VEHICLE_COLOR,
			vi.VEHICLE_NO,
			di.STAFF_ID DSTAFF_ID,
			di.DRIVER_NAME,
			di.DRIVEL_TEL
		from
		<include refid="tableName"></include> di,<include refid="vehicleTableName"></include> vi
		where  di.VEHICLEINFO_ID = vi.VEHICLEINFO_ID
		and di.STAFF_ID  in(
		select DISTINCT  DSTAFF_ID  from cms_scheduledrivermx
				where SCHEDULEMANAGEMENT_ID in(SELECT
					DISTINCT SCHEDULEMANAGEMENT_ID
				FROM
					cms_schedulemanagement
				where SCH_USER_ID=#{SCH_USER_ID})
		)
	</select>
	
	
				<!-- 列出所有空闲的司机-->
	<select id="listDriverInfoBySchUserId" parameterType="page" resultType="pd">
		select
			DRIVERINFO_ID,
			STAFF_ID DSTAFF_ID,
			DRIVER_NAME,
			DRIVEL_TEL
		from
		<include refid="tableName"></include>
		where  1=1
		and STAFF_ID  in(
		select DISTINCT  DSTAFF_ID  from cms_scheduledrivermx
				where SCHEDULEMANAGEMENT_ID in(SELECT
					DISTINCT SCHEDULEMANAGEMENT_ID
				FROM
					cms_schedulemanagement
				where SCH_USER_ID=#{SCH_USER_ID})
		)
	</select>
	
	<!-- 通过ID获取数据 -->
	<select id="findFreeDriverInfoByDriverInfoId" parameterType="pd" resultType="pd">
		select 
		di.STAFF_ID,	
		di.DRIVER_NAME,
		di.DRIVEL_TEL,	
		di.DRIVER_SEX,	
		di.DEPARTMENT_ID,	
		di.BIRTH_DATE,	
		di.WORK_DATE,	
		di.VEHICLEINFO_ID,	
		di.CREATION_DATE,	
		di.CREATION_BY,	
		di.LAST_UPDATE_DATE,	
		di.LAST_UPDATE_BY,	
		di.DRIVERINFO_ID,
		vi.VEHICLE_TYPE,
			vi.VEHICLE_BRAND,
			vi.VEHICLE_COLOR,
			vi.VEHICLE_NO
		from 
		<include refid="tableName"></include> di,	<include refid="vehicleTableName"></include> vi
		 where 1=1
		 and  di.VEHICLEINFO_ID = vi.VEHICLEINFO_ID
		and  di.DRIVERINFO_ID = #{DRIVERINFO_ID} 
	</select>
	
	
	<!-- 通过ID获取数据 -->
	<select id="findFreeDriverInfoById" parameterType="pd" resultType="pd">
		select 
		di.STAFF_ID,	
		di.DRIVER_NAME,
		di.DRIVEL_TEL,	
		di.DRIVER_SEX,	
		di.DEPARTMENT_ID,	
		di.DRIVERINFO_ID
		from 
		<include refid="tableName"></include> di
		 where 1=1
		and  di.DRIVERINFO_ID = #{DRIVERINFO_ID} 
	</select>
	
	<!-- 通过ID获取数据 -->
	<select id="findDriverByStaffId" parameterType="pd" resultType="pd">
		select 
		STAFF_ID,	
		DRIVER_NAME,
		DRIVEL_TEL,	
		DRIVER_SEX,	
		DEPARTMENT_ID,	
		DRIVERINFO_ID
		from 
		<include refid="tableName"></include> 
		 where 1=1
		and  STAFF_ID = #{STAFF_ID} 
	</select>
	
	<!-- 批量删除CMS_DRIVERINFO(司机信息表)中信息 -->
	<delete id="deleteAll" parameterType="String">
		delete from
		<include refid="tableName"></include>
		where 
			DRIVERINFO_ID in
		<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
                 #{item}
		</foreach>
	</delete>
	
	<!-- 批量删除CMS_SCHEDULEDRIVERMX(司机与管理部门关联表)中信息 -->
	<delete id="deleteAllScheduleDriverMx" parameterType="String">
		delete from
		<include refid="scheduleDriverMx"></include>
		where 
			DSTAFF_ID in
		<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
                 #{item}
		</foreach>
	</delete>
</mapper>