5

Using Hibernate, what is the most efficient way to determine if a table is empty or non-empty? In other words, does the table have 0, or more than 0 rows?

I could execute the HQL query select count(*) from tablename and then check if result is 0 or non-0, but this isn't optimal as I would be asking the database for more detail than I really need.

Landon Kuhn
  • 76,451
  • 45
  • 104
  • 130

2 Answers2

4

A lot of databases are efficient at returning a count of records in a table, but if you want to be creative, how about session.createQuery("select 1 from table").setMaxSize(1).list().isEmpty()?

Or: session.createQuery("select 1 from table").setFetchSize(1).scroll(ScrollMode.FORWARD_ONLY).next() == null

I think it will depend on the database as to which method is the fastest.

Brian Deterling
  • 13,556
  • 4
  • 55
  • 59
3

The following SQL is a very efficient way to see if a table contains a row:

SELECT EXISTS (SELECT NULL FROM tablename)

I'm not sure how to convert it to HQL - maybe it just works?

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452