package cn.hbook.dao.impl;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.transaction.annotation.Transactional;
import cn.hbook.bean.TBook;
import cn.hbook.dao.IBookDao;
import cn.hbook.form.QueryBookInfo;
@Transactional
public class BookDaoImpl extends HibernateDaoSupport implements IBookDao {
private int pageSize = 8;
private int currentPage = 1;
//添加书籍
public boolean save(TBook book) throws Exception {
System.out.println("dao - svae book");
//this.getSession().clear();
this.getHibernateTemplate().merge(book); //这样可以在添加书的时候同时更新他的作者
return true;
}
//删除书籍
public boolean delete(TBook book) throws Exception {
this.getHibernateTemplate().delete(book);
return true;
}
//批量删除
public boolean deleteAll(List<String> ids) throws Exception {
Set<TBook> delBooks = new HashSet<TBook>();
for (String id : ids) {
delBooks.add(new TBook(Integer.parseInt(id.trim())));
}
this.getHibernateTemplate().deleteAll(delBooks);
return true;
}
//更新书籍信息
public boolean update(TBook book) throws Exception {
this.getHibernateTemplate().merge(book);
return true;
}
//根据id查询数据
public TBook queryById(Integer bookid) throws Exception {
TBook book = this.getHibernateTemplate().get(TBook.class, bookid);
return book;
}
//根据ISBN查找书籍
public TBook queryBookByIsbn(String isbn) throws Exception {
@SuppressWarnings("unchecked")
List<TBook> books = this.getHibernateTemplate().find("from TBook b where b.isbn = ? ", isbn);
if (null != books && books.size() > 0) {
return books.get(0);
}
return null;
}
//查询所有书籍
@SuppressWarnings("unchecked")
public List<TBook> queryBooks() throws Exception {
List<TBook> books = this.getHibernateTemplate().find("from TBook");
return books;
}
//根据类型查找书籍
@SuppressWarnings("unchecked")
public List<TBook> queryBooksByCategory(QueryBookInfo queryBookInfo) throws Exception {
final Integer type = queryBookInfo.getType();
//分段查询
final Date beginDate = queryBookInfo.getBeginDate(); //出版时间段
final Date endDate = queryBookInfo.getEndDate();
final Double beginPrice = queryBookInfo.getBeginPrice(); //价格段查询
final Double endPrice = queryBookInfo.getEndPrice();
final Integer beginGrade = queryBookInfo.getBeginGrade(); // 按客户评分
final Integer endGrade = queryBookInfo.getEndGrade();
final Double beginDiscount = queryBookInfo.getBeginDiscount(); //按折扣查
final Double endDiscount = queryBookInfo.getEndDiscount();
final Integer beginStockNum = queryBookInfo.getBeginStockNum(); //库存量
final Integer endStockNum = queryBookInfo.getEndStockNum();
//排序条件
final String orderByPrice = queryBookInfo.getOrderByPrice(); //价格排序
final String orderByDate = queryBookInfo.getOrderByDate(); //时间排序
final String orderBySaleNum = queryBookInfo.getOrderBySaleNum(); //销量排序
final String orderByGrade = queryBookInfo.getOrderByGrade(); //按用户评分
final String orderByDiscount = queryBookInfo.getOrderByDiscount(); //按折扣
List<TBook> books = this.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session s)
throws HibernateException, SQLException {
//HQL语句
StringBuilder hql = new StringBuilder(" select distinct b from TBook b join b.TAuthors a where 1 = 1 and (b.type = ? or b.ftype = ? ) ");
// 查询结果
List<TBook> books = new ArrayList<TBook>();
//存放查询参数
List<Object> params = new ArrayList<Object>();
params.add(type);
params.add(type); //首先将参数几放入
//动态的拼接hql语句
//时间段
if (beginDate != null) {
hql.append(" and b.publishDate >= ? ");
params.add(beginDate);
}
if (endDate != null) {
hql.append(" and b.publishDate <= ? ");
params.add(endDate);
}
//价格段
if (beginPrice != null) {
hql.append(" and b.hbPrice >= ? ");
params.add(beginPrice);
}
if (endPrice != null) {
hql.append(" and b.hbPrice <= ? ");
params.add(endPrice);
}
//评分段
if(beginGrade != null) {
hql.append(" and b.grade >= ? ");
params.add(beginGrade);
}
if (endGrade != null) {
hql.append(" and b.grade <= ? ");
params.add(endGrade);
}
//折扣段
if (beginDiscount != null) {
hql.append(" and b.discount >= ? ");
params.add(beginDiscount);
}
if (endDiscount != null) {
hql.append(" and b.discount <= ? ");
params.add(endDiscount);
}
//库存量
if (beginStockNum != null) {
hql.append(" and b.stockNum >= ? ");
params.add(beginStockNum);
}
if (endStockNum != null) {
hql.append(" and b.stockNum <= ? ");
params.add(endStockNum);
}
//是否已经有order
boolean isOrder = false;
//按价格排序
if (orderByPrice != null) {
if (!isOrder) hql.append(" order by ");
if (orderByPrice.equals("1")) {
hql.append(" b.hbPrice asc , ");
} else {
hql.append(" b.hbPrice desc , ");
}
isOrder = true;
}
//时间排序
if (orderByDate != null) {
if (!isOrder) hql.append(" order by ");
if (orderByDate.equals("1")) {
hql.append(" b.publishDate asc , ");
} else {
hql.append(" b.publishDate desc , ");
}
isOrder = true;
}
//销量排序
if (orderBySaleNum != null) {
if (!isOrder) hql.append(" order by ");
if (orderBySaleNum.equals("1")) {
hql.append(" b.saleNum asc , ");
} else {
hql.append(" b.saleNum desc , ");
}
isOrder = true;
}
//按评分排序
if (orderByGrade != null) {
if (!isOrder) hql.append(" order by ");
if (orderByGrade.equals("1")) {
hql.append(" b.grade asc , ");
} else {
hql.append(" b.grade desc, ");
}
isOrder = true;
}
//按折扣查询
if (orderByDiscount != null) {
if (!isOrder) hql.append(" order by ");
if (orderByDiscount.equals("1")) {
hql.append(" b.discount asc , ");
} else {
hql.append(" b.discount desc , ");
}
isOrder = true;
}
//去掉最后边的","
String result = "";
if (isOrder == true) {
result = hql.substring(0, hql.length() - 1);
} else {
result = hql.toString();
}
//设置查询参数
System.out.println("Category dao hql = " + hql );
Query q = s.createQuery(result);
for (int i = 0; i < params.size(); ++i) {
q.setParameter(i, params.get(i));
System.out.println("参数 " + i + " = " + params.get(i));
}
// 设置页面
q.setFirstResult((currentPage - 1) * pageSize);
q.setMaxResults(pageSize);
books = q.list();
return books;
}
}
);
return books;
}
//按时间排续 查出新书
@SuppressWarnings("unchecked")
public List<TBook> queryBooksOrderByDate(QueryBookInfo queryBookInfo) throws Exception{
//没有目的性的查询不用枚举细节信息
//分段查询
final Date beginD