Spring JdbcTemplate使用

Spring配置JdbcTemplate参考：http://blog.csdn.net/mytt_10566/article/details/78214844

JdbcTemplate常用方法使用：

update()适用于增删改操作，对于参数使用'?',此时设置参数需要对应位置

0.User实体

public class User {private Integer id;private String user_name;private Integer user_age;set、get略}

1.保存

// 设置参数public void save(User user) {jdbcTemplate.update("insert into t_user(user_name, user_age) values(?, ?)", new Object[]{user.getUser_name(), user.getUser_age()});}// 设置参数类型public void save(User user) {jdbcTemplate.update("insert into t_user(user_name, user_age) values(?, ?)", new Object[]{user.getUser_name(), user.getUser_age()},new int[]{Types.VARCHAR, Types.INTEGER});}// 防注入，通过PreparedStatementSetter设置参数public void save(final User user) {jdbcTemplate.update("insert into t_user(user_name, user_age) values(?, ?)", newPreparedStatementSetter() {@Overridepublic void setValues(PreparedStatement ps) throws SQLException {ps.setObject(1, user.getUser_name());ps.setObject(2, user.getUser_age());}});}// 获取主键public List<Map<String, Object>> save4(final User user) {KeyHolder keyHolder = new GeneratedKeyHolder();jdbcTemplate.update(new PreparedStatementCreator() {@Overridepublic PreparedStatement createPreparedStatement(Connection con) throws SQLException {PreparedStatement ps = con.prepareStatement("insert into t_user(user_name, user_age) values(?, ?)",new String[] { "id" });// 指定返回主键ps.setObject(1, user.getUser_name());ps.setObject(2, user.getUser_age());return ps;}}, keyHolder);return keyHolder.getKeyList();}

2.更新

// 设置参数public void update(User user) {jdbcTemplate.update("update t_user set user_name = ?, user_age = ? where id = ?",new Object[]{user.getUser_name(), user.getUser_age(), user.getId()});}// 设置参数类型public void update(User user) {jdbcTemplate.update("update t_user set user_name = ?, user_age = ? where id = ?",new Object[]{user.getUser_name(), user.getUser_age(), user.getId()},new int[]{Types.VARCHAR, Types.INTEGER, Types.INTEGER});}// 通过PreparedStatementSetter设置参数public void update(final User user) {jdbcTemplate.update("update t_user set user_name = ?, user_age = ? where id = ?",new PreparedStatementSetter() {@Overridepublic void setValues(PreparedStatement ps) throws SQLException {ps.setObject(1, user.getUser_name());ps.setObject(2, user.getUser_age());ps.setObject(3, user.getId());}});}

3.删除

// 设置参数public void delete(User user) {jdbcTemplate.update("delete from t_user where id = ?", new Object[] { user.getId() });}// 设置参数类型public void delete2(User user) {jdbcTemplate.update("delete from t_user where id = ?", new Object[] { user.getId() },new int[] { Types.INTEGER });}// PreparedStatementSetter设置参数public void delete3(final User user) {jdbcTemplate.update("delete from t_user where id = ?", new PreparedStatementSetter() {@Overridepublic void setValues(PreparedStatement ps) throws SQLException {ps.setObject(1, user.getId());}});}

4.查询

(1).指定类型

源码：public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType) throws DataAccessException {...}

requiredType只支持基本数据类型Integer.class、String.class等，不支持int.class、long.class以及实体User.class

// 查询intpublic int queryForInt() {return jdbcTemplate.queryForObject("select count(0) from t_user", Integer.class);}// 查询Stringpublic String queryForString() {return jdbcTemplate.queryForObject("select user_name from t_user where id = ?", new Object[]{ 1 }, String.class);}

(2).根据id查询

queryForObject()

// BeanPropertyRowMapper,列名和User实体属性需要对应public User selectById(int id) {return jdbcTemplate.queryForObject("select * from t_user where id = ?", new Object[]{ id }, new BeanPropertyRowMapper<User>(User.class));  }

query()

注意RowCallbackHandler、ResultSetExtractor中ResultSet指针所在的位置。RowCallbackHandler指针默认在第一行，ResultSetExtractor指针默认在第一行之前。

列表查询时，RowCallbackHandler中将指针移动到第一行之前再循环遍历

// RowMapperpublic User selectById(int id) {return jdbcTemplate.queryForObject("select * from t_user where id = ?", new Object[] { id },new RowMapper<User>() {@Overridepublic User mapRow(ResultSet rs, int rowNum) throws SQLException {User user = new User();user.setId(rs.getInt("id"));user.setUser_name(rs.getString("user_name"));user.setUser_age(rs.getInt("user_age"));return user;}});}// RowCallbackHandlerpublic User selectById(int id) {final User user = new User();jdbcTemplate.query("select * from t_user where id = ?", new Object[] { id }, new RowCallbackHandler() {@Overridepublic void processRow(ResultSet rs) throws SQLException {user.setId(rs.getInt("id"));user.setUser_name(rs.getString("user_name"));user.setUser_age(rs.getInt("user_age"));}});return user;}// ResultSetExtractorpublic User selectById(int id) {return jdbcTemplate.query("select * from t_user where id = ?", new Object[] { id },new ResultSetExtractor<User>() {@Overridepublic User extractData(ResultSet rs) throws SQLException, DataAccessException {User user = null;if (rs.next()) {user = new User();user.setId(rs.getInt("id"));user.setUser_name(rs.getString("user_name"));user.setUser_age(rs.getInt("user_age"));}return user;}});}

(3).列表

query()

// RowMapperpublic List<User> list() {return jdbcTemplate.query("select * from t_user where user_age = ?", new Object[] { 18 },new RowMapper<User>() {@Overridepublic User mapRow(ResultSet rs, int rowNum) throws SQLException {User user = new User();user.setId(rs.getInt("id"));user.setUser_name(rs.getString("user_name"));user.setUser_age(rs.getInt("user_age"));return user;}});}// RowCallbackHandlerpublic List<User> list() {final List<User> list = new ArrayList<>();jdbcTemplate.query("select * from t_user where user_age = ?", new Object[] { 18 },new RowCallbackHandler() {@Overridepublic void processRow(ResultSet rs) throws SQLException {// 指针移动到第一行之前rs.beforeFirst();while (rs.next()) {User user = new User();user.setId(rs.getInt("id"));user.setUser_name(rs.getString("user_name"));user.setUser_age(rs.getInt("user_age"));list.add(user);}}});return list;}// ResultSetExtractorpublic List<User> list() { return jdbcTemplate.query("select * from t_user where user_age = ?", new Object[] { 18 },new ResultSetExtractor<List<User>>() { @Overridepublic List<User> extractData(ResultSet rs) throws SQLException, DataAccessException {List<User> list = new ArrayList<>();while (rs.next()) {User user = new User();user.setId(rs.getInt("id"));user.setUser_name(rs.getString("user_name"));user.setUser_age(rs.getInt("user_age"));list.add(user);}return list;}});}

(4).模糊查询

// 错误写法：select * from t_user where user_name like '%?%'、select * from t_user where user_name like %?%// 正确写法：select * from t_user where user_name like ?，参数："%" + param +"%"public List<User> list() {return jdbcTemplate.query("select * from t_user where user_name like ?", new Object[] { "%小%" },new RowMapper<User>() {@Overridepublic User mapRow(ResultSet rs, int rowNum) throws SQLException {User user = new User();user.setId(rs.getInt("id"));user.setUser_name(rs.getString("user_name"));user.setUser_age(rs.getInt("user_age"));return user;}});}

5.批量操作

// 批量操作，操作增删改public int[] batchUpdate(final List<User> users) {return jdbcTemplate.batchUpdate("insert into t_user(user_name, user_age) valuese(?, ?))", new BatchPreparedStatementSetter() {@Overridepublic void setValues(PreparedStatement ps, int i) throws SQLException {ps.setObject(1, users.get(i).getUser_name());ps.setObject(2, users.get(i).getUser_age());}@Overridepublic int getBatchSize() {return users.size();}});}

Ps：

参考博客：http://1358440610-qq-com.iteye.com/blog/1826816（API很多方法没测试，不能用）

JdbcTemplate坑比较多，，，