mybatis如何进行一对一、一对多的多表查询, 这里用一个简单的例子说明
类PrintExportInfo,对应的实体类如下:
private String exportId;
private Date exportTime;
private String exportUserId;
private List<PrintExportDetail> printExportDetails;
类 PrintExportDetail,对应的实体类如下:
private String exportId;
private String requestId;
private MrRequestInfo mrRequestInfo;
类 MrRequestInfo,对应的实体类如下:
private int requestId;
private String hosId;
private String userId;
private String userName;
PrintExportInfo 类 mapper.xml 配置
<resultMap id="printExportInfo" type="com.llc.domain.PrintExportInfo">
<result column="EXPORT_ID" property="exportId" />
<result column="EXPORT_TIME" property="exportTime" />
<result column="EXPORT_USER_ID" property="exportUserId" />
<collection property="printExportDetails" ofType="com.llc.domain.PrintExportDetail">
<result column="EXPORT_ID" property="exportId" />
<result column="REQUEST_ID" property="requestId" />
<association property="mrRequestInfo" javaType="com.llc.domain.MrRequestInfo" >
<result column="REQUEST_ID" property="requestId" />
<result column="HOS_ID" property="hosId" />
<result column="USER_ID" property="userId" />
<result column="USER_NAME" property="userName" />
</association>
</collection>
</resultMap>
注:association用来映射一对一的关系, collection用来映射一对多的关系
下面是关联查询语句
<!-- 获取列表 IHosMrFeeDao query -->
<select id="query" resultMap="printExportInfo" parameterType="com.llc.domain.PrintExportInfo">
select
pi.EXPORT_ID AS EXPORT_ID,
pi.EXPORT_TIME AS EXPORT_TIME,
pi.EXPORT_USER_ID AS EXPORT_USER_ID,
pd.EXPORT_ID AS EXPORT_IDS,
pd.REQUEST_ID AS REQUEST_ID,
r.REQUEST_ID AS REQUEST_IDS,
r.HOS_ID AS HOS_ID,
r.USER_ID AS USER_ID,
r.USER_NAME AS USER_NAME
from
KH_PRINT_EXPORT_INFO pi
LEFT JOIN KH_PRINT_EXPORT_DETAIL pd
on pi.EXPORT_ID = pd.EXPORT_ID
LEFT JOIN KH_MR_REQUEST r
on pd.REQUEST_ID = r.REQUEST_ID
where 1=1
</select>
如果 不同domain中的属性名一样,可以通过 AS 修改别名 避免出现错误
当mybatis如果报一下错误:
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exception原因就在于mapper.xml的resultType代表的是List中的元素类型,而不应该是List本身
1. 当提供的返回类型属性是resultType时,MyBatis会将Map里面的键值对取出赋给resultType所指定的对象对应的属性。所以其实MyBatis的每一个查询映射的返回类型都是ResultMap,只是当提供的返回类型属性是resultType的时候,MyBatis对自动的给把对应的值赋给resultType所指定对象的属性。
2. 当提供的返回类型是resultMap时,因为Map不能很好表示领域模型,就需要自己再进一步的把它转化为对应的对象,这常常在复杂查询中很有作用。