站在巨人的肩膀上
https://blog.csdn.net/liaoxiaohua1981/article/details/6862466
?
聚集元素用來處理“一對多”的關系。需要指定映射的Java實體類的屬性,屬性的javaType(一般為ArrayList);列表中對象的類型ofType(Java實體類);對應的數據庫表的列名稱;
不同情況需要告訴MyBatis 如何加載一個聚集。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 rwhere 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>
java collection接口,結果是OK的,哈哈
1. select: 執行一個其它映射的SQL 語句返回一個Java實體類型。較靈活但會將執行多次嵌套的SQL語句。
2. resultMap: 使用一個嵌套的結果映射來處理通過join查詢結果集,映射成Java實體類型。
兩種加載方式格式如下:
1.集合的嵌套查詢(select)
java datatable。<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>
datatable轉list。注意: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類名">
datatable select?<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 classifyIdfrom aaawhereaaa_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.",