1. BaseDAO(通用分页)
需求:
按名字分页查询对应书籍信息
package com.xxx.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.xxx.eight.test.PageBean;
/**
*作用:
* 1、将赋值的操作交给basedao
* 2、分页
* 2.1、查询出符合条件的总记录数
* 2.2、查询符合条件的某一页记录
* @author Administrator
*
*/
public class BaseDao {
/**
* 1、赋值的操作
* @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);
}
}
}
/**
* 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有很多种写法,那么它的截取办法:先转大写/小写,再去截取
*/
public String getCountHql(String hql) {
int index = hql.toUpperCase().indexOf("FROM");//获取from的下标
return "select count(*)"+hql.substring(index);
}
public List excuteQuery(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设置总记录数,最后用于分页
pageBean.setTotal(countQuery.getSingleResult().toString());
//查询展示数据
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();
}
}
}
Dao
public List<Book> list2(Book book,PageBean pageBean){
Session session = SessionFactoryUtils.getSession();
Transaction transaction = session.beginTransaction();
Map<String, Object> map=new HashMap<String, Object>();
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.excuteQuery(session, hql, pageBean, map);
transaction.commit();
session.close();
return list;
}
test测试类
@Test
public void testList2() {
PageBean pageBean=new PageBean();
pageBean.setPage(2);
Book book=new Book();
// book.setBookName("不死不灭");
List<Book> list1=this.bookDao.list2(book, pageBean);
for (Book b : list1) {
System.out.println(b);
}
}
2. 原生sql
hql实现不了的功能,可以考虑使用原生sql
1、多表(5+)联查
2、未配置映射文件中关系
3. 视图映射
场景
select * from 3表联查
Dao
public List<Object[]> list3(){
Session session = SessionFactoryUtils.getSession();
Transaction transaction = session.beginTransaction();
Map<String, Object> map=new HashMap<String, Object>();
// String hql="select b.bookName,o.OrderNo from Book b,Order o where 1=1";
// String sql="select b.book_name,o.order_no from t_hibernate_book b,t_hibernate_Order o where 1=1";//原生态sql
String sql="select * from book_order";//视图映射
// List list = session.createQuery(hql).list();
List list = session.createSQLQuery(sql).list();
transaction.commit();
session.close();
return list;
}
test类
@Test
public void testList3() {
PageBean pageBean=new PageBean();
pageBean.setPage(2);
Book book=new Book();
// book.setBookName("不死不灭");
List<Object[]> list1=this.bookDao.list3();
for (Object[] b : list1) {
System.out.println(Arrays.toString(b));
}
4、视图映射(hql)
1、创建视图
2、创建视图实体类(注:实体类要实现 Serializable接口)
package com.xxx.eight.entity;
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
import com.xxx.five.entity.Category;
public class BookOrder implements Serializable{
private Integer book_id;
private Integer order_id;
private String bookName;
private String OrderNo;
public Integer getBook_id() {
return book_id;
}
public void setBook_id(Integer book_id) {
this.book_id = book_id;
}
public Integer getOrder_id() {
return order_id;
}
public void setOrder_id(Integer order_id) {
this.order_id = order_id;
}
public String getOrderNo() {
return OrderNo;
}
public void setOrderNo(String orderNo) {
OrderNo = orderNo;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public BookOrder(Integer book_id, Integer order_id, String bookName, String orderNo) {
super();
this.book_id = book_id;
this.order_id = order_id;
this.bookName = bookName;
OrderNo = orderNo;
}
public BookOrder() {
super();
}
@Override
public String toString() {
return "BookOrder [book_id=" + book_id + ", order_id=" + order_id + ", bookName=" + bookName + ", OrderNo="
+ OrderNo + "]";
}
}
3、创建xxx.hbm.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class table="bookorder" name="com.zking.eight.entity.BookOrder">
<composite-id>
<key-property name="book_id" type="java.lang.Integer" column="book_id"></key-property>
<key-property name="order_id" type="java.lang.Integer" column="order_id"></key-property>
</composite-id>
<property name="bookName" type="java.lang.String" column="book_name"></property>
<property name="OrderNo" type="java.lang.String" column="order_no"></property>
</class>
</hibernate-mapping>
4、配置hibernate.cfg.xml文件
<mapping resource="com/zking/eight/entity/BookOrder.hbm.xml"/>
5、Dao
public List<BookOrder> list4(){
Session session = SessionFactoryUtils.getSession();
Transaction transaction = session.beginTransaction();
// Map<String, Object> map=new HashMap<String, Object>();
String hql="from BookOrder";
List list = session.createQuery(hql).list();
transaction.commit();
session.close();
return list;
}
6、Test
@Test
public void testList4() {
PageBean pageBean=new PageBean();
BookOrder bookOrder=new BookOrder();
List<BookOrder> list1=this.bookDao.list4();
for (BookOrder b : list1) {
System.out.println(b);
}
}