Sql query for pagination using hibernate and extjs paging toolbar

Question

Here is my query:

String queryString = "SELECT A,B,C,D from Table1 T1,TABLE2 T2"
              +" WHERE T1.A=T2.D"
              +" AND T1.B=T2.C";

int start=0,limit=15;

Here I am executing queryString by setting start and limit

List queryList = executeReadAllSQLQuery(queryString);


int totalcount = queryList.size();
System.out.println("before => " +totalcount);
String PaginationQuery = queryString+" LIMIT "+ start +","+ limit;
List queryList2 = executeReadAllSQLQuery(PaginationQuery); // **ERROR/EXCEPTION**

Exception : Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended

Please help me resolve this issue.

I want to do pagination for that i have set start and limit.I want to fetch records based on the start and limit from database. — Dev, May 07 '13 at 10:46
(BTW, a limit clause without an order by clause is almost always a bug) — Mat, May 07 '13 at 10:49
if you are using oracle then it does not support limit clause.. — Mahesh Patidar, May 07 '13 at 10:49
http://stackoverflow.com/questions/5634841/java-persistance-query-language-limit-resultset — Elior, May 07 '13 at 10:49
If you're using Hibernate, he will do this for you, why are you still executing native sql queries and using hibernate? — Johan Haest, May 07 '13 at 10:56

score 1 · Answer 1 · answered May 07 '13 at 11:22

In Hibernate use this one please to limit your selection:

List result = session.createQuery("from TableName").setFirstResult(0).setFetchSize(200).list();

This will Select like this one limit 0,200

you can write the values you want in setFirstResult and setFetchSize

This one will be general with any DATABASE ENGINE

Regards,

Sql query for pagination using hibernate and extjs paging toolbar

1 Answers1