I need to check if a row exists in a database in a very fast way. Let's say I've got the primary key. I found this code snippet in Hibernate's FAQ website:
Integer count = (Integer) session.createQuery("select count(*) from ....").uniqueResult();
I just started using spring, so I have HibernateTemplate object injected into my class. How do I translate this snippet to work with HibernateTemplate.
Does anyone knows a better/faster way than this ?
Thanks.
Long count = hibernateTemplate.execute(new HibernateCallback<Long>() {
@Override
public Long doInHibernate(Session session) {
return (Long) session.createQuery("select count(someEntity.id) from SomeEntiuty someEntity ...").uniqueResult();
}
});
Hibernate used Integer for count queries before, but now uses Long. Also, note that even if not deprecated, Spring recommends not to use HibernateTemplate anymore and use the Hibernate API directly (using sessionFactory.getCurrentSession()).
Fastest way of checking primary key exist or not in database.
public void exist(Long id) {
Session session = sessionFactory.getCurrentSession();
String queryString = "select 1 from Employee e where e.id= :id";
Query query = session.createQuery(queryString);
query.setParameter("id", 1l);
Integer result = (Integer) query.uniqueResult();
System.out.println(result);
}
Again this also depends on a lot on what engine that you are using MyISAM vs innodb.
select count(col1) from table; will return the number of rows where the column is not null.
select count(*) from table; will return the number of rows.
Depending upon the database that you are using , a select count(*) will be more expensive than reading it from meta data table or system level tables that keep track of the row count.
Just my 2 cents.
Depending upon various other factors like indexes and other information / joins / access privileges this may be faster
SELECT table_rows FROM `information_schema`.`TABLES` where table_schema = 'database_schema_name' and table_name = 'table_name';
I think it's better to get an specific representative field of the first row found (using the PK or at least another indexed field), than counting all of the possible records that would match your search criteria.
If you're using Spring it will throw EmptyResultDataAccessException if no record was found.
