原始目标：
配置好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>/**
* 项目管理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;
}
}<?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/
转载于:https://my.oschina.net/xiaohelong/blog/602167