Mybatis集合映射 有重名怎么办

<?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接收,就用实体类简单接收就好了。简单解决。很奇葩),那么竟然要报错。。
在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值