Hibernate getResultList() returning duplicates for a count query

Question

I am running a count query using Java 1.6 and Hibernate version 3.2.1 that I expect to return a resultList containing [2]. I am instead getting a result list containing [2, 2, 2, 2]. I turned on debugging to see the hibernate queries allowing me to see the same query being run four times.

The ejbql on the Query I'm running getResultList() on:

SELECT COUNT(*)FROM FlightLogItemEntity AS sectorItem  WHERE UPPER(sectorItem.sectorId) = :literal0 AND UPPER(sectorItem.logType) = :literal1

{literal0=0001006711, literal1=TL}

After running getResultList() only once, I see in my logs four identical queries being run:

[SQL] select count(*) as col_0_0_ from SECTOR_ITEM techlogite0_ where upper(techlogite0_.SECTOR_ID)=? and upper(techlogite0_.LOG_TYPE)=? 
[StringType] binding '0001006711' to parameter: 1 
[StringType] binding 'TL' to parameter: 2 
[LongType] returning '2' as column: col_0_0_ 
[SQL] select count(*) as col_0_0_ from SECTOR_ITEM flightlogi0_ where upper(flightlogi0_.SECTOR_ID)=? and upper(flightlogi0_.LOG_TYPE)=? 
[StringType] binding '0001006711' to parameter: 1 
[StringType] binding 'TL' to parameter: 2 
[LongType] returning '2' as column: col_0_0_ 
[SQL] select count(*) as col_0_0_ from SECTOR_ITEM flightlogi0_ where upper(flightlogi0_.SECTOR_ID)=? and upper(flightlogi0_.LOG_TYPE)=? 
[StringType] binding '0001006711' to parameter: 1 
[StringType] binding 'TL' to parameter: 2 
[LongType] returning '2' as column: col_0_0_ 
[SQL] select count(*) as col_0_0_ from SECTOR_ITEM cabinlogit0_ where upper(cabinlogit0_.SECTOR_ID)=? and upper(cabinlogit0_.LOG_TYPE)=? 
[StringType] binding '0001006711' to parameter: 1 
[StringType] binding 'TL' to parameter: 2 
[LongType] returning '2' as column: col_0_0_

At first I thought it might have to do with Entity relationships causing multiple queries to run, but after removing all relationships I still see the same duplicate queries being run.

The code generating the query (after setting the entityManager, ejbql, and Literals):

query.setFirstResult(-1);
query.setFlushMode(null);
query.setMaxResults(-1);
List<?> resultList = query.getResultList();

Does anyone know how I might prevent duplicate queries being run (to prevent duplicate results)?

Please show the code surrounding the call to getResultList(). — Sotirios Delimanolis
– Sotirios Delimanolis, Commented Jul 18, 2013 at 17:31
it's running a query in an Abstract class, try to create a native sql query or try to use getSingleResult instead of getResultList — fmodos
– fmodos, Commented Jul 18, 2013 at 17:43
The class is not actualy abstract. Removed to prevent confusion. — Thalador
– Thalador, Commented Jul 18, 2013 at 17:46
ok, could you edit the questio and post the structure of the class and the classes that extends it(if that is the case) — fmodos
– fmodos, Commented Jul 18, 2013 at 17:48

sparks · Accepted Answer · 2013-07-18 17:50:59Z

1

I'm not sure why its doing the query 4 times, but when you use query.setMaxResults(-1); you're setting it to return however many items it gets. http://docs.jboss.org/hibernate/orm/3.6/javadocs/org/hibernate/Query.html#setMaxResults(int)

Try changing query.setMaxResults(-1); to query.setMaxResults(1); This may just be a band-aid but it should allow you get just one result.

answered Jul 18, 2013 at 17:50

sparks

7622 gold badges9 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thalador Over a year ago

In my code set up we only run count if MaxResults is set to -1, so I can't use this idea. Thanks for the suggestion though.

sparks Over a year ago

Why do you only run it if MaxResults is set to -1? -1 in this case means infinite, all, the entirety of the response. If your entire query is SELECT COUNT(*) FROM [etc.] it should only return 1 value. Not trying to push my answer, just wondering why your team chose to do that.

Thalador Over a year ago

We only run it when we have maxrecords -1 because we need to make sure we don't get too much data. We have a hard-coded max, so we make sure the count is less than the hard max before running the actual query.

Collectives™ on Stack Overflow

Hibernate getResultList() returning duplicates for a count query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related