spring实现JDBC的模版
Spring的JDBC模板通过反射机制实现JDBC封装将实现数据库字段与对象数据传递
spring中的JDBC模板：
org.springframework.jdbc.core.JdbcTemplate
org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate
org.springframework.jdbc.core.simple.SimpleJdbcTemplate
1、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即可。
2、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; } }
关键点描述：
SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
可以使用SqlParameterSource来传递一个对象，来对sql的占位符进行填值，
KeyHolder keyHolder = new GeneratedKeyHolder();来捕获生成的主键值。
3、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; } }
