Limiting size of query results set

barryoreilly · **Joined:** Thu Sep 23, 2004 6:03 am **Posts:** 27

I want to limit the result set for a particual query using hibernet.

I looked at the documentation and came across setMaxResults() the only problem is that once that method is called it is applied to all queries in the entire system. Is there anyway round this? This is what my method currently looks like

Code:

   public List getIntellectualPropertyByQuery(String query) {
      
      // set limit
      getHibernateTemplate().setMaxResults(Integer.parseInt(Constants.SEARCH_LIMIT));
      
      return getHibernateTemplate().find( query );
   }

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:3

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:MySQL 4.1.10

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Code:

steckemetz · **Posted:** Wed Jun 21, 2006 2:51 am

Hi,

there is also a method

Code:

public Query setMaxResults(int maxResults);

in the Query interface. This should not be global.

barryoreilly · **Joined:** Thu Sep 23, 2004 6:03 am **Posts:** 27

I figured out the problem there are two ways it can be done:

Code:

Session session = SessionFactoryUtils.getSession(getHibernateTemplate().getSessionFactory(), true);
HibernateTemplate ht = new HibernateTemplate( session   );

ht.setMaxResults(Integer.parseInt(Constants.SEARCH_LIMIT)); 
return ht.find( query ); 

or

Code:

String queryString = "from User...." //or whatever
Query query = getSession().createQuery(queryString);
query.setMaxResults(Integer.parseInt(Constants.SEARCH_LIMIT));
return query.list();

ahjiang · **Joined:** Fri Jul 07, 2006 2:24 am **Posts:** 1

Code:

        
Query myQuery = session.createQuery("from LogDetail");
myQuery.setMaxResults(2);
List result = myQuery.list();
session.getTransaction().commit();

I having this in my code and i getting the following error

Code:

     [java] Hibernate: select top ? logdetail0_.LOG_NUMBER as LOG1_0_, logdetail
0_.DATETIME as DATETIME0_, logdetail0_.level as level0_, logdetail0_.message as
message0_ from LOG_DETAIL logdetail0_
     [java] 07 Jul 2006 14:37:06 [main] ERROR org.hibernate.util.JDBCExceptionRe
porter - You have an error in your SQL syntax; check the manual that corresponds
 to your MySQL server version for the right syntax to use near '2 logdetail0_.LO
G_NUMBER as LOG1_0_, logdetail0_.DATETIME as DATETIME0_, logdeta' at line 1

Any idea whats wrong with this?
Appreciate any help

CWitherow · **Posted:** Fri Jul 07, 2006 12:49 pm

Looks like your Dialect is not correct for the version of MySQL you are running. Since there is wide variation in how different DBMS implement limiting of resultsets, you need to load the appropriate Dialect.

Curtis ...