Databases
StackOverflow2013
Postsdbo
POMyBatis nested collection query cause duplicate wrong result
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMyBatis nested collection query cause duplicate wrong result
    text
    copied!<p>I use mybatis to query nested object</p> <p>my bean, Goods.java</p> <pre><code>public class Goods { private Integer goodsId; private String goodsName; private Integer goodsStorageNum; private Integer goodsScore; private GoodsStatus goodsStatus; private String goodsDescription; private List&lt;GoodsImg&gt; goodsImgList; getter and setter here... } </code></pre> <p>GoodsImg.java</p> <pre><code>public class Goods { private Integer goodsId; private String goodsName; private Integer goodsStorageNum; private Integer goodsScore; private GoodsStatus goodsStatus; private String goodsDescription; private List&lt;GoodsImg&gt; goodsImgList; getter and setter ... } </code></pre> <p>two result map</p> <pre><code> &lt;!-- goods resultmap --&gt; &lt;resultMap id="goodsResultMap" type="com.qunar.scoresystem.bean.Goods"&gt; &lt;id property="goodsId" column="goods_id" /&gt; &lt;result property="goodsName" column="goods_name" /&gt; &lt;result property="goodsStorageNum" column="goods_storage_num" /&gt; &lt;result property="goodsScore" column="goods_score" /&gt; &lt;result property="goodsDescription" column="goods_description" /&gt; &lt;result property="goodsStatus" column="goods_status" /&gt; &lt;collection property="goodsImgList" column="goods_id" resultMap="goodsImgResult" /&gt; &lt;/resultMap&gt; &lt;!-- goodsimage resultmap --&gt; &lt;resultMap id="goodsImgResult" type="com.qunar.scoresystem.bean.GoodsImg"&gt; &lt;id property="imgId" column="img_id" /&gt; &lt;result property="goodsId" column="goods_id" /&gt; &lt;result property="imgDir" column="img_dir" /&gt; &lt;result property="imgSize" column="img_size" /&gt; &lt;result property="imgName" column="img_name" /&gt; &lt;/resultMap&gt; </code></pre> <p>my sql </p> <pre><code> select goods.goods_id as goods_id, goods.goods_name as goods_name, goods.goods_storage_num as goods_storage_num, goods.goods_score as goods_score, goods.goods_description as goods_description, goods.goods_status as goods_status , goods_img.img_name as img_name , goods_img.img_dir as img_dir , goods_img.img_size as img_size from goods join goods_img on goods.goods_id=goods_img.goods_id limit 10; </code></pre> <p>result of the sql in mysql</p> <p>the first column below is "goods_id"</p> <hr> <p>1 test_name 1 1 update desc 1 img1 tttt 1</p> <p>1 test_name 1 1 update desc 1 img2 ttt 2</p> <p>1 test_name 1 1 update desc 1 img3 ttt 3</p> <p>2 test_name2 2 2 test desc2 0 df ttt 3</p> <p>1 test_name 1 1 update desc 1 dfdf ddd 2</p> <p>3 3333 3 3 ddfd 1 dfd sadf 1</p> <p>1 test_name 1 1 update desc 1 sdfsd sdf 2</p> <hr> <p>and the query result in java is:</p> <p>List [ [Goods(id=1) with 5 GoodsImg], [Goods(id=2) with 5 GoodsImg], [Goods(id=3) with 5 GoodsImg] ] </p> <p>this list contains 3 Goods all the same</p> <p>and the correct list should be:</p> <p>List [ [Goods(id=1) with 5 GoodsImg], [Goods(id=2) with 1 GoodsImg], [Goods(id=1) with 1 GoodsImg] ] </p> <p>and, if I change the data order in mysql like this :</p> <hr> <p>1 test_name 1 1 update desc 1 img1 tttt 1</p> <p>1 test_name 1 1 update desc 1 img2 ttt 2</p> <p>1 test_name 1 1 update desc 1 img3 ttt 3</p> <p>2 test_name2 2 2 test desc2 0 df ttt 3</p> <p>1 test_name 1 1 update desc 1 dfdf ddd 2</p> <p>1 test_name 1 1 update desc 1 sdfsd sdf 2</p> <p>3 3333 3 3 ddfd 1 dfd sadf 1</p> <hr> <p>then the query result in java is:</p> <p>List [ [Goods(id=1) with 5 GoodsImg], [Goods(id=1) with 5 GoodsImg], [Goods(id=3) with 1 GoodsImg] ] </p> <p>still worong list, there is no Goods(id=2)</p> <p>and if I change the data order in mysql like this:</p> <hr> <p>1 test_name 1 1 update desc 1 img1 tttt 1</p> <p>1 test_name 1 1 update desc 1 img2 ttt 2</p> <p>1 test_name 1 1 update desc 1 img3 ttt 3</p> <p>1 test_name 1 1 update desc 1 dfdf ddd 2</p> <p>1 test_name 1 1 update desc 1 sdfsd sdf 2</p> <p>2 test_name2 2 2 test desc2 0 df ttt 3</p> <p>3 3333 3 3 ddfd 1 dfd sadf 1</p> <hr> <p>then the query result in java is all right</p> <p>since I can't control the data order in mysql, it will always return the wrong list result</p> <p>so, what's the problem, is that a bug of mybatis ? my </p> <pre><code> &lt;dependency&gt; &lt;groupId&gt;org.mybatis&lt;/groupId&gt; &lt;artifactId&gt;mybatis&lt;/artifactId&gt; &lt;version&gt;3.2.1&lt;/version&gt; &lt;/dependency&gt; &lt;dependency&gt; &lt;groupId&gt;org.mybatis&lt;/groupId&gt; &lt;artifactId&gt;mybatis-spring&lt;/artifactId&gt; &lt;version&gt;1.2.0&lt;/version&gt; &lt;/dependency&gt; </code></pre> <p>Thx in advance</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload