queryForList returns only one row even though SQL query returns many

[dandante <da...@dandante.com>]

Hi,

I have an IBATIS select query. I also have logging at the DEBUG level so I
can see what queries are sent to my database (mysql). When I call this query
from a unit test, using queryForList(), it only returns one row. However it
prints out the following to the console:

15:18:27,696 DEBUG PreparedStatement:27 - {pstm-100001} Executing Statement:         
select distinct g.condition_group_id as id, p.condition_id as replicate,
cg.owner_id, cg.is_private         from condition_groupings g, properties p,
condition_groups cg         where g.condition_id = p.condition_id        
and g.condition_group_id = cg.id         and cg.group_type = 'experiment'        
and p.condition_id in (?)     
15:18:27,702 DEBUG PreparedStatement:27 - {pstm-100001} Parameters:
[100,200,300,400]

So if I take that and replace the ? in the query with 100,200,300,400
and paste it into a mysql client, it returns 4 rows. But in the code the
result from queryForList() has only one item in it. This happens whether I
use a resultClass of java.lang.HashMap or use a resultMap for one of my
domain objects. Other queries with queryForList() work fine for me.

My call to the query looks like this:
List<ConditionGroup> results =
sqlMap.queryForList("getFakeConditionGroupListFromPropertyList",
"100,200,300,400");
assertTrue(results.size() == 4);

The query itself looks like this:
    <select id="getFakeConditionGroupListFromPropertyList"
parameterClass="java.lang.String"
resultMap="PartialConditionGroupResultMap">
        select distinct g.condition_group_id as id, p.condition_id as
replicate, cg.owner_id, cg.is_private
        from condition_groupings g, properties p, condition_groups cg
        where g.condition_id = p.condition_id
        and g.condition_group_id = cg.id
        and cg.group_type = 'experiment'
        and p.condition_id in (#value#)
    </select>

The result map loks like this, even though as I say if I use a resultClass
of HashMap (implicit result mapping) the same thing happens:

    <resultMap id="PartialConditionGroupResultMap"
class="org.systemsbiology.GWAP.domain.ConditionGroup" groupBy="id">
        <result property="id" column="id"/>
        <result property="replicate" column="replicate"/>
        <result property="private" column="is_private"/>
        <result property="ownerId" column="owner_id"/>
    </resultMap>

Any idea why Ibatis would run a query that returns multiple rows but only
return a list containing one item (row)?

Thanks
Dan


-- 
View this message in context: http://www.nabble.com/queryForList-returns-only-one-row-even-though-SQL-query-returns-many-tp14767444p14767444.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.

Re: queryForList returns only one row even though SQL query returns many

[Stephen Boyd <sw...@gmail.com>]

Probably because you are inserting only one bind variable which is a string
of four number separated by commas.  Is that the correct datatype of
condition_id?

The quick fix would be to change #value# to $value$.  However, the proper
way to pass in the list of numbers is with a java.util.List and use the
<iterate> tag in ibatis to bind each number as a sql parameter.

On Jan 11, 2008 6:27 PM, dandante <da...@dandante.com> wrote:

>
> Hi,
>
> I have an IBATIS select query. I also have logging at the DEBUG level so I
> can see what queries are sent to my database (mysql). When I call this
> query
> from a unit test, using queryForList(), it only returns one row. However
> it
> prints out the following to the console:
>
> 15:18:27,696 DEBUG PreparedStatement:27 - {pstm-100001} Executing
> Statement:
> select distinct g.condition_group_id as id, p.condition_id as replicate,
> cg.owner_id, cg.is_private         from condition_groupings g, properties
> p,
> condition_groups cg         where g.condition_id = p.condition_id
> and g.condition_group_id = cg.id         and cg.group_type = 'experiment'
> and p.condition_id in (?)
> 15:18:27,702 DEBUG PreparedStatement:27 - {pstm-100001} Parameters:
> [100,200,300,400]
>
> So if I take that and replace the ? in the query with 100,200,300,400
> and paste it into a mysql client, it returns 4 rows. But in the code the
> result from queryForList() has only one item in it. This happens whether I
> use a resultClass of java.lang.HashMap or use a resultMap for one of my
> domain objects. Other queries with queryForList() work fine for me.
>
> My call to the query looks like this:
> List<ConditionGroup> results =
> sqlMap.queryForList("getFakeConditionGroupListFromPropertyList",
> "100,200,300,400");
> assertTrue(results.size() == 4);
>
> The query itself looks like this:
>    <select id="getFakeConditionGroupListFromPropertyList"
> parameterClass="java.lang.String"
> resultMap="PartialConditionGroupResultMap">
>        select distinct g.condition_group_id as id, p.condition_id as
> replicate, cg.owner_id, cg.is_private
>        from condition_groupings g, properties p, condition_groups cg
>        where g.condition_id = p.condition_id
>        and g.condition_group_id = cg.id
>        and cg.group_type = 'experiment'
>        and p.condition_id in (#value#)
>    </select>
>
> The result map loks like this, even though as I say if I use a resultClass
> of HashMap (implicit result mapping) the same thing happens:
>
>    <resultMap id="PartialConditionGroupResultMap"
> class="org.systemsbiology.GWAP.domain.ConditionGroup" groupBy="id">
>        <result property="id" column="id"/>
>        <result property="replicate" column="replicate"/>
>        <result property="private" column="is_private"/>
>        <result property="ownerId" column="owner_id"/>
>    </resultMap>
>
> Any idea why Ibatis would run a query that returns multiple rows but only
> return a list containing one item (row)?
>
> Thanks
> Dan
>
>
> --
> View this message in context:
> http://www.nabble.com/queryForList-returns-only-one-row-even-though-SQL-query-returns-many-tp14767444p14767444.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>