Not all rows returned by query being mapped?

[dq]

I've got a query that does a call to select a list: getSqlSessionTemplate().selectList(query, params);

When I run the query directly against mysql, I get 2 rows returned.  When Mybatis 3.1.1 runs it, it only returns 1 object in the list.

Here are some details, with some of the names changed to protect the innocent.  If I happened to make a typo doing the changes, that's probably not the source of this problem:

Here's the table:

CREATE TABLE `sanity_tests` (

  `sanity_id` int(12) unsigned NOT NULL,

  `sanitycode` char(3) NOT NULL DEFAULT '',

  `sid` int(12) unsigned NOT NULL DEFAULT '0',

  `rid` int(12) unsigned NOT NULL DEFAULT '0',

  `rcode` varchar(2) NOT NULL DEFAULT '',

  `cid` int(12) unsigned NOT NULL DEFAULT '0',

  `gid` varchar(4) NOT NULL DEFAULT '',

  PRIMARY KEY (`sanity_id`,`sanitycode`,`ctid`,`rid`,`cid`,`gid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here's the query:

<select fetchSize="100" resultSetType="FORWARD_ONLY" id="getSanityDest" resultMap="sanityDest">

    select sanity_id,sanitycode,sid ,rid,rcode,cid,gid from sanity_tests

  <where>

    <if test="sanityID != null" >

      AND sanity_id = #{sanityID}

    </if>

   </where> 

</select>

<resultMap id="imageDest" type="com.this.that.obj.SanityDest">

  <result property="sanityId" column="sanity_id"/>

  <association property="san" resultMap="fullSanity"/>

</resultMap>

<resultMap id="fullLocation" type="com.this.that.Sanity">

 <result property="AC" column="sanitycode"/>

 <result property="sidStr" column="sid"/>

 <result property="rcode" column="rcode"/>

 <result property="ridStr" column="rid"/>

 <result property="cidStr" column="cid"/>

 <result property="gid" column="gid"/>

</resultMap>

And here's what happens when I run the select directly against the database (mysql 5.5.24-0ubuntu0.12.04.1)

select sanity_id,sanitycode,ctid ,rid,rcode,cid,gid from sanity_tests WHERE sanity_id =9671;

+----------+-------------+-------+------+-------+-----+-----+

| sanity_id | sanitycode | sid  | rid  | rcode | cid | gid |

+----------+-------------+-------+------+-------+-----+-----+

|     9671 | LII         | 22103 | 2500 | MM    | 201 | BB  |

|     9671 | UUU         | 13127 |  141 | MN    | 253 | SW  |

+----------+-------------+-------+------+-------+-----+-----+

I also tried running the select without speicifying the sanity_id and mybatis seems to be only returning 1 object per sanity_id.  What's up with that?

[Larry Meadors]

Where the sanityDest result map? This code looks messed up.

Larry

[dq]

Sorry, that would be the imageDest resultMap that I didn't fully obfuscate.

Does anything else look messed up besides that?  

It does correctly map a single object, it's just that it's only mapping 1 object per sanity_id returned in the resultset.

[dq]

And likewise fullLocation should be fullSanity.

On Wednesday, October 31, 2012 5:29:19 PM UTC-4, Larry Meadors wrote:

[dq]

Here's what it looks like with those obfuscation mistakes fixed:

Here's the table:

CREATE TABLE `sanity_tests` (
  `sanity_id` int(12) unsigned NOT NULL,
  `sanitycode` char(3) NOT NULL DEFAULT '',
  `sid` int(12) unsigned NOT NULL DEFAULT '0',
  `rid` int(12) unsigned NOT NULL DEFAULT '0',
  `rcode` varchar(2) NOT NULL DEFAULT '',
  `cid` int(12) unsigned NOT NULL DEFAULT '0',
  `gid` varchar(4) NOT NULL DEFAULT '',
  PRIMARY KEY (`sanity_id`,`sanitycode`,`ctid`,`rid`,`cid`,`gid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here's the query:

<select fetchSize="100" resultSetType="FORWARD_ONLY" id="getSanityDest" resultMap="sanityDest">
    select sanity_id,sanitycode,sid ,rid,rcode,cid,gid from sanity_tests
  <where>
    <if test="sanityID != null" >
      AND sanity_id = #{sanityID}
    </if>
   </where>

</select>

<resultMap id="sanityDest" type="com.this.that.obj.SanityDest">

  <result property="sanityId" column="sanity_id"/>
  <association property="san" resultMap="fullSanity"/>
</resultMap>

<resultMap id="fullSanity" type="com.this.that.obj.Sanity">

 <result property="AC" column="sanitycode"/>
 <result property="sidStr" column="sid"/>
 <result property="rcode" column="rcode"/>
 <result property="ridStr" column="rid"/>
 <result property="cidStr" column="cid"/>
 <result property="gid" column="gid"/>
</resultMap>

And here's what happens when I run the select directly against the database (mysql 5.5.24-0ubuntu0.12.04.1)

select sanity_id,sanitycode,ctid ,rid,rcode,cid,gid from sanity_tests WHERE sanity_id =9671;
+----------+-------------+-------+------+-------+-----+-----+
| sanity_id | sanitycode | sid  | rid  | rcode | cid | gid |
+----------+-------------+-------+------+-------+-----+-----+
|     9671 | LII         | 22103 | 2500 | MM    | 201 | BB  |
|     9671 | UUU         | 13127 |  141 | MN    | 253 | SW  |
+----------+-------------+-------+------+-------+-----+-----+

Any ideas?

As I said, it's producing a result list as if I were selecting distinct on the sanity_id

[Dridi Boukelmoune]

Hi,

I don't see any id in your result maps, only results.

Does "sanity_tests" have a composite id ?
* sanity_id
* sanitycode

According to your example, I would assume that the second line with
sanity_id 9671 overwrites the first one. Without any id, all results
are part of the unique key (it would mean that associations can't be
part of the unike key).

You could try a workaround like:

<resultMap id="sanityDest" type="com.this.that.obj.SanityDest">
<result property="sanityId" column="sanity_id"/>

<result column="sanitycode"/> <!-- no property -->

<association property="san" resultMap="fullSanity"/>
</resultMap>

Or maybe:

<resultMap id="sanityDest" type="com.this.that.obj.SanityDest">

<id property="sanityId" column="sanity_id"/>
<id column="sanitycode"/> <!-- no property -->

<association property="san" resultMap="fullSanity"/>
</resultMap>

I think it works with MB 3.1.1

Dridi

--
Dridi Boukelmoune
Développeur/Formateur

GSM : +33 (0)6 17 91 14 23

[dq]

All the columns combined form the unique ID for the row.  In the end I just used nested <constructor> in my resultMaps. 

[eric]

I think I found the issue with my resultMap. I'm doing an association select on one of the nested objects in my resultMap. This causes MyBatis to attempt to look up the column from the association's resultMap in the original resultMap. Since the original resultMap does not contain that column, a column not found error is thrown.

On Wednesday, January 9, 2013 3:58:58 PM UTC-8, eric wrote:

Can you post your resultMaps? I'm having a similar problem but when I attempt to use <constructor> elements in my resultMaps I get "column not found" errors.