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(?, ?)", 
		new	PreparedStatementSetter() {
			@Override
			public 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() {
		@Override
		public 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() {
			@Override
			public 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() {
		@Override
		public 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

// 查询int
public int queryForInt() {
	return jdbcTemplate.queryForObject("select count(0) from t_user", Integer.class);
}

// 查询String
public 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中将指针移动到第一行之前再循环遍历

// RowMapper
public User selectById(int id) {
	return jdbcTemplate.queryForObject("select * from t_user where id = ?", new Object[] { id },
		new RowMapper<User>() {
			@Override
			public 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;
			}
		});
}
	
// RowCallbackHandler
public User selectById(int id) {
	final User user = new User();

	jdbcTemplate.query("select * from t_user where id = ?", new Object[] { id }, new RowCallbackHandler() {
		@Override
		public 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;
}
	
// ResultSetExtractor
public User selectById(int id) {

	return jdbcTemplate.query("select * from t_user where id = ?", new Object[] { id },
		new ResultSetExtractor<User>() {

			@Override
			public 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()

// RowMapper
public List<User> list() {
	return jdbcTemplate.query("select * from t_user where user_age = ?", new Object[] { 18 },
		new RowMapper<User>() {

			@Override
			public 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;
			}
		});
}

// RowCallbackHandler
public List<User> list() {
	final List<User> list = new ArrayList<>();
		
	jdbcTemplate.query("select * from t_user where user_age = ?", new Object[] { 18 },
		new RowCallbackHandler() {

			@Override
			public 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;
}

// ResultSetExtractor
public List<User> list() {
	
	 return jdbcTemplate.query("select * from t_user where user_age = ?", new Object[] { 18 },
		new ResultSetExtractor<List<User>>() {
			 
			@Override
			public 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>() {
			
			@Override
			public 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() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setObject(1, users.get(i).getUser_name());
				ps.setObject(2, users.get(i).getUser_age());
			}

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

Ps：

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

JdbcTemplate坑比较多，，，