MyBatis嵌套Collection
站在巨人的肩膀上
https://blog.csdn.net/liaoxiaohua1981/article/details/6862466
聚集元素用来处理“一对多”的关系。需要指定映射的Java实体类的属性,属性的javaType(一般为ArrayList);列表中对象的类型ofType(Java实体类);对应的数据库表的列名称;
不同情况需要告诉MyBatis 如何加载一个聚集。MyBatis 可以用两种方式加载:
划重点:collection中的column根据实践结果得出的白话总结
column可以这样写
column="USER_NAME"
也可以这样写
column="{id=id,userName213=user_name}"
value必须是外层查询的结果字段,必须得有,前面的key,你可以在子查询中作为条件参数去where
下面的写法就是分两层,外层的就是查询用户数量,子查询中返回的是每个用户对应的角色集合,这样子做主要是用来做分页的时候用,切记!!!
<resultMap id="userResultCollection" type="com.cloudwalk.shark.model.User"> <id property="id" column="ID" jdbcType="INTEGER"></id> <result property="userName" column="user_name" jdbcType="VARCHAR"></result> <collection property="roleList" select="selectRoles" column="{id=id,userName213=user_name}" ofType="com.cloudwalk.shark.model.Role" > </collection> </resultMap> <select id="selectRoles" resultType="com.cloudwalk.shark.model.Role"> select role_name from t_shark_user t join t_shark_role r where t.id = r.user_id </select> <select id="queryAllUser" resultMap="userResultCollection"> SELECT u.id, user_name FROM t_shark_user u JOIN t_shark_role r ON u.id = r.user_id GROUP BY u.id,user_name </select>
如果你是合并到一起写的
<resultMap id="userResultCollection" type="com.cloudwalk.shark.model.User"> <id property="id" column="ID" jdbcType="INTEGER"></id> <result property="userName" column="user_name" jdbcType="VARCHAR"></result> <collection property="roleList" ofType="com.cloudwalk.shark.model.Role" > <result property="roleName" column="role_name" jdbcType="VARCHAR"></result> </collection> </resultMap> <select id="queryAllUser" resultMap="userResultCollection"> SELECT u.id, user_name,role_name FROM t_shark_user u JOIN t_shark_role r ON u.id = r.user_id </select>
column有没有就无所谓了,我随便乱写也是OK的,因为这样子就只有一个查询语句,column根本就没有任何意义!!!!!
<resultMap id="userResultCollection" type="com.cloudwalk.shark.model.User"> <id property="id" column="ID" jdbcType="INTEGER"></id> <result property="userName" column="user_name" jdbcType="VARCHAR"></result> <collection property="roleList" column="sdfasd" ofType="com.cloudwalk.shark.model.Role" > <result property="roleName" column="role_name" jdbcType="VARCHAR"></result> </collection> </resultMap>
![](https://common.cnblogs.com/images/loading.gif)
结果是OK的,哈哈
1. select: 执行一个其它映射的SQL 语句返回一个Java实体类型。较灵活但会将执行多次嵌套的SQL语句。
2. resultMap: 使用一个嵌套的结果映射来处理通过join查询结果集,映射成Java实体类型。
两种加载方式格式如下:
1.集合的嵌套查询(select)
<collection property="Java属性名" ofType="另一Java类名" javaType="ArrayList" column="关联主键ID(用于嵌套查询SQL语句传入参数,多个用逗号分开)" select="另一个select映射SQL的ID"/>
<select parameterType="int" resultType="另一Java类名" id="另一个select映射SQL的ID">
SQL语句
<select>
注意:column属性的值必须与相应的SQL查询语句中的列名相同。MyBatis会将第一条SQL语句查询出来的该列的值用于所嵌套的SQL映射语句的入参。因第一条SQL语句查询出来的每个该列的值都将用于执行另一个SQL语句,所以嵌套的SQL语句将被多次执行。
2.集合的嵌套结果(resultMap)
<collection property="Java属性名" ofType="另一Java类名" javaType="ArrayList" resultMap="另一个resultMap的ID"/>
<resultMap="另一个resultMap的ID" type="另一Java类名">
<id property="id" column="关联主键ID"/>
........
</resultMap>
注意:column属性的值必须与相应的SQL查询语句的列名一样。
集合的嵌套查询(select)示例:
<?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.myapp.mapper.UserMapper"> <select id="getUserList" resultMap="userdetailResult"> select * from t_user where id between 1 and 10 </select>
<select id="selectRoles" resultType="com.myapp.domain.Role" parameterType="int"> select * from t_user_role a,t_role b where a.user_id=#{id} and a.role_id=b.id </select>
<resultMap id="userdetailResult" type="User"> <id property="id" column="user_id" /> <result property="name" column="user_name"/> <result property="createDate" column="create_date"/> <collection property="roles" ofType="Role" javaType="ArrayList" column="id" select="selectRoles"/> </resultMap> </mapper>
集合的嵌套结果(result)示例:
<?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.myapp.mapper.UserMapper"> <select id="getUserList" resultMap="userdetailResult"> SELECT u.id as user_id, u.name as user_name, u.create_date, r.id as role_id, r.name as role_name FROM t_user u LEFT JOIN t_user_role ur ON(u.id=ur.user_id) LEFT JOIN t_role r ON(r.id=ur.role_id) where u.id=1 </select>
<resultMap id="userdetailResultNew" type="User"> <id property="id" column="user_id" /> <result property="name" column="user_name"/> <result property="createDate" column="create_date"/> <collection property="roles" ofType="Role" javaType="ArrayList"> <id property="id" column="role_id"/> <result property="name" column="role_name"/> </collection> </resultMap>
<resultMap id="roleResult" type="Role"> <id property="id" column="role_id"/> <result property="name" column="role_name"/> </resultMap>
<resultMap id="userdetailResult" type="User"> <id property="id" column="user_id" /> <result property="name" column="user_name"/> <result property="createDate" column="create_date"/> <collection property="roles" ofType="Role" javaType="ArrayList" resultMap="roleResult"/> </resultMap> </mapper>
如果你只是简单的嵌套,可以像id="userdetailResultNew" 那样将要嵌套的结果直接写在collection子元素中去。
下面关于这个Collection中的column具体什么意思看下下面的教程
===========================================================================================================================================
mybatis collection column 传常量
想要在mybatis 的collection关联查询中,添加一个常量:classifyId=1作为参数,原先使用的添加方式为:
<collection property="imageList" column="{aaaId=aaa_id,classifyId='1'}" javaType="ArrayList" select="com.fsti.information.dao.ImageManageMapper.queryGoodsImage"> </collection>
会报找不到行:”1 “的错误。
需要将关联的语句改为:
<resultMap id="GoodsVO" type="com.fsti.aaa.bean.vo.aaaVO" > <collection property="imageList" column="{aaaId=aaa_id,classifyId=classifyId}" javaType="ArrayList" select="com.fsti.information.dao.ImageManageMapper.queryGoodsImage"> </collection> <collection property="goodsTags" column="{goodsId = goods_id}" javaType="ArrayList" select="com.fsti.goods.dao.GoodsTagsReleMapper.queryGoodsTags"> </collection> </resultMap>
基础查询的语句改为:
<select id="queryAaaVO" resultMap="aaaVO" parameterType="java.util.Map" > select <include refid="Base_Column_List" />, 1 as classifyId from aaa where aaa_id=#{aaaId,jdbcType=BIGINT} </select>
也就是在查询时添加一句 1 as classifyId
然后,将其作为变量在column中引用即可:classifyId=classifyId
最后给大家看下如果不存在mybatis给的错误提示,这样一下子就能明白了
"message": "nested exception is org.apache.ibatis.executor.result.ResultMapException:
Error attempting to get column 'aaa_id' from result set.
Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'aaa_id' from result set.
Cause: java.sql.SQLException: Column 'aaa_id' not found.",