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"?>

<beans default-autowire="byName"
 xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:tx="http://www.springframework.org/schema/tx"
 xmlns:aop="http://www.springframework.org/schema/aop"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
  http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
  http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

 <!-- 自动注入 -->
 <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>
 
 <!-- JDBC模板 -->
 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
  <property name="dataSource" ref="dataSource" />
 </bean>
 
 <!-- Hibernate模板 -->
 <bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate" >
  <property name="sessionFactory" ref="sessionFactory" />
 </bean>
 <!-- Hibernate事务 -->
 <bean id="hibernateTransaction" class="org.springframework.orm.hibernate3.HibernateTransactionManager" >
  <property name="sessionFactory" ref="sessionFactory" />
  <property name="dataSource" ref="dataSource" />
 </bean>
 
 <!-- blob、clob设置 -->
 <bean id="defaultLobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" lazy-init="true" />

 <!-- AOP事务控制 -->
  <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";
  }
 }

 

 public MenuDao getMenuDao() {
  return menuDao;
 }

 public void setMenuDao(MenuDao menuDao) {
  this.menuDao = menuDao;
 }

 

}

 

 

 

评论

2 楼 chun521521 2011-07-27  

谢谢，其实主要的就是applicationContext.xml配置，ABaseDao是我自己封装的。只要有了template，一切尽在掌握。

1 楼 KimHo 2011-07-26  

这么一长串看下去，头有点晕
整理成pdf文档就更好了，呵呵。
不过还是支持下这么热心分享技术的精神