org.springframework.jdbc.core.JdbcTemplate 常用操作

1、单条数据结果集的处理(RowCallbackHandler) public Agent getAgent(final int id) { String sql = "select * from t_agent where id = ?"; final Agent agent = new Agent(); jdbcTemplate.query(sql, new Object[] { id }, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { agent.setAge(rs.getInt("age")); agent.setName(rs.getString("name")); agent.setSex(rs.getString("sex")); } }); return agent; } 2、多条数据结果集的处理(RowCallbackHandler) public List<Agent> getAgent(final int fromId , final int toId) { String sql = "select * from t_agent where id between ? and ?"; final List<Agent> agents = new ArrayList<Agent>(); jdbcTemplate.query(sql, new Object[] { fromId,toId }, new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { Agent agent = new Agent(); agent.setAge(rs.getInt("age")); agent.setName(rs.getString("name")); agent.setSex(rs.getString("sex")); agents.add(agent); } }); return agents; } 3、使用RowMapper映射多行数据 public List<Agent> getAgent(final int fromId, final int toId){ final String sql = "select userName,sex,age from t_agent where id between ? and ?"; return jdbcTemplate.query(sql, new Object[]{fromId , toId}, new RowMapper<Agent>() { public Agent mapRow(ResultSet rs, int rowNum) throws SQLException { Agent agent = new Agent(); agent.setAge(rs.getInt("age")); agent.setName(rs.getString("userName")); agent.setSex(rs.getString("sex")); return agent; } }); } 4、批量插入操作(BatchPreparedStatementSetter) public void batchUpdateInsert(final List<Agent> agents){ final String sql = "insert into t_user (userName,sex,age) values(?,?,?)"; jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public void setValues(PreparedStatement ps, int index) throws SQLException { Agent agent = agents.get(index); ps.setString(1, agent.getName()); ps.setString(2, agent.getSex()); ps.setInt(3, agent.getAge()); } public int getBatchSize() { return agents.size(); } }); } 5、查询单值数据 public int queryForInt(){ String sql = "select count(*) from t_agent"; return jdbcTemplate.queryForInt(sql); } 注、在编写SQL语句时，特意用大写方式编写SQL的关键字， 而用小写的形式编写表名，字段等非语义的元素是比较好的编码习惯， Eclipse 转大写快捷键Ctrl + Shift + X， 转小写快捷键 Ctrl + Shift + Y。 6、调用存储函数 public int getViewPointNum(final int spaceId){ String sql = "{call P_GET_VIEW_POINT_NUM(?,?)}";//调用存储过程的的SQL语句 Integer num = (Integer) jdbcTemplate.execute(sql, new CallableStatementCallback() { public Integer doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { cs.setInt(1, spaceId);//绑定参数 cs.registerOutParameter(2, Types.INTEGER);//注册输出参数 cs.execute(); return cs.getInt(2);//获取输出参数的值 } }); return num; } 7、调用函数 //3.2.18 增加代理商坐席分组 public int storedProc_addAgentGroup(final AgentUserInfo agent) { Integer result = (Integer) jdbcTemplate.execute(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) throws SQLException { String storedProc = "{?=call HD_SF_ADDGROUP(?,?,?,?,?)}"; CallableStatement call = con.prepareCall(storedProc); call.registerOutParameter(1, Types.INTEGER); 注册输出参数的类型 call.setString(2, agent.getAgentname());// 设置输入参数的值 call.setString(3, agent.getUserphone()); call.setInt(4, agent.getGroupid()); call.setInt(5, agent.getState()); call.setString(6, agent.getPhonenumber()); return call; } }, new CallableStatementCallback() { public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { cs.execute(); System.out.println(cs.getInt(1)); return cs.getInt(1); } }); return result; }