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>