<?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.base.modules.visitor.dao.VisitorRecordInfoDao">
<!--有用的Mapper -->
<resultMap type="com.base.modules.visitor.entity.VisitorRecordInfoEntity" id="visitorRecordInfoMap">
<result property="id" column="id"/>
<result property="visitorRecordNo" column="visitor_record_no"/>
<result property="visitorType" column="visitor_type"/>
<result property="addressId" column="address_id"/>
<result property="reasonCode" column="reason_code"/>
<result property="hasFollower" column="has_follower"/>
<result property="hasCar" column="has_car"/>
<result property="applicantJobNum" column="applicant_job_num"/>
<result property="applicantName" column="applicant_name"/>
<result property="applicantPhone" column="applicant_phone"/>
<result property="receiverJobNum" column="receiver_job_num"/>
<result property="assginee" column="assginee"/>
<result property="examineCode" column="examine_code"/>
<result property="examineValue" column="examine_value"/>
<result property="startTime" column="start_time"/>
<result property="endTime" column="end_time"/>
<result property="inviteMethod" column="invite_method"/>
<result property="remarks" column="remarks"/>
<result property="status" column="status"/>
<result property="revision" column="revision"/>
<result property="createdBy" column="created_by"/>
<result property="createdTime" column="created_time"/>
<result property="updatedBy" column="updated_by"/>
<result property="updatedTime" column="updated_time"/>
<result property="todayNum" column="today_num"/>
<collection property="userList" ofType="com.base.modules.visitor.entity.VisitorUserEntity">
<result property="id" column="id"/>
<result property="jobNum" column="job_num"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="dept" column="dept"/>
<result property="company" column="company"/>
<result property="phone" column="phone"/>
<result property="wxId" column="wx_id"/>
<result property="cardCount" column="card_count"/>
<result property="carNo" column="car_no"/>
<result property="qrCode" column="qr_code"/>
<result property="revision" column="revision"/>
<result property="createdBy" column="created_by"/>
<result property="createdTime" column="created_time"/>
<result property="updatedBy" column="updated_by"/>
<result property="updatedTime" column="updated_time"/>
</collection>
</resultMap>
<select id="getMaxSerialNum" resultType="Integer" >
select IFNULL(max(today_num),0) from visitor_record_info where DATE_FORMAT(created_time, '%Y-%m-%d') =CURDATE()
</select>
<select id="selectMyStartList" parameterType="map" resultMap="visitorRecordInfoMap" >
SELECT
i.visitor_record_no AS visitorRecordNo,
i.visitor_type AS visitorType,
ddd.`name` AS visitorTypeName,
i.reason_code AS reasonCode,
dd.`name` AS reasonCodeName,
i.examine_code AS examineCode,
d.`name` AS examineCodeName,
i.applicant_job_num applicantJobNum,
i.applicant_name applicantName,
i.applicant_phone applicantPhone,
i.receiver_job_num receiverJobNum,
e.name_zh receiverName,
e.mobile receiverphone,
i.assginee AS assginee,
i.address_id addressId,
oc.address addressName,
i.start_time startTime,
i.end_time endTime,
rurr.job_num AS mainUserJobNum,
uu.`name` AS mainUserName,
rur.id AS userList.vrurId,
rur.visitor_record_no AS userList.visitorRecordNo,
rur.job_num AS userList.jobNum,
rur.visitor_status AS userList.visitorStatus,
rur.is_follower AS userList.isFollower,
rur.parent_job_num AS userList.parentJobNum,
rur.auth_job_num AS userList.assgineeJobNum,
rur.type AS userList.visitorType,
rur. STATUS AS userList.status,
u.`name` AS userList.name,
u.company AS userList.company,
u.job_num AS userList.jobNum,
u.phone AS userList.phone,
u.wx_id AS userList.wxId,
u.car_no AS userList.carNo,
f.photo_url AS userList.photoUrl,
rur.is_sign_in AS userList.isSignIn
FROM
visitor_record_info i
LEFT JOIN visitor_dist ddd ON ddd. CODE = i.visitor_type
LEFT JOIN visitor_dist dd ON dd. CODE = i.reason_code
LEFT JOIN visitor_dist d ON d. CODE = i.examine_code
LEFT JOIN oa_company oc ON oc.id = i.address_id
LEFT JOIN ehr_staff e ON e.hr_id = i.receiver_job_num
LEFT JOIN visitor_record_user_relation rurr ON i.visitor_record_no = rurr.visitor_record_no
AND (
rurr.parent_job_num IS NULL
OR rurr.parent_job_num = ''
)
LEFT JOIN visitor_record_user_relation rur ON i.visitor_record_no = rur.visitor_record_no
INNER JOIN visitor_user u ON u.job_num = rur.job_num
INNER JOIN visitor_face f ON f.job_num = u.job_num
INNER JOIN visitor_user uu ON uu.job_num = rurr.job_num
WHERE 1=1
and i.applicant_job_num = #{jobNum}
<if test="visitorName != null and visitorName != ''">
AND o.visitorName = #{visitorName}
</if>
<if test="reasonName != null and reasonName != ''">
AND o.reasonName = #{reasonName}
</if>
</select>
重名解决:
本来是想在sql里写userList.isSignIn这种东西指定,但肯定报错。依稀记得是传参时#{}可以用.语法指定吧。
观察了一下Mybatis玩法,它应该无法知道我真实数据库里列是啥样,那么这个column应该是sql的结果而已。所以,我把column改了不就是另一处的起别名吗~(sql起别名的话,表属性名不好配合它改:真实的表的真实列名和实体类是肯定不好改的。那就该Mapper这里的结果的列名吧,故,这里的column可以看作“形式列名”)
当Mapper column与实体类属性名取一样,sql里别名也叫这个,那么,result用Mapper(resultMap)就相当于直接result用实体类接收(resultType)一样。此时,用resultMap纯属于脱裤子放屁——多此一举,闲得蛋疼,纯自找麻烦,纯傻批。
总结:
sql那取别名,如果结果类型是对象,那么,取的别名与对象属性名(取别名时的思路目标就是取在这)一致即可自动映射,比较舒服又快;如果结果类型用Mapper,那么,就是与Mapper的column一致(那么,取别名时的思路目标就是取在这了,算是个中转站,但也提高了适用性、复用性、扩展性,可以不动表和实体类),然后column再与对象属性名一一对应即可自动映射,就是配起来累,费眼神。
<?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.base.modules.visitor.dao.VisitorRecordInfoDao">
<!--有用的Mapper -->
<resultMap type="com.base.modules.visitor.entity.VisitorRecordInfoEntity" id="visitorRecordInfoMap">
<result property="id" column="id"/>
<result property="visitorRecordNo" column="visitor_record_no"/>
<result property="visitorType" column="visitor_type"/>
<result property="addressId" column="address_id"/>
<result property="reasonCode" column="reason_code"/>
<result property="hasFollower" column="has_follower"/>
<result property="hasCar" column="has_car"/>
<result property="applicantJobNum" column="applicant_job_num"/>
<result property="applicantName" column="applicant_name"/>
<result property="applicantPhone" column="applicant_phone"/>
<result property="receiverJobNum" column="receiver_job_num"/>
<result property="assginee" column="assginee"/>
<result property="examineCode" column="examine_code"/>
<result property="examineValue" column="examine_value"/>
<result property="startTime" column="start_time"/>
<result property="endTime" column="end_time"/>
<result property="inviteMethod" column="invite_method"/>
<result property="remarks" column="remarks"/>
<result property="status" column="status"/>
<result property="revision" column="revision"/>
<result property="createdBy" column="created_by"/>
<result property="createdTime" column="created_time"/>
<result property="updatedBy" column="updated_by"/>
<result property="updatedTime" column="updated_time"/>
<result property="todayNum" column="today_num"/>
<collection property="userList" ofType="com.base.modules.visitor.entity.VisitorUserEntity">
<result property="vrurId" column="userList_vrurId"/>
<result property="visitorRecordNo" column="userList_visitorRecordNo"/>
<result property="jobNum" column="userList_jobNum"/>
<result property="visitorStatus" column="userList_visitorStatus"/>
<result property="isFollower" column="userList_isFollower"/>
<result property="parentJobNum" column="userList_parentJobNum"/>
<result property="assgineeJobNum" column="userList_assgineeJobNum"/>
<result property="visitorType" column="userList_visitorType"/>
<result property="status" column="userList_status"/>
<result property="name" column="userList_name"/>
<result property="company" column="userList_company"/>
<result property="phone" column="userList_phone"/>
<result property="wxId" column="userList_wxId"/>
<result property="carNo" column="userList_carNo"/>
<result property="photoUrl" column="userList_photoUrl"/>
<result property="isSignIn" column="userList_isSignIn"/>
<result property="cardCount" column="card_count"/>
<result property="gender" column="gender"/>
<result property="dept" column="dept"/>
<result property="qrCode" column="qr_code"/>
<result property="revision" column="revision"/>
<result property="createdBy" column="created_by"/>
<result property="createdTime" column="created_time"/>
<result property="updatedBy" column="updated_by"/>
<result property="updatedTime" column="updated_time"/>
</collection>
</resultMap>
还不够,得再改(编码习惯和“入参或结果类型是个对象时”不一样了,没那么自动舒服了)-------
吐了,用Mapper就是麻烦,仔细对应xml,配得累,眼花,你就失去了“入参或结果类型是个对象时”自动映射的爽了。而得事无巨细的在Mapper一个一个配(结果column和对象属性名)
<!--有用的Mapper -->
<resultMap type="com.cowain.base.modules.visitor.entity.VisitorRecordInfoEntity" id="visitorRecordInfoMap">
<result property="id" column="id"/>
<result property="visitorRecordNo" column="visitor_record_no"/>
<result property="visitorTypeName" column="visitorTypeName"/>
<result property="reasonCode" column="reasonCode"/>
<result property="reasonCodeName" column="reasonCodeName"/>
<result property="examineCode" column="examineCode"/>
<result property="examineCodeName" column="examineCodeName"/>
<result property="applicantJobNum" column="applicant_job_num"/>
<result property="applicantName" column="applicant_name"/>
<result property="applicantPhone" column="applicant_phone"/>
<result property="receiverJobNum" column="receiver_job_num"/>
<result property="receiverName" column="receiverName"/>
<result property="receiverphone" column="receiverphone"/>
<result property="assginee" column="assginee"/>
<result property="visitorType" column="visitor_type"/>
<result property="addressId" column="address_id"/>
<result property="addressName" column="addressName"/>
<result property="mainUserJobNum" column="mainUserJobNum"/>
<result property="mainUserName" column="mainUserName"/>
<result property="reasonCode" column="reason_code"/>
<result property="hasFollower" column="has_follower"/>
<result property="hasCar" column="has_car"/>
<result property="examineCode" column="examine_code"/>
<result property="examineValue" column="examine_value"/>
<result property="startTime" column="start_time"/>
<result property="endTime" column="end_time"/>
<result property="inviteMethod" column="invite_method"/>
<result property="remarks" column="remarks"/>
<result property="status" column="status"/>
<result property="revision" column="revision"/>
<result property="createdBy" column="created_by"/>
<result property="createdTime" column="created_time"/>
<result property="updatedBy" column="updated_by"/>
<result property="updatedTime" column="updated_time"/>
<result property="todayNum" column="today_num"/>
<collection property="userList" ofType="com.cowain.base.modules.visitor.entity.VisitorUserEntity">
<result property="vrurId" column="userList_vrurId"/>
<result property="visitorRecordNo" column="userList_visitorRecordNo"/>
<result property="jobNum" column="userList_jobNum"/>
<result property="visitorStatus" column="userList_visitorStatus"/>
<result property="isFollower" column="userList_isFollower"/>
<result property="parentJobNum" column="userList_parentJobNum"/>
<result property="assgineeJobNum" column="userList_assgineeJobNum"/>
<result property="visitorType" column="userList_visitorType"/>
<result property="status" column="userList_status"/>
<result property="name" column="userList_name"/>
<result property="company" column="userList_company"/>
<result property="phone" column="userList_phone"/>
<result property="wxId" column="userList_wxId"/>
<result property="carNo" column="userList_carNo"/>
<result property="photoUrl" column="userList_photoUrl"/>
<result property="isSignIn" column="userList_isSignIn"/>
<result property="cardCount" column="card_count"/>
<result property="gender" column="gender"/>
<result property="dept" column="dept"/>
<result property="qrCode" column="qr_code"/>
<result property="revision" column="revision"/>
<result property="createdBy" column="created_by"/>
<result property="createdTime" column="created_time"/>
<result property="updatedBy" column="updated_by"/>
<result property="updatedTime" column="updated_time"/>
</collection>
</resultMap>
从结果来讲,Mybatis总体上还是很智能自动好用~
多重嵌套:依样画葫芦,即可
但Mybatis < collection>(xml配置时
)有一个bug
:
这个photoUrls其实是userList每一个元素(user)的属性,而且映射结果(Mybatis把查询结果是根据集合主键userList_id去聚合的)确实是映射到了每一个用户的照片集上。但是,在配嵌套< collection>时(第三层),我只给user对象配photoUrls属性,竟然冒红,而是我得给订单配photoUrls(虽然映射完了这里是null),它竟然找这个,那我嵌套写法(< collection>套< collection>)宛如未嵌(平行)似的,这个逻辑上面属实bug了( Mybatis 写时不智能(结果映射是智能的,据说根据的是子属性的主键id,然而我第三层嵌套时又没配照片主键id column…-_-||不懂),它只对着第一层,即使它被我嵌套了)。
其他(三层以上嵌套)做法:
((Me:整麻烦了,java基本类型和String没property就不写就行了))mybatis 对象 List List属性 映射
ResultMap collection多层嵌套使用
mybatis collection多层嵌套 (三层嵌套)
Mapper如果配了三层嵌套collection但结果集里却没有第三层会有毒【感觉跟那bug的味道差不多,你嵌套了,但是Mybatis它是以第一层对象的属性去看待它(第三层集合映射)的,去找它的,而且可能是基本数据类型或string,有些小坑吧】(所有,非树状结果的话却有三层以上嵌套且存在嵌套层是基本数据类型或string为元素的集合映射,接收的结果Type慎用Mapper!):Mapper嵌套写了,要当心。如果查的sql返回不存在这个第三层嵌套的字段(解决:这是就不要用Mapper接收,就用实体类简单接收就好了。简单解决。很奇葩),那么竟然要报错。。