jdbcTemplate与hibernateTemplate混用-优快云博客

一直都觉得spring这个框架很不错，对于jdbcTemplate、hibernateTemplate这2个模板更是非常喜欢，有了他们用起来非常方便。一般情况下都是单独使用jdbcTemplate、hibernateTemplate，很少有混用的情况。为什么要混用呢？我想这个大家都懂的。

在这里呢，给大家介绍一个我自己研究的配置（因为网上根本就没有一个完整的配置，都夸夸其谈不做实事，对此种事情本人非常愤慨，对于一个非常需要帮助的人，查到这样的资料，都有慰问他十八代祖宗的想法），供大家参考，望多多指教。本文属原创，转载请注明出处。

系统架构（struts2+spring3.0.5+hibernate3.6）采用mvc模式，dao层继承ABaseDao，ABaseDao中封装了jdbcTemplate、hibernateTemplate，并提供jdbc、hibernate两种操作的CUID方法。事务由hibernateTransaction托管并有spring的aop在service层处理。每一个dao都采用泛型方式，注入一个pojo，dao中并不需要写方法，因为ABaseDao都已经实现了，如果ABaseDao中的方法满足不了业务需求，则可以在自己的dao中写相应的方法。这样在servers层就可以直接调用ABaseDao中的方法了，services的每个方法都要throws Exception，注意不要写try-catch，一定要throw出来，事务就靠这个异常回滚呢。

不多说，上代码。

1.applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>

<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:jdbc.properties" />
</bean>

<bean id="dataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
  <property name="alias" value="CS_dbname"/>
  <property name="driver" value="${jdbc.driverClassName}" />
  <property name="driverUrl" value="${jdbc.url}" />
  <property name="user" value="${jdbc.username}" />
  <property name="password" value="${jdbc.password}" />

  <property name="maximumActiveTime" value="600000"/>
  <property name="maximumConnectionCount" value="100"/>
  <property name="minimumConnectionCount" value="10"/>
  <property name="simultaneousBuildThrottle" value="50"/>
        <property name="testBeforeUse" value="true" />
  <property name="houseKeepingTestSql" value="select 1*1 from dual"/>

        <property name="prototypeCount" value="5" />
        <property name="trace" value="true" />
        <property name="verbose" value="true" />
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="lobHandler" ref="defaultLobHandler"/>
  <property name="configLocation" value="classpath:hibernate.cfg.xml" />
  <property name="hibernateProperties">
   <props>
    <prop key="hibernate.dialect">
     org.hibernate.dialect.MySQLInnoDBDialect
    </prop>
    <prop key="hibernate.connection.autocommit">false</prop>
    <prop key="hibernate.autoReconnect">true</prop>
    <prop key="hibernate.format_sql">false</prop>
    <prop key="hibernate.show_sql">false</prop>
    <prop key="hibernate.hbm2ddl.auto">none</prop>
   </props>
  </property>
</bean>


<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
  <property name="dataSource" ref="dataSource" />
</bean>


<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate" >
  <property name="sessionFactory" ref="sessionFactory" />
</bean>

<bean id="hibernateTransaction" class="org.springframework.orm.hibernate3.HibernateTransactionManager" >
  <property name="sessionFactory" ref="sessionFactory" />
  <property name="dataSource" ref="dataSource" />
</bean>


<bean id="defaultLobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" lazy-init="true" />

<tx:advice id="txAdvice1" transaction-manager="hibernateTransaction">
        <tx:attributes>
            <tx:method name="list*" propagation="NOT_SUPPORTED" />
            <tx:method name="query*" propagation="NOT_SUPPORTED" />
            <tx:method name="get*" propagation="NOT_SUPPORTED" />
            <tx:method name="find*" propagation="NOT_SUPPORTED" />
            <tx:method name="*" propagation="REQUIRED" rollback-for="Throwable"/>
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:pointcut id="allManagerMethod" expression="execution(* com.wxthtf.*.*Service.*(..))" />
        <aop:advisor advice-ref="txAdvice1" pointcut-ref="allManagerMethod" />
    </aop:config>

    <aop:config>
        <aop:pointcut id="allManagerMethod1" expression="execution(* com.wxthtf.*.*.*Service.*(..))" />
        <aop:advisor advice-ref="txAdvice1" pointcut-ref="allManagerMethod1" />
    </aop:config>

</beans>

2.ABaseDao

package com.wxthtf.common;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;

/**
* <li>Title: ABaseDao.java</li>
* <li>Project: WorkFrame</li>
* <li>Package: com.crescent.common</li>
* <li>Description: Dao的基础类</li>
* <li>Copyright: Copyright (c) 2011</li>
* <li>Company: wxthtf Technologies </li>
* <li>Created on May 23, 2011 9:46:09 PM</li>
*
* @author chun_chang
* @version 1.0.0.0
*
* @param <T>
*/
public abstract class ABaseDao<T, PK extends Serializable> {
private Class<T> entityClass = null;
//private Serializable id = null;

@SuppressWarnings("unchecked")
public ABaseDao(){
  Type genType = getClass().getGenericSuperclass();
  Type[] params = ((ParameterizedType) genType).getActualTypeArguments();
  entityClass = (Class<T>) params[0];
  //id = (Serializable) params[1];
}

protected JdbcTemplate jdbcTemplate = null;
protected HibernateTemplate hibernateTemplate = null;
protected LobHandler defaultLobHandler = null;

private String pageSqlPrefix = "select tt.* from (select t.*, rownum num from ( ";
private String pageSqlSuffix = " ) t) tt where ? < num and num <= ?";

/*
* 1.jdbc部分 ***************************************************************
*/

/**
* 描述：getPageSql oracle分页
* @param sql
* @return
* @throws Exception
* @CreateOn Jul 26, 2011 8:45:16 PM
* @author chun_chang
*/
protected String getPageSql(String sql) throws Exception {
  StringBuffer buff = new StringBuffer(pageSqlPrefix);
  buff.append(sql).append(pageSqlSuffix);
  return buff.toString();
}

/**
* query
*/
public int queryForInt(String sql) throws Exception {
return this.jdbcTemplate.queryForInt(sql);
}

public int queryForInt(String sql, Object[] params) throws Exception {
return this.jdbcTemplate.queryForInt(sql, params);
}

/**
* 描述：queryForString 只查询一个字段
* @param sql
* @return
* @throws Exception
* @CreateOn Jul 6, 2011 5:24:28 PM
* @author chun_chang
*/
@SuppressWarnings("unchecked")
public String queryForString(String sql) throws Exception {
return (String) this.jdbcTemplate.queryForObject(sql, new RowMapper(){

   public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    String str = rs.getString(1);
    return StringUtils.isBlank(str) ? "" : str;
   }

});
}

/**
* 描述：queryForString 只查询一个字段
* @param sql
* @param params
* @return
* @throws Exception
* @CreateOn Jul 6, 2011 5:24:45 PM
* @author chun_chang
*/
@SuppressWarnings("unchecked")
public String queryForString(String sql, Object[] params) throws Exception {
return (String) this.jdbcTemplate.queryForObject(sql, params, new RowMapper(){

   public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    String str = rs.getString(1);
    return StringUtils.isBlank(str) ? "" : str;
   }

});
}

public T queryForObject(String sql) throws Exception {
  List<T> list = this.queryForList(sql);
  if(null == list || list.size() == 0) {
   return null;
  } else {
   return list.get(0);
  }
}

public T queryForObject(String sql, Object[] params) throws Exception {
  List<T> list = this.queryForList(sql, params);
  if(null == list || list.size() == 0) {
   return null;
  } else {
   return list.get(0);
  }
}

public List<T> queryForList(String sql) throws Exception {
return this.jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(entityClass));
}

public List<T> queryForList(String sql, Object[] params) throws Exception {
return this.jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<T>(entityClass));
}

@SuppressWarnings("unchecked")
public List<String> query(String sql) throws Exception {
return this.jdbcTemplate.query(sql, new RowMapper(){

   public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    return rs.getString(1);
   }

});
}

@SuppressWarnings("unchecked")
public List<String> query(String sql, Object[] params) throws Exception {
return this.jdbcTemplate.query(sql, params, new RowMapper(){

   public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    String str = rs.getString(1);
    return StringUtils.isBlank(str) ? "" : str;
   }

});
}

/**
* insert、update
*/
public int save(String sql, final Object[] params) throws Exception {
return this.jdbcTemplate.update(sql, new PreparedStatementSetter(){

   public void setValues(PreparedStatement ps) throws SQLException {
    for(int i = 1; i <= params.length; i++) {
     ps.setObject(i, params[i - 1]);
    }
   }

});
}

public int[] batchSave(String sql, final List<Object[]> list) throws Exception {
return this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){

   public int getBatchSize() {
    return list.size();
   }

   public void setValues(PreparedStatement ps, int i) throws SQLException {
    Object[] params = list.get(i);
    for(int j = 1; j <= params.length; j++) {
     ps.setObject(j, params[j - 1]);
    }
   }

});
}

public int[] batchSave(String sql, final Object[] params) throws Exception {
return this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){

   public int getBatchSize() {
    return params.length;
   }

   public void setValues(PreparedStatement ps, int i) throws SQLException {
    ps.setObject(1, params[i]);
   }

});
}

/**
* delete
*/
public int delete(String sql) throws Exception {
return this.jdbcTemplate.update(sql);
}

public int delete(String sql, final Object... params) throws Exception {
return this.jdbcTemplate.update(sql, params);
}

public int deleteById(String sql, final String id) throws Exception {
return this.jdbcTemplate.update(sql, new PreparedStatementSetter(){

   public void setValues(PreparedStatement ps) throws SQLException {
    ps.setObject(1, id);
   }

});
}

public int[] batchDeleteByIds(String sql, final String[] ids) throws Exception {
return this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){

   public int getBatchSize() {
    return ids.length;
   }

   public void setValues(PreparedStatement ps, int i) throws SQLException {
    ps.setObject(1, ids[i]);
   }

});
}

public int[] batchDeleteByIds(String sql, final List<String> list) throws Exception {
return this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){

   public int getBatchSize() {
    return list.size();
   }

   public void setValues(PreparedStatement ps, int i) throws SQLException {
    ps.setObject(1, list.get(i));
   }

});
}

/*
* 2.hibernate部分 ***************************************************************
*/

/**
* 描述：queryString4Paging 使用HQL进行分页查询
* @param fields 要查询的字段数组
* @param fromAndWhereHql from与where条件字符串
* @param start 开始条数
* @param limit 长度
* @param params where条件参数
* @return
* @throws Exception
* @CreateOn Jul 6, 2011 5:33:54 PM
* @author chun_chang
*/
public String queryString4Paging(final String[] fields, final String fromAndWhereHql, final int start, final int limit, final Object[] params)
  throws Exception {

  if(null == fields || fields.length <= 0){
   return "{totalProperty:0,root:[]}";
  }

  if(StringUtils.isBlank(fromAndWhereHql) || start < 0 || limit < 0) {
   return "{totalProperty:0,root:[]}";
  }

  int totalProperty = 0;
  String json = "[]";
  JSONArray jsonArray = null;
  String[] jsonField = new String[fields.length];

  String countHql = "select count(*) " + fromAndWhereHql;
  StringBuffer queryHql = new StringBuffer("select ");
  for(int k=0; k<fields.length; k++){
   String field = fields[k];
   queryHql.append(field).append(",");
   if(field.lastIndexOf(".") > 0){
    jsonField[k] = field.substring(field.lastIndexOf(".") + 1, field.length());
   } else {
    jsonField[k] = field;
   }
  }
  queryHql.deleteCharAt(queryHql.length() - 1);
  queryHql.append(" ").append(fromAndWhereHql);

  List<?> list = this.hibernateTemplate.find(countHql, params);
  try {
   totalProperty = Integer.valueOf(String.valueOf(list.get(0)));
  } catch(Exception e) {
   totalProperty = 0;
  }

  List<?> list1 = this.execute(queryHql.toString(), start, limit, params);
  if(null != list1 && list1.size() > 0){
   List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
   if(jsonField.length > 1){
    for(int j = 0; j < list1.size(); j++) {
     Object[] obj = (Object[]) list1.get(j);
     Map<String, Object> map = new HashMap<String, Object>();
     for(int i = 0; i < jsonField.length; i++) {
      map.put(jsonField[i], obj[i]);
     }
     resultList.add(map);
    }
   } else {
    for(int j = 0; j < list1.size(); j++) {
     String obj = String.valueOf(list1.get(j));
     Map<String, Object> map = new HashMap<String, Object>();
     map.put(jsonField[0], obj);
     resultList.add(map);
    }
   }
   jsonArray = JSONArray.fromObject(resultList);
   json = jsonArray.toString();
  }
  return "{totalProperty:" + totalProperty + ",root:" + json + "}";
}

public T getById(String id) throws Exception {
  return this.hibernateTemplate.get(entityClass, id);
}

public List<T> getAll() throws Exception {
return this.hibernateTemplate.loadAll(entityClass);
}

public List<T> find(String hql) throws Exception {
return find(hql, (Object[]) null);
}

public List<T> find(String hql, Object value) throws Exception {
return find(hql, new Object[] { value });
}

@SuppressWarnings("unchecked")
public List<T> find(String hql, Object... values) throws Exception {
return this.hibernateTemplate.find(hql, values);
}

public Serializable save(T entity) throws Exception {
  Serializable ser = this.hibernateTemplate.save(entity);
  this.hibernateTemplate.flush();
  return ser;
}

public void update(T entity) throws Exception {
this.hibernateTemplate.update(entity);
this.hibernateTemplate.flush();
}

public void saveOrUpdate(T entity) throws Exception {
this.hibernateTemplate.saveOrUpdate(entity);
this.hibernateTemplate.flush();
}

public void saveOrUpdate(List<T> list) throws Exception {
this.hibernateTemplate.saveOrUpdateAll(list);
this.hibernateTemplate.flush();
}

public void delete(T entity) throws Exception {
this.hibernateTemplate.delete(entity);
this.hibernateTemplate.flush();
}

public void deleteById(PK id) throws Exception {
  T t = this.hibernateTemplate.get(entityClass, id);
  if(t != null) {
   this.hibernateTemplate.delete(t);
  }
}

public int deleteAll() throws Exception {
final String hql = "delete from " + entityClass.getName();
int count = this.hibernateTemplate.execute(new HibernateCallback<Integer>(){

   public Integer doInHibernate(Session session) throws HibernateException, SQLException {
    return session.createQuery(hql).executeUpdate();
   }

  });
  this.hibernateTemplate.flush();
  return count;
}

protected List<?> execute(final String hql, final int start, final int limit, final Object... params) {
List<?> list = this.hibernateTemplate.execute(new HibernateCallback<List<?>>(){

   public List<?> doInHibernate(Session session) throws HibernateException, SQLException {
    Query query = session.createQuery(hql);
    query.setFirstResult(start);
    query.setMaxResults(limit);
    if(null != params){
     for(int i = 0; i < params.length; i++) {
      query.setParameter(i, params[i]);
     }
    }
    return query.list();
   }

  });
  this.hibernateTemplate.flush();
  return list;
}

public void deleteAll(Collection<?> entities) throws Exception {
  this.hibernateTemplate.deleteAll(entities);
  this.hibernateTemplate.flush();
}

public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}

public LobHandler getDefaultLobHandler() {
return defaultLobHandler;
}

public void setDefaultLobHandler(LobHandler defaultLobHandler) {
this.defaultLobHandler = defaultLobHandler;
}

public HibernateTemplate getHibernateTemplate() {
return hibernateTemplate;
}

public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
this.hibernateTemplate = hibernateTemplate;
}
}

实例：

1.MenuDao

package com.wxthtf.sys.menu;

import com.wxthtf.common.ABaseDao;
import com.wxthtf.pojo.SecurityMenu;

/**
* <li>Title: MenuDao.java</li>
* <li>Project: WorkFrame</li>
* <li>Package: com.crescent.sys.menu</li>
* <li>Description: </li>
* <li>Copyright: Copyright (c) 2011</li>
* <li>Company: wxthtf Technologies </li>
* <li>Created on May 23, 2011 10:04:58 PM</li>
*
* @author chun_chang
* @version 1.0.0.0
*
*/
public class MenuDao extends ABaseDao<SecurityMenu, String>{

}

2.MenuService

package com.wxthtf.sys.menu;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.wxthtf.common.FormatUtil;
import com.wxthtf.pojo.SecurityFunction;
import com.wxthtf.pojo.SecurityMenu;
import com.wxthtf.pojo.SecurityUsers;
import com.wxthtf.sys.role.RoleFuncsMapDao;

/**
* <li>Title: MenuService.java</li>
* <li>Project: WorkFrame</li>
* <li>Package: com.crescent.sys.menu</li>
* <li>Description: </li>
* <li>Copyright: Copyright (c) 2011</li>
* <li>Company: wxthtf Technologies </li>
* <li>Created on May 24, 2011 9:55:48 PM</li>
*
* @author chun_chang
* @version 1.0.0.0
*
*/
public class MenuService {
private MenuDao menuDao = null;

public String delMenu(String id) throws Exception {
  // 检查菜单是否被桌面使用
  String sql = "select count(*) from home_panels where menuId = ?";
  int count = this.menuDao.queryForInt(sql, new Object[]{ id });
  if(count == 0){// 菜单没有被使用
   sql = "select functionId from security_function where menuId like '" + id + "%'";
   List<String> list = this.funcsDao.query(sql);

   sql = "delete from security_role_function where functionId = ?";
   this.roleFuncsMapDao.batchDeleteByIds(sql, list);

   sql = "delete from security_function where menuId like '" + id + "%'";
   this.funcsDao.delete(sql);

   sql = "delete from security_menu where menuId like '" + id + "%'";
   this.menuDao.delete(sql);
   return "1";
  } else {
   return "0";
  }
}