hibernate support dao template

package com.daluo.fortrade.dao.support;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

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

import com.daluo.fortrade.dao.IDAO;
import com.daluo.fortrade.model.BaseForm;
import com.daluo.fortrade.util.Pagination;

public class DAOSupport extends HibernateDaoSupport implements IDAO {
	public void delete(BaseForm baseForm) {
		this.getHibernateTemplate().delete(baseForm);
	}

	public void save(BaseForm baseForm) {
		this.getHibernateTemplate().save(baseForm);
	}

	public void update(BaseForm baseForm) {
		this.getHibernateTemplate().update(baseForm);
	}

	public void batchDelete(String[] Id, Class classType) {
		for(int i=0;i<Id.length;i++){
			BaseForm form = (BaseForm)load(classType, Id[i]);
        	getHibernateTemplate().delete(form);
		}
	}
	
	
	public BaseForm get(Class classType, String Id) {
		return (BaseForm)this.getHibernateTemplate().get(classType, Integer.valueOf(Id));
	}
	
	public BaseForm load(Class classType, String Id){
		return (BaseForm)this.getHibernateTemplate().load(classType, Integer.valueOf(Id));
	}


	public List findByExample(BaseForm baseForm) {
		return this.getHibernateTemplate().findByExample(baseForm);
	}
	
	public List findByHql(String hql) {
		return this.getHibernateTemplate().find(hql);
	}

	public List findBySQL(String sql) {
		return this.getSession().createQuery(sql).list();
	}
	
	public List findBySQL(String sql, List params){
		try {
			Connection con = this.getSession().connection();
			PreparedStatement ps = con.prepareStatement(sql);
			int paramSize = params.size();
			for (int i = 0; i < paramSize; i++) {
				ps.setString(i+1, (String) params.get(i));
			}
			ResultSet rs = ps.executeQuery();
			List<Object[]> items = new ArrayList<Object[]>();
			while(rs.next()){
				Object[] row = new Object[rs.getMetaData().getColumnCount()];
				for (int i = 0; i < row.length; i++) {
					row[i] = rs.getObject(i+1);
				}
				items.add(row);
			}
			//ps.close();
			//con.close();
			return items;
		} catch (DataAccessResourceFailureException e) {
			e.printStackTrace();
		} catch (HibernateException e) {
			e.printStackTrace();
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public List findByQBC(BaseForm baseForm) {
		return this.getSession().createCriteria(baseForm.getClass()).list();
	}

	public List findByHql(String hql, Object obj) {
		return this.getHibernateTemplate().find(hql, obj);
	}

	public List findByHql(String hql, Object[] obj) {
		return this.getHibernateTemplate().find(hql, obj);
	}

	public List findByPagination(final String hql, final int firstRow,
			final int maxRow) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(hql);

				query.setFirstResult(firstRow);
				query.setMaxResults(maxRow);
				List list = query.list();
				return list;
			}
		});
	}

	public List findByPagination(final String hql, final Object[] params,
			final int firstRow, final int maxRow) {
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session s) throws HibernateException,
					SQLException {
				Query query = s.createQuery(hql);
				for (int i = 0; i < params.length; i++) {
					query.setParameter(i, params[i]);
				}
				query.setFirstResult(firstRow);
				query.setMaxResults(maxRow);
				List list = query.list();
				return list;
			}
		});
	}
	
	
	// ---------------------------------------------------------------------
	// 无参数取得一页数据
	// --------------------------------------------------------------------
	public Pagination getPage(String queryName) {
		return getPage(queryName, null, Pagination.defaultPageSize(), 0);
	}

	public Pagination getPage(String queryName, int startIndex) {
		return getPage(queryName, null, Pagination.defaultPageSize(), startIndex);
	}

	public Pagination getPage(String queryName, int pageSize, int startIndex) {
		return getPage(queryName, new ArrayList(), pageSize,startIndex);
	}

	// ---------------------------------------------------------------------------
	// 使用带一个参数的HQL取得一页数据
	// ---------------------------------------------------------------------------
	@SuppressWarnings("unchecked")
	public Pagination getPage(String queryName, Object param) {
		boolean add = new ArrayList().add(param);
		return getPage(queryName, add, Pagination.defaultPageSize(), 0);
	}

	@SuppressWarnings("unchecked")
	public Pagination getPage(String queryName, Object param,
			int startIndex) {
		return getPage(queryName,new ArrayList().add(param), Pagination.defaultPageSize(), startIndex);
	}

	@SuppressWarnings("unchecked")
	public Pagination getPage(String queryName, Object param,
			int pageSize, int startIndex) {
		return getPage(queryName,new ArrayList().add(param), pageSize, startIndex);
	}

	// ------------------------------------------------------------------------------
	// 使用带有一系列参数的HQL取得一页数据
	// .------------------------------------------------------------------------------
	public Pagination getPage(String queryName, List params) {
		return getPage(queryName, params, Pagination.defaultPageSize(), 0);
	}

	public Pagination getPage(String queryName, List params, int startIndex) {
		return getPage(queryName, params, Pagination.defaultPageSize(), startIndex);
	}

	
	public Pagination getPage(final String hql, final List params,  final int curPage, final int pageSize) {
		if (params != null && params.size() != params.size()) {
			throw new IllegalArgumentException("Length of paramNames array must match length of values array");
		}
		return (Pagination) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						Query query = session.createQuery(hql);
						for (int i = 0; i < params.size(); i++) {
							query.setParameter(i, params.get(i));
						}
						int startRecord = (curPage-1)*pageSize;
						query.setFirstResult(startRecord);
						query.setMaxResults(pageSize);
						String hqlCount = query.getQueryString();
						if(hqlCount.indexOf("from")!=-1){
							hqlCount = hqlCount.substring(hqlCount.indexOf("from"),hqlCount.length());
						}
						if(hqlCount.indexOf("order by")!=-1){
							hqlCount = hqlCount.substring(0,hqlCount.indexOf("order by"));
						}
						hqlCount = "select count(*) " + hqlCount;
						Query countQuery = session.createQuery(hqlCount);
						if (params != null) {
							for (int j = 0; j < params.size(); j++) {
								countQuery.setParameter(j, params.get(j));
							}
						}
						int totalCount = ((Integer) countQuery.list().iterator().next()).intValue();
						List items = query.list();
						Pagination ps = new Pagination(items, totalCount, curPage, pageSize, startRecord);
						return ps;
					}
				});
	}

	public Pagination getPageBySQL(String sql, List params, int curPage, int pageSize){
		try {
			Connection con = this.getSession().connection();
			sql = Pagination.getLimitSQL(sql,curPage,pageSize,Pagination.PAGINATION_SQLSERVER);
			PreparedStatement ps = con.prepareStatement(sql);
			System.out.println("sql:" + sql);
			int paramSize = params.size();
			for (int i = 0; i < paramSize; i++) {
				ps.setString(i+1, (String) params.get(i));
			}
			for (int i = 0; i < paramSize; i++) {
				ps.setString(i+1+paramSize, (String) params.get(i));
			}
			ResultSet rs = ps.executeQuery();
			List<Object[]> items = new ArrayList<Object[]>();
			while(rs.next()){
				Object[] row = new Object[rs.getMetaData().getColumnCount()];
				for (int i = 0; i < row.length; i++) {
					row[i] = rs.getObject(i+1);
				}
				items.add(row);
			}
			String hqlCount = sql;
			if(hqlCount.indexOf("from")!=-1){
				hqlCount = hqlCount.substring(hqlCount.indexOf("from"),hqlCount.length());
			}
			if(hqlCount.indexOf("order by")!=-1){
				hqlCount = hqlCount.replaceAll("(order by [^\\s]+? desc)|(order by [^\\s]+? asc)", "");
			}
			hqlCount = "select count(*) " + hqlCount;
			System.out.println("hqlCount:" + hqlCount);
			ps = con.prepareStatement(hqlCount);
			for (int i = 0; i < paramSize; i++) {
				ps.setString(i+1, (String) params.get(i));
			}
			for (int i = 0; i < paramSize; i++) {
				ps.setString(i+1+paramSize, (String) params.get(i));
			}
			ResultSet rsCount = ps.executeQuery();
			int totalCount = 0;
			if(rsCount.next()){
				totalCount = rsCount.getInt(1);
			}
			Pagination pagin = new Pagination(items, totalCount, curPage, pageSize);
			return pagin;
		} catch (DataAccessResourceFailureException e) {
			e.printStackTrace();
		} catch (HibernateException e) {
			e.printStackTrace();
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}	
	
	public Pagination getPageByLimitedSQL(String sql, List params, int curPage, int pageSize){
		try {
			Connection con = this.getSession().connection();
			PreparedStatement ps = con.prepareStatement(sql);
			System.out.println("sql:" + sql);
			int paramSize = params.size();
			for (int i = 0; i < paramSize; i++) {
				ps.setString(i+1, (String) params.get(i));
			}
			for (int i = 0; i < paramSize; i++) {
				ps.setString(i+1+paramSize, (String) params.get(i));
			}
			ResultSet rs = ps.executeQuery();
			List<Object[]> items = new ArrayList<Object[]>();
			while(rs.next()){
				Object[] row = new Object[rs.getMetaData().getColumnCount()];
				for (int i = 0; i < row.length; i++) {
					row[i] = rs.getObject(i+1);
				}
				items.add(row);
			}
			String hqlCount = sql;
			if(hqlCount.indexOf("from")!=-1){
				hqlCount = hqlCount.substring(hqlCount.indexOf("from"),hqlCount.length());
			}
			if(hqlCount.indexOf("order by")!=-1){
				hqlCount = hqlCount.replaceAll("(order by [^\\s]+? desc)|(order by [^\\s]+? asc)", "");
			}
			hqlCount = "select count(*) " + hqlCount;
			System.out.println("hqlCount:" + hqlCount);
			ps = con.prepareStatement(hqlCount);
			for (int i = 0; i < paramSize; i++) {
				ps.setString(i+1, (String) params.get(i));
			}
			for (int i = 0; i < paramSize; i++) {
				ps.setString(i+1+paramSize, (String) params.get(i));
			}
			ResultSet rsCount = ps.executeQuery();
			int totalCount = 0;
			if(rsCount.next()){
				totalCount = rsCount.getInt(1);
			}
			Pagination pagin = new Pagination(items, totalCount, curPage, pageSize);
			return pagin;
		} catch (DataAccessResourceFailureException e) {
			e.printStackTrace();
		} catch (HibernateException e) {
			e.printStackTrace();
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Object[] findObjectBySQL(String sql, List params) {
		try {
			Connection con = this.getSession().connection();
			PreparedStatement ps = con.prepareStatement(sql);
			int paramSize = params.size();
			for (int i = 0; i < paramSize; i++) {
				ps.setString(i+1, (String) params.get(i));
			}
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				Object[] row = new Object[rs.getMetaData().getColumnCount()];
				for (int i = 0; i < row.length; i++) {
					row[i] = rs.getObject(i+1);
				}
				return row;
			}
		} catch (DataAccessResourceFailureException e) {
			e.printStackTrace();
		} catch (HibernateException e) {
			e.printStackTrace();
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}	
}