package com.espeed.dao.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.espeed.dao.HibernateBaseDAO;
import com.espeed.pojo.YxyReadingCount;
import com.espeed.pojo.YxySendMailMasterBase;
import com.espeed.pojo.YxyUserAddress;
import com.espeed.tool.DBUtil;
import com.espeed.vo.PageBean;
/**
 * 程序名称:    	EspeedMail_时速邮箱
 * 程序版本:    	V1.0
 * 作    者:    	深圳市科飞时速网络技术有限公司(0755-88843776)
 * 版权所有:    	深圳市科飞时速网络技术有限公司
 * 技术支持:    	Tech@21gmail.com
 * 单元名称:    通用dao操作接口实现类
 * 开始时间:    	2013.09.22
 * 程 序 员:    	谢勇
 * 最后修改:    2014-02-26
 * 备    注:	实现通用的增删改查操作,被其他dao所继承使用
 */

public abstract class HibernateBaseDAOImpl<T extends Serializable ,Pk extends Serializable> extends HibernateDaoSupport
	implements HibernateBaseDAO<T, Pk> {

	private Connection conn;
	private PreparedStatement pst;
	private ResultSet rs;
	
	/**增加记录 (返回新增加记录的主键)*/
	public int  add(T o) throws Exception {
		return (Integer) super.getHibernateTemplate().save(o);	
		
	}

	/**增加记录(无返回值)*/
	public void  adddate(T o) throws Exception {
		super.getHibernateTemplate().save(o);			
	}

	 /**修改记录*/
	public void update(T o) throws Exception {
		super.getHibernateTemplate().update(o);
	}
	/**删除记录*/
	public void del(T o) throws Exception {
		super.getHibernateTemplate().delete(o);
	}

	/**添加或更新*/
	public void saveOrUpdate(T o) throws Exception {
		super.getHibernateTemplate().saveOrUpdate(o);
	}
	/**根据ID获取一条数据*/
	@SuppressWarnings("unchecked")
	public T get(Class<T> t,Pk pk) throws Exception {
		return (T) super.getHibernateTemplate().get(t, pk);
	}
	/**根据ID获取一条数据*/
	@SuppressWarnings("unchecked")
	public T load(Class<T> t,Pk pk) throws Exception {
		return (T) super.getHibernateTemplate().load(t, pk);
	}
	
	/**根据hql进行条件查询*/
	@SuppressWarnings("unchecked")
	public List<T> getAll(String hql) throws Exception {
		return super.getHibernateTemplate().find(hql);
	}
	/**条件查询*/
	@SuppressWarnings("unchecked")
	public List<T> getAll(String whereHql, Object... params) throws Exception {
		return super.getHibernateTemplate().find(whereHql,params);
	}
	
	/**
	 * ------------------------------分页查询-----------------------------------------
	 */
    /** 根据指定的HQL(多条件)进行查询*/
	public List<T> findByPage(String hql, String hqlCount, PageBean pb)
			throws Exception {
		return this.findByPage(hql, hqlCount, pb, new Object[0]);
	}
	/** 根据指定的HQL(多条件)进行查询*/
	@SuppressWarnings("unchecked")
	public List<T> findByPage(final String hql,final String hqlCount,final PageBean pb,final Object... values)
			throws Exception {
		return (List<T>) super.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				int pageSize = pb.getPageSize();
				int currentPage = pb.getCurrentPage();
				Query queryCount = session.createQuery(hqlCount);
				Query query = session.createQuery(hql);
				for(int i = 0;i<values.length;i++){
					queryCount.setParameter(i, values[i]);
					query.setParameter(i, values[i]);
				}
				//获取总记录数
				Integer totalRecord = Integer.valueOf(queryCount.list().get(0).toString());
				pb.setTotalRecord(totalRecord);
				//获取总页数
				Integer totalPage = (totalRecord + pageSize - 1)/pageSize;
				pb.setTotalPage(totalPage);
				query.setFirstResult((currentPage - 1)*pageSize);
				query.setMaxResults(pageSize);
				List list = query.list();
				return list;
			}
			
		});
	}
	
	/** 根据指定的HQL(多条件)进行查询特殊(所有点读)*/
	@SuppressWarnings("unchecked")
	public List<T> findByPages(final String hql,final String hqlCount,final PageBean pb,final Object... values)
			throws Exception {
		return (List<T>) super.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				int pageSize = pb.getPageSize();
				int currentPage = pb.getCurrentPage();
				Query queryCount = session.createQuery(hqlCount);
				Query query = session.createQuery(hql);
				for(int i = 0;i<values.length;i++){
					queryCount.setParameter(i, values[i]);
					query.setParameter(i, values[i]);
				}
				//获取总记录数
				Integer totalRecord = queryCount.list().size();
				pb.setTotalRecord(totalRecord);
				//获取总页数
				Integer totalPage = (totalRecord + pageSize - 1)/pageSize;
				pb.setTotalPage(totalPage);
				query.setFirstResult((currentPage - 1)*pageSize);
				query.setMaxResults(pageSize);
				List list = query.list();
				return query.list();
			}
			
		});
	}

	/**根据条件查询总记录*/
	public int count(String hql) throws Exception {
		List result=super.getHibernateTemplate().find(hql);
		if(result.size()>0&&result.get(0)!=null&&!result.get(0).equals("null")){
			return Integer.valueOf(result.get(0).toString());
		}else{
			return 0;
		}
		//return Integer.valueOf(super.getHibernateTemplate().find(hql).get(0).toString());
	}
	/**根据条件和参数查询总记录*/
	public int count(String hql, Object... params) throws Exception {
		return Integer.valueOf(super.getHibernateTemplate().find(hql,params).get(0).toString());
	}
	
	/**根据ID通过jdbc删除数据*/
	public void delByID(String table,String StrID,int id)throws Exception{
		conn = DBUtil.getConnection();
		pst = conn.prepareStatement("delete  from "+table+" where "+ StrID+"=?");
		pst.setInt(1, id);
		pst.execute();
		DBUtil.close(rs, pst, conn);
	}
	
	
	
	/**sql插入返回插入的id*/
	public int insertTable(String sql)throws Exception{
		conn = DBUtil.getConnection();
		pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		int resultID=0;
		pst.executeUpdate();
		rs=pst.getGeneratedKeys();
		
		if(rs.next()){
			resultID=rs.getInt(1);
		}
		DBUtil.close(rs, pst, conn);
		return resultID;
	}
	
	/**HQL语句修改删除记录*/
	public int updateorDelByHql(String hql){
		Query query =	null;
		int result=0;		
		Session session =null;
		try {
			session=super.getHibernateTemplate().getSessionFactory().openSession();		
			query = session.createQuery(hql);
			result = query.executeUpdate();	
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(session!=null){
				session.clear();
				session.close();
			}
		}
		
		return result;
	}
	
	/**查询设定的记录条数据*/
	public List<T> findBySet(String hql,int num){
		Session session =	null;	
		Query query=null;
		try {
			session=super.getHibernateTemplate().getSessionFactory().openSession();	
			query = session.createQuery(hql);
			query.setMaxResults(num);
			return query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(session!=null){
				session.clear();
				session.close();
			}
		}		
		return null;
	}
	
	/**批量插入*/
	public void addPi(List<T> o)throws Exception{
		Session session =null;
		
		if(o!=null){				
			session=super.getHibernateTemplate().getSessionFactory().openSession();
			session.beginTransaction();
			for(int i=0;i<o.size();i++){	
				session.saveOrUpdate(o.get(i));	
				if(i%10==0){
					session.flush();
					session.clear();
				}
			}
			session.getTransaction().commit();
		} 
		session.close();
			
	}

	
	/**sql语句查询*/
	public List<Object> findBySql(String sql){
		Session session=null;
		try {
			session =	super.getHibernateTemplate().getSessionFactory().openSession();
			return session.createSQLQuery(sql).list();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(session!=null){
				session.clear();
				session.close();
			}
		}
		return null;		
	}

	/**批量更新(统计表使用)*/
	public void updatePi(List<YxyReadingCount> o){
		if(o!=null){
			Session session =	super.getHibernateTemplate().getSessionFactory().openSession();
			try {
				
				session.beginTransaction();
				String sql="";
				for(int i=0;i<o.size();i++)
				{
					sql="update YxyReadingCount set reading_count_person="+o.get(i).getReading_count_person()+",reading_count_tol="+o.get(i).getReading_count_tol() +" where reading_count_mailuid='"+o.get(i).getReading_count_mailuid()+"'";
					Query query = session.createQuery(sql);
					query.executeUpdate();
					//session.update(o.get(i));	
				}
				session.beginTransaction().commit();
				
			} catch (Exception e)
			{
				e.printStackTrace();
				session.beginTransaction().rollback();
			}finally{
				session.flush();
				session.clear();
				session.close();
			}
		}
	}
	
	/**批量更新(邮件基本信息)*/
	public void updatePiBase(List<YxySendMailMasterBase> o){
		if(o!=null){
			Session session =super.getHibernateTemplate().getSessionFactory().openSession();
			try {
				session.beginTransaction();
				String sql="";
				for(int i=0;i<o.size();i++)
				{
					sql="update YxySendMailMasterBase set reading_tol="+o.get(i).getReading_tol()+" where mail_uid='"+o.get(i).getMail_uid()+"'";
					Query query = session.createQuery(sql);
					query.executeUpdate();
				}
				session.beginTransaction().commit();
				
			} catch (Exception e)
			{
				e.printStackTrace();
				session.beginTransaction().rollback();
			}finally{
				session.flush();
				session.clear();
				session.close();
			}
		}
	}
	
	/**批量更新(用户地址)*/
	public void updatePiAddress(List<YxyUserAddress> o){
		if(o!=null){
			Session session =super.getHibernateTemplate().getSessionFactory().openSession();
			try {
				session.beginTransaction();
				String Hql="";
				for(int i=0;i<o.size();i++)
				{
					Hql="update YxyUserAddress set user_send_time='"+o.get(i).getUser_send_time()+"',user_send_num="+o.get(i).getUser_send_num()+" where user_addr_id="+o.get(i).getUser_addr_id();
					Query query = session.createQuery(Hql);
					query.executeUpdate();
				}
				session.beginTransaction().commit();
				
			} catch (Exception e)
			{
				e.printStackTrace();
				session.beginTransaction().rollback();
			}finally{
				session.flush();
				session.clear();
				session.close();
			}
		}
	}
}