设置Mybatis(3.2.8)实体嵌套关系(一对多,多对多)遇到的问题及经验总结记录

原创
2016/01/14 09:48
阅读数 5.7K
原始目标:
配置好mapper,使得可以在实体中表示表之间的联系(一个表外键,用另一个实体表示)

深读了mybatis 官方的文章,最后总结出一最重要的的一条,一定要好好利用官方 的autoMapping 特性,否则就得一条条写映射关系了。当然对于实体的嵌套填充, 我只做一层,再往下走就需要在程序逻辑上做一些处理

这里配置的逻辑只与表在逻辑上的连接相关,是否在数据中实现关系无关。

一、犯过的错误
        一开始我把所有相关的表的列都放进来,导致有错误不能排除,所以搞了一天加一晚上。后面我只要了几个关键的,这样语句有错误也好调试
二、正确的认识
1.
    为了有效利用automaping 特性,在取别名时除前缀外,后面的名称我们将采取与实体属性一样的名称,这样,我们在resultmap中就无需再一一对上属性
2.
    <resultMap id="psProjectPushResultMap"         type="com.thinkgem.jeesite.modules.projectschedule.entity.PsProjectPush"  autoMapping ="true">
  <id property="id"  column="id"/>
</resultMap>
    这里的column我们只需要考虑不包括前缀的
3.映射方式主要有两种
     第一种:直接取别名,用resultType返回结果,这种方式只能针对简单的,且没有List<Entity>之类的    collection; 直接取名时,对于entity属性别名一般取属性.id( a.customer_id as customer.id),这样mybatis会将这个值送给对像的ID字段.
    第二种:用resultMap返回结果,当有List<Entity>这种Collection时必须用这一种方式,以下为相关解释
  <association  property="customer"  column=" customerid"  resultMap="psCustomerResultMap" columnPrefix="customer_"/>
    property 为实体中的属性值,customerid为sql语句中的别名(一般是用一关联另一实体,在使用association这种方式下,这个别名保留与表格相同就好

4关于嵌套执行sql还是一条SQL连接多表
    嵌套执行:官方演示了此方式,也方便理解,但由于效率太差不推荐
    一条大SQL(JOIN):此方式是官方 推荐方式,映射时官方 会自自己移除重复记录,此也为推荐方式。
5.关联的主表字段取别名时不需要加前缀。
6.连接时注意连接方式,一般是以左连接为主
  • INNER JOIN: Returns all rows when there is at least one match in BOTH tables
  • LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
  • RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
  • FULL JOIN: Return all rows when there is a match in ONE of the tables
三、注意事项
官方举例在别名时推荐使用的是下划线,
1. 但在以下的别名方式下,.也是可以的。
 <sql id="psCustomerColumns">
        c.id as "customer_id",
        c.code as "customer_code",
        c.name as "customer_name"
    </sql>
2.此类别名方式下
<sql id="psProjectPushColumns">
        push.id as "push.id",
        push.projectid AS "push.project.id",
        push.userid AS "push.user.id",
        push.is_readed AS "push.isReaded"
    </sql>
也是可以的
如果下划线以外方式不能正确工作,就改用官方的下划线前缀。
三、其实应该注意的点

现在来看看这个mapper的映射配置如何编写,注意示例中的以下几点:
1、constructor  实体的构造方法
2、autoMapping  自动属性映射
3、collection  集合属性的映射
4、association  关联属性的映射

5、mapUnderscoreToCamelCase 是否开启自动驼峰命名规则,全局配置.

实例一,使用下划线:

PsProjectDao.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.thinkgem.jeesite.modules.projectschedule.dao.PsProjectDao">
 <!-- <typeAlias type="com.someapp.model.User" alias="User"/> -->
 <!-- begin result map area -->
<resultMap id="psProjectResultMap" type="com.thinkgem.jeesite.modules.projectschedule.entity.PsProject"  autoMapping ="true">
  <id property="id" column="id" />
  <!--  <result property="title" column="blog_title"/>-->
  <association property="customer"  column="customerid"  resultMap="psCustomerResultMap" columnPrefix="customer_"/>
  <association property="department" column="departmentid" resultMap="departmentResultMap" columnPrefix="department_"/>
  <association property="projectManager" column="projectManagerid" resultMap="userResultMap" columnPrefix="projectManager_"/>
  <association property="marketer" column="marketerid" resultMap="userResultMap" columnPrefix="marketer_"/>
  <collection  property="push"  column="push" ofType="PsProjectPush" resultMap="psProjectPushResultMap"  columnPrefix="push_"/>
</resultMap>
<resultMap id="userResultMap" type="com.thinkgem.jeesite.modules.sys.entity.User"  autoMapping ="true">
  <id property="id" column="id"/>
</resultMap>
<resultMap id="departmentResultMap" type="com.thinkgem.jeesite.modules.sys.entity.Office" autoMapping ="true" >
  <id property="id" column="id"/>
</resultMap>
<resultMap id="psCustomerResultMap" type="com.thinkgem.jeesite.modules.projectschedule.entity.PsCustomer"  autoMapping ="true">
  <id property="id" column="id"/>
</resultMap>
<resultMap id="psProjectPushResultMap" type="com.thinkgem.jeesite.modules.projectschedule.entity.PsProjectPush"  autoMapping ="true">
  <id property="id" column="id"/>
</resultMap>
<!-- end resultmap area -->
<!-- begin sql columns area -->
    <sql id="psProjectColumns">
        a.id AS "id",
        a.code AS "code",
        a.name AS "name",
        a.type AS "type",
        a.importance_degree AS "importanceDegree",
        a.tech_state AS "techState",
        a.customerid AS "customerid",
        a.departmentid AS "departmentid",
        a.project_managerid AS "projectManagerid",
        a.plan_starttime AS "planStarttime",
        a.plan_endtime AS "planEndtime",
        a.state AS "state",
        a.act_starttime AS "actStarttime",
        a.act_endtime AS "actEndtime",
        a.sending_time AS "sendingTime",
        a.old_plan_starttime AS "oldPlanStarttime",
        a.old_plan_endtime AS "oldPlanEndtime",
        a.delivery_time AS "deliveryTime",
        a.complete_status AS "completeStatus",
        a.marketerid AS "marketerid",
        a.create_by AS "createBy.id",
        a.create_date AS "createDate",
        a.update_by AS "updateBy.id",
        a.update_date AS "updateDate",
        a.remarks AS "remarks",
        a.del_flag AS "delFlag"
    </sql>
    <sql id="departmentColumns">
         d.id as "department_id",
         d.name as "department_name"
    </sql>
        <sql id="projectManagerColumns">
        pm.id as "projectManager_id",
        pm.name as "projectManager_name"
    </sql>
    <sql id="marketerColumns">
        m.id as "marketer_id",
        m.name as "marketer_name"
    </sql>
    <sql id="psCustomerColumns">
        c.id as "customer_id",
        c.code as "customer_code",
        c.name as "customer_name"
    </sql>
    <sql id="psProjectPushColumns">
        ps.id as "push_id",
        ps.projectid AS "push_project.id",
        ps.userid AS "push_user.id",
        ps.is_readed AS "push_isReaded"
    </sql>
    <!-- end sql columns area -->
    <sql id="psProjectJoins">
        LEFT JOIN ps_customer c ON c.id = a.customerid 
        LEFT JOIN sys_office  d ON d.id = a.departmentid 
        LEFT JOIN sys_user pm ON pm.id = a.project_managerid 
        LEFT JOIN sys_user m ON m.id = a.marketerid
        LEFT JOIN ps_project_push ps ON ps.projectid=a.id
    </sql>

    <select id="findAllList" resultMap="psProjectResultMap">
        SELECT 
            <include refid="psProjectColumns"/>,
            <include refid="departmentColumns"/>,
            <include refid="projectManagerColumns"/>,
            <include refid="marketerColumns"/>,
            <include refid="psCustomerColumns"/>,
            <include refid="psProjectPushColumns"/>
        FROM ps_project a 
        <include refid="psProjectJoins"/>
        <where>
            a.del_flag = #{DEL_FLAG_NORMAL}
        </where>        
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
            <otherwise>
                ORDER BY a.update_date DESC
            </otherwise>
        </choose>
    </select>

    <insert id="insert">
        INSERT INTO ps_project(
            id,
            code,
            name,
            type,
            importance_degree,
            tech_state,
            customerid,
            departmentid,
            managerid,
            plan_starttime,
            plan_endtime,
            state,
            act_starttime,
            act_endtime,
            sending_time,
            old_plan_starttime,
            old_plan_endtime,
            delivery_time,
            complete_status,
            marketerid,
            create_by,
            create_date,
            update_by,
            update_date,
            remarks,
            del_flag
        ) VALUES (
            #{id},
            #{code},
            #{name},
            #{type},
            #{importanceDegree},
            #{techState},
            #{customer.id},
            #{department.id},
            #{projectManager.id},
            #{planStarttime},
            #{planEndtime},
            #{state},
            #{actStarttime},
            #{actEndtime},
            #{sendingTime},
            #{oldPlanStarttime},
            #{oldPlanEndtime},
            #{deliveryTime},
            #{completeStatus},
            #{marketer.id},
            #{createBy.id},
            #{createDate},
            #{updateBy.id},
            #{updateDate},
            #{remarks},
            #{delFlag}
        )
    </insert>

    <update id="update">
        UPDATE ps_project SET     
            code = #{code},
            name = #{name},
            type = #{type},
            importance_degree = #{importanceDegree},
            tech_state = #{techState},
            customerid = #{customer.id},
            departmentid = #{department.id},
            managerid = #{projectManager.id},
            plan_starttime = #{planStarttime},
            plan_endtime = #{planEndtime},
            state = #{state},
            act_starttime = #{actStarttime},
            act_endtime = #{actEndtime},
            sending_time = #{sendingTime},
            old_plan_starttime = #{oldPlanStarttime},
            old_plan_endtime = #{oldPlanEndtime},
            delivery_time = #{deliveryTime},
            complete_status = #{completeStatus},
            marketerid = #{marketer.id},
            update_by = #{updateBy.id},
            update_date = #{updateDate},
            remarks = #{remarks}
        WHERE id = #{id}
    </update>

    <update id="delete">
        UPDATE ps_project SET 
            del_flag = #{DEL_FLAG_DELETE}
        WHERE id = #{id}
    </update>

</mapper>



实例二:使用.做为前缀

<?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.thinkgem.jeesite.modules.projectschedule.dao.PsProjectDao">
    <!-- <typeAlias type="com.someapp.model.User" alias="User"/> -->
    <!-- begin result map area -->
    <resultMap id="psProjectResultMap"
        type="com.thinkgem.jeesite.modules.projectschedule.entity.PsProject"
        autoMapping="true">
        <id property="id" column="id" />
        <!-- <result property="title" column="blog_title"/> -->
        <association property="customer" column="customerid"
            resultMap="psCustomerResultMap" columnPrefix="customer." />
        <association property="department" column="departmentid"
            resultMap="departmentResultMap" columnPrefix="department." />
        <association property="projectManager" column="projectManagerid"
            resultMap="userResultMap" columnPrefix="projectManager." />
        <association property="marketer" column="marketerid"
            resultMap="userResultMap" columnPrefix="marketer." />
        <collection property="push" column="push" ofType="PsProjectPush"
            resultMap="psProjectPushResultMap" columnPrefix="push." />
    </resultMap>
    <resultMap id="userResultMap" type="com.thinkgem.jeesite.modules.sys.entity.User"
        autoMapping="true">
        <id property="id" column="id" />
    </resultMap>
    <resultMap id="departmentResultMap"
        type="com.thinkgem.jeesite.modules.sys.entity.Office" autoMapping="true">
        <id property="id" column="id" />
    </resultMap>
    <resultMap id="psCustomerResultMap"
        type="com.thinkgem.jeesite.modules.projectschedule.entity.PsCustomer"
        autoMapping="true">
        <id property="id" column="id" />
    </resultMap>
    <resultMap id="psProjectPushResultMap"
        type="com.thinkgem.jeesite.modules.projectschedule.entity.PsProjectPush"
        autoMapping="true">
        <id property="id" column="id" />
    </resultMap>
    <!-- end resultmap area -->
    <!-- begin sql columns area -->
    <sql id="psProjectColumns">
        a.id AS "id",
        a.code AS "code",
        a.name AS "name",
        a.type AS
        "type",
        a.importance_degree AS "importanceDegree",
        a.tech_state AS
        "techState",
        a.customerid AS "customerid",
        a.departmentid AS
        "departmentid",
        a.project_managerid AS "projectManagerid",
        a.plan_starttime AS "planStarttime",
        a.plan_endtime AS "planEndtime",
        a.state AS "state",
        a.act_starttime AS "actStarttime",
        a.act_endtime AS
        "actEndtime",
        a.sending_time AS "sendingTime",
        a.old_plan_starttime AS
        "oldPlanStarttime",
        a.old_plan_endtime AS "oldPlanEndtime",
        a.delivery_time AS "deliveryTime",
        a.complete_status AS
        "completeStatus",
        a.marketerid AS "marketerid",
        a.create_by AS
        "createBy.id",
        a.create_date AS "createDate.id",
        a.update_by AS
        "updateBy.id",
        a.update_date AS "updateDate.id",
        a.remarks AS "remarks",
        a.del_flag AS "delFlag"
    </sql>
    <sql id="departmentColumns">
        department.id as "department.id",
        department.name as "department.name"
    </sql>
    <sql id="projectManagerColumns">
        projectManager.id as "projectManager.id",
        projectManager.name as "projectManager.name"
    </sql>
    <sql id="marketerColumns">
        marketer.id as "marketer.id",
        marketer.name as "marketer.name"
    </sql>
    <sql id="psCustomerColumns">
        customer.id as "customer.id",
        customer.code as
        "customer.code",
        customer.name as "customer.name"
    </sql>
    <sql id="psProjectPushColumns">
        push.id as "push.id",
        push.projectid AS "push.project.id",
        push.userid AS "push.user.id",
        push.is_readed AS "push.isReaded"
    </sql>
    <!-- end sql columns area -->
    <sql id="psProjectJoins">
        LEFT JOIN ps_customer customer ON customer.id =
        a.customerid
        LEFT JOIN sys_office department ON department.id = a.departmentid
        LEFT JOIN sys_user projectManager ON projectManager.id =
        a.project_managerid
        LEFT JOIN sys_user marketer ON marketer.id = a.marketerid
        LEFT JOIN ps_project_push push ON push.projectid=a.id
    </sql>

    <select id="findAllList" resultMap="psProjectResultMap">
        SELECT
        <include refid="psProjectColumns" />
        ,
        <include refid="departmentColumns" />
        ,
        <include refid="projectManagerColumns" />
        ,
        <include refid="marketerColumns" />
        ,
        <include refid="psCustomerColumns" />
        ,
        <include refid="psProjectPushColumns" />
        FROM ps_project a
        <include refid="psProjectJoins" />
        <where>
            a.del_flag = #{DEL_FLAG_NORMAL}
        </where>
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
            <otherwise>
                ORDER BY a.update_date DESC
            </otherwise>
        </choose>
    </select>

    <insert id="insert">
        INSERT INTO ps_project(
        id,
        code,
        name,
        type,
        importance_degree,
        tech_state,
        customerid,
        departmentid,
        managerid,
        plan_starttime,
        plan_endtime,
        state,
        act_starttime,
        act_endtime,
        sending_time,
        old_plan_starttime,
        old_plan_endtime,
        delivery_time,
        complete_status,
        marketerid,
        create_by,
        create_date,
        update_by,
        update_date,
        remarks,
        del_flag
        ) VALUES (
        #{id},
        #{code},
        #{name},
        #{type},
        #{importanceDegree},
        #{techState},
        #{customer.id},
        #{department.id},
        #{projectManager.id},
        #{planStarttime},
        #{planEndtime},
        #{state},
        #{actStarttime},
        #{actEndtime},
        #{sendingTime},
        #{oldPlanStarttime},
        #{oldPlanEndtime},
        #{deliveryTime},
        #{completeStatus},
        #{marketer.id},
        #{createBy.id},
        #{createDate},
        #{updateBy.id},
        #{updateDate},
        #{remarks},
        #{delFlag}
        )
    </insert>

    <update id="update">
        UPDATE ps_project SET
        code = #{code},
        name = #{name},
        type = #{type},
        importance_degree = #{importanceDegree},
        tech_state = #{techState},
        customerid = #{customer.id},
        departmentid = #{department.id},
        managerid = #{projectManager.id},
        plan_starttime = #{planStarttime},
        plan_endtime = #{planEndtime},
        state = #{state},
        act_starttime = #{actStarttime},
        act_endtime = #{actEndtime},
        sending_time = #{sendingTime},
        old_plan_starttime = #{oldPlanStarttime},
        old_plan_endtime = #{oldPlanEndtime},
        delivery_time = #{deliveryTime},
        complete_status = #{completeStatus},
        marketerid = #{marketer.id},
        update_by = #{updateBy.id},
        update_date = #{updateDate},
        remarks = #{remarks}
        WHERE id = #{id}
    </update>

    <update id="delete">
        UPDATE ps_project SET
        del_flag = #{DEL_FLAG_DELETE}
        WHERE id = #{id}
    </update>

</mapper>



实体文件(PsProject.java)

/**
 * 项目管理Entity
 * @author xiaohelong
 * @version 2016-01-06
 */
public class PsProject extends DataEntity<PsProject> {
	
	private static final long serialVersionUID = 1L;
	private String code;		// 项目编号
	private String name;		// 项目名称
	private String type;		// 项目类型(科研项目,还是交付)
	private String importanceDegree;		// 重要程度(一般,重要,紧急)
	private String techState;		// 技术状态(完全沿用,设计更改,全新设计)
	private PsCustomer customer;		// 客户单位
	private Office department;		// 所属部门
	private User projectManager;		// 项目经理
	private Date planStarttime;		// 计划开始时间
	private Date planEndtime;		// 计划结束时间
	private String state;		// 项目当前状态(等待开始,正常进行,交付延期,节点延期,项目暂停,项目终止,项目结束)
	private Date actStarttime;		// 实际开始时间
	private Date actEndtime;		// 实际结束时间
	private Date sendingTime;		// 项目下发时间
	private Date oldPlanStarttime;		// 计划开始时间(只读字段,初始化后不更改)
	private Date oldPlanEndtime;		// 计划结束时间(只读字段,初始化后不更改)
	private Date deliveryTime;		// 交付时间
	private String completeStatus;		// 完成情况(正常完成,延期完成,未完成)
	private User marketer;		// 市场人员ID
	private List<PsProjectPush> push; //相关领导数据表中并没有,放在这里便于表单操作,因为该字段数据送到另外一个表中了。
	public PsProject() {
		super();
	}

	public PsProject(String id){
		super(id);
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getImportanceDegree() {
		return importanceDegree;
	}

	public void setImportanceDegree(String importanceDegree) {
		this.importanceDegree = importanceDegree;
	}

	public String getTechState() {
		return techState;
	}

	public void setTechState(String techState) {
		this.techState = techState;
	}

	public PsCustomer getCustomer() {
		return customer;
	}

	public void setCustomer(PsCustomer customer) {
		this.customer = customer;
	}

	public Office getDepartment() {
		return department;
	}

	public void setDepartment(Office department) {
		this.department = department;
	}

	public User getProjectManager() {
		return projectManager;
	}

	public void setProjectManager(User projectManager) {
		this.projectManager = projectManager;
	}

	public Date getPlanStarttime() {
		return planStarttime;
	}

	public void setPlanStarttime(Date planStarttime) {
		this.planStarttime = planStarttime;
	}

	public Date getPlanEndtime() {
		return planEndtime;
	}

	public void setPlanEndtime(Date planEndtime) {
		this.planEndtime = planEndtime;
	}

	public String getState() {
		return state;
	}

	public void setState(String state) {
		this.state = state;
	}

	public Date getActStarttime() {
		return actStarttime;
	}

	public void setActStarttime(Date actStarttime) {
		this.actStarttime = actStarttime;
	}

	public Date getActEndtime() {
		return actEndtime;
	}

	public void setActEndtime(Date actEndtime) {
		this.actEndtime = actEndtime;
	}

	public Date getSendingTime() {
		return sendingTime;
	}

	public void setSendingTime(Date sendingTime) {
		this.sendingTime = sendingTime;
	}

	public Date getOldPlanStarttime() {
		return oldPlanStarttime;
	}

	public void setOldPlanStarttime(Date oldPlanStarttime) {
		this.oldPlanStarttime = oldPlanStarttime;
	}

	public Date getOldPlanEndtime() {
		return oldPlanEndtime;
	}

	public void setOldPlanEndtime(Date oldPlanEndtime) {
		this.oldPlanEndtime = oldPlanEndtime;
	}

	public Date getDeliveryTime() {
		return deliveryTime;
	}

	public void setDeliveryTime(Date deliveryTime) {
		this.deliveryTime = deliveryTime;
	}

	public String getCompleteStatus() {
		return completeStatus;
	}

	public void setCompleteStatus(String completeStatus) {
		this.completeStatus = completeStatus;
	}

	public User getMarketer() {
		return marketer;
	}

	public void setMarketer(User marketer) {
		this.marketer = marketer;
	}

	public List<PsProjectPush> getPush() {
		return push;
	}

	public void setPush(List<PsProjectPush> push) {
		this.push = push;
	}

}



另附直接用别名简单映射方式(无LIST collection)--JeeSite默认的方式,没有继续研究其List是如何实现的,因其并没有在XML中体现出来,所以个人觉得很可能是在JAVA SERVICE程进行了体现。

<?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.thinkgem.jeesite.modules.sys.dao.OfficeDao">

	<sql id="officeColumns">
		a.id,
		a.parent_id AS "parent.id",
		a.parent_ids,
		a.area_id AS "area.id",
		a.code,
		a.name,
		a.sort,
		a.type,
		a.grade,
		a.address, 
		a.zip_code, 
		a.master, 
		a.phone, 
		a.fax, 
		a.email, 
		a.remarks,
		a.create_by AS "createBy.id",
		a.create_date,
		a.update_by AS "updateBy.id",
		a.update_date,
		a.del_flag,
		a.useable AS useable,
		a.primary_person AS "primaryPerson.id",
		a.deputy_person AS "deputyPerson.id",
		p.name AS "parent.name",
		ar.name AS "area.name",
		ar.parent_ids AS "area.parentIds",
		pp.name AS "primaryPerson.name",
		dp.name AS "deputyPerson.name"
	</sql>
	
	<sql id="officeJoins">
		LEFT JOIN sys_office p ON p.id = a.parent_id
		LEFT JOIN sys_area ar ON ar.id = a.area_id
		LEFT JOIN SYS_USER pp ON pp.id = a.primary_person
		LEFT JOIN SYS_USER dp ON dp.id = a.deputy_person
    </sql>	
	<select id="findAllList" resultType="Office">
		SELECT
			<include refid="officeColumns"/>
		FROM sys_office a
		<include refid="officeJoins"/>
		WHERE a.del_flag = #{DEL_FLAG_NORMAL}
		ORDER BY a.code
	</select>
</mapper>


参考资料:

1. http://www.mybatis.org/mybatis-3/sqlmap-xml.html#Auto-mapping

2.http://leeyee.github.io/blog/2013/05/30/mybatis-association-autoMapping/


展开阅读全文
打赏
2
10 收藏
分享
加载中
更多评论
打赏
0 评论
10 收藏
2
分享
返回顶部
顶部