Error in Hibernate: unexpected token: LIMIT near line ...

Why do I get the "unexpected token: LIMIT..." error?

Hibernate doesn't support the LIMIT token in a SELECT statement. Whereas in a Java SQL PreparedStatement to define a limit on the result set you can use the LIMIT command in the SELECT query.

SELECT * FROM person_table as person WHERE last_name = "Miller" LIMIT 0,20;

If you use Hibernate instead this command wouldn't work. You would get an error looking like:

"unexpected token: LIMIT near line 1 column..."

How to limit results for paging in a Hibernate query?

The proper way in Hibernate select queries would be to use:

query.setFirstResult(0);
query.setMaxResults(20);

Example on how to use paging in Hibernate HQL select query?

Below an example on how to execute a SELECT query with a limit on the returned result set in Hibernate. Especially if you want to use pagination through the results this example how to pass in a page size and page number and get the according results from the table.

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import java.util.List;
.
.
.

public List<Person> getPersonsByLastName(String lastName, int pageSize, int pageNum) throws Exception
{
 //unless you need only certain columns you don't need to add the SELECT part in the select query (HQL query) in Hibernate 
 String sqlQuery = "FROM Person as person where person.lastName=:lastName";
  
 Query query = workspace.getReadOnlyDbQuery(sqlQuery);

 // Pass all the parameters
 query.setParameter("lastName", lastName);
 query.setFirstResult(getStartIndex(pageSize, pageNum));
 query.setMaxResults(pageSize);
  
 List<Person> result = query.list();
 if (result != null && result.size() > 0) {
  return result;
 } else {
  return null;
 }
}

public Query getReadOnlyDbQuery(String queryString) throws Exception
{
 Session session = getReadOnlyDbSession();
 Query query = session.createQuery(queryString);
 query.setTimeout(5);
 return query;
}

//calculates the start index for pagination
private int getStartIndex(int pageSize, int pageNum) {
 if (pageNum < 1)
  return 0;

 return (pageNum - 1) * pageSize;
}