First of all, look at a business requirement: fuzzy query data by book name and paging function. Usually, you can write the query method as follows, but it is troublesome if there are many query dimensions.

A new BaseDao is written to deal with this problem. The BaseDao code is as follows:

package com.zking.eight.util;

import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.hibernate.Session;
import org.hibernate.query.Query;

/**
*/
public class BaseDao {

/**
 * How to assign a value to query
 * @param query
 * @param map
 */
private void setParamter(Query query,Map<String, Object> map) {
	if(map==null||map.size()==0) {//Return directly if no query criteria are passed
		return;
	}
	
	Object value=null;
	for (Entry<String, Object> entry : map.entrySet()) {//Otherwise, the map set is traversed to assign a value to the named parameter
		value=entry.getValue();
		if(value instanceof Collection) {//If the value passed is a set
			query.setParameterList(entry.getKey(), (Collection) value);
		}
		else if(value instanceof Object[]) {//If the value passed is an array
			query.setParameterList(entry.getKey(), (Collection) value);
		}
		else {//If the value passed is of another type
			query.setParameter(entry.getKey(), value);
		}
	}
}

/**
 * How to get the hql statement that queries the total number of records
 * @param hql
 * @return
 */
private String getCountHql(String hql) {
	/*
	 * Because the hql statement might be: select * from Book
	 * 		      Or: select * From Book
	 * Therefore, you need to first change a whole sentence hql to lowercase or uppercase, find the location of from, and then cut down the content after from to assemble the required statement
	 */
	int indexOf = hql.toLowerCase().indexOf("from");
	return "select count(1) from "+hql.substring(indexOf);
}

/**
 * Method to call
 * @param hql
 * @param pageBean
 * @param map
 * @param session
 * @return
 */
public List executeQuery(String hql,PageBean pageBean,Map<String, Object> map,Session session) {
	if(pageBean!=null&&pageBean.isPagination()) {//If paging is required
		String countHql = getCountHql(hql);//hql statement to get the total number of records queried
		Query countQuery = session.createQuery(countHql);
		this.setParamter(countQuery, map);//Assign a value to query
		String total =countQuery.getSingleResult().toString();//Find out the result
		pageBean.setTotal(total);//Assign the result to the total attribute of pageBean
		
		Query pageQuery = session.createQuery(hql);
		this.setParamter(pageQuery, map);
		pageQuery.setFirstResult(pageBean.getStartIndex());
		pageQuery.setMaxResults(pageBean.getRows());
		return pageQuery.list();//Return the result after paging
	}
	else {//If paging is not required
		Query query = session.createQuery(hql);
		this.setParamter(query, map);
		return query.list();
	}
}	

}
PageBean code is as follows:

package com.zking.eight.util;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

/**

• Paging tool class

*/
public class PageBean {

private int page = 1;// Page number

private int rows = 2;// Page size

private int total = 0;// Total number of records

private boolean pagination = true;// Paging
// Get all parameters submitted by foreground to background
private Map<String, String[]> parameterMap;
// Get the url of the last visit to the background
private String url;

/**
 * Initialize pagebean
 * 
 * @param req
 */
public void setRequest(HttpServletRequest req) {
	this.setPage(req.getParameter("page"));
	this.setRows(req.getParameter("rows"));
	// Only when pagination=false is filled in the jsp page, pagination is not allowed
	this.setPagination(!"fasle".equals(req.getParameter("pagination")));
	this.setParameterMap(req.getParameterMap());
	this.setUrl(req.getRequestURL().toString());
}

public int getMaxPage() {
	return this.total % this.rows == 0 ? this.total / this.rows : this.total / this.rows + 1;
}

public int nextPage() {
	return this.page < this.getMaxPage() ? this.page + 1 : this.getMaxPage();
}

public int previousPage() {
	return this.page > 1 ? this.page - 1 : 1;
}

public PageBean() {
	super();
}

public int getPage() {
	return page;
}

public void setPage(int page) {
	this.page = page;
}

public void setPage(String page) {
	this.page = StringUtils.isBlank(page) ? this.page : Integer.valueOf(page);
}

public int getRows() {
	return rows;
}

public void setRows(int rows) {
	this.rows = rows;
}

public void setRows(String rows) {
	this.rows = StringUtils.isBlank(rows) ? this.rows : Integer.valueOf(rows);
}

public int getTotal() {
	return total;
}

public void setTotal(int total) {
	this.total = total;
}

public void setTotal(String total) {
	this.total = Integer.parseInt(total);
}

public boolean isPagination() {
	return pagination;
}

public void setPagination(boolean pagination) {
	this.pagination = pagination;
}

public Map<String, String[]> getParameterMap() {
	return parameterMap;
}

public void setParameterMap(Map<String, String[]> parameterMap) {
	this.parameterMap = parameterMap;
}

public String getUrl() {
	return url;
}

public void setUrl(String url) {
	this.url = url;
}

/**
 * Get the subscript of the starting record
 * 
 * @return
 */
public int getStartIndex() {
	return (this.page - 1) * this.rows;
}

@Override
public String toString() {
	return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", pagination=" + pagination
			+ ", parameterMap=" + parameterMap + ", url=" + url + "]";
}

}
The StringUtils code is as follows:

package com.zking.eight.util;

public class StringUtils {
//Private constructor, protection class cannot be instantiated externally
private StringUtils() {
}

/**
 * Returns true if the string is equal to null or equal to '' after space removal, false otherwise
 * 
 * @param s
 * @return
 */
public static boolean isBlank(String s) {
	boolean b = false;
	if (null == s || s.trim().equals("")) {
		b = true;
	}
	return b;
}

/**
 * Returns true if the string is not equal to null or is not equal to '' after space removal, false otherwise
 * 
 * @param s
 * @return
 */
public static boolean isNotBlank(String s) {
	return !isBlank(s);
}

}
Then you can inherit BaseDao to make the call

    /**
 * Optimized
 * @param book
 * @param pageBean
 * @return
 */
public List list2(Book book,PageBean pageBean) {
	Session session = SessionFactoryUtil.getSession();
	Transaction transaction = session.beginTransaction();
	String hql="from Book where 1=1";
	Map<String, Object> map=new HashMap<String, Object>();
	if(StringUtils.isNotBlank(book.getBookName())) {
		hql+=" and bookName like :bookName";
		map.put("bookName", "%"+book.getBookName()+"%");
	}
	
	List list = super.executeQuery(hql, pageBean, map, session);
	
	transaction.commit();
	SessionFactoryUtil.closeSession();
	return list;
}

In this way, the code is much simpler.

Using native SQL statements in hibernate

For the functions that hql can't realize, you can consider using native SQL, such as the joint table query of three or more tables. If you use hql, it's not only difficult to configure the relationship, but also to turn SQL into hql, so it's more convenient to use native SQL directly. When you use it, you can call the createSQLQuery(hql) method.

    public List list3() {
	Session session = SessionFactoryUtil.getSession();
	Transaction transaction = session.beginTransaction();
	String hql="select b.book_name,o.order_no,oi.product_id from t_hibernate_book b,t_hibernate_order o,t_hibernate_order_item oi";
	List list = session.createSQLQuery(hql).list();
	
	transaction.commit();
	SessionFactoryUtil.closeSession();
	return list;
}