查询主体
@TableName("change_record")
public class ChangeRecordEntity {
@TableId(value= "ID", type = IdType.NONE)
private String id;
@TableField("ENTERPRISE_ID")
private String enterpriseId;
/**
* 变更记录子表
*/
@ApiModelProperty(value = "变更记录子表")
@TableField(exist = false)
private List<ChangeRecordItemEntity> recordItems;
/**
* 司机信息
*/
@ApiModelProperty(value = "司机信息")
@TableField(exist = false)
private StaffEntity staff;
/**
* 企业信息
*/
@ApiModelProperty(value = "企业信息")
@TableField(exist = false)
private EnterpriseEntity enterprise;
...
}
查询主体的Mapper.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="jnpf.mapper.ChangeRecordMapper">
<resultMap id="StaffChangeRecordMap" type="jnpf.entity.ChangeRecordEntity">
<id column="ID" property="id"/>
<result column="STAFF_ID" property="staffId"/>
<result column="ENTERPRISE_ID" property="enterpriseId"/>
...
<association property="recordItems"
select="jnpf.mapper.ChangeRecordItemMapper.getListByChangeRecordId"
column="ID"
/>
<association
property="enterprise"
javaType="jnpf.entity.EnterpriseEntity"
column="ENTERPRISE_ID"
select="jnpf.mapper.EnterpriseMapper.getOneById"
/>
</resultMap>
<sql id="StaffChangeRecordSql">
staffChangeRecord.ID,
staffChangeRecord.STAFF_ID,
staffChangeRecord.ENTERPRISE_ID,
...
</sql>
<select id="queryStaffPage" resultMap="StaffChangeRecordMap">
SELECT
<include refid="StaffChangeRecordSql"/>
From change_record staffChangeRecord
LEFT JOIN change_record_item changeRecordItem ON hangeRecordItem.CHANGE_RECORD_ID = staffChangeRecord.ID
<where>
staffChangeRecord.DELETE_MARK != 1
AND ...
<if test="query.staffName != null">
AND (
changeRecordItem.AFTER_VALUE like CONCAT('%', #{query.staffName}, '%')
OR changeRecordItem.BEFORE_VALUE like CONCAT('%', #{query.staffName}, '%')
) AND changeRecordItem.FIELD_NAME = 'userName'
</if>
...
</where>
GROUP BY staffChangeRecord.ID
Order By ...
</select>
</mapper>
关联对象enterprise的Mapper.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="jnpf.mapper.EnterpriseMapper">
<resultMap id="DocumentEntity" type="jnpf.entity.EnterpriseEntity">
...
</resultMap>
<sql id="EnterpriseSql">
...
</sql>
<select id="getOneById" resultMap="DocumentEntity">
SELECT
*
FROM enterprise enterprise
WHERE enterprise.ID = #{enterpriseId}
</select>
</mapper>
注:
关联查询在resultMap中使用association标签,property对应当前表中关联表所对应变量名; javaType为查询关联对象返回类型; column为调用关联select语句所传的参数,对应当前表中所对应变量名; select为查询关联对象所调用的方法,注意方法名,不可以使用getById,getOne,selectOne等和mybatis原生查询方法重复的方法名; 当前表的查询语句返回主体使用include,那么必须保证ResultMap中关联的对象存在在include中,例如ResultMap关联了Enterprise,StaffChangeRecordSql中必须存在Enterprise_ID,否则查询方法不会执行Enterprise的getOneById(想简单省事可以直接Select *); 当前表查询语句中如果不需要考虑关联对象的条件,可以省去关联语句。例如关联了Enterprise,查询语句中没有判断Enterprise的字段,可以不用写Left On Enterprise。但是使用了ChangeRecordItem的字段进行判断,就必须加上Left On Change_Record_Item。 使用了Left On***之后必须加上Group By进行分组,否则关联对象有多个则返回的查询结果数量正确但都是同一个Id的对象。
最后查询出来的结果enterprise和recordItems都是有对象的不是null
若有不足,欢迎指正