JdbcTemplate 常用方法小结

注入JdbcTemplate

 @Autowired
private  JdbcTemplate jdbcTemplate;

增

String sql="insert into users(username, usersex) values(?,?)";
this.jdbcTemplate.update(sql, users.getUsername(), users.getUsersex());

删

String sql="delete from users where userid =?";
this.jdbcTemplate.update(sql,id);

改

String sql="update users set username=?, usersex=? where userid=?";
this.jdbcTemplate.update(sql,user.getUsername(),user.getUsersex(),user.getUserid());

查

相比较于增删改（dml) ，查便复杂一些

获得一条记录

 Users user=new Users();
 String sql="select * from users where userid = ?";
 Object[] arr=new Object[]{id};
 this.jdbcTemplate.query(sql, arr, new RowCallbackHandler() {
     @Override
     public void processRow(ResultSet resultSet) throws SQLException {
         user.setUserid(resultSet.getInt("userid"));
         user.setUsername(resultSet.getString("username"));
         user.setUsersex(resultSet.getString("usersex"));
     }
 });

获得多条记录

String sql = "select * from person_info where patient=\"Y\" or patient=\"S\"";
List<PersonInfo> list = jdbcTemplate.query(sql, new RowMapper<PersonInfo>() {
     @Override
     public PersonInfo mapRow(ResultSet resultSet, int i) throws SQLException {
         PersonInfo personInfo = new PersonInfo();
         personInfo.setId(resultSet.getInt("person_id"));
         personInfo.setPatient(resultSet.getString("patient"));
         personInfo.setCure(resultSet.getString("cure"));
         personInfo.setDeath(resultSet.getString("death"));
         personInfo.setInput(resultSet.getString("input"));
         personInfo.setCityId(resultSet.getInt("city"));
         return personInfo;
     }
 });

返回统计值

String sql2 = "select count(*) from statistics_city where statistics_date=? and city_id=?";
Integer count=0;
try {
    count = jdbcTemplate.queryForObject(sql2,new Object[]{today,i},Integer.class);
} catch(Exception e){
    e.printStackTrace();
}