首页 > 代码库 > ibatis2.3中groupBy 一对多 解决N+1问题过程中遇到的问题..
ibatis2.3中groupBy 一对多 解决N+1问题过程中遇到的问题..
刚学ibatis,参考网上的例子写了个一对多的小例子,用groupBy+关联查询可以只查询一次就能取出符合条件的UserList,同时每个User对象中,都还插入了UserScoreList.
刚开始是这么写的配置文件:
<resultMap class="User" id="_u" groupBy="id"> <result property="id" column="ID" /> <result property="name" column="NAME" /> <result property="gender" column="GENDER" /> <result property="age" column="AGE" /> <result property="userScoreList" resultMap="userDetail._us" /><!--对应user中的userScoreList列表--></resultMap><resultMap class="UserScore" id="_us"> <result property="scoreId" column="ID" /> <result property="userId" column="USER_ID" /> <result property="scoreName" column="SCORE_NAME" /> <result property="totalScore" column="TOTAL_SCORE" /> <result property="objectiveScore" column="OBJECTIVE_SCORE" /> <result property="subjectiveScore" column="SUBJECTIVE_SCORE" /></resultMap><select id="findUserByUsName" parameterClass="java.util.Map" resultMap="userDetail._u" > select u.ID as id, u.NAME as name, u.GENDER as gender, u.AGE as age, us.ID as scoreId, us.USER_ID as userId, us.NAME as scoreName, us.TOTAL_SCORE as totalScore, us.OBJECTIVE_SCORE as objectiveScore, us.SUBJECTIVE_SCORE as subjectiveScore from MQ_TEST_USER u left join MQ_TEST_USER_SCORE us on u.ID = us.USER_ID where us.NAME in <iterate property="subjects" open="(" close=")" conjunction=","> #subjects[]# </iterate></select>
执行之后总是报 列名无效....纠结了很久,,,突然想到as就是起别名,然后在resultMap中的column属性应该与结果集相对应,所以如果起了别名,反而会和column属性冲突..遂改之..结果不报错了,然后发现userScoreList中的数据是不对的,name的值居然是user中name的值.因为没有起别名,结果集中u.ID和us.ID还有name列名相同了,导致userScore对象插入的是user中对应的id和name..遂又改之..终于对了....
正确配置如下:
<resultMap class="User" id="_u" groupBy="id"> <result property="id" column="ID" /> <result property="name" column="NAME" /> <result property="gender" column="GENDER" /> <result property="age" column="AGE" /> <result property="userScoreList" resultMap="userDetail._us" /> </resultMap> <resultMap class="UserScore" id="_us"> <result property="scoreId" column="scoreId" /> <result property="userId" column="USER_ID" /> <result property="scoreName" column="scoreName" /> <result property="totalScore" column="TOTAL_SCORE" /> <result property="objectiveScore" column="OBJECTIVE_SCORE" /> <result property="subjectiveScore" column="SUBJECTIVE_SCORE" /> </resultMap> <select id="findUserByUsName" parameterClass="java.util.Map" resultMap="userDetail._u" > select u.ID, u.NAME, u.GENDER, u.AGE, us.ID as scoreId, //将id和name于user中的分开,然后在resultMap中配置对应的column属性. us.USER_ID, us.NAME as scoreName, us.TOTAL_SCORE, us.OBJECTIVE_SCORE, us.SUBJECTIVE_SCORE from MQ_TEST_USER u left join MQ_TEST_USER_SCORE us on u.ID = us.USER_ID where us.NAME in <iterate property="subjects" open="(" close=")" conjunction=","> #subjects[]# </iterate> </select>
ibatis2.3中groupBy 一对多 解决N+1问题过程中遇到的问题..
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。