Spring 整合HibernateDaoSupport（业务Dao继承CommonDaoImpl）

注意：如果业务DAO中的方法名与CommonDAOImpl中的方法名相同 比如save方法 需要使用super.save(Object obj)

1.公共的CommonDAOImpl

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Filter;
import org.hibernate.HibernateException;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.ReplicationMode;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;



/**
 * 
 * 通用dao的hibernate实现
 * 
 */
@SuppressWarnings({"unchecked","deprecation"})
@Repository("commonDao")
public class CommonDaoImpl extends HibernateDaoSupport implements CommonDao {
        //下面这个方法挺重要 必须加上 否则报错
	@Autowired
	public void setSessionFactoryOverride(SessionFactory sessionFactory)
	{
		super.setSessionFactory(sessionFactory);
	}
	
	public int executeUpdate(String sql) throws DataAccessException{
		return getSessionFactory().getCurrentSession().createSQLQuery(sql).executeUpdate();
	}
	
	public int bulkUpdate(String queryString) throws DataAccessException {
		return getHibernateTemplate().bulkUpdate(queryString);
	}

	public int bulkUpdate(String queryString, Object value) throws DataAccessException {
		return getHibernateTemplate().bulkUpdate(queryString, value);
	}

	public int bulkUpdate(String queryString, Object[] values) throws DataAccessException {
		return getHibernateTemplate().bulkUpdate(queryString, values);
	}

	public void clear() throws DataAccessException {
		getHibernateTemplate().clear();
	}

	public void closeIterator(Iterator it) throws DataAccessException {
		getHibernateTemplate().closeIterator(it);
	}

	public boolean contains(Object entity) throws DataAccessException {
		return getHibernateTemplate().contains(entity);
	}

	public void delete(Object entity) throws DataAccessException {
		getHibernateTemplate().delete(entity);
	}

	public void delete(Object entity, LockMode lockMode) throws DataAccessException {
		getHibernateTemplate().delete(entity, lockMode);
	}

	public void deleteAll(Collection entities) throws DataAccessException {
		getHibernateTemplate().deleteAll(entities);
	}

	public Filter enableFilter(String filterName) throws IllegalStateException {
		return getHibernateTemplate().enableFilter(filterName);
	}

	public void evict(Object entity) throws DataAccessException {
		getHibernateTemplate().evict(entity);
	}

	public Object execute(HibernateCallback action) throws DataAccessException {
		return getHibernateTemplate().execute(action);
	}

	public List executeFind(HibernateCallback action) throws DataAccessException {
		return getHibernateTemplate().executeFind(action);
	}

	public List find(String queryString) throws DataAccessException {
		return getHibernateTemplate().find(queryString);
	}
	
	public List find(String queryString, int rowrows) throws DataAccessException {
    	Session session = this.getSession();
    	List list = null;
        try {
			Query query = session.createQuery(queryString);  
			if(rowrows > 0){
				query = query.setMaxResults(rowrows);
			}
			list = query.list();
		} catch (HibernateException e) {
			throw e;
		}
        finally{
        	releaseSession(session);
        }
        
        return list;
	}
	
    public List findIntervalData(String hql, int pageNo, int endNo) {
    	Session session = this.getSession();
    	List list = null;
    	  try {
  			Query query = session.createQuery(hql);  
  			if((endNo-pageNo) > 0){
  				query = query.setFirstResult(pageNo).setMaxResults(endNo);
  			}
  			list = query.list();
  		} catch (HibernateException e) {
  			throw e;
  		}
          finally{
          	releaseSession(session);
          }
    	return list;
    }

	public List find(String queryString, Object value) throws DataAccessException {
		return getHibernateTemplate().find(queryString, value);
	}

	public List find(String queryString, Object[] values) throws DataAccessException {
		return getHibernateTemplate().find(queryString, values);
	}

	public List findByCriteria(DetachedCriteria criteria) throws DataAccessException {
		return getHibernateTemplate().findByCriteria(criteria);
	}

	public List findByCriteria(DetachedCriteria criteria, int firstResult, int maxResults)
			throws DataAccessException {
		return getHibernateTemplate().findByCriteria(criteria, firstResult, maxResults);
	}

	public List findByExample(Object exampleEntity) throws DataAccessException {
		return getHibernateTemplate().findByExample(exampleEntity);
	}

	public List findByExample(Object exampleEntity, int firstResult, int maxResults)
			throws DataAccessException {
		return getHibernateTemplate().findByExample(exampleEntity, firstResult, maxResults);
	}

	public List findByNamedParam(String queryString, String paramName, Object value)
			throws DataAccessException {
		return getHibernateTemplate().findByNamedParam(queryString, paramName, value);
	}

	public List findByNamedParam(String queryString, String[] paramNames, Object[] values)
			throws DataAccessException {
		return getHibernateTemplate().findByNamedParam(queryString, paramNames, values);
	}

	public List findByNamedQuery(String queryName) throws DataAccessException {
		return getHibernateTemplate().findByNamedQuery(queryName);
	}

	public List findByNamedQuery(String queryName, Object value) throws DataAccessException {
		return getHibernateTemplate().findByNamedQuery(queryName, value);
	}

	public List findByNamedQuery(String queryName, Object[] values) throws DataAccessException {
		return getHibernateTemplate().findByNamedQuery(queryName, values);
	}

	public List findByNamedQueryAndNamedParam(String queryName, String paramName, Object value)
			throws DataAccessException {
		return getHibernateTemplate().findByNamedQueryAndNamedParam(queryName, paramName, value);
	}

	public List findByNamedQueryAndNamedParam(String queryName, String[] paramNames, Object[] values)
			throws DataAccessException {
		return getHibernateTemplate().findByNamedQueryAndNamedParam(queryName, paramNames, values);
	}

	public List findByNamedQueryAndValueBean(String queryName, Object valueBean)
			throws DataAccessException {
		return getHibernateTemplate().findByNamedQueryAndValueBean(queryName, valueBean);
	}

	public List findByValueBean(String queryString, Object valueBean) throws DataAccessException {
		return getHibernateTemplate().findByValueBean(queryString, valueBean);
	}

	public void flush() throws DataAccessException {
		getHibernateTemplate().flush();
	}

	public Object get(Class entityClass, Serializable id) throws DataAccessException {
		return getHibernateTemplate().get(entityClass, id);
	}

	public Object get(String entityName, Serializable id) throws DataAccessException {
		return getHibernateTemplate().get(entityName, id);
	}

	public Object get(Class entityClass, Serializable id, LockMode lockMode)
			throws DataAccessException {
		return getHibernateTemplate().get(entityClass, id, lockMode);
	}

	public Object get(String entityName, Serializable id, LockMode lockMode)
			throws DataAccessException {
		return getHibernateTemplate().get(entityName, id, lockMode);
	}

	public void initialize(Object proxy) throws DataAccessException {
		getHibernateTemplate().initialize(proxy);
	}

	public Iterator iterate(String queryString) throws DataAccessException {
		return getHibernateTemplate().iterate(queryString);
	}

	public Iterator iterate(String queryString, Object value) throws DataAccessException {
		return getHibernateTemplate().iterate(queryString, value);
	}

	public Iterator iterate(String queryString, Object[] values) throws DataAccessException {
		return getHibernateTemplate().iterate(queryString, values);
	}

	public Object load(Class entityClass, Serializable id) throws DataAccessException {
		return getHibernateTemplate().load(entityClass, id);
	}

	public Object load(String entityName, Serializable id) throws DataAccessException {
		return getHibernateTemplate().load(entityName, id);
	}

	public void load(Object entity, Serializable id) throws DataAccessException {
		getHibernateTemplate().load(entity, id);
	}

	public Object load(Class entityClass, Serializable id, LockMode lockMode)
			throws DataAccessException {
		return getHibernateTemplate().load(entityClass, id, lockMode);
	}

	public Object load(String entityName, Serializable id, LockMode lockMode)
			throws DataAccessException {
		return getHibernateTemplate().load(entityName, id, lockMode);
	}

	public List loadAll(Class entityClass) throws DataAccessException {
		return getHibernateTemplate().loadAll(entityClass);
	}

	public void lock(Object entity, LockMode lockMode) throws DataAccessException {
		getHibernateTemplate().lock(entity, lockMode);
	}

	public void lock(String entityName, Object entity, LockMode lockMode)
			throws DataAccessException {
		getHibernateTemplate().lock(entityName, entity, lockMode);
	}

	public Object merge(Object entity) throws DataAccessException {
		return getHibernateTemplate().merge(entity);
	}

	public Object merge(String entityName, Object entity) throws DataAccessException {
		return getHibernateTemplate().merge(entityName, entity);
	}

	public void persist(Object entity) throws DataAccessException {
		getHibernateTemplate().persist(entity);
	}

	public void persist(String entityName, Object entity) throws DataAccessException {
		getHibernateTemplate().persist(entityName, entity);
	}

	public void refresh(Object entity) throws DataAccessException {
		getHibernateTemplate().refresh(entity);
	}

	public void refresh(Object entity, LockMode lockMode) throws DataAccessException {
		getHibernateTemplate().refresh(entity, lockMode);
	}

	public void replicate(Object entity, ReplicationMode replicationMode)
			throws DataAccessException {
		getHibernateTemplate().replicate(entity, replicationMode);
	}

	public void replicate(String entityName, Object entity, ReplicationMode replicationMode)
			throws DataAccessException {
		getHibernateTemplate().replicate(entityName, entity, replicationMode);
	}

	public Serializable save(Object entity) throws DataAccessException {
		return getHibernateTemplate().save(entity);
	}

	public Serializable save(String entityName, Object entity) throws DataAccessException {
		return getHibernateTemplate().save(entityName, entity);
	}

	public void saveOrUpdate(Object entity) throws DataAccessException {
		getHibernateTemplate().saveOrUpdate(entity);
	}

	public void saveOrUpdate(String entityName, Object entity) throws DataAccessException {
		getHibernateTemplate().saveOrUpdate(entityName, entity);
	}

	public void saveOrUpdateAll(Collection entities) throws DataAccessException {
		getHibernateTemplate().saveOrUpdateAll(entities);
	}

	public void update(Object entity) throws DataAccessException {
		getHibernateTemplate().update(entity);
	}

	public void update(Object entity, LockMode lockMode) throws DataAccessException {
		getHibernateTemplate().update(entity, lockMode);
	}

	public void update(String entityName, Object entity) throws DataAccessException {
		getHibernateTemplate().update(entityName, entity);
	}

	public void update(String entityName, Object entity, LockMode lockMode)
			throws DataAccessException {
		getHibernateTemplate().update(entityName, entity, lockMode);
	}

	public List findByExample(String entityName, Object exampleEntity) throws DataAccessException {
		return getHibernateTemplate().findByExample(entityName, exampleEntity);
	}

	public List findByExample(String entityName, Object exampleEntity, int firstResult,
			int maxResults) throws DataAccessException {
		return getHibernateTemplate().findByExample(entityName, exampleEntity, firstResult,
				maxResults);
	}


    public BigDecimal getSequence(String sequenceName) throws DataAccessException {
    	Session session = this.getSession();
        Query query = null;
        BigDecimal result = null;
		try {
			query = session.createSQLQuery("select " + sequenceName + ".nextval from dual");
			result = (BigDecimal) query.list().get(0);
		} catch (HibernateException e) {
			throw e;
		}
		finally{
			releaseSession(session);
		}
		
        return result;
    }

	public void callProcedure(String procedureDefinition, List setParamList, List outParamList)
            throws DataAccessException {
		Connection conn = this.getSession().connection();
        CallableStatement callableStatement = null;
        try{
            callableStatement = conn.prepareCall(procedureDefinition);
            if (setParamList != null){
                for (int i = 0; i < setParamList.size(); i++){
                    if (setParamList.get(i) instanceof Long){
                        callableStatement.setLong(i + 1, (Long) setParamList.get(i));
                    }else if (setParamList.get(i) instanceof String){
                        callableStatement.setString(i + 1,(String) setParamList.get(i));
                    }
                }
            }
            if (outParamList != null){
                for (int i = 0; i < outParamList.size(); i++){
                    if (outParamList.get(i) instanceof Long){
                        callableStatement.registerOutParameter(setParamList.size() + i + 1, Types.INTEGER);
                    }else if (outParamList.get(i) instanceof String){
                        callableStatement.registerOutParameter(setParamList.size()+ i + 1, Types.VARCHAR);
                    }
                }
            }
            callableStatement.executeUpdate();
            if (outParamList != null){
                for (int i = 0; i < outParamList.size(); i++){
                    if (outParamList.get(i) instanceof Long){
                        outParamList.set(i, callableStatement.getLong(setParamList.size() + i + 1));
                    }
                    else if (outParamList.get(i) instanceof String){
                        outParamList.set(i, callableStatement.getString(setParamList.size() + i + 1));
                    }
                }
            }
        }
        catch (SQLException e)
        {
            SQLExceptionTranslator translator = getHibernateTemplate().getJdbcExceptionTranslator();
            throw translator.translate("", null, e);
        }finally{
        	if(callableStatement != null){
        		try {
					callableStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
        	}
        	if(conn != null){
        		try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
        	}

        }
    }

    public int getrowsByCriteria(final DetachedCriteria detachedCriteria) {
        
        return -1;
    }
    
    
    /**
     * 通用的调用原生SQL
     * 
     * @param sql sql语句
     * @param obj 所对应的参数
     * @return List
     */
    public int countNativeSQL(final String sql,
            final Object[] obj) throws Exception{
      //// System.out.println("sql:"+sql);
        try{
            return (Integer) getHibernateTemplate().executeWithNativeSession(
                new HibernateCallback() {
                    public Object doInHibernate(Session session)
                              throws HibernateException {
                        PreparedStatement st = null;
                        ResultSet rs = null;
                        int result = 0;
                        try {
                            st = session.connection().prepareStatement(sql);
                            if (obj != null) {
                                for (int i = 0; i < obj.length; i++) {
                                	 //// System.out.println("obj[" + i + "]=" + obj[i]);
                                    st.setObject(i + 1, obj[i]);
                                }
                            }
                            rs = st.executeQuery();
                            while (rs.next()) {
                                result = rs.getInt(1);
                            }                            
                        } catch (HibernateException ex) {
                            throw ex;
                        } catch (Exception e) {
                            throw new HibernateException(e);
                        } finally {
                            if (rs != null) {
                                try {
                                    rs.close();
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            }
                            if (st != null) {
                                try {
                                    st.close();
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                        return result;
                    }
                });
        } catch (Exception ex) {
            throw ex;
        }
    }
    
    /**
     * 通用的调用原生SQL
     * 
     * @param sql sql语句
     * @param obj 所对应的参数
     * @return List
     */
    public boolean execNativeSQL(final String sql,
            final Object[] obj) throws Exception{
    	 //// System.out.println("sql:"+sql);
        try{
            return  (boolean)getHibernateTemplate().executeWithNativeSession(
                new HibernateCallback() {
                    public Object doInHibernate(Session session)
                              throws HibernateException {
                        PreparedStatement st = null;
                        ResultSet rs = null;
                        boolean result = false;
                        try {
                            st = session.connection().prepareStatement(sql);
                            if (obj != null) {
                                for (int i = 0; i < obj.length; i++) {
                                	 ////  System.out.println("obj[" + i + "]=" + obj[i]);
                                    st.setObject(i + 1, obj[i]);
                                }
                            }
                            result = st.execute();
                        } catch (HibernateException ex) {
                            throw ex;
                        } catch (Exception e) {
                            throw new HibernateException(e);
                        } finally {
                            if (rs != null) {
                                try {
                                    rs.close();
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            }
                            if (st != null) {
                                try {
                                    st.close();
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                        return result;
                    }
                });
        } catch (Exception ex) {
            throw ex;
        }
    }
    
    /**
     * 通用的调用原生SQL
     * 
     * @param sql sql语句
     * @param obj 所对应的参数
     * @return List
     */
	public List<Map<String, Object>> findNativeSQL(final String sql,
            final Object[] obj) throws Exception
    {
		 //// System.out.println("sql:"+sql);
        try{
            return (List) getHibernateTemplate().executeWithNativeSession(
                    new HibernateCallback() {
                        public Object doInHibernate(Session session)
                                throws HibernateException
                        {
                            List resultList = new java.util.ArrayList();
                            PreparedStatement st=null;
                            ResultSet rs=null;
                            try
                            {
                                st = session.connection()
                                        .prepareStatement(sql);

                                if (obj != null)
                                {
                                    for (int i = 0; i < obj.length; i++)
                                    {
                                    	 //// System.out.println("obj["+i+"]="+obj[i]);
                                        st.setObject(i + 1, obj[i]);
                                    }
                                }
                                rs = st.executeQuery();
                                
                                ResultSetMetaData rsm = rs.getMetaData();
                                if(rs!=null){
	                                while (rs.next())
	                                {
	                                    Map map = new HashMap();
	                                    for (int col = 0; col < rsm.getColumnCount(); col++)
	                                    {
	                                        map.put(rsm.getColumnLabel(col + 1)
	                                                .toLowerCase(), rs
	                                                .getObject(col + 1));
	                                    }
	                                    resultList.add(map);
	                                }
                                }
                            }
                            catch (HibernateException ex)
                            {   
                                throw ex;
                            }catch(Exception e){
                                throw new HibernateException(e);
                            }finally{
                                if(rs!=null){
                                    try {
                                        rs.close();
                                    } catch (SQLException e) {
                                        e.printStackTrace();
                                    }
                                }
                                if(st!=null){
                                    try {
                                        st.close();
                                    } catch (SQLException e) {
                                        e.printStackTrace();
                                    }
                                }                               
                            }
                            return resultList;
                        }
                    });
           }catch(Exception ex){
               throw ex;
           }
    }

    public void delete(String entityName, Object entity) throws DataAccessException
    {
        getHibernateTemplate().delete(entityName, entity);
    }

    public void delete(String entityName, Object entity, LockMode lockMode) throws DataAccessException
    {
        getHibernateTemplate().delete(entityName, entity, lockMode);
    }
    
    public List<Object[]> findBySqlQuery(final String sql,final Object...paras) throws Exception{
    	List<Object[]> result = null;
    	Session session = this.getSession();
    	
    	Query query = session.createSQLQuery(sql);
		if (paras != null && paras.length > 0) 
		{
		    for (int index = 0; index < paras.length; index++) 
		    {
		        query.setParameter(index, paras[index]);
		    }
		}
		result = query.list();
		releaseSession(session);
		
		return result;
    }
    
	public Object uniqueResult(final String hql,final Object... paras) {
        return getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException {
            	
            	Query query = session.createQuery(hql);
            	query.setMaxResults(1);
            	for (int index = 0; index < paras.length; index++) 
                {
                    query.setParameter(index, paras[index]);
                }
            	
            	return query.uniqueResult();
            }
        });
    }
	
	public Object uniqueResultByNativeSql(final String sql,final Object... paras) {
        return getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException {
            	
            	Query query = session.createSQLQuery(sql);
            	query.setMaxResults(1);
            	for (int index = 0; index < paras.length; index++) 
                {
                    query.setParameter(index, paras[index]);
                }
            	
            	return query.uniqueResult();
            }
        });
    }
	
	/**
     *  根据原生sql查询dojo的gridx分页
     *  
     * @param sql 查询语句 第一个字段为rownumber_，后面为顺序传入的sql中的字段顺序
     * @param paras 参数
     * @return page data为object数组
	 * @throws Exception 
     */
	@SuppressWarnings("rawtypes")
	public List<Map<String, Object>> findGridByNativeSql(String sql,Object[] paras,int page,int rows) throws Exception{
		List<Map<String, Object>> list=null;
		Session session = this.getSession();
		/*DB2 写法
		 * String result = sql.replaceFirst("select", "select * from ( select rownumber() over() as rownumber_, ") + ") as temp_ where rownumber_ between "+(page+1)+" and "+(page+rows)+" ";
		 * */
		/*oracle 写法*/
		String result = "select * from ( select rownum as rownumber_,tmp_.* from ( "+sql+ ") tmp_ ) temp_ where rownumber_  between "+((page-1)*rows+1)+" and "+(page*rows)+" ";
		//mysql写法sql
		//String result =  "SELECT * FROM ("+sql+" ) TABLE_PAGE LIMIT "+page+","+rows;
		 //// System.out.println("SQL:"+result);
		return this.findNativeSQL(result, paras);
		/*// 查询
		List listQuery = query.list();
		Iterator it= listQuery.iterator();
		while(it.hasNext()){
			Object[] obj=(Object[]) it.next();
			int i=0;
			i=obj.length;
			HashMap map=new HashMap();
			for (int j=0;j<i;j++) { 
				map.put(fieldList.get(j), obj[j+1]);
			}
			list.add(map);
		}
		
		return list;*/
		
	}

}

2.业务Dao

import java.util.List;

import org.springframework.stereotype.Repository;

@Repository
public class LoginDaoImpl extends CommonDaoImpl implements LoginDao{
	
	@Override
	public List<?> verify(String username, String password) throws Exception {
		Object[] values = new Object[] {username.trim(), password.trim()}; 
		return this.findNativeSQL(" select t1.*,t2.deptname from Tuscuser t1 left join tuscdept t2 on t1.deptid = t2.deptid where t1.userid=? and t1.userpassword=? and t1.status='1' ", values);
		//return commonDao.find("from Tuscuser t1 where t1.userid=? and t1.userpassword=? and t1.status='1' ", values);
	}

	@Override
	public List<?> queryMyAuth(String userid) throws Exception {
		Object[] values = new Object[] {userid}; 
		return this.findNativeSQL(" select t1.autcode from tuscroleaut t1 join tuscroleuser t2 on t1.roleid = t2.roleid and t2.userid=? group by t1.autcode ", values);
	}
	
	@Override
	public List<?> queryMyMenu(String userid) throws Exception {
		Object[] values = new Object[] {userid}; 
		return this.findNativeSQL(" select t1.menuid,t1.parentmenuid,t1.menuname,t1.menucode,t1.menutype,t1.urlcode,t1.iconclass from tcfgmenu t1 where t1.menuid in (select t2.menuid from tuscrolemenu t2,tuscroleuser t3 where t2.roleid = t3.roleid and t3.userid=? group by t2.menuid) order by t1.menucode  ", values);
	}

	@Override
	public List<?> queryMenuTitle() throws Exception {
		return  this.find("from Tcfgmenu t1 where t1.menutype='0' order by t1.menucode");
	}

	@Override
	public List<?> queryMyDataAuth(String userid) throws Exception {
		Object[] values = new Object[] {userid}; 
		return this.findNativeSQL(" select t1.datacode from tuscroledata t1 where t1.roleid in (select roleid from tuscroleuser where userid=? ) group by t1.datacode ", values);
	}
}