You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Linda van der Pal <lv...@heritageagenturen.nl> on 2009/07/15 16:08:25 UTC
Sorting problem
Hello,
Can anybody tell me what I'm doing wrong? I want my query to return a
sorted list.
Here are the relevant parts from my boek_SqlMap.xml (which is included
in SqlMapConfig.xml)
<resultMap class="nl.lunaris.bookdb.model.database.Book"
id="BoekResultMap">
<result column="ISBN" jdbcType="VARCHAR" property="isbn" />
<result column="Titel" jdbcType="VARCHAR" property="title" />
<result column="Uitgever" jdbcType="INTEGER" property="publisher" />
<result column="Subgenre" jdbcType="INTEGER" property="subgenre" />
<result column="Taal" jdbcType="INTEGER" property="language" />
<result column="Beschrijving" jdbcType="LONGVARCHAR"
property="description" />
<result property="authors" resultMap="boek.Auteurlijst" />
</resultMap>
<resultMap class="nl.lunaris.bookdb.model.database.BookHasAuthorKey"
id="Auteurlijst">
<result column="AUTEUR_ID" jdbcType="INTEGER" property="id" />
<result column="ISBN" jdbcType="VARCHAR" property="isbn" />
</resultMap>
<select id="selectSelective"
parameterClass="nl.lunaris.bookdb.model.frontend.SearchCriteria"
resultMap="BoekResultMap">
select b.ISBN, b.Titel, b.Uitgever, b.Subgenre, b.Taal,
b.Beschrijving, a.ID as AUTEUR_ID
from boek b
join boek_has_auteur a ON b.ISBN = a.ISBN
join boek_exemplaar e on b.ISBN = e.ISBN
<dynamic prepend="where ">
<isNotNull prepend="AND" property="isbn">
b.ISBN = #isbn:VARCHAR#
</isNotNull>
<isNotNull prepend="AND" property="owner">
e.eigenaar = #owner:INTEGER#
</isNotNull>
<isNotNull prepend="AND" property="publisher">
b.Uitgever = #publisher.id:INTEGER#
</isNotNull>
<isNotNull prepend="AND" property="subgenre">
b.Subgenre = #subgenre.id:INTEGER#
</isNotNull>
<isNotNull prepend="AND" property="language">
b.Taal = #language.id:INTEGER#
</isNotNull>
<isNotNull prepend="AND" property="authors">
a.ID = #authors.id:INTEGER#
</isNotNull>
</dynamic>
<dynamic prepend="order by ">
<isNotNull property="sort">
#sort:VARCHAR# #sortAsc:VARCHAR#
</isNotNull>
</dynamic>
</select>
And here is the code I am calling it with:
List result = sqlMapClient.queryForList("boek.selectSelective", key);
I can see in my logging that the query is called and filled with the
right parameters:
2009-07-15 15:14:50,299 DEBUG [PreparedStatement] {pstm-100019}
Executing Statement: select b.ISBN, b.Titel, b.Uitgever, b.Subgenre,
b.Taal, b.Beschrijving, a.ID as AUTEUR_ID from boek b join
boek_has_auteur a ON b.ISBN = a.ISBN join boek_exemplaar e on b.ISBN
= e.ISBN where e.eigenaar =
? order by ?
? 2009-07-15 15:14:50,299 DEBUG [PreparedStatement]
{pstm-100019} Parameters: [1, titel, asc]
Regards,
Linda van der Pal
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: Sorting problem
Posted by Linda van der Pal <lv...@heritageagenturen.nl>.
Thanks a million, that did the trick! I had tried that before, but had
probably made some other mistake then.
Regards,
Linda
Jeff Butler wrote:
> I don't think you can send the order by clause as a prepared statement
> parameter. You should try string substitution instead. Also, you
> don't need the <dynamic> tag here:
>
> <isNotNull property="sort">
> order by $sort$ $sortAsc$
> </isNotNull>
>
> Jeff Butler
>
> On Wed, Jul 15, 2009 at 9:08 AM, Linda van der
> Pal<lv...@heritageagenturen.nl> wrote:
>
>> Hello,
>>
>> Can anybody tell me what I'm doing wrong? I want my query to return a sorted
>> list.
>>
>> Here are the relevant parts from my boek_SqlMap.xml (which is included in
>> SqlMapConfig.xml)
>>
>> <resultMap class="nl.lunaris.bookdb.model.database.Book" id="BoekResultMap">
>> <result column="ISBN" jdbcType="VARCHAR" property="isbn" />
>> <result column="Titel" jdbcType="VARCHAR" property="title" />
>> <result column="Uitgever" jdbcType="INTEGER" property="publisher" />
>> <result column="Subgenre" jdbcType="INTEGER" property="subgenre" />
>> <result column="Taal" jdbcType="INTEGER" property="language" />
>> <result column="Beschrijving" jdbcType="LONGVARCHAR" property="description"
>> />
>> <result property="authors" resultMap="boek.Auteurlijst" />
>> </resultMap>
>>
>> <resultMap class="nl.lunaris.bookdb.model.database.BookHasAuthorKey"
>> id="Auteurlijst">
>> <result column="AUTEUR_ID" jdbcType="INTEGER" property="id" />
>> <result column="ISBN" jdbcType="VARCHAR" property="isbn" />
>> </resultMap>
>>
>> <select id="selectSelective"
>> parameterClass="nl.lunaris.bookdb.model.frontend.SearchCriteria"
>> resultMap="BoekResultMap">
>> select b.ISBN, b.Titel, b.Uitgever, b.Subgenre, b.Taal, b.Beschrijving,
>> a.ID as AUTEUR_ID
>> from boek b
>> join boek_has_auteur a ON b.ISBN = a.ISBN
>> join boek_exemplaar e on b.ISBN = e.ISBN
>> <dynamic prepend="where ">
>> <isNotNull prepend="AND" property="isbn">
>> b.ISBN = #isbn:VARCHAR#
>> </isNotNull>
>> <isNotNull prepend="AND" property="owner">
>> e.eigenaar = #owner:INTEGER#
>> </isNotNull>
>> <isNotNull prepend="AND" property="publisher">
>> b.Uitgever = #publisher.id:INTEGER#
>> </isNotNull>
>> <isNotNull prepend="AND" property="subgenre">
>> b.Subgenre = #subgenre.id:INTEGER#
>> </isNotNull>
>> <isNotNull prepend="AND" property="language">
>> b.Taal = #language.id:INTEGER#
>> </isNotNull>
>> <isNotNull prepend="AND" property="authors">
>> a.ID = #authors.id:INTEGER#
>> </isNotNull>
>> </dynamic>
>> <dynamic prepend="order by ">
>> <isNotNull property="sort">
>> #sort:VARCHAR# #sortAsc:VARCHAR#
>> </isNotNull>
>> </dynamic>
>> </select>
>>
>>
>> And here is the code I am calling it with:
>> List result = sqlMapClient.queryForList("boek.selectSelective", key);
>>
>>
>> I can see in my logging that the query is called and filled with the right
>> parameters:
>> 2009-07-15 15:14:50,299 DEBUG [PreparedStatement] {pstm-100019} Executing
>> Statement: select b.ISBN, b.Titel, b.Uitgever, b.Subgenre, b.Taal,
>> b.Beschrijving, a.ID as AUTEUR_ID from boek b join boek_has_auteur a
>> ON b.ISBN = a.ISBN join boek_exemplaar e on b.ISBN = e.ISBN where
>> e.eigenaar = ?
>> order by ? ? 2009-07-15 15:14:50,299 DEBUG
>> [PreparedStatement] {pstm-100019} Parameters: [1, titel, asc]
>>
>> Regards,
>> Linda van der Pal
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.375 / Virus Database: 270.13.16/2240 - Release Date: 07/15/09 17:58:00
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: Sorting problem
Posted by Jeff Butler <je...@gmail.com>.
I don't think you can send the order by clause as a prepared statement
parameter. You should try string substitution instead. Also, you
don't need the <dynamic> tag here:
<isNotNull property="sort">
order by $sort$ $sortAsc$
</isNotNull>
Jeff Butler
On Wed, Jul 15, 2009 at 9:08 AM, Linda van der
Pal<lv...@heritageagenturen.nl> wrote:
> Hello,
>
> Can anybody tell me what I'm doing wrong? I want my query to return a sorted
> list.
>
> Here are the relevant parts from my boek_SqlMap.xml (which is included in
> SqlMapConfig.xml)
>
> <resultMap class="nl.lunaris.bookdb.model.database.Book" id="BoekResultMap">
> <result column="ISBN" jdbcType="VARCHAR" property="isbn" />
> <result column="Titel" jdbcType="VARCHAR" property="title" />
> <result column="Uitgever" jdbcType="INTEGER" property="publisher" />
> <result column="Subgenre" jdbcType="INTEGER" property="subgenre" />
> <result column="Taal" jdbcType="INTEGER" property="language" />
> <result column="Beschrijving" jdbcType="LONGVARCHAR" property="description"
> />
> <result property="authors" resultMap="boek.Auteurlijst" />
> </resultMap>
>
> <resultMap class="nl.lunaris.bookdb.model.database.BookHasAuthorKey"
> id="Auteurlijst">
> <result column="AUTEUR_ID" jdbcType="INTEGER" property="id" />
> <result column="ISBN" jdbcType="VARCHAR" property="isbn" />
> </resultMap>
>
> <select id="selectSelective"
> parameterClass="nl.lunaris.bookdb.model.frontend.SearchCriteria"
> resultMap="BoekResultMap">
> select b.ISBN, b.Titel, b.Uitgever, b.Subgenre, b.Taal, b.Beschrijving,
> a.ID as AUTEUR_ID
> from boek b
> join boek_has_auteur a ON b.ISBN = a.ISBN
> join boek_exemplaar e on b.ISBN = e.ISBN
> <dynamic prepend="where ">
> <isNotNull prepend="AND" property="isbn">
> b.ISBN = #isbn:VARCHAR#
> </isNotNull>
> <isNotNull prepend="AND" property="owner">
> e.eigenaar = #owner:INTEGER#
> </isNotNull>
> <isNotNull prepend="AND" property="publisher">
> b.Uitgever = #publisher.id:INTEGER#
> </isNotNull>
> <isNotNull prepend="AND" property="subgenre">
> b.Subgenre = #subgenre.id:INTEGER#
> </isNotNull>
> <isNotNull prepend="AND" property="language">
> b.Taal = #language.id:INTEGER#
> </isNotNull>
> <isNotNull prepend="AND" property="authors">
> a.ID = #authors.id:INTEGER#
> </isNotNull>
> </dynamic>
> <dynamic prepend="order by ">
> <isNotNull property="sort">
> #sort:VARCHAR# #sortAsc:VARCHAR#
> </isNotNull>
> </dynamic>
> </select>
>
>
> And here is the code I am calling it with:
> List result = sqlMapClient.queryForList("boek.selectSelective", key);
>
>
> I can see in my logging that the query is called and filled with the right
> parameters:
> 2009-07-15 15:14:50,299 DEBUG [PreparedStatement] {pstm-100019} Executing
> Statement: select b.ISBN, b.Titel, b.Uitgever, b.Subgenre, b.Taal,
> b.Beschrijving, a.ID as AUTEUR_ID from boek b join boek_has_auteur a
> ON b.ISBN = a.ISBN join boek_exemplaar e on b.ISBN = e.ISBN where
> e.eigenaar = ?
> order by ? ? 2009-07-15 15:14:50,299 DEBUG
> [PreparedStatement] {pstm-100019} Parameters: [1, titel, asc]
>
> Regards,
> Linda van der Pal
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org