Friday, March 20, 2009

Getting Limit Query from Hibernate

This is a short note continuing older note about creating google wannabe paging, in case I want to write a note but Im not telling you yet how I create my own paging completely.

There are many ways how to get limited query in hibernate, there are HQL (Hibernate Query Language), Criterion, or native sql. I will tell you with the first way the HQL way. For me HQL is simply SQL using hibernate POJO (Plain Old Java Object), the object representative of tables. For example if the table named LOG_ACTIVITY the POJO could be named LogActivity. if the SQL looks like: "SELECT * FROM LOG_ACTIVITY", the HQL could simply looks like: "FROM LogActivity", and it has more functions.

In this note Im not only telling you about getting limitation records from 1 to 100, but also parameterized, such as getting records from yesterday to now only the first 100 records.
1. Get the searching criteria. The default value is "*". For example field username, if there is a value from the input such as "imam" then the username="imam", otherwise username="*"
2. Count the total record,
3. Have a constant of how many records shown in every page, for example 10, and it can parameterized also.
4. I divide total record with that constant so I get the total of pages
5. I do query with the same syntax when I search the count of the total records (the syntax I move into an own method), but this time is for getting the records not the count.
6. I pass all of the variables (page, totalPages, listPages, perPage, totRecord,
recordList, qName, qDate, etc etc)
- which page =page requested, foe example page 13
- totalPage for example 5
- listPages, so the values are: 11, 12, 13, 14, 15
- perPage, how many records shown in every page
- totRecord, because it will be shown in my application
- recordList
- qName, qDate, and other question as search parameter that saved in a session.

Anyway this is the code snipped, the first method to get the count of the record, the second is to get the actual records, while buildQueryString is method that resulting hql syntax with parameter criteria=names of the searching fields such as username, dateForm, dateTo, and the second parameter is question the values of the searching fields, I set "*" if null or empty.

public Long getLogActivitiesSearchingPagingCount(String criteria, String
question) {
String queryString = "select count(*) " + buildQueryString(criteria, question);
Long total = (Long) getSession().createQuery(queryString).uniqueResult();

return total;
}

public List getLogActivitiesSearchingPaging(int start, int length, String
criteria, String question) {
List logActivities = getSession().createQuery(buildQueryString(criteria, question))
.setFirstResult(start)
.setMaxResults(length).list();
return logActivities;
}

No comments: