I have several queries which are used to display results in a web page. My original code used the criteria API to formulate these queries:
Code:
Criteria crit = session.createCriteria(Learner.class);
if (learnerCriteria.getFirstNames() != null) {
crit.add(Expression.ilike("firstNames", learnerCriteria.getFirstNames(), MatchMode.START));
}
if (learnerCriteria.getSurname() != null) {
crit.add(Expression.ilike("surname", learnerCriteria.getSurname(), MatchMode.START));
}
if (learnerCriteria.getUserName() != null) {
crit.add(Expression.ilike("userName", learnerCriteria.getUserName(), MatchMode.START));
}
All the results where displayed on a single page.
With the increase in the number of learners, a request was made to display these results in a scrollable manner (page-by-page), with a customizable range size, hyperlinks to other offsets and a display of the total number of results. To implement this I used the
Query interface, calling scroll() as follows:
Code:
StringBuffer sql = new StringBuffer(300);
sql.append("FROM Learner l WHERE 1=1 ");
if (learnerCriteria.getFirstNames() != null) {
sql.append("WHERE UPPER(l.firstNames) like :firstNames ");
}
if (learnerCriteria.getSurname() != null) {
sql.append("WHERE UPPER(l.surname) like :surname ");
}
if (learnerCriteria.getUserName() != null) {
sql.append("WHERE UPPER(l.userName) like :userName ");
}
sql.append("ORDER by l.userName ");
// bind parameters
Query query = session.createQuery(sql.toString());
if (learnerCriteria.getFirstNames() != null) {
query.setString("firstNames", learnerCriteria.getFirstNames().toUpperCase() + "%");
}
if (learnerCriteria.getSurname() != null) {
query.setString("surname", learnerCriteria.getSurname().toUpperCase() + "%");
}
if (learnerCriteria.getUserName() != null) {
query.setString("userName", learnerCriteria.getUserName().toUpperCase() + "%");
}
ScrollableResults scroller = query.scroll();
RangedQueryResult rangedResult = new RangedQueryResult();
rangedResult.setRangeSize(rangeSize);
// set up the ranged result
if (scroller.last()) {
rangedResult.setTotalSize(scroller.getRowNumber());
rangedResult.setRangeOffSet(offSet);
if (offSet <= 0) {
scroller.beforeFirst();
} else {
scroller.scroll(offSet);
}
} else {
rangedResult.setTotalSize(0);
rangedResult.setItems(Collections.EMPTY_LIST);
rangedResult.setRangeOffSet(0);
return rangedResult;
}
// populate the selected contents
List results = new ArrayList();
int count = 1;
while (scroller.next() && (count < rangeSize)) {
results.add(scroller.get()[0]);
count++;
}
rangedResult.setItems(results);
return rangedResult;
where
RangedQueryResult is the following:
Code:
public class RangedQueryResult implements Serializable {
private List items; // the actual items selected to display (may be less than rangeSize)
private int rangeSize; // the maximum size of the items in the items list
private int rangeOffSet; // the offset at which the subset of items starts in the greater scheme of things
private int totalSize; // the total size of the results returned by the query
//... getters and setters
}
As you can imaging, I would have preferred to just have called criterial.scroll()
(Note - I haven't run/tested the new code yet)
Justin
Login
FAQ
Search
