第一章：Spring对JDBC的封装

1.1 JdbcTemplate

1.1.1 概述

1.1.1.1 基本介绍

  • Spring对数据库的操作在JDBC上面做了基本的封装，让开发者在操作数据库的时候只需要关注SQL语句和查询结果处理器，即可完成功能。
  • 在配合Spring的IOC功能，可以把DataSource注册到JdbcTemplate中，同时利用Spring基于AOP的事务即可完成简单的数据库的CRUD操作。

1.1.1.2 源码

  • JdbcTemplate:
  1. public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {
  3.     private static final String RETURN_RESULT_SET_PREFIX = "#result-set-";
  5.     private static final String RETURN_UPDATE_COUNT_PREFIX = "#update-count-";
  8.     /** If this variable is false, we will throw exceptions on SQL warnings. */
  9.     private boolean ignoreWarnings = true;
  11.     /**
  12.      * If this variable is set to a non-negative value, it will be used for setting the
  13.      * fetchSize property on statements used for query processing.
  14.      */
  15.     private int fetchSize = -1;
  17.     /**
  18.      * If this variable is set to a non-negative value, it will be used for setting the
  19.      * maxRows property on statements used for query processing.
  20.      */
  21.     private int maxRows = -1;
  23.     /**
  24.      * If this variable is set to a non-negative value, it will be used for setting the
  25.      * queryTimeout property on statements used for query processing.
  26.      */
  27.     private int queryTimeout = -1;
  29.     /**
  30.      * If this variable is set to true, then all results checking will be bypassed for any
  31.      * callable statement processing. This can be used to avoid a bug in some older Oracle
  32.      * JDBC drivers like 10.1.0.2.
  33.      */
  34.     private boolean skipResultsProcessing = false;
  36.     /**
  37.      * If this variable is set to true then all results from a stored procedure call
  38.      * that don't have a corresponding SqlOutParameter declaration will be bypassed.
  39.      * All other results processing will be take place unless the variable
  40.      * {@code skipResultsProcessing} is set to {@code true}.
  41.      */
  42.     private boolean skipUndeclaredResults = false;
  44.     /**
  45.      * If this variable is set to true then execution of a CallableStatement will return
  46.      * the results in a Map that uses case insensitive names for the parameters.
  47.      */
  48.     private boolean resultsMapCaseInsensitive = false;
  51.     /**
  52.      * Construct a new JdbcTemplate for bean usage.
  53.      * <p>Note: The DataSource has to be set before using the instance.
  54.      * @see #setDataSource
  55.      */
  56.     public JdbcTemplate() {
  57.     }
  59.     /**
  60.      * Construct a new JdbcTemplate, given a DataSource to obtain connections from.
  61.      * <p>Note: This will not trigger initialization of the exception translator.
  62.      * @param dataSource the JDBC DataSource to obtain connections from
  63.      */
  64.     public JdbcTemplate(DataSource dataSource) {
  65.         setDataSource(dataSource);
  66.         afterPropertiesSet();
  67.     }
  69.     /**
  70.      * Construct a new JdbcTemplate, given a DataSource to obtain connections from.
  71.      * <p>Note: Depending on the "lazyInit" flag, initialization of the exception translator
  72.      * will be triggered.
  73.      * @param dataSource the JDBC DataSource to obtain connections from
  74.      * @param lazyInit whether to lazily initialize the SQLExceptionTranslator
  75.      */
  76.     public JdbcTemplate(DataSource dataSource, boolean lazyInit) {
  77.         setDataSource(dataSource);
  78.         setLazyInit(lazyInit);
  79.         afterPropertiesSet();
  80.     }
  82.     //略
  83. }

1.1.1.3 方法说明

  • JdbcTemplate主要提供以下的方法：
    • execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句。
    • update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句。
    • query方法及queryForXxx方法：用于执行查询相关语句。
    • call方法：用于执行存储过程、函数相关语句。

1.1.2 入门案例

  • 导入相关jar包的Maven坐标：
  1. <properties>
  2.     <!--    5.2.7.RELEASE    -->
  3.     <spring.version>5.2.7.RELEASE</spring.version>
  4. </properties>
  6. <dependencies>
  7.     <dependency>
  8.         <groupId>org.springframework</groupId>
  9.         <artifactId>spring-context</artifactId>
  10.         <version>${spring.version}</version>
  11.     </dependency>
  12.     <dependency>
  13.         <groupId>org.springframework</groupId>
  14.         <artifactId>spring-web</artifactId>
  15.         <version>${spring.version}</version>
  16.     </dependency>
  17.     <dependency>
  18.         <groupId>org.springframework</groupId>
  19.         <artifactId>spring-webmvc</artifactId>
  20.         <version>${spring.version}</version>
  21.     </dependency>
  22.     <dependency>
  23.         <groupId>org.springframework</groupId>
  24.         <artifactId>spring-aspects</artifactId>
  25.         <version>${spring.version}</version>
  26.     </dependency>
  27.     <dependency>
  28.         <groupId>org.springframework</groupId>
  29.         <artifactId>spring-aop</artifactId>
  30.         <version>${spring.version}</version>
  31.     </dependency>
  32.     <dependency>
  33.         <groupId>com.alibaba</groupId>
  34.         <artifactId>druid</artifactId>
  35.         <version>1.1.23</version>
  36.     </dependency>
  37.     <dependency>
  38.         <groupId>org.springframework</groupId>
  39.         <artifactId>spring-jdbc</artifactId>
  40.         <version>${spring.version}</version>
  41.     </dependency>
  42.     <dependency>
  43.         <groupId>org.springframework</groupId>
  44.         <artifactId>spring-test</artifactId>
  45.         <version>${spring.version}</version>
  46.     </dependency>
  47.     <dependency>
  48.         <groupId>mysql</groupId>
  49.         <artifactId>mysql-connector-java</artifactId>
  50.         <version>8.0.19</version>
  51.     </dependency>
  52.     <!--    导入YAML解析工厂坐标    -->
  53.     <dependency>
  54.         <groupId>org.yaml</groupId>
  55.         <artifactId>snakeyaml</artifactId>
  56.         <version>1.26</version>
  57.     </dependency>
  58.     <dependency>
  59.         <groupId>junit</groupId>
  60.         <artifactId>junit</artifactId>
  61.         <version>4.13</version>
  62.         <scope>test</scope>
  63.     </dependency>
  64.     <dependency>
  65.         <groupId>javax.inject</groupId>
  66.         <artifactId>javax.inject</artifactId>
  67.         <version>1</version>
  68.     </dependency>
  69.     <dependency>
  70.         <groupId>javax.annotation</groupId>
  71.         <artifactId>javax.annotation-api</artifactId>
  72.         <version>1.3.2</version>
  73.     </dependency>
  74.     <dependency>
  75.         <groupId>javax.servlet</groupId>
  76.         <artifactId>javax.servlet-api</artifactId>
  77.         <version>4.0.1</version>
  78.     </dependency>
  79. </dependencies>
  • sql脚本：
  1. DROP TABLE IF EXISTS `account`;
  2. CREATE TABLE `account`  (
  3.   `id` int(11) NOT NULL AUTO_INCREMENT,
  4.   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  5.   `money` double NULL DEFAULT NULL,
  6.   PRIMARY KEY (`id`) USING BTREE
  7. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  • 日志文件log4j2.xml
  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!--日志级别以及优先级排序: OFF > FATAL > ERROR > WARN > INFO > DEBUG > TRACE > ALL -->
  3. <!--Configuration后面的status用于设置log4j2自身内部的信息输出，可以不设置，当设置成trace时，可以看到log4j2内部各种详细输出-->
  4. <configuration status="INFO">
  5.     <!--先定义所有的appender-->
  6.     <appenders>
  7.         <!--输出日志信息到控制台-->
  8.         <console name="Console" target="SYSTEM_OUT">
  9.             <!--控制日志输出的格式-->
  10.             <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"/>
  11.         </console>
  12.     </appenders>
  13.     <!--然后定义logger，只有定义了logger并引入的appender，appender才会生效-->
  14.     <!--root：用于指定项目的根日志，如果没有单独指定Logger，则会使用root作为默认的日志输出-->
  15.     <loggers>
  16.         <root level="info">
  17.             <appender-ref ref="Console"/>
  18.         </root>
  19.     </loggers>
  20. </configuration>
  • jdbc.yml
  1. jdbc:
  2.     driver: com.mysql.cj.jdbc.Driver
  3.     password: 123456
  4.     url: jdbc:mysql://192.168.2.112:3306/spring5?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true
  5.     user: root
  • 自定义YAMLPropertySourceFactory：
  1. package com.sunxiaping.spring5.factory;
  3. import org.springframework.beans.factory.config.YamlPropertiesFactoryBean;
  4. import org.springframework.core.env.PropertiesPropertySource;
  5. import org.springframework.core.env.PropertySource;
  6. import org.springframework.core.io.support.EncodedResource;
  7. import org.springframework.core.io.support.PropertySourceFactory;
  9. import java.io.IOException;
  10. import java.util.Properties;
  12. /**
  13.  * 自定义YAMLPropertySourceFactory
  14.  */
  15. public class YAMLPropertySourceFactory implements PropertySourceFactory {
  16.     @Override
  17.     public PropertySource<?> createPropertySource(String name, EncodedResource encodedResource) throws IOException {
  18.         YamlPropertiesFactoryBean bean = new YamlPropertiesFactoryBean();
  19.         bean.setResources(encodedResource.getResource());
  20.         Properties properties = bean.getObject();
  22.         return (name != null ? new PropertiesPropertySource(name, properties) : new PropertiesPropertySource(encodedResource.getResource().getFilename(), properties));
  23.     }
  24. }
  • 编写实体类：
  1. package com.sunxiaping.spring5.domain;
  3. import java.io.Serializable;
  5. public class Account implements Serializable {
  7.     private Integer id;
  9.     private String name;
  11.     private Double money;
  13.     public Integer getId() {
  14.         return id;
  15.     }
  17.     public void setId(Integer id) {
  18.         this.id = id;
  19.     }
  21.     public String getName() {
  22.         return name;
  23.     }
  25.     public void setName(String name) {
  26.         this.name = name;
  27.     }
  29.     public Double getMoney() {
  30.         return money;
  31.     }
  33.     public void setMoney(Double money) {
  34.         this.money = money;
  35.     }
  37.     @Override
  38.     public String toString() {
  39.         return "Account{" +
  40.                 "id=" + id +
  41.                 ", name='" + name + '\'' +
  42.                 ", money=" + money +
  43.                 '}';
  44.     }
  45. }
  • JdbcConfig.java
  1. package com.sunxiaping.spring5.config;
  3. import com.alibaba.druid.pool.DruidDataSource;
  4. import com.sunxiaping.spring5.factory.YAMLPropertySourceFactory;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.beans.factory.annotation.Value;
  7. import org.springframework.context.annotation.Bean;
  8. import org.springframework.context.annotation.Configuration;
  9. import org.springframework.context.annotation.PropertySource;
  10. import org.springframework.jdbc.core.JdbcTemplate;
  12. import javax.sql.DataSource;
  14. @Configuration
  15. @PropertySource(value = "classpath:jdbc.yml", factory = YAMLPropertySourceFactory.class)
  16. public class JdbcConfig {
  18.     @Value("${jdbc.driver}")
  19.     private String driver;
  21.     @Value("${jdbc.url}")
  22.     private String url;
  24.     @Value("${jdbc.user}")
  25.     private String user;
  27.     @Value("${jdbc.password}")
  28.     private String password;
  31.     /**
  32.      * 配置数据源
  33.      *
  34.      * @return 数据源
  35.      */
  36.     @Bean
  37.     public DataSource dataSource() {
  38.         DruidDataSource dataSource = new DruidDataSource();
  39.         dataSource.setDriverClassName(driver);
  40.         dataSource.setUrl(url);
  41.         dataSource.setUsername(user);
  42.         dataSource.setPassword(password);
  43.         return dataSource;
  44.     }
  46.     /**
  47.      * 配置JdbcTemplate
  48.      *
  49.      * @param dataSource 数据源
  50.      * @return JdbcTemplate
  51.      */
  52.     @Bean
  53.     public JdbcTemplate jdbcTemplate(@Autowired DataSource dataSource) {
  54.         return new JdbcTemplate(dataSource);
  55.     }
  56. }
  • SpringConfig.java
  1. package com.sunxiaping.spring5.config;
  3. import org.springframework.context.annotation.ComponentScan;
  4. import org.springframework.context.annotation.Configuration;
  5. import org.springframework.context.annotation.Import;
  7. /**
  8.  * Spring的配置类
  9.  */
  10. @Configuration
  11. @ComponentScan(value = "com.sunxiaping.spring5")
  12. @Import(value = JdbcConfig.class)
  13. public class SpringConfig {
  15. }
  • AccountDao.java
  1. package com.sunxiaping.spring5.dao;
  3. import com.sunxiaping.spring5.domain.Account;
  5. import java.util.List;
  7. public interface AccountDao {
  9.     /**
  10.      * 保存账户信息
  11.      *
  12.      * @param account
  13.      */
  14.     void saveAccount(Account account);
  16.     /**
  17.      * 更新账户信息
  18.      *
  19.      * @param account
  20.      */
  21.     void updateAccount(Account account);
  23.     /**
  24.      * 删除账户信息
  25.      *
  26.      * @param id
  27.      */
  28.     void deleteAccount(Integer id);
  30.     /**
  31.      * 根据id查询账户信息
  32.      *
  33.      * @param id
  34.      * @return
  35.      */
  36.     Account findById(Integer id);
  38.     /**
  39.      * 查询全部账户信息
  40.      *
  41.      * @return
  42.      */
  43.     List<Account> findAll();
  45.     /**
  46.      * 查询数量
  47.      *
  48.      * @return
  49.      */
  50.     Long findCount();
  52. }
  • AccountDaoImpl.java
  1. package com.sunxiaping.spring5.dao.impl;
  3. import com.sunxiaping.spring5.dao.AccountDao;
  4. import com.sunxiaping.spring5.domain.Account;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.jdbc.core.BeanPropertyRowMapper;
  7. import org.springframework.jdbc.core.JdbcTemplate;
  8. import org.springframework.jdbc.core.SingleColumnRowMapper;
  9. import org.springframework.stereotype.Repository;
  11. import java.util.List;
  13. @Repository
  14. public class AccountDaoImpl implements AccountDao {
  16.     @Autowired
  17.     private JdbcTemplate jdbcTemplate;
  19.     @Override
  20.     public void saveAccount(Account account) {
  21.         jdbcTemplate.update(" INSERT INTO `account` (`name`,`money`) VALUES (?,?) ", account.getName(), account.getMoney());
  22.     }
  24.     @Override
  25.     public void updateAccount(Account account) {
  26.         jdbcTemplate.update(" UPDATE `account` SET `name` =?,`money` =? WHERE id = ? ", account.getName(), account.getMoney(), account.getId());
  27.     }
  29.     @Override
  30.     public void deleteAccount(Integer id) {
  31.         jdbcTemplate.update(" DELETE FROM `account` WHERE id = ? ", id);
  32.     }
  34.     @Override
  35.     public Account findById(Integer id) {
  36.         return jdbcTemplate.queryForObject("SELECT * FROM `account` WHERE id= ?", new BeanPropertyRowMapper<>(Account.class), id);
  37.     }
  39.     @Override
  40.     public List<Account> findAll() {
  41.         return jdbcTemplate.query("SELECT * FROM `account`",new BeanPropertyRowMapper<>(Account.class));
  42.     }
  44.     @Override
  45.     public Long findCount() {
  46.         return jdbcTemplate.queryForObject(" SELECT count(*) FROM `account` ",new SingleColumnRowMapper<>());
  47.     }
  48. }
  • 测试：
  1. package com.sunxiaping.spring5;
  3. import com.sunxiaping.spring5.config.SpringConfig;
  4. import com.sunxiaping.spring5.dao.AccountDao;
  5. import com.sunxiaping.spring5.domain.Account;
  6. import org.junit.Test;
  7. import org.junit.runner.RunWith;
  8. import org.springframework.beans.factory.annotation.Autowired;
  9. import org.springframework.test.context.ContextConfiguration;
  10. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
  12. import java.util.List;
  14. @RunWith(SpringJUnit4ClassRunner.class)
  15. @ContextConfiguration(classes = SpringConfig.class)
  16. public class Spring5Test {
  18.     @Autowired
  19.     private AccountDao accountDao;
  21.     @Test
  22.     public void testSaveAccount(){
  23.         Account account = new Account();
  24.         account.setName("张三");
  25.         account.setMoney(1.00);
  27.         accountDao.saveAccount(account);
  28.     }
  30.     @Test
  31.     public void testUpdateAccount(){
  32.         Account account = accountDao.findById(1);
  33.         account.setMoney(2.00);
  35.         accountDao.updateAccount(account);
  36.     }
  38.     @Test
  39.     public void testDeleteAccount(){
  40.         Account account = accountDao.findById(1);
  41.         if(null != account){
  42.             accountDao.deleteAccount(1);
  43.         }
  44.     }
  46.     @Test
  47.     public void testFindById(){
  48.         Account account = accountDao.findById(1);
  49.         System.out.println("account = " + account);
  50.     }
  52.     @Test
  53.     public void testFindAll(){
  54.         List<Account> accountList = accountDao.findAll();
  55.         System.out.println("accountList = " + accountList);
  56.     }
  58.     @Test
  59.     public void testFindCount(){
  60.         Long count = accountDao.findCount();
  61.         System.out.println("count = " + count);
  62.     }
  64. }

1.2 NamedParameterJdbcTemplate

1.2.1 概述

1.2.1.1 基本介绍

  • 在经典的JDBC用法中，SQL的参数是用占位符?表示的，并且受到位置的限制。定位参数的问题在于，一旦参数的位置发生改变，就必须改变参数绑定。在Spring JDBC框架中，绑定SQL参数的另一种选择是使用具名参数。
  • 具名参数：SQL按名称而不是按照位置进行指定。具名参数更易于维护，也提升了可读性，具名参数由框架类在运行时用占位符取代。

1.2.1.2 源码

  • NamedParameterJdbcTemplate：
  1. public class NamedParameterJdbcTemplate implements NamedParameterJdbcOperations {
  3.     /** Default maximum number of entries for this template's SQL cache: 256. */
  4.     public static final int DEFAULT_CACHE_LIMIT = 256;
  7.     /** The JdbcTemplate we are wrapping. */
  8.     private final JdbcOperations classicJdbcTemplate;
  10.     private volatile int cacheLimit = DEFAULT_CACHE_LIMIT;
  12.     /** Cache of original SQL String to ParsedSql representation. */
  13.     @SuppressWarnings("serial")
  14.     private final Map<String, ParsedSql> parsedSqlCache =
  15.             new LinkedHashMap<String, ParsedSql>(DEFAULT_CACHE_LIMIT, 0.75f, true) {
  16.                 @Override
  17.                 protected boolean removeEldestEntry(Map.Entry<String, ParsedSql> eldest) {
  18.                     return size() > getCacheLimit();
  19.                 }
  20.             };
  23.     /**
  24.      * Create a new NamedParameterJdbcTemplate for the given {@link DataSource}.
  25.      * <p>Creates a classic Spring {@link org.springframework.jdbc.core.JdbcTemplate} and wraps it.
  26.      * @param dataSource the JDBC DataSource to access
  27.      */
  28.     public NamedParameterJdbcTemplate(DataSource dataSource) {
  29.         Assert.notNull(dataSource, "DataSource must not be null");
  30.         this.classicJdbcTemplate = new JdbcTemplate(dataSource);
  31.     }
  32.      //略   
  33. }

1.2.2 入门案例

  • JdbcConfig.java
  1. package com.sunxiaping.spring5.config;
  3. import com.alibaba.druid.pool.DruidDataSource;
  4. import com.sunxiaping.spring5.factory.YAMLPropertySourceFactory;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.beans.factory.annotation.Value;
  7. import org.springframework.context.annotation.Bean;
  8. import org.springframework.context.annotation.Configuration;
  9. import org.springframework.context.annotation.PropertySource;
  10. import org.springframework.jdbc.core.JdbcTemplate;
  11. import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
  13. import javax.sql.DataSource;
  15. @Configuration
  16. @PropertySource(value = "classpath:jdbc.yml", factory = YAMLPropertySourceFactory.class)
  17. public class JdbcConfig {
  19.     @Value("${jdbc.driver}")
  20.     private String driver;
  22.     @Value("${jdbc.url}")
  23.     private String url;
  25.     @Value("${jdbc.user}")
  26.     private String user;
  28.     @Value("${jdbc.password}")
  29.     private String password;
  32.     /**
  33.      * 配置数据源
  34.      *
  35.      * @return 数据源
  36.      */
  37.     @Bean
  38.     public DataSource dataSource() {
  39.         DruidDataSource dataSource = new DruidDataSource();
  40.         dataSource.setDriverClassName(driver);
  41.         dataSource.setUrl(url);
  42.         dataSource.setUsername(user);
  43.         dataSource.setPassword(password);
  44.         return dataSource;
  45.     }
  47.     /**
  48.      * 配置JdbcTemplate
  49.      *
  50.      * @param dataSource 数据源
  51.      * @return JdbcTemplate
  52.      */
  53.     @Bean
  54.     public JdbcTemplate jdbcTemplate(@Autowired DataSource dataSource) {
  55.         return new JdbcTemplate(dataSource);
  56.     }
  59.     /**
  60.      * 配置NamedParameterJdbcTemplate
  61.      *
  62.      * @param jdbcTemplate
  63.      * @return
  64.      */
  65.     @Bean
  66.     public NamedParameterJdbcTemplate namedParameterJdbcTemplate(JdbcTemplate jdbcTemplate) {
  67.         return new NamedParameterJdbcTemplate(jdbcTemplate);
  68.     }
  69. }
  • SpringConfig.java
  1. package com.sunxiaping.spring5.config;
  3. import org.springframework.context.annotation.ComponentScan;
  4. import org.springframework.context.annotation.Configuration;
  5. import org.springframework.context.annotation.Import;
  7. /**
  8.  * Spring的配置类
  9.  */
  10. @Configuration
  11. @ComponentScan(value = "com.sunxiaping.spring5")
  12. @Import(value = JdbcConfig.class)
  13. public class SpringConfig {
  15. }
  • 测试：
  1. package com.sunxiaping.spring5;
  3. import com.sunxiaping.spring5.config.SpringConfig;
  4. import com.sunxiaping.spring5.domain.Account;
  5. import org.junit.Test;
  6. import org.junit.runner.RunWith;
  7. import org.springframework.beans.factory.annotation.Autowired;
  8. import org.springframework.cglib.beans.BeanMap;
  9. import org.springframework.jdbc.core.BeanPropertyRowMapper;
  10. import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
  11. import org.springframework.test.context.ContextConfiguration;
  12. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
  14. import java.util.HashMap;
  15. import java.util.Map;
  17. @RunWith(SpringJUnit4ClassRunner.class)
  18. @ContextConfiguration(classes = SpringConfig.class)
  19. public class Spring5Test {
  21.     @Autowired
  22.     private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
  24.     @Test
  25.     public void testSaveAccount() {
  26.         Account account = new Account();
  27.         account.setName("李四");
  28.         account.setMoney(1d);
  30.         BeanMap beanMap = BeanMap.create(account);
  32.         namedParameterJdbcTemplate.update(" INSERT INTO `account` (`name`,`money`) VALUES (:name,:money) ",beanMap);
  33.     }
  35.     @Test
  36.     public void testFindById(){
  37.         Map<String,Object> map = new HashMap<>();
  38.         map.put("id",1);
  39.         Account account = namedParameterJdbcTemplate.queryForObject(" SELECT * FROM `account` WHERE id = :id ", map, new BeanPropertyRowMapper<>(Account.class));
  40.         System.out.println("account = " + account);
  41.     }
  42. }

第二章：Spring中的事务

2.1 API介绍

2.1.1 PlatformTransactionManager和其实现类

  • 作用：此接口是Spring的事务管理器的核心接口。Spring本身并不支持事务实现，只是负责提供标准，应用底层支持什么样的事务，需要提供具体实现类。在Spring框架中，也为我们内置了一些具体策略，例如：DatasourceTransactionManager、HibernateTransactionManager、jpaTransactionManager等。

  • 类图：

PlatformTransactionManager和其实现类.png

  • PlatformTransactionManager：
  1. public interface PlatformTransactionManager extends TransactionManager {
  3.     /**
  4.      * 获取事务状态信息
  5.      */
  6.     TransactionStatus getTransaction(@Nullable TransactionDefinition definition)
  7.             throws TransactionException;
  9.     /**
  10.      * 提交事务
  11.      */
  12.     void commit(TransactionStatus status) throws TransactionException;
  14.     /**
  15.      * 回滚事务
  16.      */
  17.     void rollback(TransactionStatus status) throws TransactionException;
  19. }

2.1.2 TransactionDefinition

  • 作用：此接口是Spring中事务可控属性的顶层接口，里面定义了事务的一些属性以及获取属性的方法。例如：事务的传播行为、事务的隔离级别、事务的只读、事务的超时等待。通常情况下，我们在开发洪都可以配置这些属性，以求达到最佳效果。

  • 类图：

TransactionDefinition.png

  • TransactionDefinition：
  1. public interface TransactionDefinition {
  3.     /**
  4.      * REQUIRED:如果当前没有事务，就新建一个事务，如果已经存在一个事务中，加入到这个事务中。一般的选择（默认值）
  5.      */
  6.     int PROPAGATION_REQUIRED = 0;
  8.     /**
  9.      * SUPPORTS:支持当前事务，如果当前没有事务，就以非事务方式执行（没有事务）
  10.      */
  11.     int PROPAGATION_SUPPORTS = 1;
  13.     /**
  14.      * MANDATORY：使用当前的事务，如果当前没有事务，就抛出异常
  15.      */
  16.     int PROPAGATION_MANDATORY = 2;
  18.     /**
  19.      * REQUERS_NEW:新建事务，如果当前在事务中，把当前事务挂起。
  20.      */
  21.     int PROPAGATION_REQUIRES_NEW = 3;
  23.     /**
  24.      * NOT_SUPPORTED:以非事务方式执行操作，如果当前存在事务，就把当前事务挂起
  25.      */
  26.     int PROPAGATION_NOT_SUPPORTED = 4;
  28.     /**
  29.      * NEVER:以非事务方式运行，如果当前存在事务，抛出异常
  30.      */
  31.     int PROPAGATION_NEVER = 5;
  33.     /**
  34.      * NESTED:如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行REQUIRED类似的操作。
  35.      */
  36.     int PROPAGATION_NESTED = 6;
  38.     /**
  39.      * 事务的隔离级别默认值，当取值-1时，会采用下面的4个值其中一个。
  40.      * 事务的隔离级别默认值，当取值-1时，会采用下面的4个值其中一个。
  41.      */
  42.     int ISOLATION_DEFAULT = -1;
  44.     /**
  45.      * 事务隔离级别为：读未提交
  46.      * 事务隔离级别为：读未提交
  47.      */
  48.     int ISOLATION_READ_UNCOMMITTED = 1;  
  50.     /**
  51.      * 事务隔离级别为：读已提交
  52.      * 可以防止脏读的发生，但是无法防住不可重复读和幻读的发生
  53.      */
  54.     int ISOLATION_READ_COMMITTED = 2;  
  56.     /**
  57.      * 事务隔离级别为：可重复读
  58.      * 可以防止脏读和不可重复读的发生，但是无法防住幻读的发生
  59.      */
  60.     int ISOLATION_REPEATABLE_READ = 4;  
  62.     /**
  63.      * 事务隔离级别为：串行化
  64.      * 此时所有错误情况均可防住，但是由于事务变成了独占模式（排他模式），因此效率最低
  65.      */
  66.     int ISOLATION_SERIALIZABLE = 8;  
  69.     /**
  70.      * 超时限制。默认值是-1，没有超时限制。如果有，以秒为单位进行设置。
  71.      */
  72.     int TIMEOUT_DEFAULT = -1;
  75.     /**
  76.      * 获取事务传播行为
  77.      */
  78.     default int getPropagationBehavior() {
  79.         return PROPAGATION_REQUIRED;
  80.     }
  82.     /**
  83.      * 获取事务隔离级别
  84.      */
  85.     default int getIsolationLevel() {
  86.         return ISOLATION_DEFAULT;
  87.     }
  89.     /**
  90.      * 获取事务超时时间
  91.      */
  92.     default int getTimeout() {
  93.         return TIMEOUT_DEFAULT;
  94.     }
  96.     /**
  97.      * 获取事务是否只读
  98.      */
  99.     default boolean isReadOnly() {
  100.         return false;
  101.     }
  103.     /**
  104.      * 获取事务名称
  105.      */
  106.     @Nullable
  107.     default String getName() {
  108.         return null;
  109.     }
  112.     // Static builder methods
  114.     /**
  115.      * Return an unmodifiable {@code TransactionDefinition} with defaults.
  116.      * <p>For customization purposes, use the modifiable
  117.      * {@link org.springframework.transaction.support.DefaultTransactionDefinition}
  118.      * instead.
  119.      * @since 5.2
  120.      */
  121.     static TransactionDefinition withDefaults() {
  122.         return StaticTransactionDefinition.INSTANCE;
  123.     }
  125. }

2.1.3 TransactionStatus

  • 作用：此接口是事务运行状态表示的顶层接口，里面定义着获取事务运行状态的一些方法。

  • 类图：

TransactionStatus.png

  • TransactionStatus：
  1. public interface TransactionStatus extends TransactionExecution, SavepointManager, Flushable {
  3.     /**
  4.      * 是否包含存储点
  5.      */
  6.     boolean hasSavepoint();
  8.     /**
  9.      * 刷新事务
  10.      */
  11.     @Override
  12.     void flush();
  14. }

2.2 入门案例

  • JdbcConfig.java
  1. package com.sunxiaping.spring5.config;
  3. import com.alibaba.druid.pool.DruidDataSource;
  4. import com.sunxiaping.spring5.factory.YAMLPropertySourceFactory;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.beans.factory.annotation.Value;
  7. import org.springframework.context.annotation.Bean;
  8. import org.springframework.context.annotation.Configuration;
  9. import org.springframework.context.annotation.PropertySource;
  10. import org.springframework.jdbc.core.JdbcTemplate;
  11. import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
  12. import org.springframework.jdbc.datasource.DataSourceTransactionManager;
  13. import org.springframework.transaction.TransactionManager;
  15. import javax.sql.DataSource;
  17. @Configuration
  18. @PropertySource(value = "classpath:jdbc.yml", factory = YAMLPropertySourceFactory.class)
  19. public class JdbcConfig {
  21.     @Value("${jdbc.driver}")
  22.     private String driver;
  24.     @Value("${jdbc.url}")
  25.     private String url;
  27.     @Value("${jdbc.user}")
  28.     private String user;
  30.     @Value("${jdbc.password}")
  31.     private String password;
  34.     /**
  35.      * 配置数据源
  36.      *
  37.      * @return 数据源
  38.      */
  39.     @Bean
  40.     public DataSource dataSource() {
  41.         DruidDataSource dataSource = new DruidDataSource();
  42.         dataSource.setDriverClassName(driver);
  43.         dataSource.setUrl(url);
  44.         dataSource.setUsername(user);
  45.         dataSource.setPassword(password);
  46.         return dataSource;
  47.     }
  49.     /**
  50.      * 配置JdbcTemplate
  51.      *
  52.      * @param dataSource 数据源
  53.      * @return JdbcTemplate
  54.      */
  55.     @Bean
  56.     public JdbcTemplate jdbcTemplate(@Autowired DataSource dataSource) {
  57.         return new JdbcTemplate(dataSource);
  58.     }
  61.     /**
  62.      * 配置NamedParameterJdbcTemplate
  63.      *
  64.      * @param jdbcTemplate
  65.      * @return
  66.      */
  67.     @Bean
  68.     public NamedParameterJdbcTemplate namedParameterJdbcTemplate(JdbcTemplate jdbcTemplate) {
  69.         return new NamedParameterJdbcTemplate(jdbcTemplate);
  70.     }
  73.     /**
  74.      * 配置事务管理器
  75.      *
  76.      * @param dataSource
  77.      * @return
  78.      */
  79.     @Bean
  80.     public TransactionManager transactionManager(@Autowired DataSource dataSource) {
  81.         DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
  82.         dataSourceTransactionManager.setDataSource(dataSource);
  83.         return dataSourceTransactionManager;
  84.     }
  85. }
  • SpringConfig.java
  1. package com.sunxiaping.spring5.config;
  3. import org.springframework.context.annotation.ComponentScan;
  4. import org.springframework.context.annotation.Configuration;
  5. import org.springframework.context.annotation.Import;
  6. import org.springframework.transaction.annotation.EnableTransactionManagement;
  8. /**
  9.  * Spring的配置类
  10.  */
  11. @Configuration
  12. @ComponentScan(value = "com.sunxiaping.spring5")
  13. @Import(value = JdbcConfig.class)
  14. @EnableTransactionManagement
  15. public class SpringConfig {
  17. }
  • AccountDao.java
  1. package com.sunxiaping.spring5.dao;
  3. import com.sunxiaping.spring5.domain.Account;
  5. import java.util.List;
  7. public interface AccountDao {
  9.     /**
  10.      * 保存账户信息
  11.      *
  12.      * @param account
  13.      */
  14.     void saveAccount(Account account);
  16.     /**
  17.      * 更新账户信息
  18.      *
  19.      * @param account
  20.      */
  21.     void updateAccount(Account account);
  23.     /**
  24.      * 删除账户信息
  25.      *
  26.      * @param id
  27.      */
  28.     void deleteAccount(Integer id);
  30.     /**
  31.      * 根据id查询账户信息
  32.      *
  33.      * @param id
  34.      * @return
  35.      */
  36.     Account findById(Integer id);
  38.     /**
  39.      * 查询全部账户信息
  40.      *
  41.      * @return
  42.      */
  43.     List<Account> findAll();
  45.     /**
  46.      * 查询数量
  47.      *
  48.      * @return
  49.      */
  50.     Long findCount();
  52.     /**
  53.      * 根据名称查询账户信息
  54.      *
  55.      * @param name
  56.      * @return
  57.      */
  58.     Account findByName(String name);
  61. }
  • AccountDaoImpl.java
  1. package com.sunxiaping.spring5.dao.impl;
  3. import com.sunxiaping.spring5.dao.AccountDao;
  4. import com.sunxiaping.spring5.domain.Account;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.jdbc.core.BeanPropertyRowMapper;
  7. import org.springframework.jdbc.core.JdbcTemplate;
  8. import org.springframework.jdbc.core.SingleColumnRowMapper;
  9. import org.springframework.stereotype.Repository;
  10. import org.springframework.util.CollectionUtils;
  11. import org.springframework.util.StringUtils;
  13. import java.util.List;
  15. @Repository
  16. public class AccountDaoImpl implements AccountDao {
  18.     @Autowired
  19.     private JdbcTemplate jdbcTemplate;
  21.     @Override
  22.     public void saveAccount(Account account) {
  23.         jdbcTemplate.update(" INSERT INTO `account` (`name`,`money`) VALUES (?,?) ", account.getName(), account.getMoney());
  24.     }
  26.     @Override
  27.     public void updateAccount(Account account) {
  28.         jdbcTemplate.update(" UPDATE `account` SET `name` =?,`money` =? WHERE id = ? ", account.getName(), account.getMoney(), account.getId());
  29.     }
  31.     @Override
  32.     public void deleteAccount(Integer id) {
  33.         jdbcTemplate.update(" DELETE FROM `account` WHERE id = ? ", id);
  34.     }
  36.     @Override
  37.     public Account findById(Integer id) {
  38.         return jdbcTemplate.queryForObject("SELECT * FROM `account` WHERE id= ?", new BeanPropertyRowMapper<>(Account.class), id);
  39.     }
  41.     @Override
  42.     public List<Account> findAll() {
  43.         return jdbcTemplate.query("SELECT * FROM `account`", new BeanPropertyRowMapper<>(Account.class));
  44.     }
  46.     @Override
  47.     public Long findCount() {
  48.         return jdbcTemplate.queryForObject(" SELECT count(*) FROM `account` ", new SingleColumnRowMapper<>());
  49.     }
  51.     @Override
  52.     public Account findByName(String name) {
  53.         if (StringUtils.isEmpty(name)) {
  54.             return null;
  55.         }
  57.         List<Account> accountList = jdbcTemplate.query("SELECT * FROM `account` WHERE `name` =?", new BeanPropertyRowMapper<>(Account.class), name);
  59.         if (CollectionUtils.isEmpty(accountList)) {
  60.             return null;
  61.         }
  63.         if (accountList.size() > 1) {
  64.             throw new RuntimeException(name + "在数据库中不止一个");
  65.         }
  67.         return accountList.get(0);
  68.     }
  70. }
  • AccountService.java
  1. package com.sunxiaping.spring5.service;
  3. public interface AccountService {
  5.     /**
  6.      * 转账
  7.      *
  8.      * @param sourceName 转出人
  9.      * @param targetName 转入人
  10.      * @param money      金额
  11.      */
  12.     void transfer(String sourceName, String targetName, Double money);
  14. }
  • AccountServiceImpl.java
  1. package com.sunxiaping.spring5.service.impl;
  3. import com.sunxiaping.spring5.dao.AccountDao;
  4. import com.sunxiaping.spring5.domain.Account;
  5. import com.sunxiaping.spring5.service.AccountService;
  6. import org.springframework.beans.factory.annotation.Autowired;
  7. import org.springframework.stereotype.Service;
  8. import org.springframework.transaction.annotation.Transactional;
  9. import org.springframework.util.ObjectUtils;
  11. @Service
  12. @Transactional
  13. public class AccountServiceImpl implements AccountService {
  15.     @Autowired
  16.     private AccountDao accountDao;
  19.     @Override
  20.     public void transfer(String sourceName, String targetName, Double money) {
  21.         Account source = accountDao.findByName(sourceName);
  23.         Account target = accountDao.findByName(targetName);
  25.         if (!ObjectUtils.isEmpty(source) && !ObjectUtils.isEmpty(target)) {
  26.             source.setMoney(source.getMoney() - money);
  27.             target.setMoney(target.getMoney() + money);
  29.             accountDao.updateAccount(source);
  31.             //模拟异常
  32.             int num = 10 / 0;
  34.             accountDao.updateAccount(target);
  35.         }
  36.     }
  37. }
  • 测试：
  1. package com.sunxiaping.spring5;
  3. import com.sunxiaping.spring5.config.SpringConfig;
  4. import com.sunxiaping.spring5.service.AccountService;
  5. import org.junit.Test;
  6. import org.junit.runner.RunWith;
  7. import org.springframework.beans.factory.annotation.Autowired;
  8. import org.springframework.test.context.ContextConfiguration;
  9. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
  11. @RunWith(SpringJUnit4ClassRunner.class)
  12. @ContextConfiguration(classes = SpringConfig.class)
  13. public class Spring5Test {
  15.     @Autowired
  16.     private AccountService accountService;
  18.     @Test
  19.     public void test() {
  20.         accountService.transfer("张三","李四",100d);
  21.     }
  23. }