Spring学习(九）

JdbcTemplate需要的jar包

1、Spring核心必须依赖的库：commons-logging-1.1.1.jar
2、Spring IoC部分核心库：

spring-beans-4.3.9.RELEASE.jar
spring-context-4.3.9.RELEASE.jar
spring-context-support-4.3.9.RELEASE.jar
spring-core-4.3.9.RELEASE.jar
spring-expression-4.3.9.RELEASE.jar
spring-web-4.3.9.RELEASE.jar ------> 支持在Web环境中使用Spring IoC容器

3、Spring AOP部分核心库：

spring-aop-4.3.9.RELEASE.jar
spring-aspects-4.3.9.RELEASE.jar

4、Spring AOP需要依赖于aspectj库:

aspectjrt.jar
aspectjweaver.jar

5、Spring JDBC部分核心库：

spring-jdbc-4.3.9.RELEASE.jar
spring-tx-4.3.9.RELEASE.jar

6、数据库对应的驱动包：mysql-connector-java-5.1.40-bin.jar

Spring Jdbc 的使用

1、Spring通过抽象JDBC访问并一致的API来简化JDBC编程的工作量。我们只需要声明SQL、调用合适的SpringJDBC框架API、处理结果集即可。事务由Spring管理，并将JDBC受查异常转换为Spring一致的非受查异常，从而简化开发。

2、XML配置（AOP）事务控制

<?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:aop="http://www.springframework.org/schema/aop"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf8" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
    </bean>
    
    <!-- 配置平台事务管理器 -->
    <bean id="platformTransactionManager" 
             class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" /> 
    </bean>
    
    <!-- 提供对事务的配置 ( Advice ) Advice不需要由我们完成只要提供配置就好了，会根据配置生成相应的事物配置的方法-->
    <!--  no-rollback-for 设置不回滚 isolation 隔离级别 -->
    <tx:advice id="transactionAdvice" transaction-manager="platformTransactionManager">
        <tx:attributes>
            <tx:method name="persist*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="false" />
            <tx:method name="save*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="false" />
            <tx:method name="update*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="false" />
            <tx:method name="delete*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="false" />
            <tx:method name="remove*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="false" />
            
            <tx:method name="load*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="true" />
            <tx:method name="get*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="true" />
            <tx:method name="find*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="true" />
            <tx:method name="query*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="true" />
        </tx:attributes>
    </tx:advice>
    
    <!-- 使用 aop:config 实现将 Advice 织入到 相应的 连接点中 -->
    <aop:config>
        <!-- 事务对应的切点应该选择到 Service 层次，这里 为了简化步骤 暂时 选择了 Dao 层次 -->
        <aop:pointcut  id="tx-pointcut" expression="execution(* ecut.jdbc.dao.*.*(..))"/>
        <!-- 声明事务控制 切面 -->
        <aop:advisor pointcut-ref="tx-pointcut" advice-ref="transactionAdvice"/>
    </aop:config>

</beans>

propagation传播属性详解

REQUIRED：支持当前事务，如果当前没有事务，就新建一个事务。这是最常见的选择。
SUPPORTS：支持当前事务，如果当前没有事务，就以非事务方式执行。
MANDATORY：支持当前事务，如果当前没有事务，就抛出异常。
REQUIRES_NEW：新建事务，如果当前存在事务，把当前事务挂起。
NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
NEVER：以非事务方式执行，如果当前存在事务，则抛出异常。
NESTED：支持当前事务，如果当前事务存在，则执行一个嵌套事务，如果当前没有事务，就新建一个事务

isolation隔离级别属性详解

DEFAULT：使用后端数据库默认的隔离级别(spring中的默认选择项)。
READ_UNCOMMITED：允许你读取还未提交的改变了的数据。可能导致脏、幻、不可重复读。
READ_COMMITTED：允许在并发事务已经提交后读取。可防止脏读，但幻读和不可重复读仍可发生。
REPEATABLE_READ：对相同字段的多次读取是一致的，除非数据被事务本身改变。可防止脏、不可重复读，但幻读仍可能发生。
SERIALIZABLE：完全服从ACID的隔离级别，确保不发生脏、幻、不可重复读。这在所有的隔离级别中是最慢的，它是典型的通过完全锁定在事务中涉及的数据表来完成的。

name属性详解

与事务属性关联的方法名。通配符（*）可以用来指定一批关联到相同的事务属性的方法。

平台事务管理器，包含事务的提交回滚等这些信息，并以ref的方式为平台事务管理器注入dataSource的引用。提供对事务的配置 Advice ，并以方法为单位,指定方法应用什么事务属性 isolation:隔离级别 propagation:传播行为 read-only:是否只读。使用 aop:config 实现将事务的配置 Advice 织入到相应的连接点中。

3、JdbcTemplate使用的基本步骤

引用jar包

创建数据库和表

DROP TABLE IF EXISTS t_customer ;

CREATE TABLE t_customer (
    id  INT(5) PRIMARY KEY ,
    email  VARCHAR(60)  UNIQUE NOT NULL,
    password VARCHAR(32) NOT NULL ,
    nickname VARCHAR(150) ,
    gender VARCHAR(3) ,
    birthdate DATE ,
    married     CHAR(1)
);

创建类

Customer类

package ecut.jdbc.entity;

import java.util.Date;

public class Customer {

    private Integer id; 
    private String email;
    private String password;
    private String nickname;
    private char gender;
    private Date birthdate;
    private boolean married;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getNickname() {
        return nickname;
    }

    public void setNickname(String nickname) {
        this.nickname = nickname;
    }

    public char getGender() {
        return gender;
    }

    public void setGender(char gender) {
        this.gender = gender;
    }

    public Date getBirthdate() {
        return birthdate;
    }

    public void setBirthdate(Date birthdate) {
        this.birthdate = birthdate;
    }

    public boolean isMarried() {
        return married;
    }

    public void setMarried(boolean married) {
        this.married = married;
    }

}

CustomerController类

package ecut.jdbc.controller;

import ecut.jdbc.service.CustomerService;

public class CustomerController {
    
    private CustomerService customerService ;

    public CustomerService getCustomerService() {
        return customerService;
    }

    public void setCustomerService(CustomerService customerService) {
        this.customerService = customerService;
    }

}

CustomerService类

package ecut.jdbc.service;

import ecut.jdbc.dao.CustomerDao;

public class CustomerService {
    
    private CustomerDao customerDao ;

    public CustomerDao getCustomerDao() {
        return customerDao;
    }

    public void setCustomerDao(CustomerDao customerDao) {
        this.customerDao = customerDao;
    }

}

配置文件

<?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:aop="http://www.springframework.org/schema/aop"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf8" />
        <property name="username" value="root" />
        <property name="password" value="123456" />
    </bean>
    
    <!-- 配置平台事务管理器 -->
    <bean id="platformTransactionManager" 
             class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" /> 
    </bean>
    
    <!-- 提供对事务的配置 ( Advice ) Advice不需要由我们完成只要提供配置就好了，会根据配置生成相应的事物配置的方法-->
    <!--  no-rollback-for 设置不回滚 isolation 隔离级别 tx:method 与事物的那些方法-->
    <tx:advice id="transactionAdvice" transaction-manager="platformTransactionManager">
        <tx:attributes>
            <tx:method name="persist*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="false" />
            <tx:method name="save*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="false" />
            <tx:method name="update*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="false" />
            <tx:method name="delete*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="false" />
            <tx:method name="remove*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="false" />
            
            <tx:method name="load*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="true" />
            <tx:method name="get*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="true" />
            <tx:method name="find*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="true" />
            <tx:method name="query*" propagation="REQUIRED" isolation="READ_COMMITTED" read-only="true" />
        </tx:attributes>
    </tx:advice>
    
    <!-- 使用 aop:config 实现将 Advice 织入到 相应的 连接点中 -->
    <aop:config>
        <!-- 事务对应的切点应该选择到 Service 层次，这里 为了简化步骤 暂时 选择了 Dao 层次 -->
        <aop:pointcut  id="tx-pointcut" expression="execution(* ecut.jdbc.dao.*.*(..))"/>
        <!-- 声明事务控制 切面 -->
        <aop:advisor pointcut-ref="tx-pointcut" advice-ref="transactionAdvice"/>
    </aop:config>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!-- 数据源 -->
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="customerDao" class="ecut.jdbc.dao.CustomerDao" >
        <property name="jdbcTemplate" ref="jdbcTemplate" />    
    </bean>
    
    <bean id="customerService" class="ecut.jdbc.service.CustomerService" >
        <property name="customerDao" ref="customerDao" />
    </bean>
    
    <bean id="customerController" class="ecut.jdbc.controller.CustomerController" >
        <property name="customerService" ref="customerService" />
    </bean>

</beans>

controller中添加了CustomerService的对象，因此需要将CustomerService以ref的方式引入到controller中。service中添加了CustomerDao的对象，因此需要将CustomerDao以ref的方式引入到service中。dao中添加了jdbcTemplate的对象，因此需要将jdbcTemplate以ref的方式引入到dao中。而Template依赖与DataSource，以ref的方式为JdbcTemplate注入引用。DataSource的属性可以通过注入数据库的一些配置属性添加。

CustomerDao类

package ecut.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import ecut.jdbc.entity.Customer;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;

public class CustomerDao {
    
    private JdbcTemplate jdbcTemplate;
    //在表名前后加空格避免空格缺失
    private static final String TABLE = " t_customer " ;
    
    public boolean persist( Customer c ) {
        
        final Integer id = jdbcTemplate.queryForObject( "SELECT max(id) FROM " + TABLE , Integer.class ) ;
        
        System.out.println( "id : " + id );
        
        
        final String SQL = "INSERT INTO " + TABLE + 
                                    " ( id , email , password , nickname , gender , birthdate , married ) " + 
                                    " VALUES ( ? , ? , ? , ? , ? , ? , ? ) " ;
        //是一个接口用匿名内部类的方法去实现
        PreparedStatementCallback<Integer> action = new PreparedStatementCallback<Integer>(){
            @Override
            public Integer doInPreparedStatement( PreparedStatement ps ) 
                    throws SQLException, DataAccessException {
                
                ps.setInt( 1 ,  id + 1 );
                ps.setString( 2 ,  c.getEmail() );
                ps.setString( 3 ,  c.getPassword() );
                ps.setString( 4 ,  c.getNickname() );
                ps.setString( 5 ,  c.getGender() + "" );
                // c.getBirthdate().getTime() 获得毫秒数
                java.sql.Date date = new java.sql.Date( c.getBirthdate().getTime() ) ;
                ps.setDate( 6 ,  date );
                ps.setString( 7 ,  c.isMarried() ? "Y" : "N" );
                
                int count = ps.executeUpdate() ;
                
                return count;
            }
        };
        //doInPreparedStatement返回类型则jdbcTemplate.execute( SQL , action );也是返回什么类型
        Integer count = jdbcTemplate.execute( SQL , action );
        
        // return count != null && count > 0  ;
        
        if( count != null && count > 0 ) {
            return true ;
        } else {
            return false ;
        }
        
    }
    
    public boolean update( Customer c , Integer id ) {
        final String SQL = "UPDATE " + TABLE + 
                                    " SET email =?, password = ? , nickname = ? ,gender =? , birthdate = ? , married = ? where id = ?";
        PreparedStatementCallback<Integer> action = new PreparedStatementCallback<Integer>(){
            @Override
            public Integer doInPreparedStatement( PreparedStatement ps ) 
                    throws SQLException, DataAccessException {
                
                ps.setString( 1 ,  c.getEmail() );
                ps.setString( 2 ,  c.getPassword() );
                ps.setString( 3 ,  c.getNickname() );
                ps.setString( 4 ,  c.getGender() + "" );
                // c.getBirthdate().getTime() 获得毫秒数
                java.sql.Date date = new java.sql.Date( c.getBirthdate().getTime() ) ;
                ps.setDate( 5 ,  date );
                ps.setString( 6 ,  c.isMarried() ? "Y" : "N" );
                ps.setInt( 7 ,  id );

                int count = ps.executeUpdate() ;
                
                return count;
            }
        };
        //doInPreparedStatement返回类型则jdbcTemplate.execute( SQL , action );也是返回什么类型
        Integer count = jdbcTemplate.execute( SQL , action );
        
        // return count != null && count > 0  ;
        
        if( count != null && count > 0 ) {
            return true ;
        } else {
            return false ;
        }
        
    }
     
    public boolean delete( Integer id  ) {
        
        final String SQL  = "DELETE FROM " + TABLE +"WHERE id  = ?";
        PreparedStatementCallback<Integer>  action = new PreparedStatementCallback<Integer>() {

            @Override
            public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                ps.setInt(1, id);
                int count = ps.executeUpdate();
                return count;
            }
            
        };
        Integer count = jdbcTemplate.execute( SQL , action );
        
        // return count != null && count > 0  ;
        
        if( count != null && count > 0 ) {
            return true ;
        } else {
            return false ;
        }        
    }
    
    public Customer load( Integer id  ) {
        
        final String SQL = "SELECT id , email , password , nickname , gender , birthdate , married FROM " + TABLE + " WHERE id =  ? "  ; 
        
        PreparedStatementCreator psc = new  PreparedStatementCreator(){

            @Override
            public PreparedStatement createPreparedStatement( Connection conn ) throws SQLException {
                
                PreparedStatement ps = conn.prepareStatement( SQL );
                
                ps.setInt( 1 ,  id );
                
                return ps ;
            }
            
        };
        
        final RowMapper<Customer> rowMapper = new RowMapper<Customer>() {
            @Override
            public Customer mapRow( ResultSet rs, int count ) throws SQLException {
                
                Customer c = new Customer();
                //Integer id = rs.getInt( "id" );可读性好效率低
                Integer id = rs.getInt( 1 );
                c.setId( id );
                
                String email = rs.getString( 2 );
                c.setEmail( email );
                
                String password = rs.getString( 3 );
                c.setPassword(password);
                
                String nickname = rs.getString( 4 );
                c.setNickname(nickname);
                
                String gender = rs.getString( 5 );
                if( gender != null && gender.length() > 0 ) {
                    c.setGender( gender.charAt( 0 ) );
                }
                
                java.sql.Date birthdate = rs.getDate( 6 );
                c.setBirthdate( birthdate );
                
                String married = rs.getString( 7 );
                c.setMarried( "Y".equals( married ) ? true : false );
                
                return c;
            }
        } ;
        
        List<Customer> list = jdbcTemplate.query( psc , rowMapper);
        
        if( list != null && list.size() > 0 ) {
            Customer c = list.get( 0 );
            return c ;
        } else {
            return null ;
        }
        
    }
    
    public List<Customer> loadAll() {
        
        final String SQL = "SELECT id , email , password , nickname , gender , birthdate , married FROM " + TABLE ;
        //吧查询后的结果拼装起来返回的对象，将行记录包装成一个个的对象
        final RowMapper<Customer> rowMapper = new RowMapper<Customer>() {
            @Override
            public Customer mapRow( ResultSet rs, int count ) throws SQLException {
                
                Customer c = new Customer();
                
                Integer id = rs.getInt( 1 );
                c.setId( id );
                
                String email = rs.getString( 2 );
                c.setEmail( email );
                
                String password = rs.getString( 3 );
                c.setPassword(password);
                
                String nickname = rs.getString( 4 );
                c.setNickname(nickname);
                
                String gender = rs.getString( 5 );
                if( gender != null && gender.length() > 0 ) {
                    c.setGender( gender.charAt( 0 ) );
                }
                
                java.sql.Date birthdate = rs.getDate( 6 );
                c.setBirthdate( birthdate );
                
                String married = rs.getString( 7 );
                c.setMarried( "Y".equals( married ) ? true : false );
                
                return c;
            }
        } ;
        
        List<Customer> list = jdbcTemplate.query( SQL , rowMapper );
        
        return list ;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

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

}

测试类

package ecut.jdbc;

import java.util.Date;
import java.util.List;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import ecut.jdbc.dao.CustomerDao;
import ecut.jdbc.entity.Customer;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestCustomerDao {

    private static AbstractApplicationContext container;

    public @BeforeClass static void init() {
        String configLocations = "classpath:ecut/**/jdbc/beans.xml";
        container = new ClassPathXmlApplicationContext(configLocations);
    }

    public @Test void testSaveCustomer() {

        Customer c = new Customer();

        c.setEmail("Amy@ecut.edu.cn");
        c.setPassword("hello2017");

        Date birthdate = new Date();
        c.setBirthdate(birthdate);
        c.setGender('女');

        c.setNickname("Saber");
        c.setMarried(false);

        CustomerDao customerDao = container.getBean("customerDao", CustomerDao.class);

        customerDao.persist(c);

    }

    public @Test void testLoadCustomer() {

        CustomerDao customerDao = container.getBean("customerDao", CustomerDao.class);

        Customer c = customerDao.load(1);

        System.out.println(c.getEmail());

    }

    public @Test void testLoadAllCustomer() {

        CustomerDao customerDao = container.getBean("customerDao", CustomerDao.class);

        List<Customer> list = customerDao.loadAll();

        for (Customer c : list) {
            System.out.println(c.getEmail() + " : " + c.getNickname());
        }

    }

    public @Test void testUpdateCustomer() {

        Customer c = new Customer();

        c.setEmail("Saber@ecut.edu.cn");
        c.setPassword("hello2017");

        Date birthdate = new Date();
        c.setBirthdate(birthdate);
        c.setGender('女');

        c.setNickname("Amy");
        c.setMarried(false);

        CustomerDao customerDao = container.getBean("customerDao", CustomerDao.class);

        customerDao.update(c, 2);

    }
    
    public @Test void  testDeleteCustomer() {

        CustomerDao customerDao = container.getBean("customerDao", CustomerDao.class);

        boolean flag = customerDao.delete(1);
        
        System.out.println(flag);
    }

    public @AfterClass static void destory() {
        container.close();
    }

}

转载请于明显处标明出处：

https://www.cnblogs.com/AmyZheng/p/9281810.html

相关阅读:
【DUBBO】dubbo架构详解（转载）
【数据库】sql连表查询
 第十三章三种非对称加密算法总结
 第十二章非对称加密算法-RSA
第十一章非对称加密算法--DH
第十章五种对称加密算法总结
 第九章对称加密算法--IDEA
第八章对称加密算法--AES
第七章对称加密算法--DES
第六章三大消息摘要算法总结
原文地址：https://www.cnblogs.com/AmyZheng/p/9281810.html