CODEWITHSUNDEEP
hibernate
Landon Kuhn
Landon Kuhn

Reputation: 78471

How to test if a table is empty, using Hibernate

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.

Upvotes: 5

Views: 6263

Answers (2)

Brian Deterling
Brian Deterling

Reputation: 13734

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.

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 838796

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?

Upvotes: 3

Related Questions