Non-empty list with null elements returned from Hibernate query

Question

I'm new to hibernate so not sure if this is an expected behaviour, anyway:

Session session = (Session)entityManager.getDelegate();
Criteria criteria = session.createCriteria(myRequest.class);
criteria.add(Restrictions.eq("username", username));
criteria.setProjection(Projections.max("accesscount"));
List<myRequest> results = criteria.list();

The returned results is a non-empty list with a single null element.

I can't think of any reason why it should behave this way, any idea if this is the expected behaviour or have I done something wrong?

System is on hibernate/Syabse.

Thanks.

Affe · Accepted Answer · 2010-05-06 07:22:13Z

3

It's only surprising if there was a user with that username who had a non-null value n the accesscount column. Your return type leads one to believe that you were trying to get the instance of myRequest that has the max accesscount? this is not what that query does. The query is the equivalent of

select max(m.accesscount) from myRequest m where m.username = :username

It's just (trying to) return a number, not a myRequest.

edited May 6, 2010 at 7:22

answered May 6, 2010 at 6:48

Affe

48.1k12 gold badges85 silver badges86 bronze badges

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

5 Comments

John Over a year ago

Thanks! That's what I was trying to do, any idea what the corresponding hibernate query would be? ... I would very much like to avoid the obvious order by then select first, thanks again.

Affe Over a year ago

Tough to say without seeing the entire table. Is username + accesscount unique? Select m from myRequest m where m.username = :username and accesscount = (select max(m.accesscount) from myRequest m where m.username = :username) ??? Not necc guaranteed to return a single result.

John Over a year ago

Say if we assume there is always a unique maximum access, how would I approach it? My main problem is really lying in how do I get the "having max(m.accesscount)" going in the hibernate version of SQL.

Affe Over a year ago

having max(accesscount) what? Having is a restriction clause, you need to compare it to something. max(accesscount) > 0? max(accesscount) not null? If username + accesscount is unique then the query given above will work. The problem with using grouping if you want a managed entity as your return type, is you have to put every property that's on the object into the group by clause. This can make it hard to get a query that does the aggregation you wanted in the first place! :)

Affe Over a year ago

It may be helpful to look at your overall architecture and performance needs and decide if it's easier to just write a scalar query that returns the ID of the Entity you want, and then do a findById. If it's a high traffic item that's likely to be hit in the L2 cache, or even part of a collection of data you already have in your session cache, you're effectively still only executing one query.

Collectives™ on Stack Overflow

Non-empty list with null elements returned from Hibernate query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related