Pageable data list with Hibernate

[yfmoan]

hihi ... l have a funny solution ,

my DAO - HiberBookDao.java

..
	public Map findBooksByCriteriaWithTotal(BookCommand bookCommand)
			throws DataAccessException {
		
		ArrayList books = (ArrayList) findBooksByCriteriaWithLimit(bookCommand);
		Integer totalFound = findBooksTotalByCriteria(bookCommand);

		Map model = new HashMap();
		model.put("_totalElements", totalFound);
		model.put("_resultset", books);

		return model;
	}

	private BookCommand makeMCommand(BookCommand source){
		
		int m = (source.getS_page() == 0)? 0 : source.getS_page()-1;
		BookCommand target = new BookCommand();
		BeanUtils.copyProperties(source,target);
		target.setS_page(m);
		return target;
	}
	
	public Collection findBooksByCriteriaWithLimit(BookCommand bookCommand) 
			throws DataAccessException {

		final int page = bookCommand.getS_page();
		final BookCommand nbookCommand = bookCommand;
		final BookCommand mbookCommand = makeMCommand(bookCommand);
		final Book book = bookCommand.getBook();
		final Author iAuthor = bookCommand.getAuthor();
		final Publisher iPublisher = book.getPublisher();

		return getHibernateTemplate().executeFind(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						
						StringBuffer queryString = new StringBuffer();
						boolean conditionFound = false;
						String title = book.getTitle();
						String isbn = book.getIsbn();
						String authorName = iAuthor.getAuthorName();
						String publisherName = iPublisher.getPublisherName();
						
						if(title != null){
							queryString.append("lower(b.title) like :title ");
							conditionFound=true;
						}
						if(isbn != null){
							if (conditionFound) queryString.append("and ");
							queryString.append("lower(b.isbn) like :isbn ");
							conditionFound=true;
						}
						if (authorName != null) {
							if (conditionFound) queryString.append("and ");
							queryString.append("lower(a.authorName) like :authorName ");
							conditionFound=true;
						}
						if (publisherName != null) {
							if (conditionFound) queryString.append("and ");
							queryString.append("lower(p.publisherName) like :publisherName ");
							conditionFound=true;
						}
						
						String fromClause = conditionFound ?
								"from Book b " +
								"left join fetch b.authors a " +
								"left join fetch b.publisher p where " 
								: 
								"from Book b " +
								"left join fetch b.authors a " +
								"left join fetch b.publisher p ";
						
						queryString.insert(0, fromClause).append("order by b.title");
						
						Query query = getSession().createQuery( queryString.toString() );
						
						if (title != null)
							query.setString( "title",'%' + title.toLowerCase() + '%' );
						if (isbn != null)
							query.setString( "isbn",'%' + isbn.toLowerCase() + '%' );
						if (authorName != null)
							query.setString( "authorName",'%' + authorName.toLowerCase() + '%' );
						if (publisherName != null)
							query.setString( "publisherName",'%' + publisherName.toLowerCase() + '%' );
						
						if(page == 0){
							query.setFirstResult(0)
							 	 .setMaxResults(pageNodeByCriteria(nbookCommand).intValue());
						}else{
							query.setFirstResult(pageNodeByCriteria(mbookCommand).intValue())
							 	 .setMaxResults(pageNodeByCriteria(nbookCommand).intValue()
									 		- pageNodeByCriteria(mbookCommand).intValue());
						}
						
						Set distinctResults = new HashSet(query.list());
						List result = new ArrayList(distinctResults);
						Collections.sort(result,new BookNameComparator());

						return result;
					}
			});

	}

	public Integer pageNodeByCriteria(BookCommand bookCommand)
			throws DataAccessException {
		
		final Book book = bookCommand.getBook();
		final Author iAuthor = bookCommand.getAuthor();
		final Publisher iPublisher = book.getPublisher();
		final int page = bookCommand.getS_page();
		final int pageSize = bookCommand.getS_pageSize();
		
		return (Integer) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {

						StringBuffer queryString = new StringBuffer();
						boolean conditionFound = false;
						String title = book.getTitle();
						String isbn = book.getIsbn();
						String authorName = iAuthor.getAuthorName();
						String publisherName = iPublisher.getPublisherName();
						
						if(title != null){
							queryString.append("lower(b.title) like :title ");
							conditionFound=true;
						}
						if(isbn != null){
							if (conditionFound) queryString.append("and ");
							queryString.append("lower(b.isbn) like :isbn ");
							conditionFound=true;
						}
						if (authorName != null) {
							if (conditionFound) queryString.append("and ");
							queryString.append("lower(a.authorName) like :authorName ");
							conditionFound=true;
						}
						if (publisherName != null) {
							if (conditionFound) queryString.append("and ");
							queryString.append("lower(p.publisherName) like :publisherName ");
							conditionFound=true;
						}
						
						String fromClause = conditionFound ?
								"select count(*) from Book b " +
								"left join b.authors a " +
								"left join b.publisher p where " 
								: 
								"select count(*) from Book b " +
								"left join b.authors a " +
								"left join b.publisher p ";
						
						queryString.insert(0, fromClause).append("group by b.id order by b.title");
						
						Query query = getSession().createQuery( queryString.toString() );
						
						if (title != null)
							query.setString( "title",'%' + title.toLowerCase() + '%' );
						if (isbn != null)
							query.setString( "isbn",'%' + isbn.toLowerCase() + '%' );
						if (authorName != null)
							query.setString( "authorName",'%' + authorName.toLowerCase() + '%' );
						if (publisherName != null)
							query.setString( "publisherName",'%' + publisherName.toLowerCase() + '%' );
						
						Iterator iter = query.setFirstResult(0)
							 				 .setMaxResults((page+1) * pageSize)
							 				 .list()
							 				 .iterator();
						
						int sum = 0;
						while ( iter.hasNext() ) {
							Integer count = (Integer) iter.next();
							sum = sum + count.intValue();
						}
						
						return new Integer(sum);				
						
			}
		});
		
	}
	
	public Integer findBooksTotalByCriteria(BookCommand bookCommand)
			throws DataAccessException {
		
		final Book book = bookCommand.getBook();
		final Author iAuthor = bookCommand.getAuthor();
		final Publisher iPublisher = book.getPublisher();

		return (Integer) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {

						StringBuffer queryString = new StringBuffer();
						boolean conditionFound = false;
						String title = book.getTitle();
						String isbn = book.getIsbn();
						String authorName = iAuthor.getAuthorName();
						String publisherName = iPublisher.getPublisherName();
						
						if(title != null){
							queryString.append("lower(b.title) like :title ");
							conditionFound=true;
						}
						if(isbn != null){
							if (conditionFound) queryString.append("and ");
							queryString.append("lower(b.isbn) like :isbn ");
							conditionFound=true;
						}
						if (authorName != null) {
							if (conditionFound) queryString.append("and ");
							queryString.append("lower(a.authorName) like :authorName ");
							conditionFound=true;
						}
						if (publisherName != null) {
							if (conditionFound) queryString.append("and ");
							queryString.append("lower(p.publisherName) like :publisherName ");
							conditionFound=true;
						}
						
						String fromClause = conditionFound ?
								"select count(distinct b.id) from Book b " +
								"left join b.authors a " +
								"left join b.publisher p where " 
								: 
								"select count(distinct b.id) from Book b " +
								"left join b.authors a " +
								"left join b.publisher p ";
						
						queryString.insert(0, fromClause);
						
						Query query = getSession().createQuery( queryString.toString() );
						
						if (title != null)
							query.setString( "title",'%' + title.toLowerCase() + '%' );
						if (isbn != null)
							query.setString( "isbn",'%' + isbn.toLowerCase() + '%' );
						if (authorName != null)
							query.setString( "authorName",'%' + authorName.toLowerCase() + '%' );
						if (publisherName != null)
							query.setString( "publisherName",'%' + publisherName.toLowerCase() + '%' );
						
						return (Integer)query.uniqueResult();				
						
			}
		});
		
	}

to be continue...

[yfmoan]

BookCommand.java

package org.yourschool.library.domain.command;

import org.yourschool.library.domain.Author;
import org.yourschool.library.domain.Book;
import org.yourschool.library.domain.Note;
import org.yourschool.library.domain.OtherTitle;
import org.yourschool.library.domain.Publisher;
import org.yourschool.library.domain.Series;
import org.yourschool.library.domain.Subject;

public class BookCommand extends BaseCommand {

	private Book book;
	private Author author;
	private OtherTitle otherTitle;
	private Series series;	
	private Subject subject;


	public BookCommand() {
		
		this.book = new Book();
		book.setNote(new Note());
		book.setPublisher(new Publisher());
		
		this.author = new Author();
		this.otherTitle = new OtherTitle();
		this.series = new Series();
		this.subject = new Subject();
		
	}


	public Author getAuthor() {
		return author;
	}


	public Book getBook() {
		return book;
	}


	public OtherTitle getOtherTitle() {
		return otherTitle;
	}


	public Series getSeries() {
		return series;
	}


	public Subject getSubject() {
		return subject;
	}

	
	
}

TotalPagingTag.java ,

package org.simplePage.web.tags;

import java.util.Map;
import java.util.List;
import java.io.IOException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.jsp.tagext.BodyTagSupport;
import javax.servlet.jsp.JspException;
import org.simplePage.web.tags.support.LinkEncoder;
import org.simplePage.web.util.RequestUtil;

public class TotalPagingTag extends BodyTagSupport {

    private Map parameters;
    private String modelName ;
    private int listSize;
    private int pageSize;
    private int page;
    private int totalElements;
    private String previousPageLink;
    private String nextPageLink;
    public static final String PAGE = "s_page";
    public static final String PAGE_SIZE = "s_pageSize";
    //private LinkEncoder UrlEncoder = new LinkEncoder();
    
    public void setModelName(String modelName){
        this.modelName = modelName;
    }

    public int doStartTag() throws JspException {
                                                	
           initProperties();

           if(pageSize > 0){
                try{
              	    writePaging();
                }catch(IOException ex) {
                    ex.getMessage();
                }
           }

           return SKIP_BODY;
    }

    public int doEndTag() throws JspException {

           return EVAL_PAGE;
    }

    public void initProperties(){

           HttpServletRequest request = (HttpServletRequest) pageContext.getRequest();
           this.parameters = RequestUtil.getRequestParameterMap(request);
    
           String Page = (String)parameters.get(PAGE);

           if (Page == null || Page.equals("")){
                 this.page = 0;
           }else{
                 this.page = Integer.parseInt(Page);
           }

           String PageSize = (String)parameters.get(PAGE_SIZE);

           if (PageSize == null || PageSize.equals("")){
                 this.pageSize = 0;
           }else{
                 this.pageSize = Integer.parseInt(PageSize);
           }

           this.listSize = ((List)((Map)request.getAttribute(modelName)).get("_resultset")).size();
           this.totalElements =  ((Integer)((Map)request.getAttribute(modelName)).get("_totalElements")).intValue();

           this.previousPageLink = LinkEncoder.encode(makePreviousPageParameters(parameters));
           this.nextPageLink = LinkEncoder.encode(makeNextPageParameters(parameters));

    }

    public void writePaging() throws IOException {

           StringBuffer sb = new StringBuffer();

           //Display total number
           sb.append("|").append(totalElements).append("| .......");
           
           if (isPreviousPage()) {
                        sb.append("<a href=\"?").append(previousPageLink);
                        sb.append("\"><font color=\"blue\"><B>&lt;&lt;&lt;</B></font></a>\n");
           }
                        sb.append("&nbsp;");
           if (isNextPage()) {
                        sb.append("<a href=\"?").append(nextPageLink);
                        sb.append("\"><font color=\"blue\"><B>&gt;&gt;&gt;</B></font></a>\n");
           }

           //Print out to jsp
           pageContext.getOut().print(sb.toString());

    }

    public Map makePreviousPageParameters(Map parameters){

           int previousPage;
           previousPage = page-1;
           //NumberFormatException
           parameters.put(PAGE,""+previousPage);
           return parameters;
    }

    public Map makeNextPageParameters(Map parameters){

           int nextPage;
           nextPage = page+1;
           parameters.put(PAGE,""+nextPage);
           return parameters;
    }

    public boolean isNextPage() {
           return listSize > pageSize-1;
    }

    public boolean isPreviousPage() {
           return page > 0;
    }

}

moon

[yfmoan]

l just found a thread form the hibernate forum "*weep* solving paginated non-distinct eager fetch"
http://forum.hibernate.org/viewtopic.php?t=936589 ,

the recommendation from the hibernate developer michael said that :

the only advice I can give you is dont use eager fetching when doing pagination.

.....

moon

[vasil]

This is just a specific implementation of the TableModel, targeted at browsing through large tabular data sets. Of course you can provide your own TableModel implementation that is more suitable in other cases.

---

A natural extension of this approach is a generic implementation of the table model that is based not necessarily on SQL, but has as configurable properties a bean and method for getting the id's and a method for getting the records with those id's - let's say, MethodInvokingTableModel.

The advantage is that then you can use Hibernate instead of SQL, with potential more stuff like fetching nested objects .

---

Other good upgrades starting from these models are to have optional search filters, which works both with the JDBC and Hibernate model, that gives a lot of flexibility too and adds for faster development.