Spring使用JdbcTemplate操作数据库

Spring对数据库的操作在jdbc上面做了深层次的封装，提供了JdbcTemplate或NamedParameterJdbcTemplate模板。

JdbcTemplate是Spring里最基本的JDBC模板，利用JDBC和简单的索引参数查询提供对数据库的简单访问。

NamedParameterJdbcTemplate能够在执行查询时把值绑定到SQL里的命名参数，而不是使用索引参数。

传统JDBC API与Spring JdbcTemplate比较：

//JDBC API
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery("select count(*) from COUNT student")
if(resultSet.next()){
    Integer count = resultSet.getInt("COUNT");
}

//JDBC Template
Integer count = jdbcTemplate.queryForObject("select count(*) from student",Integer.class);

注意：JdbcTemplate采用默认的自动提交。

JdbcTemplate快速入门

主要依赖项：

<!--mysql驱动包-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.35</version>
</dependency>
<!--spring jdbc-->
<dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>5.0.2.RELEASE</version>
</dependency>

辅助依赖：

<!--lombok-->
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <version>1.18.16</version>
  <scope>provided</scope>
</dependency>

<!--fastjson-->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>fastjson</artifactId>
  <version>1.2.76</version>
</dependency>

配置数据源

1、在resources目录下新建application.properties

jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
jdbc.username=root
jdbc.password=root
jdbc.driverClassName=com.mysql.jdbc.Driver

2、读取配置项

@Data
@Configuration
@PropertySource("classpath:application.properties")
public class AppPropertiesConfig {

    @Value("${jdbc.url}")
    private String jdbcUrl;
    @Value("${jdbc.username}")
    private String userName;
    @Value("${jdbc.password}")
    private String password;
    @Value("${jdbc.driverClassName}")
    private String driverClassName;

}

3、创建DataSource

Spring本身提供了一个简单的数据源实现类DriverManagerDataSource，它位于org.springframework.jdbc.datasource包中。这个类实现了javax.sql.DataSource接口，但它并没有提供池化连接的机制，每次调用getConnection()获取新连接时，只是简单地创建一个新的连接。因此，这个数据源类比较适合在单元测试或简单的独立应用中使用，因为它不需要额外的依赖类。 

@Bean("springBuiltDataSource")
public DataSource springBuiltDataSource(AppPropertiesConfig propertiesConfig){
    DriverManagerDataSource ds = new DriverManagerDataSource();
    ds.setDriverClassName(propertiesConfig.getDriverClassName());
    ds.setUrl(propertiesConfig.getJdbcUrl());
    ds.setUsername(propertiesConfig.getUserName());
    ds.setPassword(propertiesConfig.getPassword());
    return ds;
}

创建JdbcTemplate

@Bean("jdbcTemplateOne")
public JdbcTemplate jdbcTemplateOne(DataSource dataSource){
    return new JdbcTemplate(dataSource);
}

使用JdbcTemplate

@Resource(name = "jdbcTemplateOne")
private JdbcTemplate jdbcTemplate;

List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from tb_student");
System.out.println(JSON.toJSONString(list));

JdbcTemplate常用API

常用方法

1、 execute方法主要用于建表相关,平常使用较少。

String sql = "create table user_test (id bigint, name varchar(100))";
jdbcTemplate.execute(sql);

2、update方法支持单条数据的增删改

//直接拼接参数
jdbcTemplate.update("insert into user_test(id, name) values(1, 'harvey')");
//使用占位符
jdbcTemplate.update("insert into user_test(id, name) values (?, ?)", 2, "hello");
//指定参数数组和对应的JDBC类型
jdbcTemplate.update("insert into user_test(id, name) values (?, ?)", new Object[]{3, "world"}, new int[]{
        Types.BIGINT, Types.VARCHAR
});

3、batchUpdate支持批量数据的增删改

//批量几条SQL
jdbcTemplate.batchUpdate("insert into user_test(id, name) values(10, 'harvey')",
        "insert into user_test(id, name) values(11, 'helloWorld')");

//批量指定参数
List<Object[]> paramList = new ArrayList();
Object[] one = new Object[]{15, "harvey"};
paramList.add(one);
Object[] two = new Object[]{16, "world"};
paramList.add(two);
jdbcTemplate.batchUpdate("insert into user_test(id, name) values(?, ?)", paramList);

4、query方法，支持返回实体的映射转换

//映射查询结果，一般是定义实体进行映射
List<Map<String, Object>> list = jdbcTemplate.query("select * from user_test", new RowMapper<Map<String, Object>>() {
    @Override
    public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
        Map<String, Object> rowMap = new HashMap();
        rowMap.put("id", resultSet.getLong("id"));
        rowMap.put("name", resultSet.getString("name"));
        return rowMap;
    }
});
System.out.println("list:"+JSON.toJSONString(list));


List<Map<String, Object>> list2 = jdbcTemplate.query("select * from user_test", new ResultSetExtractor<List<Map<String, Object>>>() {
    @Override
    public List<Map<String, Object>> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
        List<Map<String, Object>> resultList = new ArrayList();
        while (resultSet.next()){
            Map<String, Object> rowMap = new HashMap();
            rowMap.put("id", resultSet.getLong("id"));
            rowMap.put("name", resultSet.getString("name"));
            resultList.add(rowMap);
        }
        return resultList;
    }
});
System.out.println("list2:" + JSON.toJSONString(list2));

Spring jdbc中目前有两个主要的RowMapper实现，使用它们应该能解决大部分的场景了：SingleColumnRowMapper和BeanPropertyRowMapper。

• SingleColumnRowMapper：返回单列数据
• BeanPropertyRowMapper：当查询数据库返回的是多列数据，且需要将这些多列数据映射到某个具体的实体类上

5、queryForMap方法，以Map类型返回查询结果 

//直接指定返回类型
Map<String, Object> userMap = jdbcTemplate.queryForObject("select * from user_test where id=1", HashMap.class);
System.out.println("userMap:" + JSON.toJSONString(userMap));

//通过RowMapper映射成期望的类型
Map<String, Object> userMap2 = jdbcTemplate.queryForObject("select * from user_test where id=1",
        new RowMapper<Map<String, Object>>() {
    @Override
    public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
        Map<String,Object> rowInfo = new HashMap();
        rowInfo.put("id", resultSet.getLong("id"));
        rowInfo.put("name", resultSet.getString("name"));
        return rowInfo;
    }
});
System.out.println("userMap2:" + JSON.toJSONString(userMap2));

//查询返回某一个值：查询表中数据总数
int count = jdbcTemplate.queryForObject("select count(*) fromuser_test", Integer.class);
System.out.println("count:" + count);

6、queryForList方法，以List类型返回查询结果 

//查询列表，默认是HashMap类型来接收
List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from user_test");
System.out.println("mapList:" + JSON.toJSONString(mapList));

//指定查询参数
List<Map<String, Object>> mapList2 = jdbcTemplate.queryForList("select * from user_test where id=?", 1);
System.out.println("mapList2:" + JSON.toJSONString(mapList2));

//查询列表，指定接收查询结果的类型，注意这里只能是查询一列数据
List<Long> idList = jdbcTemplate.queryForList("select id from user_test", Long.class);
System.out.println("idList:" + idList);

//查询列表，指定查询参数和接收类型,注意这里只能是查询一列数据
List<Long> idList2 = jdbcTemplate.queryForList("select id from user_test where name like concat('%', ?, '%')", Long.class, "har");
System.out.println("idList2:" + idList2);

queryForList的坑：不支持自定义bean，这个class只是支持Integer,String这些基本类型。

提供回调类

1、预编译语句及存储过程创建回调：用于根据JdbcTemplate提供的连接创建相应的语句

• PreparedStatementCreator：通过回调获取JdbcTemplate提供的Connection，由用户使用该Conncetion创建相关的PreparedStatement；
• CallableStatementCreator：通过回调获取JdbcTemplate提供的Connection，由用户使用该Conncetion创建相关的CallableStatement；

int count = jdbcTemplate.execute(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        return connection.prepareStatement("select count(*) from user_test");
    }
}, new PreparedStatementCallback<Integer>() {
    public Integer doInPreparedStatement(java.sql.PreparedStatement pst)
            throws SQLException, DataAccessException {
        pst.execute();
        ResultSet rs = pst.getResultSet();
        rs.next();
        return rs.getInt(1);
    }
});
System.out.println(count);

首先使用PreparedStatementCreator创建一个预编译语句，其次由JdbcTemplate通过PreparedStatementCallback回调传回，由用户决定如何执行该PreparedStatement。此处我们使用的是execute方法。以下是我们mysql中常用的插入时返回自增的主键：

String sql = "insert into tb_class(class_code, class_name, is_deleted) values(?,?, 0) ";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
        // 设置返回的主键字段名
        PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, "43102");
        ps.setString(2, "开发四班");
        return ps;
    }
}, keyHolder);
// 获取到插入数据生成的ID，主键是自增的
System.out.println(keyHolder.getKey().longValue());

2、预编译语句设值回调：用于给预编译语句相应参数设值

• PreparedStatementSetter：通过回调获取JdbcTemplate提供的PreparedStatement，由用户来对相应的预编译语句相应参数设值；
• BatchPreparedStatementSetter：；类似于PreparedStatementSetter，但用于批处理，需要指定批处理大小；

String insertSql = "insert into user_test(id, name) values (?,?)";
int count = jdbcTemplate.update(insertSql, new PreparedStatementSetter() {
    public void setValues(PreparedStatement pst) throws SQLException {
        pst.setObject(1, "21");
        pst.setObject(2, "world");
    }
});
System.out.println("count:"+count);

List<Object[]> dataList = new ArrayList();
dataList.add(new Object[]{25, "张桑"});
dataList.add(new Object[]{26, "林冲"});
dataList.add(new Object[]{27, "宋江"});

String insertSql = "insert into user_test(id, name) values (?,?)";
int[] result = jdbcTemplate.batchUpdate(insertSql, new BatchPreparedStatementSetter() {
    @Override
    public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
        //批操作中的每个操作都使用相同的sql语句，而该接口则sql语句中的占位符设置具体的值。
        Object[] obj = dataList.get(i);
        preparedStatement.setObject(1, obj[0]);
        preparedStatement.setObject(2, obj[1]);
    }

    @Override
    public int getBatchSize() {
        //用来返回批次的大小
        return dataList.size();
    }
});
//返回 [1, 1, 1]
System.out.println(Arrays.toString(result));

3、自定义功能回调：提供给用户一个扩展点，用户可以在指定类型的扩展点执行任何数量需要的操作

• ConnectionCallback：通过回调获取JdbcTemplate提供的Connection，用户可在该Connection执行任何数量的操作；
• StatementCallback：通过回调获取JdbcTemplate提供的Statement，用户可以在该Statement执行任何数量的操作；
• PreparedStatementCallback：通过回调获取JdbcTemplate提供的PreparedStatement，用户可以在该PreparedStatement执行任何数量的操作；
• CallableStatementCallback：通过回调获取JdbcTemplate提供的CallableStatement，用户可以在该CallableStatement执行任何数量的操作；

4、结果集处理回调：通过回调处理ResultSet或将ResultSet转换为需要的形式

• RowMapper：用于将结果集每行数据转换为需要的类型，用户需实现方法mapRow(ResultSet rs, int rowNum)来完成将每行数据转换为相应的类型。
• RowCallbackHandler：用于处理ResultSet的每一行结果，用户需实现方法processRow(ResultSet rs)来完成处理，在该回调方法中无需执行rs.next()，该操作由JdbcTemplate来执行，用户只需按行获取数据然后处理即可。
• ResultSetExtractor：用于结果集数据提取，用户需实现方法extractData(ResultSet rs)来处理结果集，用户必须处理整个结果集；

NamedParameterJdbcTemplate常用API

在经典的 JDBC 用法中, SQL 参数是用占位符 ? 表示，并且受到位置的限制。定位参数的问题在于, 一旦参数的顺序发生变化, 就必须改变参数绑定。

在 Spring JDBC 框架中, 绑定 SQL 参数的另一种选择是使用具名参数(named parameter)。

什么是具名参数

SQL 按名称(以冒号开头)而不是按位置进行指定. 具名参数更易于维护, 也提升了可读性。具名参数由框架类在运行时用占位符取代。

具名参数只在 NamedParameterJdbcTemplate 中得到支持。NamedParameterJdbcTemplate可以使用全部jdbcTemplate方法。

创建NamedParameterJdbcTemplate

@Bean("namedJdbcTemplateOne")
public NamedParameterJdbcTemplate namedJdbcTemplateOne(DataSource dataSource){
    return new NamedParameterJdbcTemplate(dataSource);
}

NamedParameterJdbcTemplate示例

Map<String, Object> sqlParams = new HashMap();
sqlParams.put("name", "%har%");
List<Map<String, Object>> list = namedJdbcTemplateOne.queryForList("select * from user_test where name like :name", sqlParams);
System.out.println(JSON.toJSONString(list));

SQL分页查询

使用JdbcTemplate都是使用SQL进行查询的，如果需要分页，则需要对SQL进行修改。下面提供了几种常用数据库的分页SQL拼接的工具类及分页对象：

/**
 * 分页对象
 */
public final class PageBean<T> implements Serializable {
    //当前页码
    private Integer currentPage = 1;
    //每页显示的记录数
    private Integer pageSize = 10;
    //总记录数
    private Integer totalSize = 0;
    //本页的数据列表
    private List<T> recordList;
    //总页数
    private Integer totalPageCount = 0;
    //开始行号
    private Integer startIndex = 0;
    //页码列表的开始索引
    private Integer beginPageIndex;
    //页码列表的结束索引
    private Integer endPageIndex;

    public PageBean(int currentPage, int pageSize, int totalSize, List<T> recordList) {
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        this.totalSize = totalSize;
        this.recordList = recordList;
        //计算beginPageIndex 和 endPageIndex
        //>>总页数不多于10页，则全部显示
        if (totalPageCount <= 10) {
            beginPageIndex = 1;
            endPageIndex = totalPageCount;
        }
        //总页数多于10页，则显示当前页附近的共10个页码
        else {
            //当前页附近的共10个页码（前4个+当前页+后5个）
            beginPageIndex = currentPage - 4;
            endPageIndex = currentPage + 5;

            //当前面的页码不足4个时，则显示前10个页码
            if (beginPageIndex < 1) {
                beginPageIndex = 1;
                endPageIndex = 10;
            }
            //当后面的页码不足5个时，则显示后10个页码
            if (endPageIndex > totalPageCount) {
                endPageIndex = totalPageCount;
                beginPageIndex = totalPageCount - 10 + 1;
            }
        }
    }

    public Integer getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(Integer currentPage) {
        this.currentPage = currentPage;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotalSize() {
        return totalSize;
    }

    public void setTotalSize(Integer totalSize) {
        this.totalSize = totalSize;
    }

    public List<T> getRecordList() {
        return recordList;
    }

    public void setRecordList(List<T> recordList) {
        this.recordList = recordList;
    }

    public Integer getTotalPageCount() {
        //总页数
        totalPageCount = totalSize / pageSize;
        if (totalSize == 0 || totalSize % pageSize != 0) {
            totalPageCount++;
        }
        return totalPageCount;
    }

    public void setTotalPageCount(Integer totalPageCount) {
        this.totalPageCount = totalPageCount;
    }

    public void setStartIndex(Integer startIndex) {
        this.startIndex = startIndex;
    }

    public Integer getStartIndex() {
        //开始行号=(当前页数-1)x每页显示的条数
        return (currentPage - 1) * pageSize;
    }

    public Integer getBeginPageIndex() {
        return beginPageIndex;
    }

    public void setBeginPageIndex(Integer beginPageIndex) {
        this.beginPageIndex = beginPageIndex;
    }

    public Integer getEndPageIndex() {
        return endPageIndex;
    }

    public void setEndPageIndex(Integer endPageIndex) {
        this.endPageIndex = endPageIndex;
    }
}

/**
 * SQL的分页工具类
 */
public final class SqlPageUtil {

    private SqlPageUtil(){}

    /**
     * @MethodName populateMySQLPageQuerySQL
     * @Description mysql的分页拼接
     * @param sqlStr
     * @param startIndex 起始索引
     * @param currentSize 每天显示的记录数
     * @Return java.lang.String 拼接后的分页语句
     */
    public static String populateMySQLPageQuerySQL(String sqlStr, int startIndex, int currentSize){
        StringBuffer pageBuffer = new StringBuffer();
        if (startIndex == 0 && currentSize <= 0) {
            pageBuffer.append(sqlStr);
        } else {
            pageBuffer.append(sqlStr);
            pageBuffer.append(" limit " + startIndex + "," + currentSize);
        }
        return pageBuffer.toString();
    }

    /**
     * @MethodName populateOraclePageQuerySQL
     * @Description oracle的分页拼接
     * @param sqlStr
     * @param startIndex 起始索引
     * @param currentSize 每天显示的记录数
     * @Return java.lang.String 拼接后的分页语句
     */
    public static String populateOraclePageQuerySQL(String sqlStr, int startIndex, int currentSize) {
        StringBuffer pageBuffer = new StringBuffer();
        if (startIndex == 0 && currentSize <= 0) {
            pageBuffer.append(sqlStr);
        } else {
            pageBuffer.append(" SELECT ROW_.*, ROWNUM ROWNUM_ FROM ( ");
            pageBuffer.append(sqlStr);
            pageBuffer.append(" ) ROW_ ");
            if (startIndex == 0) {
                pageBuffer.append(" WHERE ROWNUM <= ");
                pageBuffer.append(startIndex + currentSize);
            } else if (currentSize <= 0) {
                pageBuffer.append(" WHERE ROWNUM > ");
                pageBuffer.append(startIndex);
            } else {
                pageBuffer.insert(0, "SELECT * FROM ( ");
                pageBuffer.append(" WHERE ROWNUM <= ");
                pageBuffer.append(startIndex + currentSize);
                pageBuffer.append(") WHERE ROWNUM_ > ");
                pageBuffer.append(startIndex);
            }
        }
        return pageBuffer.toString();
    }

    /**
     * @MethodName populateDB2PageQuerySQL
     * @Description DB2的分页拼接
     * @param sqlStr
     * @param startIndex 起始索引
     * @param currentSize 每天显示的记录数
     * @Return java.lang.String 拼接后的分页语句
     */
    public static String populateDB2PageQuerySQL(String sqlStr, int startIndex, int currentSize) {
        if (startIndex == 0 ) {
            return currentSize>1?sqlStr + " fetch first " + currentSize + " rows only":
                    " fetch first 1 row only";
        }
        return "select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( "
                + sqlStr + " fetch first " + String.valueOf(startIndex+currentSize)
                + " rows only ) as inner2_ ) as inner1_ where rownumber_ > "
                + startIndex + " order by rownumber_";
    }
}