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 Corrado Alesso <co...@fastwebnet.it> on 2008/12/14 15:33:40 UTC
Wrong sorting when using orderBy clause
Hello everyone, I'm facing a problem with iBatis.
I guess this is a known problem, but after a couple of hours looking for a
solution with no luck, I have to write here...
I have an entity that has a complex colletion as a property. So I perform
a query with a join, and I use the 'groupBy' clause with the ResultMap.
Everything is working fine, and the complex property is populated with all
the objects.
The problem is that I've put an 'order by' clause in the query, and this
ordering is not respected by iBatis. I mean, when I access the complex
property (which is a List of objects) it is not ordered the same way.
For example, when I execute the query in the console I get:
id user tag
4 u1 graphic
4 u1 layout
4 u1 website
(the query has an 'order by' on 'tag')
When I iterate the complex property tag of my Java object I get:
website
layout
graphic
Any idea or keyword I should search for?
Thank you
Corrado
Re: Wrong sorting when using orderBy clause
Posted by Corrado Alesso <co...@fastwebnet.it>.
Thanks for the reply.
The result class is a class of my domain model, not a Map. And the complex
property is a List.
Here the stub of my class and of my ResultMap:
public class Service {
private Integer id;
// Some other primitive properties here...
private List<Tag> tagList;
}
<resultMap id="service" class="net.mydomain.model.Service" groupBy="id">
<result property="id" column="id" />
....
<result property="tagList" resultMap="tagList" />
</resultMap>
<resultMap id="tagList" class="net.mydomain.model.Tag">
<result property="tag" column="tag" />
</resultMap>
In data 14 dicembre 2008 alle ore 16:22:01, Nathan Maves
<na...@gmail.com> ha scritto:
> result class? I have a feeling that you might be trying
> to user a map which by default insertion order
Re: Wrong sorting when using orderBy clause
Posted by Nathan Maves <na...@gmail.com>.
What is the result class? I have a feeling that you might be trying
to user a map which by default insertion order it not retained.
On Sun, Dec 14, 2008 at 7:33 AM, Corrado Alesso <co...@fastwebnet.it> wrote:
> Hello everyone, I'm facing a problem with iBatis.
>
> I guess this is a known problem, but after a couple of hours looking for a
> solution with no luck, I have to write here...
>
> I have an entity that has a complex colletion as a property. So I perform a
> query with a join, and I use the 'groupBy' clause with the ResultMap.
> Everything is working fine, and the complex property is populated with all
> the objects.
>
> The problem is that I've put an 'order by' clause in the query, and this
> ordering is not respected by iBatis. I mean, when I access the complex
> property (which is a List of objects) it is not ordered the same way.
>
> For example, when I execute the query in the console I get:
>
> id user tag
> 4 u1 graphic
> 4 u1 layout
> 4 u1 website
>
> (the query has an 'order by' on 'tag')
>
> When I iterate the complex property tag of my Java object I get:
>
> website
> layout
> graphic
>
>
> Any idea or keyword I should search for?
>
> Thank you
>
> Corrado
>
Re: Wrong sorting when using orderBy clause
Posted by Larry Meadors <la...@gmail.com>.
Blargh - CURSE YOU IBM!!
It IS an ibatis thing that contributes to it - ibatis strips line
feeds from the SQL...
Larry
On Mon, Dec 15, 2008 at 1:44 PM, Corrado Alesso <co...@fastwebnet.it> wrote:
> THE COMMENT.
>
> It was the comment in the query.
>
> It looks like pgAdmin can digest the comment in the query, while JDBC not.
> My eyes were also commenting out that line, because I didn't realized till
> now...
>
> Guys, sorry for the time I made you waste on this thread and for doubting
> about iBatis, I was really getting mad on this problem.
>
Re: Wrong sorting when using orderBy clause
Posted by Corrado Alesso <co...@fastwebnet.it>.
THE COMMENT.
It was the comment in the query.
It looks like pgAdmin can digest the comment in the query, while JDBC not.
My eyes were also commenting out that line, because I didn't realized till
now...
Guys, sorry for the time I made you waste on this thread and for doubting
about iBatis, I was really getting mad on this problem.
Re: Wrong sorting when using orderBy clause
Posted by Corrado Alesso <co...@fastwebnet.it>.
In data 15 dicembre 2008 alle ore 20:45:07, Nathan Maves
<na...@gmail.com> ha scritto:
> you have yet to give us your complete SQL for this select. That might
> really help us to help you :)
Ok, thanks. Actually I trimmed the query because many parts are
irrelevant, but this was a mistake because playing with the query I found
a lot of interesting things. First of all, sorry to everyone for this
thread becoming a sql/postgres related topic, I don't think anymore that
this is an iBatis problem.
My environment:
Tomcat 6.0.18
PostgreSQL 8.3 and postgresql-8.3-604.jdbc4.jar as driver (but tested also
with jdbc3 driver)
iBatis 2.3.4.726 (just upgraded from 2.3.0.677)
Spring 2.5.6
The pojo:
http://pastebin.com/d7f1ba086
The sqlmap:
http://pastebin.com/m42d91c56 (note the order by clause)
The SQL:
http://pastebin.com/d7569333d
So, I have a service and a list of tags associated to it. If the user
search for 'gra' I want to look for all the services that has 'gra' in
their short_description, and all the services that has an associated tag
that has 'gra' in the name.
Here the result I get:
List<Service> list = dao.getServicesBySearchQuery("gra");
System.out.println(list);
[[Service@3020ad id = 4, userId = [null], shortDescription = 'Creazione
layout grafico', longDescription = [null], creation = Mon Dec 15 20:56:40
CET 2008, price = 20.00, state = 1, effort = 1, effortType = 3, tagList =
list[graphic, layout, website], username = [null]]]
List<Service> list = dao.getServicesBySearchQuery("graph");
System.out.println(list);
[[Service@3020ad id = 4, userId = [null], shortDescription = 'Creazione
layout grafico', longDescription = [null], creation = Mon Dec 15 20:56:40
CET 2008, price = 20.00, state = 1, effort = 1, effortType = 3, tagList =
list[website, layout, graphic], username = [null]]]
As you can see, the tagList is ordered in different ways.
Thank you very much for your help.
Corrado
Re: Wrong sorting when using orderBy clause
Posted by Corrado Alesso <co...@fastwebnet.it>.
A note to my previous post.
If I comment out the AND clause:
AND t1.state_id = 1
the problem cannot be reproduced.
:(
Re: Wrong sorting when using orderBy clause
Posted by Nathan Maves <na...@gmail.com>.
you have yet to give us your complete SQL for this select. That might
really help us to help you :)
On Dec 15, 2008, at 12:40 PM, Corrado Alesso wrote:
> In data 15 dicembre 2008 alle ore 20:29:29, Larry Meadors <larry.meadors@gmail.com
> > ha scritto:
>
>> You may have said this already, I took a quick scan and didn't see -
>> what version of ibatis? If not the latest, can you try it?
>>
>> Larry
>
>
> I'm using iBatis version 2.3.0.677
>
> I'll try the latest version immediately, but now I think that it's
> not iBatis "fault":
> I had the query running on a small Derby db and it works OK.
>
> Here some links of the relevant topics I found surfing.
>
> http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=83&t=001796
> http://archives.postgresql.org/pgsql-jdbc/2002-05/msg00032.php
>
> Thank you for your help, any idea is welcome.
> I will write the solution as soon as I undestand the problem.
>
> Corrado
Re: Wrong sorting when using orderBy clause
Posted by Corrado Alesso <co...@fastwebnet.it>.
In data 15 dicembre 2008 alle ore 20:29:29, Larry Meadors
<la...@gmail.com> ha scritto:
> You may have said this already, I took a quick scan and didn't see -
> what version of ibatis? If not the latest, can you try it?
>
> Larry
I'm using iBatis version 2.3.0.677
I'll try the latest version immediately, but now I think that it's not
iBatis "fault":
I had the query running on a small Derby db and it works OK.
Here some links of the relevant topics I found surfing.
http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=83&t=001796
http://archives.postgresql.org/pgsql-jdbc/2002-05/msg00032.php
Thank you for your help, any idea is welcome.
I will write the solution as soon as I undestand the problem.
Corrado
Re: Wrong sorting when using orderBy clause
Posted by Larry Meadors <la...@gmail.com>.
You may have said this already, I took a quick scan and didn't see -
what version of ibatis? If not the latest, can you try it?
Larry
Re: Wrong sorting when using orderBy clause
Posted by Corrado Alesso <co...@fastwebnet.it>.
In data 15 dicembre 2008 alle ore 19:07:58, Jeff Butler
<je...@gmail.com> ha scritto:
> If you turn on logging, then you can verify exactly what's coming back
> from the DB.
I will perform some deeper debugging, also in the driver code.
> My guess is that something's not quite right in your Service class.
> groupBy works best when there is a very simple getter/setter for the
> list.
I'm sorry, I should have provided these informations on my previous
messages. Yes, I do have simple (autogenerated by eclipse) getter and
setter. In the specific case of 'tagList', they are
public List<Tag> getTagList() {
return tagList;
}
public void setTagList(List<Tag> tagList) {
this.tagList = tagList;
}
Re: Wrong sorting when using orderBy clause
Posted by Jeff Butler <je...@gmail.com>.
If you turn on logging, then you can verify exactly what's coming back
from the DB.
My guess is that something's not quite right in your Service class.
groupBy works best when there is a very simple getter/setter for the
list. For example, in your Service class you should have something
like this:
public class Service {
private List<Tag> tagList;
// other properties
public List<Tag> getTagList() {
return tagList;
}
public void setTagList(List<Tag> tagList) {
this.tagList = tagList;
}
}
You don't need any more logic with the tagList property. Most of the
issues we see with groupBy are related to users trying to do something
more complex with the List properties.
Jeff Butler
On Mon, Dec 15, 2008 at 11:55 AM, Corrado Alesso
<co...@fastwebnet.it> wrote:
> Thanks for your reply.
>
> Yes, the query has an 'order by' on just those two columns.
>
> ORDER BY t1.id ASC, t2.tag ASC
>
>
> I can provide some more information to track down the problem...
>
> The query has a LIKE clause defined as
>
> WHERE LOWER(tag) LIKE LOWER('%$value$%')
>
>
> With pgAdmin I always get the correct results, no matter what I pass to the
> LIKE clause.
> Instead, when I refresh my page that fetches the data with iBatis, I get
> different results if I pass "grap", "graph", or "graphic" (I mean, different
> ordering in the 'tagList' property).
>
>
> Between the DB and iBatis there is Spring and the JDBC driver
> (postgresql-8.3-603.jdbc4.jar).
>
> I can provide a test program that reproduce this behaviour.
>
> If iBatis use, as expected, the order returned by the DB, maybe there's an
> issue in the driver?
>
> Please help me track down this problem :(
>
>
> Thank you,
> Corrado
>
>
> In data 15 dicembre 2008 alle ore 18:21:47, Jeff Butler
> <je...@gmail.com> ha scritto:
>
>> No - I have tests running that show this is not an issue.
>>
>> iBATIS will use the order returned from the database. Make sure your
>> SQL has an order by clause containing both id and tag.
>>
>> Jeff Butler
>>
>>
>> On Mon, Dec 15, 2008 at 11:03 AM, Corrado Alesso
>> <co...@fastwebnet.it> wrote:
>>>
>>> In data 14 dicembre 2008 alle ore 15:33:40, Corrado Alesso
>>> <co...@fastwebnet.it> ha scritto:
>>>
>>>> Hello everyone, I'm facing a problem with iBatis.
>>>
>>> [cut]
>>>
>>> Do you think that this should be filed as an iBatis issue?
>>>
>
>
>
Re: Wrong sorting when using orderBy clause
Posted by Corrado Alesso <co...@fastwebnet.it>.
Thanks for your reply.
Yes, the query has an 'order by' on just those two columns.
ORDER BY t1.id ASC, t2.tag ASC
I can provide some more information to track down the problem...
The query has a LIKE clause defined as
WHERE LOWER(tag) LIKE LOWER('%$value$%')
With pgAdmin I always get the correct results, no matter what I pass to
the LIKE clause.
Instead, when I refresh my page that fetches the data with iBatis, I get
different results if I pass "grap", "graph", or "graphic" (I mean,
different ordering in the 'tagList' property).
Between the DB and iBatis there is Spring and the JDBC driver
(postgresql-8.3-603.jdbc4.jar).
I can provide a test program that reproduce this behaviour.
If iBatis use, as expected, the order returned by the DB, maybe there's an
issue in the driver?
Please help me track down this problem :(
Thank you,
Corrado
In data 15 dicembre 2008 alle ore 18:21:47, Jeff Butler
<je...@gmail.com> ha scritto:
> No - I have tests running that show this is not an issue.
>
> iBATIS will use the order returned from the database. Make sure your
> SQL has an order by clause containing both id and tag.
>
> Jeff Butler
>
>
> On Mon, Dec 15, 2008 at 11:03 AM, Corrado Alesso
> <co...@fastwebnet.it> wrote:
>> In data 14 dicembre 2008 alle ore 15:33:40, Corrado Alesso
>> <co...@fastwebnet.it> ha scritto:
>>
>>> Hello everyone, I'm facing a problem with iBatis.
>>
>> [cut]
>>
>> Do you think that this should be filed as an iBatis issue?
>>
Re: Wrong sorting when using orderBy clause
Posted by Jeff Butler <je...@gmail.com>.
No - I have tests running that show this is not an issue.
iBATIS will use the order returned from the database. Make sure your
SQL has an order by clause containing both id and tag.
Jeff Butler
On Mon, Dec 15, 2008 at 11:03 AM, Corrado Alesso
<co...@fastwebnet.it> wrote:
> In data 14 dicembre 2008 alle ore 15:33:40, Corrado Alesso
> <co...@fastwebnet.it> ha scritto:
>
>> Hello everyone, I'm facing a problem with iBatis.
>
> [cut]
>
> Do you think that this should be filed as an iBatis issue?
>
Re: Wrong sorting when using orderBy clause
Posted by Corrado Alesso <co...@fastwebnet.it>.
In data 14 dicembre 2008 alle ore 15:33:40, Corrado Alesso
<co...@fastwebnet.it> ha scritto:
> Hello everyone, I'm facing a problem with iBatis.
[cut]
Do you think that this should be filed as an iBatis issue?