BaseDAO需求
- 作用:
- 1、将赋值的操作交给basedao
- 2、分页
a、查询出符合条件的总记录数
b、查询符合条件的某一页记录
package com.zking.eight.dao;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import org.hibernate.Session;
import org.hibernate.query.Query;
import com.zking.eight.util.PageBean;
/**
* 作用:
* 1、将赋值的操作交给basedao
* 2、分页
* a、查询出符合条件的总记录数
* b、查询符合条件的某一页记录
* @author Administrator
*
*/
public class BaseDao {
/**
* 赋值的操作
* @param query 预定义对象
* @param map 前台传递过来的参数
*/
public void setParameter(Query query,Map<String, Object> map) {
// query.setParameter("bookname", "%"+book.getBookname()+"%");
if(map == null || map.size()==0) {
return;
}
Object values = null;
for (Map.Entry<String, Object> entry : map.entrySet()) {
values = entry.getValue();
if(values instanceof Collection) {
query.setParameterList(entry.getKey(), (Collection) values);
}
else if(values instanceof Object[]) {
query.setParameterList(entry.getKey(), (Object[]) values);
}
else {
query.setParameter(entry.getKey(), values);
}
}
}
/**
* 思路:
* 截取from后的sql语句,前面拼接select count(*)
*
* 转成大写
*/
//查询出符合条件的总记录数
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()) {
String countHql = getCountHql(hql);
Query countQuery = session.createQuery(countHql);
this.setParameter(countQuery, map);
//pagebean设置总记录数,最后用于分页
pageBean.setTotal(countQuery.getSingleResult().toString());
//查询展示的数据
Query pageQuery = session.createQuery(hql);
this.setParameter(pageQuery, map);
pageQuery.setFetchSize(pageBean.getStartIndex());
pageQuery.setMaxResults(pageBean.getRows());
return pageQuery.list();
}
else {
Query query = session.createQuery(hql);
this.setParameter(query, map);
return query.list();
}
}
}
dao方法:
没有BaseDao方法:
public List<Book> list1(Book book,PageBean pageBean){
Session session = SessionFactoryUtils.getSession();
Transaction transaction = session.beginTransaction();
String hql = "from Book where 1=1";
if(StringUtils.isNotBlank(book.getBookname())) {
hql += " and bookname like :bookname ";
}
Query query = session.createQuery(hql);
if(StringUtils.isNotBlank(book.getBookname())) {
query.setParameter("bookname", "%"+book.getBookname()+"%");
}
if( pageBean != null && pageBean.isPagination()) {
query.setFirstResult(pageBean.getStartIndex());
query.setMaxResults(pageBean.getRows());
}
List list = query.list();
transaction.commit();
session.close();
return list;
}
继承BaseDao方法:
public List<Book> list2(Book book,PageBean pageBean){
Session session = SessionFactoryUtils.getSession();
Transaction transaction = session.beginTransaction();
Map<String, Object> map = new HashMap<>();
String hql = "from Book where 1=1";
if(StringUtils.isNotBlank(book.getBookname())) {
hql += " and bookname like :bookname ";
map.put("bookname", book.getBookname());
}
List list = super.executeQuery(session, hql, pageBean, map);
transaction.commit();
session.close();
return list;
}
原生态SQL
hql实现不了的功能,可以考虑使用原生sql,例如:
1、多表(5+)联查
2、未配置映射文件中关系
public List<Object[]> list3(Book book,PageBean pageBean){
Session session = SessionFactoryUtils.getSession();
Transaction transaction = session.beginTransaction();
Map<String, Object> map = new HashMap<>();
// String hql = " select b.bookname,o.orderId from Book b,Order o where 1=1";
// List list = session.createQuery(hql).list();
//原生态SQL
String sql = " select b.book_name,o.order_id from t_hibernate_book b,t_hibernate_order o where 1=1";
List list = session.createSQLQuery(sql).list();
transaction.commit();
session.close();
return list;
}
视图映射
hibernate支持视图映射
public List<Object[]> list3(Book book,PageBean pageBean){
Session session = SessionFactoryUtils.getSession();
Transaction transaction = session.beginTransaction();
Map<String, Object> map = new HashMap<>();
//视图映射
String sql = " select * from book_order";
List list = session.createSQLQuery(sql).list();
transaction.commit();
session.close();
return list;
}