spring揭秘14-JdbcTemplate概述与使用操作对象访问数据

【README】

本文总结自《spring揭秘》，作者王福强，非常棒的一本书，墙裂推荐；

【1】JdbcTemplate概述

【1.1】Jdbc原生api操作数据库

1）使用jdbc操作数据库代码

【DaoViaJdbc】

public class DaoViaJdbc implements IDao {

    public int update(String sql) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection();
            statement = connection.createStatement(); 
            return statement.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    System.out.println("关闭statement成功"); 
                } catch (SQLException e) {
                    System.out.println("关闭statement异常"); 
                    e.printStackTrace(System.err);// 仅演示，不要这么做 
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                    System.out.println("关闭connection成功"); 
                } catch (SQLException e) {
                    System.out.println("关闭connection异常"); 
                    e.printStackTrace(System.err);// 仅演示，不要这么做 
                }
            }
        }
    }

    public static Connection getConnection() throws SQLException {
        Properties props = new Properties();
        ResourceLoader resourceLoader = new DefaultResourceLoader();
        Resource resource = resourceLoader.getResource("classpath:chapter14/springdiscover_db.properties");
        try (InputStream dbConnConfInputStream = resource.getInputStream()) {
            props.load(dbConnConfInputStream); // 加载数据库连接信息的 .properties 文件
        } catch (IOException ioException) {
            throw new RuntimeException(ioException);
        }
        String drivers = props.getProperty("jdbc.drivers"); //数据库驱动器
        if (drivers != null) System.setProperty("jdbc.drivers", drivers);
        String url = props.getProperty("jdbc.url");
        String username = props.getProperty("jdbc.username");
        String password = props.getProperty("jdbc.password");

        return DriverManager.getConnection(url, username, password); //利用驱动管理器打开一个数据库连接
    }
}

2）使用jdbc原生api操作数据库的问题：并不是一条sql就是一个事务，就是一个连接；如果多条sql是一个事务，则上述需要修改，需要自定义操作jdbc api的代码；这会导致如下问题（简单理解是： jdbc原生api提供的是简单功能或原子功能，业务场景复杂多变，使用jdbc原生api实现业务功能，开发与运维成本高）：

• 通过jdbc api操作数据库的代码本不属于业务功能代码，但却散落在系统各个业务逻辑中（散弹式代码）；
• 操作jdbc api有技术门槛，如果操作不当，如数据库连接没有关闭，事务没有控制好，极易导致系统问题；
• 没有统一的异常类型确认机制；SQLException是一个比较泛的异常类，不足以说明异常原因；如主键冲突异常，非空异常等；
  • 对于同一个异常原因，不同数据库定义的异常类型不一样？ 岂不是换一个数据库，整个dao层代码都要重构？ 我想这不是一个合理的设计 ；
  • 对于同一个异常原因，不同数据库虽然使用相同的异常类，但使用不用的错误码；如mysql把errocode=1作为表示数据库连不上，而oracle把errorcode=2表示数据库连不上；
• 其他：jdbc原生api本身没有提供池化技术（数据库连接池），不满足性能要求；

基于此，我想一个良好的设计，大概思路是要封装jdbc的数据访问代码（如connection，statement），对外暴露封装后的代理对象或包装类对象；此外，应该还会统一异常类型及异常码，做到对数据库类型无感；由此 JdbcTemplate产生了；

【1.1.1】基于Jdbc+PreparedStatement批量更新

1）因为JdbcTemplate有批量更新的api，所以本文也贴出Jdbc批量更新的代码，以便对比；

【BatchUpdateViaJdbcMain】

public class BatchUpdateViaJdbcMain {
    public static void main(String[] args) {
        BatchUpdateViaJdbc batchUpdateViaJdbc = new BatchUpdateViaJdbc();
        batchUpdateViaJdbc.batchInsert(Arrays.asList(
           new UserDto("批量王五01", "12345678901", "成都01", "批量备注01")
           , new UserDto("批量王五02", "12345678902", "成都02", "批量备注02")
        ));
    }
}

【BatchUpdateViaJdbc】

public class BatchUpdateViaJdbc { 
    public int[] batchInsert(List<UserDto> userDtoList) {
        String sql = "insert into user_tbl(name, mobile_phone, addr, remark) values(?, ?, ?, ?)";
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = ConnectionUtils.getConnection();
            connection.setAutoCommit(false); // 设置手动提交
            preparedStatement = connection.prepareStatement(sql);
            // 批量设置占位符参数
            for (UserDto userDto : userDtoList) {
                preparedStatement.setString(1, userDto.getName());
                preparedStatement.setString(2, userDto.getMobilePhone());
                preparedStatement.setString(3, userDto.getAddr());
                preparedStatement.setString(4, userDto.getRemark());
                preparedStatement.addBatch(); 
            }
            // 执行批量更新
            int[] resultArr = preparedStatement.executeBatch();
            connection.commit();
            return resultArr;
        } catch (SQLException e) { 
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                    System.out.println("关闭statement成功");
                } catch (SQLException e) {
                    System.out.println("关闭statement异常");
                    e.printStackTrace(System.err);// 仅演示，不要这么做 
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                    System.out.println("关闭connection成功");
                } catch (SQLException e) {
                    System.out.println("关闭connection异常");
                    e.printStackTrace(System.err);// 仅演示，不要这么做 
                }
            }
        }
    }
} 

【1.2】JdbcTemplate概述

1）JdbcTemplate主要关注两件事情 ：

• 封装所有基于jdbc的数据访问代码； 以统一格式和规范来使用jdbc api；
• 把jdbc的数据访问异常(SQLException及其子类，包括各数据库驱动提供的子类)进行统一转译，统一异常类型，简化客户端对数据访问异常的处理；

2）JdbcTemplate采用模板方法模式封装jdbc的数据访问代码； 模版方法模式作用是定义算法步骤，而由子类提供具体实现；

【DaoViaJdbcTemplate】通过JdbcTemplate实现dao层 （ 显然，与jdbc原生api相比，通过JdbcTemplate操作数据库，代码更加简洁 ）

public class DaoViaJdbcTemplate {
    public Integer update(String sql) {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
        StatementCallback<Integer> statementCallback = new StatementCallback<>() {
            @Override
            public Integer doInStatement(Statement stmt) throws SQLException {
                return stmt.executeUpdate(sql);
            }
        };
        return jdbcTemplate.execute(statementCallback);
    }

    public static DataSource getDataSource() {
        Properties props = new Properties();
        ResourceLoader resourceLoader = new DefaultResourceLoader();
        Resource resource = resourceLoader.getResource("classpath:chapter14/springdiscover_db.properties");
        try (InputStream dbConnConfInputStream = resource.getInputStream()) {
            props.load(dbConnConfInputStream); // 加载数据库连接信息的 .properties 文件
        } catch (IOException ioException) {
            throw new RuntimeException(ioException);
        }
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(props.getProperty("jdbc.drivers"));
        druidDataSource.setUrl(props.getProperty("jdbc.url"));
        druidDataSource.setUsername(props.getProperty("jdbc.username"));
        druidDataSource.setPassword(props.getProperty("jdbc.password"));
        return druidDataSource;
    }
}

【JdbcTemplate#execute】

@Nullable
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
    return this.execute(action, true);
}
@Nullable
    private <T> T execute(StatementCallback<T> action, boolean closeResources) throws DataAccessException {
        Assert.notNull(action, "Callback object must not be null");
        Connection con = DataSourceUtils.getConnection(this.obtainDataSource());
        Statement stmt = null;

        Object var12;
        try {
            stmt = con.createStatement();
            this.applyStatementSettings(stmt);
            T result = action.doInStatement(stmt);
            this.handleWarnings(stmt);
            var12 = result;
        } catch (SQLException var10) {
            if (stmt != null) {
                this.handleWarnings(stmt, var10);
            }

            String sql = getSql(action);
            JdbcUtils.closeStatement(stmt);
            stmt = null;
            DataSourceUtils.releaseConnection(con, this.getDataSource());
            con = null;
            throw this.translateException("StatementCallback", sql, var10); // JdbcTemplate对jdbc原生异常SQLException进行转译 
        } finally {
            if (closeResources) {
                JdbcUtils.closeStatement(stmt);
                DataSourceUtils.releaseConnection(con, this.getDataSource());
            }

        }

        return var12;
    }

protected DataAccessException translateException(String task, @Nullable String sql, SQLException ex) { // 具体异常转译方法
        DataAccessException dae = this.getExceptionTranslator().translate(task, sql, ex);
        return (DataAccessException)(dae != null ? dae : new UncategorizedSQLException(task, sql, ex));
    }

【代码解说】

显然， jdcbTemplate会捕获SQLException异常，调用translateException()方法进行转译，转译为DataAccessException子类，如下。；

【1.2.1】JdbcTemplate类图

1）JdbcTemplate定义：继承JdbcAccessor， 实现 JdbcOperations接口

public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {

2）JdbcAccessor类：主要为JdbcTemplate提供公用属性：

• DataSource（javax.sql.DataSource）：数据源；可以看做是jdbc连接工厂，用来替代基于DriverManager的数据库连接创建方式（参见DaoViaJdbc）；
  • 具体实现类（如DruidDataSource）可以引入对数据库连接池以及分布式事务支持；
  • DataSource可以作为数据库资源的统一接口；
• SQLExceptionTranslator：SQL异常转换器； 即把不同数据库类型的异常，不同错误码但根因相同的异常，转换为根因相同的统一异常；

public abstract class JdbcAccessor implements InitializingBean {
    protected final Log logger = LogFactory.getLog(this.getClass());
    @Nullable
    private DataSource dataSource; // 数据源, 可以看做是jdbc连接工厂
    @Nullable
    private volatile SQLExceptionTranslator exceptionTranslator; // SQL异常转换器
    private boolean lazyInit = true;

    public JdbcAccessor() {
    }

    public void setDataSource(@Nullable DataSource dataSource) {
        this.dataSource = dataSource;
    }
    ......
}

3）JdbcOperations接口：定义了基于jdbc数据访问方法，包括查询query，更新update，执行execute，以及批量操作； 其中update可以执行增加删除修改等写操作；

public interface JdbcOperations {
    <T> T execute(ConnectionCallback<T> action) throws DataAccessException;

    @Nullable
    <T> T execute(StatementCallback<T> action) throws DataAccessException;
	
	<T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action) throws DataAccessException;
	
	int update(String sql) throws DataAccessException;	
	
	<T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException;
	
	@Nullable
    <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException;
	
	Map<String, Object> queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException;
	
	<T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType) throws DataAccessException;
	
	int[] batchUpdate(String... sql) throws DataAccessException;
	
    @Nullable
    <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action) throws DataAccessException;
	......
}
@FunctionalInterface
public interface ConnectionCallback<T> {
    @Nullable
    T doInConnection(Connection con) throws SQLException, DataAccessException;
} 
@FunctionalInterface
public interface StatementCallback<T> {
    @Nullable
    T doInStatement(Statement stmt) throws SQLException, DataAccessException;
} 
@FunctionalInterface
public interface PreparedStatementCallback<T> {
    @Nullable
    T doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException;
}
@FunctionalInterface
public interface CallableStatementCallback<T> {
    @Nullable
    T doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException;
}

4）JdbcTemplate中各种模版方法，可以分为如下4组：

• ConnectionCallback面向Connection的模版方法：基于Connection访问数据的回调方法，不建议，因为Connection操作数据库逻辑复杂，如一个连接多个statement，涉及到连接关闭，事务管理等；

• StatementCallback面向Statement的模版方法：基于Statement处理静态sql的数据访问请求；

• PreparedStatementCallback面向PreparedStatement的模版方法：基于PreparedStatement处理包含参数的sql的数据访问请求； 此外，使用 PreparedStatement 可以避免sql 注入攻击；

• CallableStatementCallback面向CallableStatement的模版方法：主要处理存储过程；（ 不推荐存储过程，因为移植性非常差 ）

【1.2.2】使用DataSourceUtils管理Connection

1）获取数据库连接：使用 DataSourceUtils.getConnection(DataSource) 获取连接； 与直接从DataSource获取连接不同的是，DataSourceUtils获取连接会将取得的 Connection 绑定到当前线程（底层通过ThreadLocal实现）， 以便在spring事务管理中使用；

【JdbcTemplate#execute()】

@Nullable
    private <T> T execute(StatementCallback<T> action, boolean closeResources) throws DataAccessException {
        Assert.notNull(action, "Callback object must not be null");
        Connection con = DataSourceUtils.getConnection(this.obtainDataSource()); // 获取数据库连接
        Statement stmt = null;

        Object var12;
        try {
            stmt = con.createStatement(); // 根据连接创建语句对象
            this.applyStatementSettings(stmt);  // 为语句对象设置参数
            T result = action.doInStatement(stmt); // 传入语句对象到StatementCallback回调对象，其doInStatement执行对应sql 
            this.handleWarnings(stmt);
            var12 = result;
        } catch (SQLException var10) {
            if (stmt != null) {
                this.handleWarnings(stmt, var10);
            }
            String sql = getSql(action);
            JdbcUtils.closeStatement(stmt); // 异常时关闭语句
            stmt = null; 
            DataSourceUtils.releaseConnection(con, this.getDataSource()); // 异常时释放连接
            con = null;
            throw this.translateException("StatementCallback", sql, var10); // 异常转译 
        } finally {
            if (closeResources) {
                JdbcUtils.closeStatement(stmt); // 关闭语句
                DataSourceUtils.releaseConnection(con, this.getDataSource()); // 释放连接 
            }
        }
        return var12;
    }

【1.2.3】设置Statement参数（控制行为）

1）设置Statement参数：

• fetchSize： 设置每次从数据库获取的数据行数，即通过迭代器流式取数，每次 iterator.next() 获取fetchSize条数据并缓存；
• maxRows：设置结果集的最大行数，设置为0表示不限制；（类似于 limit ， 如每次最多查询1000条数据）
• timeout：设置statement执行的超时时间；

【JdbcTemplate#applyStatementSettings】设置语句参数

protected void applyStatementSettings(Statement stmt) throws SQLException {
    int fetchSize = this.getFetchSize();
    if (fetchSize != -1) {
        stmt.setFetchSize(fetchSize);
    }

    int maxRows = this.getMaxRows();
    if (maxRows != -1) {
        stmt.setMaxRows(maxRows);
    }

    DataSourceUtils.applyTimeout(stmt, this.getDataSource(), this.getQueryTimeout());
}

【1.3】JdbcTemplate中SQLException到DataAccessException的转译（统一异常类型）

1） 作用：把SQLException转译到DataAccessException，以统一数据访问异常；

• 转译工作交给了 SQLExceptionTranslator 来完成；
• SQLExceptionTranslator接口的子类包括：SQLErrorCodeSQLExceptionTranslator， SQLExceptionSubclassTranslator， SQLStateSQLExceptionTranslator， AbstractFallbackSQLExceptionTranslator（抽象类）

2）SQLErrorCodeSQLExceptionTranslator转译SQLException异常到DataAccessException的步骤：

• 步骤1：检查classpath是否配置 sql-error-codes.xml 文件 ，若配置使用 SQLErrorCodeSQLExceptionTranslator ，否则使用 SQLExceptionSubclassTranslator； 当然了，可以通过 setExceptionTranslator(SQLExceptionTranslator exceptionTranslator) 设置转译器；
• 步骤2：调用 AbstractFallbackSQLExceptionTranslator#translate 转译；
  • 判断是否有自定义转译器，若有且转译成功，则直接返回转译结果；
  • 若没有，则调用SQLErrorCodeSQLExceptionTranslator #doTranslate(…) 或 SQLExceptionSubclassTranslator#doTranslate(…)
    • SQLErrorCodeSQLExceptionTranslator#doTranslate(…) 调用 this.customTranslate(…) 尝试转译 ；customTranslate方法默认返回null，则表示不能转译，则继续；
      • 检测是否设置 SQLExceptionTranslator，若有则尝试转译，转译结果不为null，则直接返回； 否则获取 errorcode，errorcode不为空，则使用SQLErrorCodeSQLExceptionTranslator 默认转译逻辑进行转译，如用BadSqlGrammarException封装errocdoe；若errorcode为null，返回null；
    • SQLExceptionSubclassTranslator#doTranslate(…)
      • 根据Exception类型进行判断，然后做异常转译，即使用spring统一异常封装SQLException；
• 若上述第2步异常转译结果为null，则获取降级转译器fallbackTranslator，若设置则使用fallbackTranslator转译；若没有设置则返回null；
• 若上述3步转译结果为null，则最后使用 UncategorizedSQLException封装；

3）自定义异常转换器（转译器）

• 继承 SQLErrorCodeSQLExceptionTranslator ，重写 customTranslate方法，转译异常；
• 在classpath新增sql-error-codes.xml 文件，增加errorcode定义；
• 手工设置 SQLExceptionTranslator 转译器；

【SQLExceptionSubclassTranslator#doTranslate(…)】

protected DataAccessException doTranslate(String task, @Nullable String sql, SQLException ex) {
    if (ex instanceof SQLTransientException) {
        if (ex instanceof SQLTransientConnectionException) {
            return new TransientDataAccessResourceException(this.buildMessage(task, sql, ex), ex);
        }

        if (ex instanceof SQLTransactionRollbackException) {
            if (SQLStateSQLExceptionTranslator.indicatesCannotAcquireLock(ex.getSQLState())) {
                return new CannotAcquireLockException(this.buildMessage(task, sql, ex), ex);
            }

            return new PessimisticLockingFailureException(this.buildMessage(task, sql, ex), ex);
        }

        if (ex instanceof SQLTimeoutException) {
            return new QueryTimeoutException(this.buildMessage(task, sql, ex), ex);
        }
        ...... 
}

【1.3.1】自定义异常转译器

1）继承 SQLErrorCodeSQLExceptionTranslator ，重写customTranslate方法；

public class CustomSqlExceptionTranslator extends SQLErrorCodeSQLExceptionTranslator {
    @Override
    protected DataAccessException customTranslate(String task, String sql, SQLException sqlEx) {
        if (Objects.equals(sqlEx.getErrorCode(), "119")) {
            return new BadSqlGrammarException(task, sql, sqlEx);
        }
        return null;
    }
}

2）新增sql-error-codes.xml 文件 ，增加errorcode定义；

格式参考org\springframework\jdbc\support\sql-error-codes.xml文件，拷贝一份作为自定义xml文件；

【2】使用JdbcTemplate进行数据访问

1）使用spring ioc容器管理JdbcTemplate；

【2.1】使用JdbcTemplate查询数据

1）查询数据的模版方法：

public interface JdbcOperations {
    @Nullable
    <T> T query(String sql, ResultSetExtractor<T> rse) throws DataAccessException;

    void query(String sql, RowCallbackHandler rch) throws DataAccessException;

    <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException;

    <T> Stream<T> queryForStream(String sql, RowMapper<T> rowMapper) throws DataAccessException;

    @Nullable
    <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws DataAccessException;

    @Nullable
    <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException;

    Map<String, Object> queryForMap(String sql) throws DataAccessException;

    <T> List<T> queryForList(String sql, Class<T> elementType) throws DataAccessException;

    List<Map<String, Object>> queryForList(String sql) throws DataAccessException;

    SqlRowSet queryForRowSet(String sql) throws DataAccessException;

    @Nullable
    <T> T query(PreparedStatementCreator psc, ResultSetExtractor<T> rse) throws DataAccessException;

    @Nullable
    <T> T query(String sql, @Nullable PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException;

    @Nullable
    <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) throws DataAccessException;

    /** @deprecated */
    @Deprecated
    @Nullable
    <T> T query(String sql, @Nullable Object[] args, ResultSetExtractor<T> rse) throws DataAccessException;

    @Nullable
    <T> T query(String sql, ResultSetExtractor<T> rse, @Nullable Object... args) throws DataAccessException;

    void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException;

    void query(String sql, @Nullable PreparedStatementSetter pss, RowCallbackHandler rch) throws DataAccessException;

    void query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch) throws DataAccessException;

    /** @deprecated */
    @Deprecated
    void query(String sql, @Nullable Object[] args, RowCallbackHandler rch) throws DataAccessException;

    void query(String sql, RowCallbackHandler rch, @Nullable Object... args) throws DataAccessException;

    <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException;

    <T> List<T> query(String sql, @Nullable PreparedStatementSetter pss, RowMapper<T> rowMapper) throws DataAccessException;

    <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException;

    @Deprecated
    <T> List<T> query(String sql, @Nullable Object[] args, RowMapper<T> rowMapper) throws DataAccessException;

    <T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object... args) throws DataAccessException;

    <T> Stream<T> queryForStream(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException;

    <T> Stream<T> queryForStream(String sql, @Nullable PreparedStatementSetter pss, RowMapper<T> rowMapper) throws DataAccessException;

    <T> Stream<T> queryForStream(String sql, RowMapper<T> rowMapper, @Nullable Object... args) throws DataAccessException;

    @Nullable
    <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException;

    @Deprecated
    @Nullable
    <T> T queryForObject(String sql, @Nullable Object[] args, RowMapper<T> rowMapper) throws DataAccessException;

    @Nullable
    <T> T queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object... args) throws DataAccessException;

    @Nullable
    <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType) throws DataAccessException;

    @Deprecated
    @Nullable
    <T> T queryForObject(String sql, @Nullable Object[] args, Class<T> requiredType) throws DataAccessException;

    @Nullable
    <T> T queryForObject(String sql, Class<T> requiredType, @Nullable Object... args) throws DataAccessException;

    Map<String, Object> queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException;

    Map<String, Object> queryForMap(String sql, @Nullable Object... args) throws DataAccessException;

    <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType) throws DataAccessException;

    /** @deprecated */
    @Deprecated
    <T> List<T> queryForList(String sql, @Nullable Object[] args, Class<T> elementType) throws DataAccessException;

    <T> List<T> queryForList(String sql, Class<T> elementType, @Nullable Object... args) throws DataAccessException;

    List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes) throws DataAccessException;

    List<Map<String, Object>> queryForList(String sql, @Nullable Object... args) throws DataAccessException;

    SqlRowSet queryForRowSet(String sql, Object[] args, int[] argTypes) throws DataAccessException;

    SqlRowSet queryForRowSet(String sql, @Nullable Object... args) throws DataAccessException;
} 

2）查询结果类型如下：

• 单个：泛型T（如javabean），Map<String, Object>
• 批量：泛型T（如List）， List<T>， List<Map> ，Stream<T>, SqlRowSet

3）自定义查询结果处理逻辑（策略模式）；

• ResultSetExtractor：结果集抽取器，处理结果集并以任何形式返回；
• RowCallbackHandler：数据行回调处理器，处理单行数据；
  • 底层通过 RowCallbackHandlerResultSetExtractor（ResultSetExtractor子类） 封装 RowCallbackHandler ，接着调用RowCallbackHandlerResultSetExtractor.extractData()进行处理；
• RowMapper：数据行映射，处理单行数据；
  • 底层通过 RowMapperResultSetExtractor（ResultSetExtractor子类） 封装 RowMapper，接着调用RowMapperResultSetExtractor.extractData()进行处理；

下面，我们使用场景示例介绍结果集的不同处理逻辑； 业务场景为： 查询user表后， 把数据映射到User javabean，并以list返回查询结果；

【2.1.1】使用ResultSetExtractor处理查询结果集

1）ResultSetExtractor结果集抽取器定义：

@FunctionalInterface
public interface ResultSetExtractor<T> {
    @Nullable
    T extractData(ResultSet rs) throws SQLException, DataAccessException; // 有返回值，泛型T （泛型T可以是javabean，可以是Map，可以是List<javabean>，可以是List<Map> 等）
}

【QueryByResultSetExtractorMain】

public class QueryByResultSetExtractorMain {
    public static void main(String[] args) {
        QueryByResultSetExtractor queryByResultSetExtractor = new QueryByResultSetExtractor();
        List<UserDto> userDtoList = queryByResultSetExtractor.query(
                "select id, name, mobile_phone, addr from user_tbl where id > 500000");
        System.out.println(userDtoList.size());
    }
}

【QueryByResultSetExtractor】 使用结果集抽取器处理查询结果集

public class QueryByResultSetExtractor {
    public List<UserDto> query(String sql) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
        return jdbcTemplate.query(sql, new ResultSetExtractor<>() { 
            List<UserDto> userDtoList = new ArrayList<>();
            @Override
            public List<UserDto> extractData(ResultSet rs) throws SQLException, DataAccessException {
                while(rs.next()) {
                    UserDto userDto = new UserDto();
                    userDto.setId(Integer.parseInt(rs.getString("id")));
                    userDto.setName(rs.getString("name"));
                    userDto.setMobilePhone(rs.getString("mobile_phone"));
                    userDto.setAddr(rs.getString("addr"));
                    userDtoList.add(userDto);
                }
                return userDtoList;
            }
        });
    }
}

【DataSourceUtils】

public class DataSourceUtils {
    public static DataSource getDataSource() {
        Properties props = new Properties();
        ResourceLoader resourceLoader = new DefaultResourceLoader();
        Resource resource = resourceLoader.getResource("classpath:chapter14/springdiscover_db.properties");
        try (InputStream dbConnConfInputStream = resource.getInputStream()) {
            props.load(dbConnConfInputStream); // 加载数据库连接信息的 .properties 文件
        } catch (IOException ioException) {
            throw new RuntimeException(ioException);
        }
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(props.getProperty("jdbc.drivers"));
        druidDataSource.setUrl(props.getProperty("jdbc.url"));
        druidDataSource.setUsername(props.getProperty("jdbc.username"));
        druidDataSource.setPassword(props.getProperty("jdbc.password"));
        return druidDataSource;
    }

    private DataSourceUtils() {
        // do nothing.
    }
} 

【JdbcTemplate#query(final String sql, final ResultSetExtractor rse)】

@Nullable
public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException {
    Assert.notNull(sql, "SQL must not be null");
    Assert.notNull(rse, "ResultSetExtractor must not be null");
    if (this.logger.isDebugEnabled()) {
        this.logger.debug("Executing SQL query [" + sql + "]");
    }

    class QueryStatementCallback implements StatementCallback<T>, SqlProvider {
        QueryStatementCallback() {
        }

        @Nullable
        public T doInStatement(Statement stmt) throws SQLException {
            ResultSet rs = null;

            Object var3;
            try {
                rs = stmt.executeQuery(sql);
                var3 = rse.extractData(rs); // 调用结果集抽取器的方法extractData，处理结果集 
            } finally {
                JdbcUtils.closeResultSet(rs);
            }

            return var3;
        }

        public String getSql() {
            return sql;
        }
    }

    return this.execute(new QueryStatementCallback(), true); // 策略模式
}

【2.1.2】使用RowCallbackHandler处理查询结果集

1）RowCallbackHandler数据行回调处理器定义：

@FunctionalInterface
public interface RowCallbackHandler { 
    void processRow(ResultSet rs) throws SQLException;
}

【QueryByRowCallbackHandlerMain】

public class QueryByRowCallbackHandlerMain {
    public static void main(String[] args) {
        QueryByRowCallbackHandler queryByRowCallbackHandler = new QueryByRowCallbackHandler();
        List<UserDto> userDtoList = queryByRowCallbackHandler.query(
                "select id, name, mobile_phone, addr from user_tbl where id > 500000");
        System.out.println(userDtoList);
    }
}

【QueryByRowCallbackHandler】使用数据行回调处理器处理查询结果集

public class QueryByRowCallbackHandler {
    public List<UserDto> query(String sql) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
        List<UserDto> userDtoList = new ArrayList<>();
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                UserDto userDto = new UserDto();
                userDto.setId(Integer.parseInt(rs.getString("id")));
                userDto.setName(rs.getString("name"));
                userDto.setMobilePhone(rs.getString("mobile_phone"));
                userDto.setAddr(rs.getString("addr"));
                userDtoList.add(userDto);
            }
        });
        return userDtoList;
    }
}

【JdbcTemplate#query(String sql, RowCallbackHandler rch)】

public void query(String sql, RowCallbackHandler rch) throws DataAccessException {
    this.query((String)sql, (ResultSetExtractor)(new RowCallbackHandlerResultSetExtractor(rch))); // 底层实际调用【JdbcTemplate#query(final String sql, final ResultSetExtractor<T> rse)】 
}

显然： 使用 RowCallbackHandlerResultSetExtractor 封装RowCallbackHandler 对象，然后调用 JdbcTemplate#query(final String sql, final ResultSetExtractor rse)方法；即底层还是使用 ResultSetExtractor 结果集抽取器进行处理；

【RowCallbackHandlerResultSetExtractor】 遍历结果集，结果集游标滑动，RowCallbackHandler.processRow() 只需要处理单行结果即可；

private static class RowCallbackHandlerResultSetExtractor implements ResultSetExtractor<Object> {
    private final RowCallbackHandler rch;

    public RowCallbackHandlerResultSetExtractor(RowCallbackHandler rch) {
        this.rch = rch;
    }

    @Nullable
    public Object extractData(ResultSet rs) throws SQLException {
        while(rs.next()) { // 结果集游标滑动 
            this.rch.processRow(rs); // 仅处理单行结果 
        }

        return null;
    }
}

【2.1.3】使用RowMapper处理查询结果集（推荐，因为代码简单）

1）RowMapper单行数据映射器定义；

@FunctionalInterface
public interface RowMapper<T> {
    @Nullable
    T mapRow(ResultSet rs, int rowNum) throws SQLException;
}

【QueryByRowMapperMain】

public class QueryByRowMapperMain {
    public static void main(String[] args) {
        QueryByRowMapper queryByRowMapper = new QueryByRowMapper();
        List<UserDto> userDtoList = queryByRowMapper.query(
                "select id, name, mobile_phone, addr from user_tbl where id > 500000");
        System.out.println(userDtoList);
    }
}

【QueryByRowMapper】 使用 RowMapper 数据行映射器处理查询结果集

public class QueryByRowMapper {
    public List<UserDto> query(String sql) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
        return jdbcTemplate.query(sql, new RowMapper<>() {
            @Override
            public UserDto mapRow(ResultSet rs, int rowNum) throws SQLException {
                UserDto userDto = new UserDto();
                userDto.setId(Integer.parseInt(rs.getString("id")));
                userDto.setName(rs.getString("name"));
                userDto.setMobilePhone(rs.getString("mobile_phone"));
                userDto.setAddr(rs.getString("addr"));
                return userDto;
            }
        });
    }
}

【2.1.4】查询结果集处理接口总结

1）使用ResultSetExtractor处理查询结果集：是最基本的结果集处理接口； 返回值类型为泛型T，T可以表示单行数据，也可以表示多行数据如List；

• 需要在 ResultSetExtractor实现类内部新建容器，如List，收集处理后的结果；

@FunctionalInterface
public interface ResultSetExtractor<T> {
    @Nullable
    T extractData(ResultSet rs) throws SQLException, DataAccessException; // 有返回值，泛型T （泛型T可以是javabean，可以是Map，可以是List<javabean>，可以是List<Map> 等）
}
//  ResultSetExtractor 实现类
public class QueryByResultSetExtractor {
    public List<UserDto> query(String sql) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
        return jdbcTemplate.query(sql, new ResultSetExtractor<>() { //  ResultSetExtractor 实现类 
            List<UserDto> userDtoList = new ArrayList<>(); 
            @Override
            public List<UserDto> extractData(ResultSet rs) throws SQLException, DataAccessException {
                while(rs.next()) {
                    UserDto userDto = new UserDto();
                    userDto.setId(Integer.parseInt(rs.getString("id")));
                    userDto.setName(rs.getString("name"));
                    userDto.setMobilePhone(rs.getString("mobile_phone"));
                    userDto.setAddr(rs.getString("addr"));
                    userDtoList.add(userDto);
                }
                return userDtoList;
            }
        });
    }
}

2）使用RowCallbackHandler处理查询结果集： 单行数据回调处理器； 返回值类型为空；

• 需要在RowCallbackHandler实现类外部新建容器，如List，收集处理后的结果；
• 底层使用 RowCallbackHandlerResultSetExtractor（ResultSetExtractor子类） 封装RowCallbackHandler 对象 , 即底层实现是 ResultSetExtractor ；

@FunctionalInterface
public interface RowCallbackHandler { 
    void processRow(ResultSet rs) throws SQLException;
}
//  RowCallbackHandler 实现类
public class QueryByRowCallbackHandler {
    public List<UserDto> query(String sql) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
        List<UserDto> userDtoList = new ArrayList<>();
        jdbcTemplate.query(sql, new RowCallbackHandler() { //  RowCallbackHandler 实现类 
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                UserDto userDto = new UserDto();
                userDto.setId(Integer.parseInt(rs.getString("id")));
                userDto.setName(rs.getString("name"));
                userDto.setMobilePhone(rs.getString("mobile_phone"));
                userDto.setAddr(rs.getString("addr"));
                userDtoList.add(userDto);
            }
        });
        return userDtoList;
    }
}

3）使用RowMapper处理查询结果集： 单行数据回调处理器； 返回值类型为T；

• 无需新建容器收集处理后的结果；
• 底层通过 RowMapperResultSetExtractor（ResultSetExtractor子类） 封装 RowMapper，即底层实现是 ResultSetExtractor ；

@FunctionalInterface
public interface RowMapper<T> {
    @Nullable
    T mapRow(ResultSet rs, int rowNum) throws SQLException;
}
// RowMapper 
public class QueryByRowMapper {
    public List<UserDto> query(String sql) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
        return jdbcTemplate.query(sql, new RowMapper<>() { // RowMapper 实现类 
            @Override
            public UserDto mapRow(ResultSet rs, int rowNum) throws SQLException {
                UserDto userDto = new UserDto();
                userDto.setId(Integer.parseInt(rs.getString("id")));
                userDto.setName(rs.getString("name"));
                userDto.setMobilePhone(rs.getString("mobile_phone"));
                userDto.setAddr(rs.getString("addr"));
                return userDto;
            }
        });
    }
}

综上： 使用 RowMapper 或者 RowCallbackHandler 处理查询结果集，比较常用，特别推荐RowMapper ，其代码最简单 ；底层都是使用 ResultSetExtractor做处理；

【2.2】使用JdbcTemplate更新数据

1）更新数据的模版方法（包括单个更新， 批量更新）：

public interface JdbcOperations { 
    int[] batchUpdate(String... sql) throws DataAccessException;

    int update(PreparedStatementCreator psc) throws DataAccessException;

    int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) throws DataAccessException;

    int update(String sql, @Nullable PreparedStatementSetter pss) throws DataAccessException;

    int update(String sql, Object[] args, int[] argTypes) throws DataAccessException;

    int update(String sql, @Nullable Object... args) throws DataAccessException;

    int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) throws DataAccessException;

    int[] batchUpdate(PreparedStatementCreator psc, BatchPreparedStatementSetter pss, KeyHolder generatedKeyHolder) throws DataAccessException;

    int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException;

    int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes) throws DataAccessException;

    <T> int[][] batchUpdate(String sql, Collection<T> batchArgs, int batchSize, ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException;   
}

2）更新数据方法分类：

• 通过sql更新（参数封装到sql中）；
• 通过 PreparedStatement 预编译语句更新，PreparedStatement 为sql中的占位符赋值；
  • PreparedStatementCreator 可以创建 PreparedStatement ；
  • 使用 PreparedStatementSetter 或 BatchPreparedStatementSetter 对占位符进行设置；

【2.2.1】使用JdbcTemplate单个更新

1） 使用JdbcTemplate单个更新，底层使用PreparedStatement预编译语句替换占位符：

• 使用 PreparedStatementCreator 创建 PreparedStatement
• 使用 PreparedStatementSetter 为占位符赋值；

public class SingleUpdateViaJdbcTemplate {
    // 使用 PreparedStatementCreator 创建 PreparedStatement 
    public void updateByPreparedStatementSetter(String sql) { // 有入参sql但没有使用，想说明sql可以由上游传入
        sql = "update user_tbl set name = ? where id = ?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
        jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, "张三01");
                ps.setInt(2, 500001);
            }
        });
    }
	
    // 使用 PreparedStatementCreator 创建 PreparedStatement 
    public void updateByPreparedStatementCreator(String sql) {
        String sql2 = "update user_tbl set name = ? where id = ?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql2);
                ps.setString(1, "张三02");
                ps.setInt(2, 500002);
                return ps;
            }
        });
    }
}

【2.2.2】使用JdbcTemplate批量更新（特别重要）*

1）使用JdbcTemplate批量更新方法batchUpdate，底层使用 BatchPreparedStatementSetter 对批量更新中每次更新所需要的参数进行设置：

【BatchUpdateViaJdbcTemplate】通过JdbcTemplate批量更新

public class BatchUpdateViaJdbcTemplate  {
    public void batchUpdateByPreparedStatementCreator(List<UserDto> userDtoList) {
        String sql2 = "update user_tbl set name = ? where id = ?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
        jdbcTemplate.batchUpdate(sql2, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                UserDto userDto = userDtoList.get(i);
                ps.setString(1, userDto.getName());
                ps.setInt(2, userDto.getId());
            }

            @Override
            public int getBatchSize() {
                return userDtoList.size();
            }
        });
    }
}

2）此外，批量更新并不只是更新， 确切说是批量写，除了批量更新，还有批量新增与批量删除 ；

2.1）批量新增

public void batchInsertByPreparedStatementCreator(List<UserDto> userDtoList) {
    String sql2 = "insert into user_tbl(name, mobile_phone, addr, remark) values (?, ?, ?, ?)";
    JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
    jdbcTemplate.batchUpdate(sql2, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            UserDto userDto = userDtoList.get(i);
            ps.setString(1, userDto.getName());
            ps.setString(2, userDto.getMobilePhone());
            ps.setString(3, userDto.getAddr());
            ps.setString(4, userDto.getRemark());
        }

        @Override
        public int getBatchSize() {
            return userDtoList.size();
        }
    });
}

2.2）批量删除

public void batchDeleteByPreparedStatementCreator(List<UserDto> userDtoList) {
    String sql2 = "delete from user_tbl where id = ?";
    JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
    jdbcTemplate.batchUpdate(sql2, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            UserDto userDto = userDtoList.get(i);
            ps.setInt(1, userDto.getId());
        }

        @Override
        public int getBatchSize() {
            return userDtoList.size();
        }
    });
}

【2.3】*spring对数据库主键生成策略的抽象

1）spring使用 DataFieldMaxValueIncrementer 接口对数据库主键生成策略进行抽象；

public interface DataFieldMaxValueIncrementer {
    int nextIntValue() throws DataAccessException;

    long nextLongValue() throws DataAccessException;

    String nextStringValue() throws DataAccessException;
}

2）DataFieldMaxValueIncrementer 实现类分类：

• 基于独立数据库主键表的 DataFieldMaxValueIncrementer
• 基于数据库sequence的 DataFieldMaxValueIncrementer

【2.3.1】*基于独立数据库主键表的 DataFieldMaxValueIncrementer（非常重要）

1）以mysql数据库为例，使用DataFieldMaxValueIncrementer 子类 MySQLMaxValueIncrementer 作为递增主键生成器； 需要依赖独立的数据库主键表；

2）新建独立的数据库主键表 sequence_tbl

CREATE TABLE `sequence_tbl` (
  `cur_value` bigint(20) NOT NULL DEFAULT '0' COMMENT '主键',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_modify_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`cur_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='sequence序号表'

insert into sequence_tbl(cur_value) values (0);  -- 初始值为0 

【PrimaryKeyGeneratorViaJdbcTemplate】主键生成器实现

public class PrimaryKeyGeneratorViaJdbcTemplate {

    private MySQLMaxValueIncrementer pkIncrementGenerator;

    public PrimaryKeyGeneratorViaJdbcTemplate() {
        DataSource dataSource = DataSourceUtils.getDataSource();
        this.pkIncrementGenerator =
                new MySQLMaxValueIncrementer(dataSource, "sequence_tbl", "cur_value");
        pkIncrementGenerator.setCacheSize(5); // 设置缓存大小5， 即每5次刷新1次db cur_value 
    }

    public long getPkViaSequence() {
        return pkIncrementGenerator.nextLongValue();
    }
}

3）MySQLMaxValueIncrementer#nextLongValue()底层原理：

【MySQLMaxValueIncrementer#nextLongValue()】

protected synchronized long getNextKey() throws DataAccessException {
        if (this.maxId == this.nextId) { // 若下一个id与maxId相等，则需要更新数据库主键表的sequence值 
           // ...... 
                stmt = con.createStatement();
                if (!this.useNewConnection) {
                    DataSourceUtils.applyTransactionTimeout(stmt, this.getDataSource());
                }
                String columnName = this.getColumnName();
                try {
                    stmt.executeUpdate("update " + this.getIncrementerName() + " set " + columnName + " = last_insert_id(" + columnName + " + " + this.getCacheSize() + ") limit 1"); // 刷新数据库主键表的sequence值 cur_value列 （递增cacheSize大小）
                } catch (SQLException var20) {
                    throw new DataAccessResourceFailureException("Could not increment " + columnName + " for " + this.getIncrementerName() + " sequence table", var20);
                }
                ResultSet rs = stmt.executeQuery("select last_insert_id()"); // 查询上一次插入的id，即stmt.executeUpdate更新的值（因为没有提交事务，所以会有行锁） 
                try {
                    if (!rs.next()) {
                        throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
                    }

                    this.maxId = rs.getLong(1); // 获取更新后的cur_value（sequence值）
                } finally {
                    JdbcUtils.closeResultSet(rs); // 关闭结果集 
                }

                this.nextId = this.maxId - (long)this.getCacheSize() + 1L; // 下一个id=更新后的cur_value减去cacheSize大小 再加1
            } catch (SQLException var23) {
                throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", var23);
            } finally {
                JdbcUtils.closeStatement(stmt); // 关闭语句
                if (con != null) {
                    if (this.useNewConnection) {
                        try {
                            con.commit();  // 提交事务
                            if (mustRestoreAutoCommit) {
                                con.setAutoCommit(true);
                            }
                        } catch (SQLException var21) {
                            throw new DataAccessResourceFailureException("Unable to commit new sequence value changes for " + this.getIncrementerName());
                        }

                        JdbcUtils.closeConnection(con);  // 关闭连接 
                    } else {
                        DataSourceUtils.releaseConnection(con, this.getDataSource()); // 释放连接 
                    }
                }
            }
        } else {
            ++this.nextId; // nextId自增1
        }
        return this.nextId;
    }

【代码解说】MySQLMaxValueIncrementer获取下一个id的步骤：

• 步骤1：判断 nextId 与 maxId 是否相等（初始时，nextId 与 maxId 都等于0L ）；
  • 不等，则nextId自增1后返回；
• 步骤2(nextId == maxId)： 数据库主键表的sequence值cur_value列，cur_value列 = cur_value列+cacheSize
• 步骤3：查询上一次插入的id，select last_insert_id() ；即 刚刚更新的cur_value列值； （因为更新操作没有提交事务，还在缓存中，且当前事务持有行锁）
• 步骤4：用上一次插入的id 更新回 maxId ；
• 步骤5：下一个id等于 maxId 减去cacheSize 再加1 ；
• 步骤6：提交事务，关闭连接（释放行锁）；

补充：更新cur_value sql如下：

update sequence_tbl set cur_value = last_insert_id(cur_value + 5) limit 1  

【举例】MySQLMaxValueIncrementer获取下一个id：数据库主键表sequence_tbl，序号列cur_value的初始值为0；MySQLMaxValueIncrementer的cacheSize配置为5， 初始时，nextId 与 maxId 都等于0L ；

• 第1次获取id：nextId == maxId， 则刷新cur_value=cur_value+cacheSize=5； maxId =select last_insert_id() = 5；nextId = maxId - cacheSize + 1= 5-5+1 = 1；返回 nextId =1；
• 第2次获取id：nextId != maxId（nextId=1，maxId =5 ）， nextId自加1后返回， 即返回nextId=2；
• 第3次获取id：nextId != maxId（nextId=2，maxId =5 ）， nextId自加1后返回， 即返回nextId=3；
• 第4次获取id：nextId != maxId（nextId=3，maxId =5 ）， nextId自加1后返回， 即返回nextId=4；
• 第5次获取id：nextId != maxId（nextId=4，maxId =5 ）， nextId自加1后返回， 即返回nextId=5；
• 第6次获取id：nextId == maxId（nextId=5，maxId =5 ）， 则刷新cur_value=cur_value+cacheSize=5+5=10； maxId =select last_insert_id() = 10；nextId = maxId - cacheSize + 1= 10-5+1 = 6；返回 nextId =6；
• …

【2.3.2】基于数据库sequence的DataFieldMaxValueIncrementer

1）spring为支持 sequence的DB2，Oracle和 PostgreSQL数据库提供了相应的 DataFieldMaxValueIncrementer 实现类；

基于数据库sequence生成递增主键，本文认为这与数据库强行绑定，移植性低，故不做进一步介绍 ；

补充：mysql属于开源数据库，且目前tdsql也是基于mysql的协议，所以本文介绍了 MySQLMaxValueIncrementer；

【2.4】 使用NamedParameterJdbcTemplate解析命名参数符号

1）命名参数符号：为sql中的参数命名，而不是使用没有语义的?占位符；

--  使用 ?占位符表示参数的sql 
String sql = "select id, name from user_tbl where id = ?";
--  使用 命名参数(id)的sql 
String sql = "select id, name from user_tbl where id = :id";

【BusiNamedParameterJdbcTemplate】

public class BusiNamedParameterJdbcTemplate {
	
    // 方式1： 使用 SqlParameterSource 封装参数键值
    public Map<String, Object> queryUserById(int id) {
        String sql = "select id, name from user_tbl where id = :id";
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(DataSourceUtils.getDataSource());
        SqlParameterSource parameterSource = new MapSqlParameterSource("id", id);
        return namedParameterJdbcTemplate.queryForMap(sql, parameterSource);
    }
	
    // 方式2： 使用 Map 封装参数键值 
    public Map<String, Object> queryUserById2(int id) {
        String sql = "select id, name from user_tbl where id = :id";
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(DataSourceUtils.getDataSource());
        Map<String, Object> params = new HashMap<>();
        params.put("id" ,id);
        return namedParameterJdbcTemplate.queryForMap(sql, params);
    }
}

【NamedParameterJdbcTemplate#queryForMap()】

public Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap) throws DataAccessException {
    Map<String, Object> result = (Map)this.queryForObject(sql, (Map)paramMap, (RowMapper)(new ColumnMapRowMapper()));
    Assert.state(result != null, "No result map");
    return result;
}
// RowMapper 参见  【2.1.3】 
public <T> T queryForObject(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper) throws DataAccessException {
        return this.queryForObject(sql, (SqlParameterSource)(new MapSqlParameterSource(paramMap)), (RowMapper)rowMapper);
}

【2.5】DataSource 数据源

1）datasource数据源接口： 基本角色是ConnectionFactory， 连接工厂；所有的数据库连接都通过DataSource接口统一管理；

2）DataSource分类：

• 简单的DataSource（不用于生产，仅用于开发测试）：
  • DriverManagerDataSource； （继承自AbstractDataSource）
  • SingleConnectionDataSource；（继承自DriverManagerDataSource）
• 拥有连接缓冲池的DataSource实现：这类DataSource 通过连接缓冲池对数据库连接进行管理；
  • 使用数据库连接缓冲池，可以在系统启动时就初始化一定数量的数据库连接；
  • 返回给客户端的连接通过close()被关闭，实际上只是还给缓冲池，并没有被真正关闭； （这极大促进了数据库连接资源的复用，提高系统性能）
  • 基于缓冲池的数据源例子：ComboPooledDataSource(C3P0提供)；Druid ；Jakarta Commons DBCP；
• 支持分布式事务的DataSource实现类；
  • 具体实现是： XADataSource

3）自定义 DataSource：继承 AbstractDataSource 或 AbstractRoutingDataSource ；

public class CustomDataSource {
    static class InnerCustomDataSource extends AbstractDataSource {
        @Override
        public Connection getConnection() throws SQLException {
            return null;
        }
        @Override
        public Connection getConnection(String username, String password) throws SQLException {
            return null;
        }
    }

    static class InnerCustomDataSource2 extends AbstractRoutingDataSource {
        @Override
        protected Object determineCurrentLookupKey() {
            return null;
        }
    }
}

【2.5.1】DelegatingDataSource委派数据源

1）DelegatingDataSource可以为DataSource添加新行为；

2）DelegatingDataSource作用：DelegatingDataSource本身持有一个DataSource实例作为目标对象， 当 getConnection()方法被调用时， DelegatingDataSource会把调用委派给DataSource目标对象； 在把调用委派给目标对象前，可以新增自定义逻辑；类似aop，这是委派DataSource的意义所在；

3）DelegatingDataSource的子类列表：

• UserCredentialsDataSourceAdapter：用户凭证数据源适配器；通过用户名和密码验证用户信息；验证通过后再获取连接；
• TransactionAwareDataSourceProxy：事务装配数据源代理；
  • 所有从TransactionAwareDataSourceProxy获取的连接，将自动加入spring事务管理；
  • 使用 DataSourceUtils管理连接，该连接就自动加入spring事务管理；
• LazyConnectionDataSourceProxy：懒加载连接数据源代理；
  • 通过LazyConnectionDataSourceProxy获取的连接是一个代理对象，当该连接被真正使用时，才会从其持有的DataSource返回真实的连接；
• IsolationLevelDataSourceAdapter ：事务隔离级别数据源适配器；
• ShardingKeyDataSourceAdapter：分片key数据源适配器；

【2.6】JdbcDaoSupport

1）JdbcDaoSupport-jdbcdao助手： 封装了DataSource 和 JdbcTemplate；业务dao都可以通过继承JdbcDaoSupport ，简化dao开发，而不用自己单独定义DataSource 和 JdbcTemplate；

public abstract class JdbcDaoSupport extends DaoSupport {
    @Nullable
    private JdbcTemplate jdbcTemplate;

    public JdbcDaoSupport() {
    }

    public final void setDataSource(DataSource dataSource) {
        if (this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {
            this.jdbcTemplate = this.createJdbcTemplate(dataSource);
            this.initTemplateConfig();
        }

    }

    protected JdbcTemplate createJdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
......
}

【3】基于操作对象操作数据

1）spring把查询，更新，调用存储过程等数据访问操作抽象为操作对象，顶层接口为 RdbmsOperation ；

2）RdbmsOperation 是一个接口：提供了子类所需的公共设施，包括sql语句声明， 参数列表处理，JdbcTemplate实例等；

• 查询操作对象：SqlQuery；
• 更新操作对象：SqlUpdate；
• 存储过程操作对象：SqlCall 或者 StoredProcedure；（不推荐使用存储过程）

3）实际上，操作对象实现类底层封装了JdbcTemplate，即底层还是调用JdbcTemplate api，其目的或价值在于简化代码逻辑 ；

【3.1】基于操作对象查询数据

1）查询操作对象：顶层接口为SqlQuery ；

【3.1.1】 MappingSqlQueryWithParameters

1）继承MappingSqlQueryWithParameters，并重写mapRow() 方法 ；

• MappingSqlQueryWithParameters 底层使用RowMapper封装查询结果，RowMapper详情参见 【2.1.3】 ；

【QueryByMappingSqlQueryWithParametersMain】

public class QueryByMappingSqlQueryWithParametersMain {
    public static void main(String[] args) {
        QueryByMappingSqlQueryWithParameters queryByMappingSqlQueryWithParameters =
                new QueryByMappingSqlQueryWithParameters(DataSourceUtils.getDataSource());
        List<UserDto> resutlList = queryByMappingSqlQueryWithParameters.execute(50000);
        System.out.println(resutlList);
    }
}

【QueryByMappingSqlQueryWithParameters】

public class QueryByMappingSqlQueryWithParameters extends MappingSqlQueryWithParameters<UserDto> {

    static String sql = "select id, name, mobile_phone from user_tbl where id >= ?";

    public QueryByMappingSqlQueryWithParameters(DataSource dataSource) {
        super(dataSource, sql);
        super.declareParameter(new SqlParameter(Types.BIGINT)); // 声明sql参数值类型
        super.compile();// 在使用操作对象 RdbmsOperation前，必须调用 compile()方法检查与设置操作对象所必须的各种参数 
    }

    @Override
    protected UserDto mapRow(ResultSet rs, int rowNum, Object[] parameters, Map context) throws SQLException {
        UserDto userDto = new UserDto();
        userDto.setId(rs.getLong(1));
        userDto.setName(rs.getString(2));
        userDto.setMobilePhone(rs.getString(3));
        return userDto;
    }
}

【代码解说】

• 在使用操作对象 RdbmsOperation前，需要调用 compile()方法，检查与设置操作对象所必须的各种参数 ；只有compile方法通过后，当前操作对象才可以使用；
• declareParameter()：为操作对象指明sql语句中的各种参数类型；
• 重写mapRow方法：最后两个参数：
  • Object[] parameters： 查询方法所传入的参数值列表；如parameters[0] 返回的是我们传入的值 50000 ；
  • Map context：查询方法所传入的上下文；如 重载方法 List execute(Object[] parmas, Map Context);

2）QueryByMappingSqlQueryWithParameters.execute() 底层原理：( 底层使用了 JdbcTemplate与PreparedStatement查询数据 )

// SqlQuery
public List<T> execute(int p1) throws DataAccessException {
        return this.execute(p1, (Map)null);
    }
// 
public List<T> execute(@Nullable Object[] params, @Nullable Map<?, ?> context) throws DataAccessException {
        this.validateParameters(params);
        RowMapper<T> rowMapper = this.newRowMapper(params, context); // 新建RowMapper，参见 【2.1.3】 
        return this.getJdbcTemplate().query(this.newPreparedStatementCreator(params), rowMapper);// 底层使用了 JdbcTemplate与PreparedStatement查询数据
    }

【3.1.2】MappingSqlQuery（最常使用）

1）MappingSqlQuery： 映射sql查询操作， 继承MappingSqlQueryWithParameters， 实现了 MappingSqlQueryWithParameters 拥有4个参数的mapRow方法，对外暴露了4个参数与2个参数的mapRow方法；

• 继承 MappingSqlQuery 实现查询，与通过 RowMapper使用JdbcTemplate效果是相同的；

public abstract class MappingSqlQuery<T> extends MappingSqlQueryWithParameters<T> {
    public MappingSqlQuery() {
    }

    public MappingSqlQuery(DataSource ds, String sql) {
        super(ds, sql);
    }

    @Nullable
    protected final T mapRow(ResultSet rs, int rowNum, @Nullable Object[] parameters, @Nullable Map<?, ?> context) throws SQLException {
        return this.mapRow(rs, rowNum);
    }

    @Nullable
    protected abstract T mapRow(ResultSet rs, int rowNum) throws SQLException;
}

2）自定义MappingSqlQuery： 继承 MappingSqlQuery ；

【BusiMappingSqlQueryMain】

public class BusiMappingSqlQueryMain {
    public static void main(String[] args) {
        BusiMappingSqlQuery busiMappingSqlQuery = new BusiMappingSqlQuery(DataSourceUtils.getDataSource());
        List<UserDto> resutlList = busiMappingSqlQuery.execute(50000);
        System.out.println(resutlList);
    }
}

【BusiMappingSqlQuery】

public class BusiMappingSqlQuery extends MappingSqlQuery<UserDto> {

    private static String sql = "select id, name, mobile_phone from user_tbl where id >= ?";

    public BusiMappingSqlQuery(DataSource ds) {
        super(ds, sql);
        super.declareParameter(new SqlParameter(Types.BIGINT)); // 声明sql参数值类型 
        super.compile(); // 在使用操作对象前，必须调用 compile()方法检查与设置操作对象所必须的各种参数  
    }

    @Override
    protected UserDto mapRow(ResultSet rs, int rowNum) throws SQLException {
        UserDto userDto = new UserDto();
        userDto.setId(rs.getLong(1));
        userDto.setName(rs.getString(2));
        userDto.setMobilePhone(rs.getString(3));
        return userDto;
    }
}

【3.1.3】SqlFunction（MappingSqlQuery子类）

1）SqlFunction：返回一行且一列的查询结果的查询操作对象；如 select count(1) from user_tbl

2）SqlFunction： 是 MappingSqlQuery子类，如下。

public class SqlFunction<T> extends MappingSqlQuery<T> {
    private final SingleColumnRowMapper<T> rowMapper = new SingleColumnRowMapper();

    public SqlFunction() {
        this.setRowsExpected(1);
    }

    public SqlFunction(DataSource ds, String sql) {
        this.setRowsExpected(1);
        this.setDataSource(ds);
        this.setSql(sql);
    }
    ...
}

【SqlFunctionMain】

public class SqlFunctionMain {
    public static void main(String[] args) {
        String sql = "select count(1) from user_tbl where id >= ?";
        SqlFunction<Integer> sqlFunction = new SqlFunction<>(DataSourceUtils.getDataSource(), sql, new int[]{Types.BIGINT});
        sqlFunction.compile();  // 使用操作对象前，必须调用compile()方法，检查与设置操作对象sqlUpdate所必须的各种参数
        System.out.println(sqlFunction.run(500004));
    }
}

【3.1.4】UpdatableSqlQuery

1）UpdatableSqlQuery：对应可更新结果集的查询，即对查询后的结果进行更新； 底层使用 RowMapper 处理查询结果集；

public abstract class UpdatableSqlQuery<T> extends SqlQuery<T> {
    public UpdatableSqlQuery() {
        this.setUpdatableResults(true);
    }

    public UpdatableSqlQuery(DataSource ds, String sql) {
        super(ds, sql);
        this.setUpdatableResults(true);
    }

    protected RowMapper<T> newRowMapper(@Nullable Object[] parameters, @Nullable Map<?, ?> context) {
        return new RowMapperImpl(context);
    }

    protected abstract T updateRow(ResultSet rs, int rowNum, @Nullable Map<?, ?> context) throws SQLException;

    protected class RowMapperImpl implements RowMapper<T> {
        @Nullable
        private final Map<?, ?> context;

        public RowMapperImpl(@Nullable Map<?, ?> context) {
            this.context = context;
        }

        public T mapRow(ResultSet rs, int rowNum) throws SQLException {
            T result = UpdatableSqlQuery.this.updateRow(rs, rowNum, this.context);
            rs.updateRow();
            return result;
        }
    }
}

2）自定义可更新查询： 继承 UpdatableSqlQuery ，重写updateRow方法；

【3.2】基于操作对象更新数据

1）spring提供的用于更新的操作对象有2个，包括 SqlUpdate 与 BatchSqlUpdate ；

【3.2.1】 SqlUpdate基本更新操作

1）SqlUpdate 继承自SqlOperation ，其更新操作依赖于 SqlOperation 创建PreparedStatementCreator以便生成PreparedStatement；

【SqlUpdateMain】

public class SqlUpdateMain {
    public static void main(String[] args) {
        String sql = "update user_tbl set mobile_phone = ? where id >= ?";
        SqlUpdate sqlUpdate = new SqlUpdate(DataSourceUtils.getDataSource(), sql);
        sqlUpdate.declareParameter(new SqlParameter(Types.VARCHAR));
        sqlUpdate.declareParameter(new SqlParameter(Types.BIGINT));
        sqlUpdate.compile(); // 使用操作对象前，必须调用compile()方法，检查与设置各种参数

        // 执行sql
        sqlUpdate.update(new Object[]{"123456", 500004});
    }
}

【SqlUpdate】 显然：SqlUpdate封装了 JdbcTemplate 与 PreparedStatement ，并基于此进行更新 ；

public int update(Object... params) throws DataAccessException {
    this.validateParameters(params);
    int rowsAffected = this.getJdbcTemplate().update(this.newPreparedStatementCreator(params)); // 执行具体更新
    this.checkRowsAffected(rowsAffected);
    return rowsAffected; 
}

【3.2.2】 BatchSqlUpdate批量更新操作

1）BatchSqlUpdate：继承自SqlUpdate，底层使用 JdbcTemplate 结合 BatchPreparedStatementSetter 进行批量更新；

2）调用 BatchSqlUpdate.update()方法提交更新数据到缓存队列parameterQueue；若队列大小等于batchSize，则自动执行flush()方法；

• 或手动调用BatchSqlUpdate.flush() 把缓存队列中的数据更新到数据库；

【BatchSqlUpdateMain】

public class BatchSqlUpdateMain {
    public static void main(String[] args) {
        String sql = "update user_tbl set name = ? where id = ?";
        BatchSqlUpdate batchSqlUpdate = new BatchSqlUpdate(DataSourceUtils.getDataSource(), sql);
        batchSqlUpdate.declareParameter(new SqlParameter(Types.VARCHAR));
        batchSqlUpdate.declareParameter(new SqlParameter(Types.BIGINT));
        batchSqlUpdate.compile(); //  使用操作对象前，必须调用compile()方法，检查与设置各种参数 

        // 执行sql
        List<UserDto> userDtoList = Arrays.asList(
                new UserDto(500003, "李四01")
                , new UserDto(500004, "李四02")
                , new UserDto(500005, "李四03"));
        for (UserDto userDto : userDtoList) {
            batchSqlUpdate.update(new Object[]{userDto.getName(), userDto.getId()});
        }
        // BatchSqlUpdate.update()方法： 把数据添加到批量更新的缓存队列中； 当队列中的数据量等于batchSize时，会自动触发批量更新操作；
        // 否则， 数据只是被添加到了缓存队列而已，并没有真正提交更新sql；
        // 调用flush方法会强行刷新缓存队列中的更新操作
        // batchSize可以调整
        batchSqlUpdate.setBatchSize(500);
        batchSqlUpdate.flush();
    }
}

【BatchSqlUpdate】 底层使用 JdbcTemplate的批量更新api；

private final Deque<Object[]> parameterQueue = new ArrayDeque();

public int update(Object... params) throws DataAccessException {
    this.validateParameters(params);
    this.parameterQueue.add((Object[])params.clone()); // 添加到队列，没有执行更新sql 
    if (this.parameterQueue.size() == this.batchSize) {
        if (this.logger.isDebugEnabled()) {
            this.logger.debug("Triggering auto-flush because queue reached batch size of " + this.batchSize);
        }

        this.flush();
    }

    return -1;
}

public int[] flush() {
    if (this.parameterQueue.isEmpty()) {
        return new int[0];
    } else {
        // 真正执行sql 
        int[] rowsAffected = this.getJdbcTemplate().batchUpdate(this.resolveSql(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return BatchSqlUpdate.this.parameterQueue.size();
            }

            public void setValues(PreparedStatement ps, int index) throws SQLException {
                Object[] params = (Object[])BatchSqlUpdate.this.parameterQueue.removeFirst();
                BatchSqlUpdate.this.newPreparedStatementSetter(params).setValues(ps);
            }
        });
        int[] var2 = rowsAffected;
        int var3 = rowsAffected.length;

        for(int var4 = 0; var4 < var3; ++var4) {
            int rowCount = var2[var4];
            this.checkRowsAffected(rowCount);
            if (this.trackRowsAffected) {
                this.rowsAffected.add(rowCount);
            }
        }

        return rowsAffected;
    }
}

【3.3】总结

1） 显然， 操作对象内部封装了JdbcTemplate实例，这简化了操作数据的逻辑，包括查询与更新；

• 基于操作对象查询数据：使用 JdbcTemplate 与 PreparedStatement 查询数据，并使用 RowMapper 处理结果集；
• 基于操作对象更新数据（单个或批量）：使用 JdbcTemplate 与 PreparedStatement 更新数据；