Spring + Hibernate + JdbcTemplate

spring同时使用hibernate和jdbcTemplate事务 http://hi.baidu.com/i_ccboy/item/c9b82d88bc07cacdef083d1f
Spring的事务管理难点剖析(5):联合军种作战的混乱 http://www.iteye.com/topic/1121586
spring jdbcTemplate使用 http://log-cd.iteye.com/blog/215059
spring3 jdbctemplate 注解实例 http://blog.csdn.net/shadowsick/article/details/8888187
Spring JdbcTemplate方法详解 http://blog.csdn.net/dyllove98/article/details/7772463
Spring--JdbcTemplate详解-1 http://canann.iteye.com/blog/1729600
Spring--JdbcTemplate详解-2 http://canann.iteye.com/blog/1729604

两个配合使用的类：
NamedParameterJdbcTemplate 详解 http://blog.csdn.net/dyllove98/article/details/7772470
MapSqlParameterSource  http://wangjianwei866.blog.163.com/blog/static/9295823200991545549940/



部分源码
web.xml
==================================================================

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
		  http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
         version="3.0">

    <display-name>Archetype Created Web Application</display-name>
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath*:applicationContext*.xml</param-value>
    </context-param>
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
    <servlet>
        <servlet-name>springmvc</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <load-on-startup>1</load-on-startup>
    </servlet>

    <servlet-mapping>
        <servlet-name>springmvc</servlet-name>
        <url-pattern>*.do</url-pattern>
    </servlet-mapping>
    <filter>
        <filter-name>openSessionInViewFilter</filter-name>
        <filter-class>org.springframework.orm.hibernate4.support.OpenSessionInViewFilter</filter-class>
    </filter>
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
</web-app>

servlet.xml
=================================================

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    	http://www.springframework.org/schema/beans/spring-beans.xsd
    	http://www.springframework.org/schema/mvc
    	http://www.springframework.org/schema/mvc/spring-mvc.xsd
    	http://www.springframework.org/schema/context
    	http://www.springframework.org/schema/context/spring-context.xsd">
    <mvc:annotation-driven/>
    <context:component-scan base-package="com.controller" use-default-filters="false">
        <context:include-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
        <!--<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Service"/>
        <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Repository"/>-->
    </context:component-scan>
    <mvc:resources location="/resources/" mapping="/resources/**"/>

    <bean class="org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter"></bean>
    <bean class="org.springframework.web.servlet.mvc.HttpRequestHandlerAdapter"></bean>

    <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="requestContextAttribute" value="rc"/>
        <property name="viewClass" value="org.springframework.web.servlet.view.JstlView"/>
        <property name="prefix" value="/"/>
        <property name="suffix" value=".jsp"/>
    </bean>
</beans>

applicationContext-hibernate.xml
===============================================================

<?xml version="1.0" encoding="UTF-8"?>
<beans 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:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:p="http://www.springframework.org/schema/p"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    	http://www.springframework.org/schema/beans/spring-beans.xsd
    	http://drools.org/schema/drools-spring
    	http://drools.org/schema/drools-spring-1.3.0.xsd
    	http://www.springframework.org/schema/tx
    	http://www.springframework.org/schema/tx/spring-tx.xsd
    	http://www.springframework.org/schema/context
    	http://www.springframework.org/schema/context/spring-context.xsd
    	http://www.springframework.org/schema/aop
    	http://www.springframework.org/schema/aop/spring-aop.xsd
    	http://www.springframework.org/schema/util
    	http://www.springframework.org/schema/util/spring-util.xsd"
       default-lazy-init="true">

    <!-- spring的jdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!-- hibernate的sessionFactory -->
    <bean id="hibernateTemplate" class="org.springframework.orm.hibernate4.HibernateTemplate">
        <property name="sessionFactory" ref="sessionFactory"/>
    </bean>
    <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"
          p:packagesToScan="com.model">
        <property name="dataSource">
            <ref bean="dataSource"/>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.format_sql">true</prop>
                <!--<prop key="hibernate.cache.use_query_cache">true</prop>
                <prop key="hibernate.cache.use_second_level_cache">true</prop>
                <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
                <prop key="hibernate.cache.use_structured_entries">true</prop>
                <prop key="hibernate.generate_statistics">true</prop>-->
            </props>
        </property>
    </bean>
    <!-- 事务管理 -->
    <!-- 同时管理jdbcTemplate和hibernate事务 -->
    <bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory"/>
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!-- 单独管理jdbcTemplate的事务 -->
    <!--<bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>-->
    <tx:annotation-driven transaction-manager="transactionManager"  proxy-target-class="true"/>

</beans>

service
==========================================================

package com.service.impl;

import com.dao.SysUserDao;
import com.model.SysUser;
import com.service.SysUserService;
import org.hibernate.SessionFactory;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * //Hibernate和JdbcTemplate的混合使用来测试事务
 * Created by Administrator on 14-6-16.
 */
@Service("sysUserService")
@Transactional
public class SysUserServiceImpl implements SysUserService {
    @Resource(name = "dataSource")
    private DataSource dataSource;
    @Resource(name = "hibernateTemplate")
    private HibernateTemplate hibernateTemplate;
    @Resource(name = "sessionFactory")
    private SessionFactory sessionFactory;
    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Resource(name = "sysUserDao")
    private SysUserDao sysUserDao;

    //NamedParameterJdbcTemplate对JdbcTemplate封装，增加了命名参数特性
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    //SimpleJdbcTemplate对JdbcTemplate封装,某些特性要在java5以上才工作
    //private SimpleJdbcTemplate simpleJdbcTemplate;

    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public void test(boolean isNeedCommit) {
        //Hibernate
        testHibernate();
        testHibernateTemplate();


        //Spring JDBCTemplate
        testJdbcTemplateInsert1();
        testJdbcTemplateInsert2();
        testJdbcTemplateDelete();
        testJdbcTemplateUpdate();

        testJdbcFindCountByFirstName();

        testJdbcTemplateSearch1();
        testJdbcTemplateSearch2();
        testJdbcTemplateSearch3();

        //是否回滚，由前台提交的参数决定
        sysUserDao.isNeedCommit(isNeedCommit);
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public void testHibernate() {
        Date date = new Date();
        SysUser user = new SysUser();
        user.setUserName("Hibernate[" + date.getTime() + "]");
        sessionFactory.getCurrentSession().save(user);
    }


    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public void testHibernateTemplate() {
        Date date = new Date();
        SysUser user = new SysUser();
        user.setUserName("HibernateTemplate[" + date.getTime() + "]");
        hibernateTemplate.save(user);
    }

    /**
     * 用SimpleJdbcInsert插入一条记录
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public void testJdbcTemplateInsert1() {
        System.out.println("===================================================testJdbcTemplateInsert1");
        Date date = new Date();
        //jdbcTemplate = new JdbcTemplate(dataSource);
        //namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        //SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource); //这个类已经过时，使用NamedParameterJdbcTemplate代替
        SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource)
                .withTableName("sys_user")
                .usingColumns("user_name", "password")//插入这些字段
                .usingGeneratedKeyColumns("user_id");//带回生成的id

        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("user_name", "testJdbcTemplateInsert1[" + date.getTime() + "]");
        parameters.put("password", "testJdbcTemplateInsert1");
        long id = simpleJdbcInsert.executeAndReturnKey(parameters).longValue();
        System.out.println(id);
    }

    /**
     * 插入记录并返回自动生成的主键Id(MySQL中不行，Oracle可以)
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public void testJdbcTemplateInsert2() {
        System.out.println("===================================================testJdbcTemplateInsert2");
        final String addSql = "insert into sys_user(user_name,password) values (?,?)";
        final Date date = new Date();
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                PreparedStatement ps =
                        conn.prepareStatement(addSql, new String[]{"id"});//返回id
                ps.setString(1, "testJdbcTemplateInsert2[" + date.getTime() + "]");
                ps.setString(2, null);
                return ps;
            }

        });
        System.out.println(keyHolder.getKey());
    }

    /**
     * //使用SqlUpdate编译sql并执行
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public void testJdbcTemplateUpdate() {
        System.out.println("===================================================testJdbcTemplateUpdate");
        SqlUpdate sqlUpdate = new SqlUpdate();
        sqlUpdate.setDataSource(dataSource);
        sqlUpdate.setSql("update sys_user set password=? where user_name=?");
        sqlUpdate.declareParameter(new SqlParameter(Types.VARCHAR));
        sqlUpdate.declareParameter(new SqlParameter(Types.VARCHAR));
        sqlUpdate.setReturnGeneratedKeys(true);
        sqlUpdate.compile();

        Date date = new Date();
        Object[] params = new Object[]{date.getTime() + "------", "testJdbcTemplateUpdate[1403262022766]"};
        KeyHolder keyHolder = new GeneratedKeyHolder();
        sqlUpdate.update(params, keyHolder);
        int id = -1;
        if (keyHolder.getKey() != null)
            id = keyHolder.getKey().intValue();
        System.out.println(id);
    }

    @Override
    public void testJdbcTemplateDelete() {

    }

    /**
     * 插入/更新/删除数据
     */
    public int testJdbcUpdate(String sql, Object[] obj) {
        return jdbcTemplate.update(sql, obj);
    }

    /**
     * 根据SQL查询记录总数
     *
     * @param sql
     * @return
     */
    public int testJdbcFindRowCountBySQL(String sql) {
        return jdbcTemplate.queryForInt(sql);
    }


    /**
     * SqlParameterSource
     * 统计user_name相同的总数
     */
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public void testJdbcFindCountByFirstName() {
        System.out.println("===================================================findCountByFirstName");
        String sql = "select count(0) from sys_user where user_name = :user_name";
        SqlParameterSource namedParameters = new MapSqlParameterSource("user_name", "pandy");
        //Map namedParameter = Collections.singletonMap("first_name",firstName);
        //还有一种Bean封装的方式
        //SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
        namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        int id = namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
        System.out.println(id);
    }

    /**
     * //使用RowMapper接口处理查询结果集
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public void testJdbcTemplateSearch1() {
        System.out.println("===================================================testJdbcTemplateSearch1");
        String sql = "select t1.* from sys_user t1 where t1.user_name = ?";
        List<SysUser> list = jdbcTemplate.query(sql,
                new Object[]{"pandy"}, new RowMapper() {
            @Override
            public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                SysUser vo = new SysUser();
                vo.setUserId(resultSet.getInt("user_id"));
                vo.setUserName(resultSet.getString("user_name"));
                vo.setPassword(resultSet.getString("password"));
                vo.setEmail(resultSet.getString("email"));
                return vo;
            }
        });
        System.out.println(list == null ? 0 : list.size());
    }

    /**
     * // MapSqlParameterSource 参数设定
     * // namedParameterJdbcTemplate //执行查询
     * // 使用ParameterizedRowMapper处理结果集
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public void testJdbcTemplateSearch2() {
        System.out.println("===================================================testJdbcTemplateSearch2");
        //String sql = "select user_id,user_name,password,email from sys_user where user_name = ?";
        String sql = "select user_id,user_name,password,email from sys_user where user_name = :user_name";
        ParameterizedRowMapper<SysUser> mapper = new ParameterizedRowMapper<SysUser>() {
            //notice the return type with respect to java 5 covariant return types
            public SysUser mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                SysUser vo = new SysUser();
                vo.setUserId(resultSet.getInt("user_id"));
                vo.setUserName(resultSet.getString("user_name"));
                vo.setPassword(resultSet.getString("password"));
                vo.setEmail(resultSet.getString("email"));
                return vo;
            }
        };
        SysUser vo = null;
        /*simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        vo = simpleJdbcTemplate.queryForObject(sql, mapper, "pandy");*/

        MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource();
        sqlParameterSource.addValue("user_name", "pandy");
        namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        vo = namedParameterJdbcTemplate.queryForObject(sql, sqlParameterSource, mapper);
        System.out.println(vo.getEmail());

    }

    /**
     * 返回所有对象
     *
     * @return
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public void testJdbcTemplateSearch3() {
        System.out.println("===================================================testJdbcTemplateSearch3");
        List<SysUser> list = jdbcTemplate.query(
                "select user_id,user_name,password,email from sys_user",
                new RowMapper() {
                    @Override
                    public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                        SysUser vo = new SysUser();
                        vo.setUserId(resultSet.getInt("user_id"));
                        vo.setUserName(resultSet.getString("user_name"));
                        vo.setPassword(resultSet.getString("password"));
                        vo.setEmail(resultSet.getString("email"));
                        return vo;
                    }
                });
    }

    /**
     * 批量更新
     */
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public int[] testJdbcUpdateBatchActors(final List list) {
        System.out.println("===================================================updateBatchActors");
        int[] updateCounts = jdbcTemplate.batchUpdate(
                "update actors set first_name = ?, last_name = ? where id =? ",
                new BatchPreparedStatementSetter() {

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

                    public void setValues(PreparedStatement ps, int i) throws SQLException {
//                        ps.setString(1, ((Actor)actors.get(i)).getFirstName());
//                        ps.setString(2, ((Actor)actors.get(i)).getLastName());
//                        ps.setLong(3, ((Actor)actors.get(i)).getId());
                    }

                });
        return updateCounts;
    }

    /**
     * 批量更新,
     * MapSqlParameterSource s,NamedParameterJdbcTemplate配合替换这里的代码，还没实现。
     */
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = RuntimeException.class)
    public int[] testJdbcUpdateBatchActorsSimple(final List<SysUser> sysUsers) {
        System.out.println("===================================================updateBatchActorsSimple");
        /*//如果对象数组与占位符出现位置一一对应
        //SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
        List<Object[]> batch = new ArrayList<Object[]>();
        for (SysUser sysUser : sysUsers) {
            Object[] values = new Object[]{//注意顺序
//                    actor.getFirstName(),
//                    actor.getLastName(),
//                    actor.getId()
            };
            batch.add(values);
        }
        int[] updateCounts = this.simpleJdbcTemplate.batchUpdate(
                "update actors set first_name = ?, last_name = ? where id =? ",
                batch);
        return updateCounts;*/
        return null;
    }

}