一、入门使用篇

1、依赖

    <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

2、mysql中创建表结构

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `sex` tinyint(4) DEFAULT NULL COMMENT '性别(0表示男1表示女）',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

3.创建model和service类

• User类

public class User {

    private int id;
    private String username;
    private Date birthday;
    private int sex;
    private String address;
...

• UserService接口

public interface UserService {

    public List<User> getAll();
    public User getUser(int id);
    public int insert(User user);
    public int insertList(List<User> list);
    public boolean update(User user);
    public boolean delete(int id);

    public boolean deleteAll(List<Integer> ids);
    public int updateAll(List<User> list);

    public List<User> getUsersByIds(List<Integer> ids);

}

• UserServiceImpl实现类

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    //1.获取所有列表
    @Override
    public List<User> getAll() {
        //法一
        String sql = "select * from user";
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
        //法二
//        List<User> userList1 = jdbcTemplate.query(sql, (rs, rowNum) -> {
//            User user = new User();
//            user.setId(rs.getInt("id"));
//            user.setUsername(rs.getString("username"));
//            user.setBirthday(rs.getDate("birthday"));
//            user.setSex(rs.getInt("sex"));
//            user.setAddress(rs.getString("address"));
//            return user;
//        });
        return userList;
    }

    //2.根据id获取对象
    @Override
    public User getUser(int id) {
        String sql = "select * from user where id=?";
        //法1
        User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
        //法2
//        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
//        if (CollectionUtils.isEmpty(userList)){
//            user=userList.get(0);
//        }
        return user;
    }

    //3.插入对象
    @Override
    public int insert(User user) {
        //插入数据并获取主键key
        String sql = "insert into user(username,birthday,sex,address) values(?,?,?,?) ";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        //法1
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement preparedStatement = con.prepareStatement(sql);
                preparedStatement.setObject(1, user.getUsername());
                preparedStatement.setObject(2, user.getBirthday());
                preparedStatement.setObject(3, user.getSex());
                preparedStatement.setObject(4, user.getAddress());
                return preparedStatement;
            }
        }, keyHolder);
        //法2lambda表达式
//        jdbcTemplate.update(con -> {
//            PreparedStatement preparedStatement = con.prepareStatement(sql);
//            preparedStatement.setObject(1, user.getUsername());
//            preparedStatement.setObject(2, user.getBirthday());
//            preparedStatement.setObject(3, user.getSex());
//        preparedStatement.setObject(4, user.getAddress());
//        return preparedStatement;
//    }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    //4.批量插入对象
    @Override
    public int insertList(List<User> list) {
        String sql = "insert into user(username,birthday,sex,address) values(?,?,?,?) ";
        int[] batchUpdate = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setObject(1, list.get(i).getUsername());
                ps.setObject(2, list.get(i).getBirthday());
                ps.setObject(3, list.get(i).getSex());
                ps.setObject(4, list.get(i).getAddress());
            }

            @Override
            public int getBatchSize() {
                return list.size();
            }
        });
        return batchUpdate != null ? batchUpdate.length : 0;
    }

    //5.更新数据
    @Override
    public boolean update(User user) {
        String sqlString = "update user set username=?,birthday=?,sex=? address=? where id=?";
        int result = jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement(sqlString);
                preparedStatement.setObject(1, user.getUsername());
                preparedStatement.setObject(2, user.getBirthday());
                preparedStatement.setObject(3, user.getSex());
                preparedStatement.setObject(4, user.getAddress());
                preparedStatement.setObject(5, user.getId());
                return preparedStatement;
            }
        });
        return result == 1 ? true : false;
    }

    //6.根据id删除user
    @Override
    public boolean delete(int id) {
        String sqlString = "delete from user where id=?";
        int result = jdbcTemplate.update(sqlString, id);
        return result == 1 ? true : false;
    }

    //7.批量删除
    @Override
    public boolean deleteAll(List<Integer> ids) {
        if (Objects.isNull(ids)) {
            return false;
        }
        String sqlString = "delete from user where id=?";
        int[] batchUpdate = jdbcTemplate.batchUpdate(sqlString, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {

                ps.setObject(1, ids.get(i));
            }

            @Override
            public int getBatchSize() {
                return ids.size();
            }
        });
        return batchUpdate.length == ids.size() ? true : false;
    }

    @Override
    public int updateAll(List<User> list) {
        //实现方式和insertAll一样
        return 0;
    }

    //8.根据主键列表查询user列表
    @Override
    public List<User> getUsersByIds(List<Integer> ids) {
        if (CollectionUtils.isEmpty(ids)) {
            return null;
        }
        //法1
//        StringBuilder stringBuilder=new StringBuilder();
//        stringBuilder.append("select * from user where 1=1 ");
//        stringBuilder.append(" and id in ( ");
//        for (int i = 0; i < ids.size(); i++) {
//            stringBuilder.append(ids.get(i));
//            if (i==ids.size()-1){
//                stringBuilder.append(" )");
//            }else{
//                stringBuilder.append(",");
//            }
//        }
//        List<User> userl = jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(User.class));
        //法2
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        String sql = "select * from user where id in (:ids)";
        Map<String,Object> map = new HashMap<>();
        map.put("ids", ids);
        List<User> userList = namedParameterJdbcTemplate.query(sql, map,
                new BeanPropertyRowMapper<>(User.class));

        return userList;
    }
}
//法3
public List<User> getUserByIds1(List<Integer> ids) {
        if (CollectionUtils.isEmpty(ids)) {
            return null;
        }
        String afterSpliceIds = ids.stream()
                .map(id -> String.valueOf(id))
                .collect(Collectors.joining(","));
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("select * from user where id in( ")
                .append(afterSpliceIds)
                .append(")");
        return jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(User.class));
    }

4.配置数据源

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

二、spring jdbc自动装配

• 前面讲了SpringBoot自动装配的原理，接下来先看看关于springboot关于jdbc相关的自动装配类。

  
  springboot中关于jdbc相关的自动装配类

1JdbcTemplateAutoConfiguration

• 下面将介绍SpringJdbc自动装配的原理，先看SpringJdbc的自动装配相关类。从类中可以看到jdbctemplate自动装配类依赖于数据源自动装配类DataSourceAutoConfiguration

  
  JdbcTemplateAutoConfiguration类

2.看数据源自动配置类DataSourceAutoConfiguration

@Configuration
//EmbeddedDatabaseType内嵌数据源，默认有HSQL、H2、DERBY
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
//EnableConfigurationProperties数据源属性配置类，实现
//BeanClassLoaderAware和InitializingBean接口
@EnableConfigurationProperties(DataSource.class)
//1.DataSourcePoolMetadataProvidersConfiguration注册了一些被支持的数据源
//比如HikariDataSource数据源（默认就有）、tomcat数据源、Dbcp数据源
//（后面两个是需要引入相应额jar后才可以使用）
//2.DataSourceInitializationConfiguration是数据源配置的初始化相关类，同时
//引入DataSourceInitializerInvoker（初始化数据库的schema和sql语句，并实现监听器）
//和Registrar（注册）组件完成相关的初始化操作
@Import({ DataSourcePoolMetadataProvidersConfiguration.class,
        DataSourceInitializationConfiguration.class })
public class DataSourceAutoConfiguration {
...
}

3.DataSourceConfiguration

• 通过DataSourceAutoConfiguration真实注入的数据源配置项，之前知道DataSourceAutoConfiguration会注入DataSourcePoolMetadataProvidersConfiguration组件，而这个组件会注入一些相关的数据源比如HikariDataSource，而HikariDataSource数据源继承了DataSourceConfiguration类。同时在DataSourceConfiguration类中还提供了一些扩展，方便自定义数据源。

abstract class DataSourceConfiguration {
//DataSourceProperties中创建数据源
    @SuppressWarnings("unchecked")
    protected <T> T createDataSource(DataSourceProperties properties,
            Class<? extends DataSource> type) {
        return (T) properties.initializeDataSourceBuilder().type(type).build();
    }
//springboot中默认的数据源
@ConditionalOnClass(HikariDataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true)
    static class Hikari extends DataSourceConfiguration {

        @Bean
        @ConfigurationProperties(prefix = "spring.datasource.hikari")
        public HikariDataSource dataSource(DataSourceProperties properties) {
            HikariDataSource dataSource = createDataSource(properties,
                    HikariDataSource.class);
            if (StringUtils.hasText(properties.getName())) {
                dataSource.setPoolName(properties.getName());
            }
            return dataSource;
        }

    }   @ConditionalOnMissingBean(DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type")
    static class Generic {
//提供的扩展点，自定数据源
        @Bean
        public DataSource dataSource(DataSourceProperties properties) {
            return properties.initializeDataSourceBuilder().build();
        }

    }

• 最后在DataSourceProperties中创建datasource

  
  DataSourceProperties中创建datasource

4.总结

先DataSourceAutoConfiguration自动配置类中完成相应的属性设置后，PooledDataSourceAvailableCondition方法看有没有支持的连接池可用，然后EmbeddedDatabaseCondition方法是否有内置的类型可以用
----->DataSourceInitializationConfiguration.Registrar中完成dataSourceInitializerPostProcessor的注册
------>DataSourceConfiguration中创建默认的HikariDataSource数据库连接池
----->JdbcTemplateAutoConfiguration中创建jdbcTemplate
----->DataSourcePoolMetadataProvidersConfiguration中注册数据源

三、JDBCTemplate原理

• 设计哲学： jdbctemplate使用Spring封装好的模板，封装了数据库存取的基本过程， JDBCTemplate继承了基类JdbcAccessor和接口类JdbcOperation。在基类JdbcAccessor的设计中，对DataSource数据源进行管理和配置。在JdbcOperation接口中，定义了通过Jdbc操作数据库的基本操作方法，而JdbcTemplate提供这些接口方法的实现，比如execute方法、query方法、update方法等

  
  jdbctemplate类
• 模板模式：所谓模板板式，就是在父类中定义算法的主要流程，而把一些需要用户自定义的步骤暴露给子类去实现或者使用回调函数来实现，父类始终控制着整个流程的主动权，对于JdbcTemplate，其主要采用回调函数的方式来实现
• JdbcTemplate回调类主要有如下:

(1)ConnectionCallback：通过回调类中doInStatement()方法所提供的数据访问的操作句柄Connection来进行数据访问操作
(2)StatementCallback：同理获得Statement操作句柄来进行操作
(3)PreparedStatementCallback：同理获得PreparedStatement操作句柄进行操作
(4)CallableStatementCallback：主要用于数据库存储过程的访问

1.jdbcTemplate查询query

以List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));为例

query执行流程

1.new BeanPropertyRowMapper<>(User.class)进行BeanPropertyRowMapper封装，然后把RowMapper封装进RowMapperResultSetExtractor类中

进行rowMapper封装
2.执行query方法
query方法

3.上面是传了一个callback对象的实例进入execute，，然后执行execute方法

 
    public <T> T execute(StatementCallback<T> action) throws DataAccessException {
        Assert.notNull(action, "Callback object must not be null");
                //创建数据库连接
        Connection con = DataSourceUtils.getConnection(getDataSource());
        Statement stmt = null;
        try {
            Connection conToUse = con;
            if (this.nativeJdbcExtractor != null &&
                    this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) {
                conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
            }
            stmt = conToUse.createStatement();
            //应用用户输入的参数
            applyStatementSettings(stmt);
            Statement stmtToUse = stmt;
            if (this.nativeJdbcExtractor != null) {
                stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt);
            }
            //回调QueryStatementCallback的doInStatement方法，然后doStatement方法里面又回调BeanPropertyRowMapper类的rowMap方法，
                        //rowMap取得每一行的信息封装成Person对象返回，一直返回，知道返回到这里的result
            T result = action.doInStatement(stmtToUse);
            //警告处理
            handleWarnings(stmt);
            return result;
        }
        catch (SQLException ex) {
            // 发生异常时候释放资源
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            DataSourceUtils.releaseConnection(con, getDataSource());
            con = null;
            throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);
        }
        finally {
            //释放Statement
            JdbcUtils.closeStatement(stmt);
            //释放Connection
            DataSourceUtils.releaseConnection(con, getDataSource());
        }
    }

4.获取到List对象后，就直接退出execute方法，逐步返回result，知道返回给最初的调用者

2.update方法流程

update流程

四、jdbcTemplate线程安全问题

• 看TransactionSynchronizationManager类

---

参考：https://blog.csdn.net/fighterandknight/article/details/51358140