Hibernate hql create 'Any' query

Question

I hope you guys forgive me ... I know this is simple but its proving impossible to google.

I want to write the following hql query: "Are there any instances of Person with name = 'Bob'"

I know I can do a count but that seems like it will unnecessarily chew up processing power when I don't actually need to enumerate all rows.

What is the hql query to do this?

dcernahoschi · Accepted Answer · 2012-01-26 20:21:18Z

1

Query personsQuery = session.createQuery("from Person p where p.name = 'Bob'");
if(personsQuery.iterate().hasNext()) {
    //there is at least one Bob
}

only the primary keys of person are loaded in memory.

or

ScrollableResultSet scroll = session.createQuery("from Person p where p.name = 'Bob'").scroll();

if(scroll.next()) {
    //there is at least one Bob
}

scroll.close();

nothing is loaded in memory, just a database cursor is opened.

edited Jan 26, 2012 at 20:21

answered Jan 26, 2012 at 20:13

dcernahoschi

15.3k5 gold badges40 silver badges62 bronze badges

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

7 Comments

George Mauer Over a year ago

Nothing is loaded in memory, but is a full query executed on the db? Say you have 200 million rows - will it need to check all of them or just iterate till it finds one? It seems like I should be able to signify to the server "I only want that first result". Also, are cursors really supported by SQL Server? MySQL?

dcernahoschi Over a year ago

From the hibernate point of view is the maximum you can do. MySQL, supports cursors, I don't now about SQL Server. What happens at the database level, how the query is executed depends on the database. For MySQL only it seems the best solution is this: stackoverflow.com/questions/1676551/…

George Mauer Over a year ago

hmm...I also just found the setMaxResults method ... I wonder if that would get translated to a limit query/whatever the server supports

dcernahoschi Over a year ago

Yes. I forgot about it. setMaxResult should also improve the performance and hibernate should know how to adapt the sql according to the database in use.

dcernahoschi Over a year ago

Hmmm SELECT EXISTS(SELECT 1 FROM table1 WHERE ...) should work on any database, EXISTS is included in SQL standard. It should be more efficient than SELECT 1 FROM table1 WHERE LIMIT 0, 1

|

Collectives™ on Stack Overflow

Hibernate hql create 'Any' query

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related