How to test if a table is empty, using Hibernate

Question

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.

Answer 1

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.

Answer 2

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?