使用spring自己对数据库操作时,可以使用这三个数据库操作模板。
JdbcTemplate中方法主要传递sql,和数组参数,其方法要求sql占位符和参数数组位置需要对应,参考代码:
描述:1、在excute方法中可以传递一个ConnectionCallback回调接口,在接口方法中将获取connection对象,可以自定义进行操作。
2、结果集的封装可以使用spring的RowMapper接口对象,也可以使用rowBeanPropertyRowMapper,这个只需传递一个对象的class即可。
NamedParameterJdbcTemplate是对JdbcTemplate进行了封装,主要多了一层对参数的解析,sql使用特殊组合的占位符,参数主要使用map,这样sql的占位符和参数数据就不需要在顺序上一一进行对应,参照代码:
关键点描述:SqlParameterSource ps = new BeanPropertySqlParameterSource(user);可以使用SqlParameterSource来传递一个对象,来对sql的占位符进行填值,
KeyHolder keyHolder = new GeneratedKeyHolder();来捕获生成的主键值。
SimpleJdbcTemplate的使用建立在JDK1.5版本之上,里面封装了一个NamedParameterJdbcTemplate,主要添加了支持变长参数。
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;
- }
- }
关键点描述:SqlParameterSource ps = new BeanPropertySqlParameterSource(user);可以使用SqlParameterSource来传递一个对象,来对sql的占位符进行填值,
KeyHolder keyHolder = new GeneratedKeyHolder();来捕获生成的主键值。
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;
- }
- }