0

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?

George Mauer
  • 117,483
  • 131
  • 382
  • 612

1 Answers1

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.

dcernahoschi
  • 14,968
  • 5
  • 37
  • 59
  • 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? – George Mauer Jan 26 '12 at 20:58
  • 1
    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: http://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table – dcernahoschi Jan 26 '12 at 21:12
  • hmm...I also just found the setMaxResults method ... I wonder if that would get translated to a limit query/whatever the server supports – George Mauer Jan 26 '12 at 21:24
  • 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 Jan 26 '12 at 21:33
  • 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 – dcernahoschi Jan 26 '12 at 21:53
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/7081/discussion-between-dcernahoschi-and-george-mauer) – dcernahoschi Jan 26 '12 at 22:30
  • it exists in HQL too. Meantime I was curious which approach is the fastest and I've asked another question: http://stackoverflow.com/questions/9026184/an-efficient-way-to-test-if-a-table-row-exists/9026320#9026320 – dcernahoschi Jan 26 '12 at 22:46