1、 BaseDAO
需求:
按名字分页查询对应书籍信息
package com.eight.dao;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import org.hibernate.Session;
import org.hibernate.query.Query;
/**
* 作用:
* 1.将赋值的操作交给basedao
* 2.分页:
* a 符合条件的总记录数
* b 查询符合条件的某一页记录
*
* @author pc
*
*/
public class BaseDao {
/**
* 赋值的操作
* @param query预定义对象
* @param map 前台传递过来的参数
*/
private void setParameter(Query query,Map<String, Object> map) {
// query.setParameter("bookName", "%"+book.getBookName+"%");
if(map == null || map.size() == 0) {
return;
}
Object value = null;
for(Map.Entry<String, Object> entry:map.entrySet()) {
value = entry.getValue();
if(value instanceof Collection) {
query.setParameterList(entry.getKey(), (Collection)value);
}else if (value instanceof Object[]){
query.setParameterList(entry.getKey(), (Object[])value);
}else {
query.setParameter(entry.getKey(), value);
}
}
}
/**
* sql = select * from t_hibernate_book where book_name like '%圣墟%'
* countSql = select count(*) from (select * from t_hibernate_book where book_name like '%圣墟%') t
* pageSql = sql+" limit ?,? hibernate中这一步省略
*
* hql = from Book where bookName like '%圣墟%'
* hql = select bookName,price from Book where bookName like '%圣墟%'
*
* 思路:
* 截取from后的sql语句 前面拼接select count(*)
* From
* from
* frOm
* froM
* 转成大写FROM
*/
/**
* 查询出符合条件的总记录数
* @param sql
* @return
*/
public String getCountHql(String hql) {
int index = hql.toUpperCase().indexOf("FROM");
return "select count(*) "+hql.substring(index);
}
public List executeQuery(Session session, String hql,PageBean pageBean,Map<String, Object> map) {
if(pageBean != null && pageBean.isPagination()) {
//select count(*) from Book where bookName like '%圣墟%'
String countHql = getCountHql(hql);
Query countquery = session.createQuery(countHql);
this.setParameter(countquery, map);
//pageBean中设置总记录数,最后用于分页
String total = countquery.getSingleResult().toString();
pageBean.setTotal(total);
//查询展示的数据
Query pageQuery = session.createQuery(hql);
this.setParameter(pageQuery, map);
pageQuery.setFirstResult(pageBean.getStartIndex());
pageQuery.setMaxResults(pageBean.getRows());
return pageQuery.list();
}else {
Query query = session.createQuery(hql);
this.setParameter(query, map);
return query.list();
}
}
}
2、原生sql
hql实现不了的功能,可以考虑使用原生sql
1、多表(5+)联查
2、未配置映射文件中关系
3、 视图映射
场景
select * from 3表联查 where bname like ‘%bname%’
select * from 3表联查 where bname = :bname and …
select * from 3表联查 where …