04.JdbcTemplate&声明式事务

1.JdbcTemplate的使用

• 需要导入的jar包

     spring-beans- 4.0 .0. RELEASE . jar

     spring -context- 4.0 .0.RELEASE. jar

     spring -core- 4.0 .0.RELEASE. jar

     spring -expression- 4.0 .0. RELEASE . jar

    commons-logging- 1.1 .3. jar

     spring -aop- 4.0 .0.RELEASE. jar

    

    //以下jar包是需要额外导入的

      spring -jdbc- 4.0 .0.RELEASE. jar

     spring -orm- 4.0 .0.RELEASE. jar

     spring -tx- 4.0 .0. RELEASE . jar

    c3p0-0.9 .1 .2. jar

    mysql-connector-java-5.1 .37 -bin. jar

• Spring配置文件中的配置

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

     xsi:schemaLocation = " http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd" ; >

     <!-- 设置自动扫描的包 -->

     < context:component-scan base-package = "com.atguigu.spring.jdbc" ></ context:component-scan >

    

     <!-- 引入外部属性文件 -->

     < context:property-placeholder location = "classpath:db.properties" />

    

     <!-- 配置数据源 -->

     < bean id = "dataSource" class = "com.mchange.v2.c3p0.ComboPooledDataSource" >

          < property name = "user" value = "${jdbc.username}" ></ property >

          < property name = "password" value = "${jdbc.password}" ></ property >

          < property name = "jdbcUrl" value = "${jdbc.jdbcUrl}" ></ property >

          < property name = "driverClass" value = "${jdbc.driverClass}" ></ property >

         

          < property name = "initialPoolSize" value = "${jdbc.initPoolSize}" ></ property >

          < property name = "minPoolSize" value = "${jdbc.minPoolSize}" ></ property >

          < property name = "maxPoolSize" value = "${jdbc.maxPoolSize}" ></ property >

     </ bean >

    

     <!-- 配置JdbcTemplate -->

     < bean id = "jdbcTemplate" class = "org.springframework.jdbc.core.JdbcTemplate" >

          <!-- 通过属性注入的方式注入DataSource -->

          < property name = "dataSource" ref = "dataSource" ></ property >

     </ bean >

    

     <!-- 配置NamedParameterJdbcTemplate -->

     < bean id = "namedParameterJdbcTemplate" class = "org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" >

          <!-- 通过构造器注入的方式注入DataSource -->

          < constructor-arg ref = "dataSource" ></ constructor-arg >

     </ bean >

</ beans >

import java.sql.Connection;

import javax.sql.DataSource; 必须是这个包

测试

@Test

     public void test() throws SQLException {

       DataSource datasource = ioc .getBean(DataSource. class );

       Connection connection = datasource .getConnection();

       System. out .println( connection );

    }

• 测试
  • 创建IOC容器及获取JdbcTemplate和NamedParameterJdbcTemplate

// 创建IOC容器

    ApplicationContext ioc = new ClassPathXmlApplicationContext( "beans-jdbc.xml" );

     // 获取JdbcTemplate对象

    JdbcTemplate jdbcTemplate = (JdbcTemplate) ioc .getBean( "jdbcTemplate" );

     // 获取NamedParameterJdbcTemplate

    NamedParameterJdbcTemplate npjt = (NamedParameterJdbcTemplate) ioc .getBean( "namedParameterJdbcTemplate" );

• 1）通用的增删改的方法
  • 调用的是JdbcTemplate中的update方法

// 通用的增删改的方法

     @Test

     public void update() {

          // 写 slq 语句

         String sql = "insert into employees(last_name,email,salary,dept_id) values(?,?,?,?)" ;

          jdbcTemplate .update( sql , "马蓉" , " mr@wbq.com " , 100000.00, 6);

    }

• 2）批处理的方法
  • 调用的是JdbcTemplate中的batchUpdate方法

// 批处理的方法

     @Test

     public void batchUpdate () {

          // 写 slq 语句

         String sql = "insert into employees(last_name,email,salary,dept_id) values(?,?,?,?)" ;

          // 创建一个List

         List<Object[]> batchArgs = new ArrayList<>();

          batchArgs .add( new Object[] { "李彦宏" , " lyh@baidu.com " , 8000.00, 5 });

          batchArgs .add( new Object[] { "雷军" , " lj@xiaomi.com " , 7000.00, 7 });

          batchArgs .add( new Object[] { "王健林" , " wjl@wanda.com " , 6000.00, 4 });

          jdbcTemplate .batchUpdate( sql , batchArgs );

    }

• 3）获取一个对象的方法
  • 调用的是JdbcTemplate中的queryForObject方法

// 获取一个对象的方法

     /**

     * 调用的是queryForObject()方法，该方法中需要传入一个RowMapper类型的参数，

     * 使用的实现类是BeanPropertyRowMapper 就相当于queryRunner中的BeanHandler

     */

     @Test

     public void getBean() {

         String sql = "select id,last_name lastName,email,salary,dept_id deptId from employees where id = ?" ;

          // 创建rowMapper对象告诉Spring要映射的类型

         RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee. class );

         Employee employee = jdbcTemplate .queryForObject( sql , rowMapper , 1);

         System. out .println( employee );

    }

• 4）获取一个单一值的方法
  • 调用的是JdbcTemplate中的queryForObject方法

// 获取一个单一的值

     /**

     * 调用的是queryForObject()方法，里面需要传入一个要获取的个数的类型，如Integer、Long

     */

     @Test

     public void getSingleValue() {

         String sql = "select count(*) from employees" ;

         Integer count = jdbcTemplate .queryForObject( sql , Integer. class );

         System. out .println( count );

    }

• 5）获取一个集合的方法
  • 调用的是JdbcTemplate中的query方法

// 获取一个集合的方法

     /**

     * 调用的是query()方法，里面也需要传入一个RowMapper对象

     */

     @Test

     public void getBeanList() {

         String sql = "select id,last_name lastName,email,salary,dept_id deptId from employees" ;

          // 创建rowMapper对象告诉Spring要映射的类型

         RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee. class );

         List<Employee> employees = jdbcTemplate .query( sql , rowMapper );

          for (Employee employee : employees ) {

             System. out .println( employee );

         }

    }

• 带具名参数的操作（了解）

// 使用带具名参的 sql 语句通过NamedParameterJdbcTemplate来操作数据库

     //当传入的参数为Map时

     @Test

     public void testMap() {

         String sql = "insert into employees(last_name,email,salary,dept_id) values(:lastname,:email,:salary,:deptId)" ;

         Map<String , Object> paramMap = new HashMap<>();

          paramMap .put( "deptId" , 3);

          paramMap .put( "lastname" , "刘强东" );

          paramMap .put( "email" , " lqd@nc.com " );

          paramMap .put( "salary" , 5000.00);

          npjt .update( sql , paramMap );

    }

     //传入的参数为SqlParameterSource时，使用的实现类是BeanPropertySqlParameterSource

     //注意：此时具名参数的名字必须要与BeanPropertySqlParameterSource中传入的对象的属性名保持一致，否则会抛出异常

     @Test

     public void testSqlParameterSource(){

         String sql = "insert into departments(name) values(:deptName)" ;

          //创建Department对象

         Department department = new Department();

          department .setDeptName( "公关部" );

          //创建SqlParameterSource对象

         SqlParameterSource paramSource = new BeanPropertySqlParameterSource( department );

          npjt .update( sql , paramSource );

    }

2.有了JdbcTemplate之后Dao的实现方式（如：DepartmentDaoImpl）

• 接口

public interface DepartmentDao {

     public List<Department> getDepartments();

}

• 实现

@Repository ( "departmentDao" )

public class DepartmentDaoImpl implements DepartmentDao {

     @Autowired

     private JdbcTemplate jdbcTempate ;

    

     @Override

     public List<Department> getDepartments() {

          //写 sql 语句

         String sql = "select id,name deptName from departments" ;

          //创建RowMapper对象

         RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department. class );

          //调用jdbcTempate中的query方法

         List<Department> depts = jdbcTempate .query( sql , rowMapper );

          return depts ;

    }

}

3. 声明式事务的使用

• 1）使用Spring的声明式事务需要导入AOP的jar包

    com.springsource.net .sf.cglib -2.2.0 . jar

     com. springsource.org .aopalliance-1.0 .0 .jar

     com .springsource.org .aspectj.weaver-1.6 .8 . RELEASE .jar

     spring -aspects-4.0 .0 .RELEASE. jar

• 2）在Spring的配置文件中的配置，额外加了配置事务管理器和开启基于注解的事务支持

<? 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:tx = " http://www.springframework.org/schema/tx" ;

     xsi:schemaLocation = " http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd" ; >

          <!-- 设置自动扫描的包 -->

     < context:component-scan base-package = "com.atguigu.spring.transaction" ></ context:component-scan >

    

     <!-- 引入外部属性文件 -->

     < context:property-placeholder location = "classpath:db_tx.properties" />

    

     <!-- 配置数据源 -->

     < bean id = "dataSource" class = "com.mchange.v2.c3p0.ComboPooledDataSource" >

          < property name = "user" value = "${jdbc.username}" ></ property >

          < property name = "password" value = "${jdbc.password}" ></ property >

          < property name = "jdbcUrl" value = "${jdbc.jdbcUrl}" ></ property >

          < property name = "driverClass" value = "${jdbc.driverClass}" ></ property >

         

          < property name = "initialPoolSize" value = "${jdbc.initPoolSize}" ></ property >

          < property name = "minPoolSize" value = "${jdbc.minPoolSize}" ></ property >

          < property name = "maxPoolSize" value = "${jdbc.maxPoolSize}" ></ property >

     </ bean >

    

     <!-- 配置JdbcTemplate -->

     < bean id = "jdbcTemplate" class = "org.springframework.jdbc.core.JdbcTemplate" >

          <!-- 通过属性注入的方式注入DataSource -->

          < property name = "dataSource" ref = "dataSource" ></ property >

     </ bean >

    

     <!-- 配置事务管理器 -->

     < bean id = "transactionManager" class = "org.springframework.jdbc.datasource.DataSourceTransactionManager" >

          <!-- 通过属性注入的方式注入数据源 -->

          < property name = "dataSource" ref = "dataSource" ></ property >

     </ bean >

    

     <!-- 启用声明式事务的注解支持 -->

     <!-- transaction-manager属性：用来配置事务管理器，如果事务管理器的名称是transactionManager，该属性可以省略 -->

<!--     <tx:annotation-driven transaction-manager="transactionManager"/> -->

     < tx:annotation-driven />

</ beans >

• 3）在要添加事务的方法上添加@Transactional注解即可
  • @Transactional可以添加到类上，也可以添加到方法上
    • 添加到类上：类中所有的方法都添加了事务
    • 添加到方法上：该方法添加上了事务

@Transactional

     @Override

     public void checkout( int userId , List<String> isbns ) {

          //遍历集合

          for (String isbn : isbns ) {

              //买书

              bookShopService .purchase( userId , isbn );

         }

    }

• 事务的一些属性

/**

     * 事务的属性：

     * 1）事务的传播行为：一个方法运行在一个开启了事务的方法中时，当前方法是开启一个新事务还是在之前的事务中运行

     *        - 事务的传播行为 通过propagation属性来设置

     *            - REQUIRED：默认，使用原来的事务

     *            - REQUIRES_NEW：开启一个新事务

     * 2）事务的隔离级别

     *        - 事务的隔离级别通过isolation属性来设置

     *            - MySQL默认的隔离级别是可重复读 （REPEATABLE_READ，可重复读）

     *            - Oracle默认的隔离级别是读已提交 （READ_COMMITTED，读已提交，这是我们常用的）

     * 3）设置那些异常回滚或者不回滚 （ 默认回滚 ）

     *        - rollbackFor：设置出现那些异常回滚，值是异常的类型

     *        - rollbackForClassName：设置出现那些异常回滚，值是异常的名字

     *        - noRollbackFor：设置出现那些异常不回滚，值是异常的类型

     *        - noRollbackForClassName：设置出现那些异常不回滚，值是异常的名字

     * 4）设置某个方法是只读的（通常查询的操作设置为是只读的） 

     *        - readOnly：设置是否是只读的，默认是false，不是只读的；当值为true时是只读的

     * 5）设置超时

     *        - timeout：用来设置多长时间该事务没有完成自动回滚，单位是秒

     */

      @Transactional (propagation=Propagation. REQUIRES_NEW ,isolation=Isolation. READ_COMMITTED

             ,noRollbackForClassName={ "ArithmeticException" },readOnly= false ,timeout=3)

     @Override

     public void purchase( int userId , String isbn ) {

          //让线程睡4秒

          try {

             Thread. sleep (4000);

         } catch (InterruptedException e ) {

              e .printStackTrace();

         }

          //1.获取图书的价格

          double bookPrice = bookShopDao .getBookPriceByIsbn( isbn );

//       System.out.println(bookPrice);

//       bookPrice = bookShopDao.getBookPriceByIsbn( isbn );

//       System.out.println(bookPrice);

          //2.更新图书的库存

          bookShopDao .updateBookStock( isbn );

//        int i = 10/0;

          //3.更新账户余额

          bookShopDao .updateAccount( userId , bookPrice );

    }

}

4.基于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: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.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd

http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd" ; >

     <!-- 引入外部属性文件 -->

     < context:property-placeholder location = "classpath:db_tx.properties" />

    

     <!-- 配置数据源 -->

     < bean id = "dataSource" class = "com.mchange.v2.c3p0.ComboPooledDataSource" >

          < property name = "user" value = "${jdbc.username}" ></ property >

          < property name = "password" value = "${jdbc.password}" ></ property >

          < property name = "jdbcUrl" value = "${jdbc.jdbcUrl}" ></ property >

          < property name = "driverClass" value = "${jdbc.driverClass}" ></ property >

         

          < property name = "initialPoolSize" value = "${jdbc.initPoolSize}" ></ property >

          < property name = "minPoolSize" value = "${jdbc.minPoolSize}" ></ property >

          < property name = "maxPoolSize" value = "${jdbc.maxPoolSize}" ></ property >

     </ bean >

    

     <!-- 配置JdbcTemplate -->

     < bean id = "jdbcTemplate" class = "org.springframework.jdbc.core.JdbcTemplate" >

          <!-- 通过属性注入的方式注入DataSource -->

          < property name = "dataSource" ref = "dataSource" ></ property >

     </ bean >

    

     <!-- 配置事务管理器 -->

     < bean id = "transactionManager" class = "org.springframework.jdbc.datasource.DataSourceTransactionManager" >

          <!-- 通过属性注入的方式注入数据源 -->

          < property name = "dataSource" ref = "dataSource" ></ property >

     </ bean >

    

     <!-- 配置BookShopDaoImpl -->

     < bean id = "bookShopDao" class = "com.atguigu.spring.transaction.xml.dao.BookShopDaoImpl" >

          < property name = "jdbcTemplate" ref = "jdbcTemplate" ></ property >

     </ bean >

     <!-- 配置BookShopServiceImpl -->

     < bean id = "bookShopService" class = "com.atguigu.spring.transaction.xml.service.BookShopServiceImpl" >

          < property name = "bookShopDao" ref = "bookShopDao" ></ property >

     </ bean >

    

     <!-- 配置事务 -->

     < tx:advice id = "advice" >

          < tx:attributes >

              <!-- 配置要添加事务的方法 -->

              < tx:method name = "purchase" propagation = "REQUIRED" />

              <!-- 给查询的方法添加事务 -->

              < tx:method name = "get*" read-only = "true" />

              <!-- 给所有的方法添加事务 -->

              < tx:method name = "*" />

          </ tx:attributes >

     </ tx:advice >

     <!-- 配置AOP -->

     < aop:config >

          <!-- 配置切入点表达式 -->

          < aop:pointcut expression = "execution(* com.atguigu.spring.transaction.xml.service.BookShopServiceImpl.purchase(..))"

                  id = "pointCut" />

          <!-- 将切入点表达式与上面配置的事务关联起来 -->

          < aop:advisor advice-ref = "advice" pointcut-ref = "pointCut" />       

     </ aop:config >

</ beans >