spring的JdbcTemplate、NamedParameterJdbcTemplate和SimpleJdbcTemplate

使用spring自己对数据库操作时，可以使用这三个数据库操作模板。

JdbcTemplate中方法主要传递sql,和数组参数，其方法要求sql占位符和参数数组位置需要对应，参考代码：

public class JdbcTemplateTest {

	static JdbcTemplate jdbc = new JdbcTemplate(JdbcUtils.getDataSource());

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		User user = findUser("zhangsan");
		System.out.println("data:" + getData(1));
	}

	static int addUser(final User user) {
		jdbc.execute(new ConnectionCallback() {
			public Object doInConnection(Connection con) throws SQLException,
					DataAccessException {
				String sql = "insert into user(name,birthday, money) values (?,?,?) ";
				PreparedStatement ps = con.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, user.getName());
				ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
				ps.setFloat(3, user.getMoney());
				ps.executeUpdate();

				ResultSet rs = ps.getGeneratedKeys();
				if (rs.next())
					user.setId(rs.getInt(1));
				return null;
			}
		});
		return 0;
	}

	static Map getData(int id) {
		String sql = "select id as userId, name, money, birthday  from user where id="
				+ id;
		return jdbc.queryForMap(sql);
	}

	static String getUserName(int id) {
		String sql = "select name from user where id=" + id;
		Object name = jdbc.queryForObject(sql, String.class);
		return (String) name;
	}

	static int getUserCount() {
		String sql = "select count(*) from user";
		return jdbc.queryForInt(sql);
	}

	static List findUsers(int id) {
		String sql = "select id, name, money, birthday  from user where id<?";
		Object[] args = new Object[] { id };
		int[] argTypes = new int[] { Types.INTEGER };
		List users = jdbc.query(sql, args, argTypes, new BeanPropertyRowMapper(
				User.class));
		return users;
	}

	static User findUser(String name) {
		String sql = "select id, name, money, birthday  from user where name=?";
		Object[] args = new Object[] { name };
		Object user = jdbc.queryForObject(sql, args, new BeanPropertyRowMapper(
				User.class));
		return (User) user;
	}

	static User findUser1(String name) {
		String sql = "select id, name, money, birthday  from user where name=?";
		Object[] args = new Object[] { name };
		Object user = jdbc.queryForObject(sql, args, new RowMapper() {

			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setMoney(rs.getFloat("money"));
				user.setBirthday(rs.getDate("birthday"));
				return user;
			}
		});
		return (User) user;
	}
}

描述：1、在excute方法中可以传递一个ConnectionCallback回调接口，在接口方法中将获取connection对象，可以自定义进行操作。

2、结果集的封装可以使用spring的RowMapper接口对象，也可以使用rowBeanPropertyRowMapper，这个只需传递一个对象的class即可。

NamedParameterJdbcTemplate是对JdbcTemplate进行了封装，主要多了一层对参数的解析，sql使用特殊组合的占位符，参数主要使用map，这样sql的占位符和参数数据就不需要在顺序上一一进行对应，参照代码：

public class NamedJdbcTemplate {
	static NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(
			JdbcUtils.getDataSource());

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		User user = new User();
		user.setMoney(10);
		user.setId(2);
		System.out.println(findUser1(user));
	}

	static void addUser(User user) {
		String sql = "insert into user(name,birthday, money) values (:name,:birthday,:money) ";
		SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		named.update(sql, ps, keyHolder);
		int id = keyHolder.getKey().intValue();
		user.setId(id);
		
		Map map = keyHolder.getKeys();
	}

	static User findUser(User user) {
		String sql = "select id, name, money, birthday  from user "
				+ "where money > :m and id < :id";
		Map params = new HashMap();
		// params.put("n", user.getName());
		params.put("m", user.getMoney());
		params.put("id", user.getId());
		Object u = named.queryForObject(sql, params, new BeanPropertyRowMapper(
				User.class));
		return (User) u;
	}

	static User findUser1(User user) {
		String sql = "select id, name, money, birthday  from user "
				+ "where money > :money and id < :id";
		SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
		Object u = named.queryForObject(sql, ps, new BeanPropertyRowMapper(
				User.class));
		return (User) u;
	}

}

关键点描述：SqlParameterSourceps=newBeanPropertySqlParameterSource(user);可以使用SqlParameterSource来传递一个对象，来对sql的占位符进行填值，

KeyHolderkeyHolder=newGeneratedKeyHolder();来捕获生成的主键值。

SimpleJdbcTemplate的使用建立在JDK1.5版本之上，里面封装了一个NamedParameterJdbcTemplate，主要添加了支持变长参数。

public class NamedJdbcTemplate {
	static NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(
			JdbcUtils.getDataSource());

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		User user = new User();
		user.setMoney(10);
		user.setId(2);
		System.out.println(findUser1(user));
	}

	static void addUser(User user) {
		String sql = "insert into user(name,birthday, money) values (:name,:birthday,:money) ";
		SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		named.update(sql, ps, keyHolder);
		int id = keyHolder.getKey().intValue();
		user.setId(id);
		
		Map map = keyHolder.getKeys();
	}

	static User findUser(User user) {
		String sql = "select id, name, money, birthday  from user "
				+ "where money > :m and id < :id";
		Map params = new HashMap();
		// params.put("n", user.getName());
		params.put("m", user.getMoney());
		params.put("id", user.getId());
		Object u = named.queryForObject(sql, params, new BeanPropertyRowMapper(
				User.class));
		return (User) u;
	}

	static User findUser1(User user) {
		String sql = "select id, name, money, birthday  from user "
				+ "where money > :money and id < :id";
		SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
		Object u = named.queryForObject(sql, ps, new BeanPropertyRowMapper(
				User.class));
		return (User) u;
	}

}