What is the Hibernate equivalent of "select 1 from DUAL"?

Question

What is the minimal Hibernate query? Is there an equivalent of "select 1 from DUAL" for Hibernate?

I am writing a healthcheck function and would like to execute a minimal query, but use HQL to keep the code portable (as opposed to using a native query).

Answer 1

Hibernate's dialects are what translate to the various databases, and there's no "health check" or "validation" query support in the dialects. This kind of thing is normally done by the connection pool, not by Hibernate. See, e.g., DBCP's validation query property.

Answer 2

The SQL select 1 from DUAL; is not a very good health check, the only thing you see is a server response that doesn't tell if the data itself is healthy. But if you want to make such a question, just make your own data entity that simulates DUAL and you're in control of the object and you'll stay generic for any RDBMS.

Something like

@Entity
@Table(name="myDual")
public class MyDual implements Serializable {

  @Id
  @Column(name = "id")
  Integer id;

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

}

Don't forget to use CacheMode.IGNORE or CacheMode.REFRESH to force the query. Otherwise you might check the cache and not the database...

Answer 3

Dynamic native sql query will work for it. for example - session.createSQLQuery("Select 1 from dual").uniqueResult();

Also you can query with an existing table like

session.createQuery("select 1 from existingTable").setMaxResults(1).uniqueResult();