Hello,

This is my first post to the forum, so all my appologies if I make any mistake in the form of the post.

We are now working with DetachedCriterias to get a list of objects in the database, just like that :

It's working great, and the transformer removes the duplicate items returned by the criteria search (that we still don't know why there are duplicates but anyway, it does) perfectly (as we expected).

Now, we are changing it a little, we want to get the results 4 to 8. Here is what we do :

The problem is that the list now contains only 1 item, where the previous list contained 20+ items. In fact, we think that the findByCriteria(criteria,int,int) is scaling the result before it removes the duplicate. That's a very big problem. We don't know how to do except like that. We were expecting the findByCriteria to scale after it did all the criteria things.

Are we wrong thinking that way ? If so, can someone kindlt indicate us where we are wrong, and how we can fix this ?

Thanks a lot

Fred

No-one has a clue about this problem ?
We tried to solve it but we didn't find any solution (yet)

Hi Mavina,

removing duplicates by DistinctRootEntityResultTransformer and fetching desired number of results from criteria works for me in following way.
I am using MySQL db.
Table :
CREATE TABLE Sample
(
id INT,
title VARCHAR(30),
heading VARCHAR(50),
detail VARCHAR(500)
);

Result of select query :
mysql> select * from sample;
+------+-----------+---------+--------+
| id | title | heading | detail |
+------+-----------+---------+--------+
| 1 | Bangalore | | |
| 1 | Bangalore | | |
| 3 | Chennai | | |
| 1 | Bangalore | | |
| 4 | Pune | | |
| 4 | Pune | | |
| 4 | Pune | | |
| 5 | Hyderabad | | |
| 5 | Delhi | | |
| 6 | Delhi | | |
+------+-----------+---------+--------+
10 rows in set (0.00 sec)

Following is the code i have used to fetch records :
1. DetachedCriteria dCriteria = DetachedCriteria.forClass(Sample.class);
dCriteria.setResultTransformer(new org.hibernate.transform.DistinctRootEntityResultTransformer());
List<Sample> usrs = getHibernateTemplate().findByCriteria(dCriteria);
2. System.out.println("\n usrs..."+usrs !=null ? usrs.size() : null);
// to fetch desired rows
3. usrs = getHibernateTemplate().findByCriteria(dCriteria, 2, 4);
4. System.out.println("\n usrs..."+usrs !=null ? usrs.size() : null);

Following are the queries built during execution of above statements:
Hibernate: select this_.id as id6_0_, this_.detail as detail6_0_, this_.heading as heading6_0_, this_.title as title6_0_ from Sample this_
----Result set size for st at line 2 is => 5

Hibernate: select this_.id as id6_0_, this_.detail as detail6_0_, this_.heading as heading6_0_, this_.title as title6_0_ from Sample this_ limit ?, ?
----Result set size for st at line 4 is=> 3


So the queries are returning distinct rows at first and second time returns rows from 2 to 4.