Spring-JDBC 操作SQL数据库

Spring-JDBC 操作SQL数据库

1.关于org.springframework.jdbc

Java操作关系数据库，例如MySQL，可以使用数据库对应的JDBC驱动程序(如：mysql-connector-java)提供的的方法。但由于JDBC驱动提供的API使用起来每次都要编写连接、操作、关闭数据库和异常处理的模板（Template）代码，代码显得非常冗长重复，不利于集中精力处理实际问题。

org.springframework.jdbc包中提供了经典类JdbcTemplate来简化数据库操作，它实现了JdbcOperations接口。其实现是基于callback（回调）来实现的。并且能把JDBC的SQLException转换为更为容易理解的springframework包org.springframework.dao中定义的异常类型。

NamedParameterJdbcTemplate包装了JdbcTemplate，提供JdbcTemplate的所有功能，由于增加了命名参数，便于编写和修改sql，使用更为方便。一般都是在xml中定义template的bean然后直接在dao中注入来使用。

在spring的application-context.xml配置文件中配置相关beans:

<context:property-placeholder location="jdbc.properties"/><bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">        <property name="driverClassName" value="${jdbc.driverClassName}"/>        <property name="url" value="${jdbc.url}"/>        <property name="username" value="${jdbc.username}"/>        <property name="password" value="${jdbc.password}"/></bean><bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">          <property name="dataSource" ref="dataSource" />  </bean>

使用注解注入到DAO的实现类中：

@Resourceprivate NamedParameterJdbcTemplate namedJdbcTemplate;

或者使用代码直接创建相关beans:

MysqlDataSource dataSource = new MysqlDataSource();dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8");dataSource.setPassword("123");dataSource.setUser("mysql");NamedParameterJdbcTemplate  namedJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

2. NamedParameterJdbcTemplate的接口分析

现在看看template提供的方法：

• excute
  
  1. <T> T execute(String sql, SqlParameterSource paramSource, PreparedStatementCallback<T> action)
  2. <T> T execute(String sql, Map<String, ?> paramMap, PreparedStatementCallback<T> action)
  3. <T> T execute(String sql, PreparedStatementCallback<T> action)

• query

  1. <T> T query(String sql, SqlParameterSource paramSource, ResultSetExtractor<T> rse)
  2. <T> T query(String sql, Map<String, ?> paramMap, ResultSetExtractor<T> rse)
  3. <T> T query(String sql, ResultSetExtractor<T> rse)
  4. void query(String sql, SqlParameterSource paramSource, RowCallbackHandler rch)
  5. void query(String sql, Map<String, ?> paramMap, RowCallbackHandler rch)
  6. void query(String sql, RowCallbackHandler rch)
  7. <T> List<T> query(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
  8. <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper
  9. <T> List<T> query(String sql, RowMapper<T> rowMapper)
  10. <T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)
  11. <T> T queryForObject(String sql, Map<String, ?> paramMap, RowMapper<T>rowMapper)
  12. <T> T queryForObject(String sql, SqlParameterSource paramSource, Class<T> requiredType)
  13. <T> T queryForObject(String sql, Map<String, ?> paramMap, Class<T> requiredType)
  14. Map<String, Object> queryForMap(String sql, SqlParameterSource paramSource)
  15. Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap)
  16. <T> List<T> queryForList(String sql, SqlParameterSource paramSource, Class<T> elementType)
  17. <T> List<T> queryForList(String sql, Map<String, ?> paramMap, Class<T> elementType)
  18. List<Map<String, Object>> queryForList(String sql, SqlParameterSource paramSource)
  19. List<Map<String, Object>> queryForList(String sql, Map<String, ?> paramMap)
  20. SqlRowSet queryForRowSet(String sql, SqlParameterSource paramSource)
  21. SqlRowSet queryForRowSet(String sql, Map<String, ?> paramMap)

• update

  1. int update(String sql, SqlParameterSource paramSource)
  2. int update(String sql, Map<String, ?> paramMap)
  3. int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder)
  4. int update(
String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, String[] keyColumnNames)
  5. int[] batchUpdate(String sql, Map<String, ?>[] batchValues)
  6. int[] batchUpdate(String sql, SqlParameterSource[] batchArgs)

查看接口参数很容易发现，所有方法的参数都包含sql，大部分包含param、callback:

参数 类型和名称 说明 sql String sql SQL语句 param SqlParameterSource paramSource、Map<String, ?> paramMap SQL语句中的参数 callback PreparedStatementCallback<T> action,ResultSetExtractor<T> rse,RowCallbackHandler rch,RowMapper<T> rowMapper, 用于对一行数据进行映射 elementType Class<T> requiredType 返回单列数据时，指定数据类型 generatedKeyHolder KeyHolder generatedKeyHolder 返回生成的key

其中，SqlParameterSource 的实现类有：

• MapSqlParameterSource：通过Map构造。
• BeanPropertySqlParameterSource：通过Bean的class构造。

RowMapper的实现类：

• BeanPropertyRowMapper：直接从Bean的class生成mapper。
• ColumnMapRowMapper：直接把ResultSet映射为Map.

最后看一下返回值：

• void: 没有返回值。
• T: query时返回一行数据，excute时T可以是集合类型。
• List<?>, SqlRowSet: 返回多行数据。
• int, int[]: 返回执行一条SQL受影响的行数。

3. 使用示例

使用MySQL,先创建表：

CREATE TABLE `user` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `name` varchar(20) NOT NULL DEFAULT '',  `age` int(10) unsigned DEFAULT NULL,  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `area_id` bigint(20) DEFAULT NULL,  `mobile` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建对应的PO(使用lombok简化setter,getter)：

@Datapublic class User {    private Long id;    private String name;    private Integer age;    private Date createTime;    private Long areaId;    private String mobile;}

3.1 excute使用示例

excute-1,excute-2,excute-3: 注意返回值T可以是集合哦。

String sql = "select * from user where id = :id";Map<String, Object> params = Maps.newHashMap();params.put("id", 1L);User user1 = namedJdbcTemplate.execute(sql, new MapSqlParameterSource(params),    new PreparedStatementCallback<User>() {    public User doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {        ResultSet rs = ps.executeQuery();        rs.next();        return buildUser(rs);    }});User user2 = namedJdbcTemplate.execute(sql, params, new PreparedStatementCallback<User>() {    public User doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {        ResultSet rs = ps.executeQuery();        rs.next();        return buildUser(rs);    }});List<User> users = namedJdbcTemplate.execute("select * from user", new PreparedStatementCallback<List<User>>() {    @Override    public List<User> doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {        ResultSet rs = ps.executeQuery();        List<User> users = Lists.newArrayList();        while (rs.next()) {            users.add(buildUser(rs));        }        return users;    }});

如果excute不需要任何参数，也不返回值，怎么办呢？ 可以返回namedJdbcTemplate内部的jdbcTemplate，它有相应的方法：

  String sql = "create table mytable (id integer, name varchar(100))";  namedJdbcTemplate.getJdbcOperations().execute(sql);

3.2 query使用示例

Sql语句和参数：

String sql = "select * from user";

query-1~3：用于返回整个结果集，在excute上简单封装。1,2和3只有参数的区别，只举例3：

List<User> list = namedJdbcTemplate.query(sql, new ResultSetExtractor<List<User>>() {    @Override    public List<User> extractData(ResultSet rs) throws SQLException, DataAccessException {        List<User> users = Lists.newArrayList();        while (rs.next()) {            users.add(buildUser(rs));        }        return users;    }});

query-4~6类似,都是用RowCallbackHandler，只用处理一行即可。举例6:

 List<User> list =     namedParameterJdbcTemplate.query(sql, new RowCallbackHandler() {        @Override        public void processRow(ResultSet rs) throws SQLException {            users.add(buildUser(rs));        }    });

query-7~9类似,都是用RowMapper，只用处理一行即可,注意：可以获取到rowNum。举例9:

 List<User> list =      namedJdbcTemplate.query(sql, new RowMapper<User>() {        @Override        public User mapRow(ResultSet rs, int rowNum) throws SQLException {            return buildUser(rs);        }    });

query-10~15，作用一样，都是返回一行数据，只是参数和返回值类型不一样而已，注意11，12的requiredType参数，
当使用11，12时，sql只能返回简单的一列数据,例如(select count(*) from user）,否则报错,查看源码其内部是调用query-10实现，并且使用的是SingleColumnRowMapper,只支持一列数据。
query-10:使用BeanPropertySqlParameterSource, BeanPropertyRowMapper可极大简化编码。

两者都是用了反射实现映射。BeanPropertyRowMapper支持直接映射或者驼峰(camel),即: sql语句中的name直接映射到User类中的name, area_id映射到areaId。
BeanPropertySqlParameterSource则要求User类中的参数和sql中的冒号后的参数一致：areaId 对应 :areaId。

User user = new User();user.setId(1L);List<User> listUser =namedParameterJdbcTemplate.query("select * from user where id = :id",         new BeanPropertySqlParameterSource(user4), new BeanPropertyRowMapper<User>(User.class)    );

query-11: 只能返回基本数据。

try {    List<User> users10 =        namedParameterJdbcTemplate.queryForList(sql, new BeanPropertySqlParameterSource(user4), User.class);} catch (org.springframework.jdbc.IncorrectResultSetColumnCountException e) {    e.printStackTrace();    // 注意此处会发生异常,只允许返回一列数据，所以只能用来返回基本数据，比如Integer,String,Long等的List，见SingleColumnRowMapper}// 只能用来返回基本数据Integer count2 =    namedParameterJdbcTemplate.queryForObject("select count(1) from user where id = :id", paramMap,        Integer.class);List<Long> ids =    namedParameterJdbcTemplate.queryForList(sql, new BeanPropertySqlParameterSource(user4), Long.class);// 由此可见，queryForList只能用来返回基本数据哦

3.2 update使用示例

update-1~2比较简单，传入相应参数和sql即可返回影响的数据行数。略过。用于UPDATE,INSERT语句。
INSERT时如果设定了自动生成主键ID,update-3~4可以返回ID值。update-3用于一个自动生成ID，update-4可以用于多个，需要指定key的名称。

update-3:

String sql = "insert into user (name,age,area_id,mobile) values (:name,:age,:areaId,:mobile)";User user = new User();user.setAge(11);user.setAreaId(110L);user.setMobile("15997666334");user.setName("cookies");GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();// 获取自动生成的idint count =    namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(user), generatedKeyHolder);logger.debug("count = {}, key = {}", count, generatedKeyHolder.getKey());

update-5~6: batch update，批量操作，两个方法类似:

String sql = "insert into user (name,age,area_id,mobile) values (:name,:age,:areaId,:mobile)";@SuppressWarnings("rawtypes")Map[] batchValues = new HashMap[10];for (int i = 0; i < batchValues.length; i++) {    Map v = new HashMap();    batchValues[i] = v;    batchValues[i].put("name", "哈哈" + i);    batchValues[i].put("age", 11 + i);    batchValues[i].put("areaId", 123 + i);    batchValues[i].put("mobile", "1867297999" + i);}// update-4:int[] c1 = namedParameterJdbcTemplate.batchUpdate(sql, batchValues);logger.debug("counts = {}", counts);// update-5:SqlParameterSource[] batchArgs = new SqlParameterSource[10];for (int i = 0; i < batchArgs; i++) {    User user = new User();    user.setAge(11 + i);    user.setAreaId(110L + i);    user.setMobile("1599766633" + i);    user.setName("cookies" + i);    batchArgs[i] = new BeanPropertySqlParameterSource(user);}int[] c2 = namedParameterJdbcTemplate.batchUpdate(sql, batchArgs);logger.debug("counts = {}", c2);

---

参考

1.Data access with JDBC