之后对数据库的操作就变得很简单:private JdbcTemplate sjc; @Autowired public void setDataSource(DataSource dataSource) { this.sjc = new JdbcTemplate(dataSource); }
对于只有一条记录的查询可以将结果直接封装给特定对象,这样就大大简化了数据库操作代码。public String getName() { return this.jdbcTemplate.queryForObject("select name from mytable where id = 1", String.class); }
对于多条结果集的,可以直接将其封装到list中。public List<Map<String, Object>> getList() { return this.jdbcTemplate.queryForList("select * from mytable"); }
public void setName(int id, String name) { this.jdbcTemplate.update("update mytable set name = ? where id = ?", name, id); }
另一种处理方式:public int[] batchUpdate(final List<Actor> actors) { int[] updateCounts = jdbcTemplate.batchUpdate("update t_actor set first_name = ?, " + "last_name = ? where id = ?", new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setString(1, actors.get(i).getFirstName()); ps.setString(2, actors.get(i).getLastName()); ps.setLong(3, actors.get(i).getId().longValue()); } public int getBatchSize() { return actors.size(); } }); return updateCounts; }
对于大批量操作,可将其切分成一个一个小段来进行批量操作,下面的就是按每100个进行一次数据库交互来实现:public int[] batchUpdate(final List<Actor> actors) { List<Object[]> batch = new ArrayList<Object[]>(); for (Actor actor : actors) { Object[] values = new Object[] { actor.getFirstName(), actor.getLastName(), actor.getId()}; batch.add(values); } int[] updateCounts = jdbcTemplate.batchUpdate( "update t_actor set first_name = ?, last_name = ? where id = ?", batch); return updateCounts; }
public int[][] batchUpdate(final Collection<Actor> actors) { int[][] updateCounts = jdbcTemplate.batchUpdate( "update t_actor set first_name = ?, last_name = ? where id = ?", actors, 100, new ParameterizedPreparedStatementSetter<Actor>() { public void setValues(PreparedStatement ps, Actor argument) throws SQLException{ ps.setString(1, argument.getFirstName()); ps.setString(2, argument.getLastName()); ps.setLong(3, argument.getId().longValue()); } }); return updateCounts; }