org.springframework.jdbc.core.JdbcTemplate用法

1、execute()方法执行SQL语句 

jdbcTemplate.execute("CREATE TABLE USER (user_id integer, name varchar(100))");

jdbcTemplate.execute("CREATE TABLE USER (user_id integer, name varchar(100))");

2、update()方法执行UPDATE或INSERT语句 

jdbcTemplate.update("UPDATE USER SET name = ? WHERE user_id = ?", 

new Object[] {name, id});

jdbcTemplate.update("INSERT INTO USER VALUES(?, ?, ?, ?)", 

new Object[] {user.getId(), user.getName(), user.getSex(), user.getAge()});

或

jdbcTemplate.update("INSERT INTO USER VALUES(?, ?, ?, ?)",

new PreparedStatementSetter() {

public void setValues(PreparedStatement ps) throws SQLException {

ps.setString(1, user.getId());

ps.setString(2,  user.getName());

ps.setString(3, user.getSex() );

ps.setInt(4, user.getAge());

}

});

jdbcTemplate.update("INSERT INTO USER VALUES(?, ?, ?, ?)", new Object[] {user.getId(), user.getName(), user.getSex(), user.getAge()});

3、queryForXXX()方法执行查询（注意返回值）

--内部执行的是queryForObject(sql, rowMapper)；

//仅有SQL情况

int count = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM USER");

List<Map> rows = jdbcTemplate.queryForList("SELECT * FROM USER");

for(Map userMap:rows){

System.out.print(userMap.get("user_id") + "\t");

System.out.print(userMap.get("name") + "\t");

System.out.print(userMap.get("sex") + "\t");

System.out.println(userMap.get("age") + "\t");

}

//带参数的SQL情况，这里name字段类型是字符串

String name = (String) jdbcTemplate.queryForObject("SELECT name FROM USER “

+" WHERE user_id = ?",

new Object[] {id}, java.lang.String.class);

//带参数的SQL情况，这里* 类型是POJO类类型

Person p= (Person) jdbcTemplate.queryForObject("select * from person where id = 8",

new RowMapper (){

public Object mapRow(ResultSet rs, int value) throws SQLException { 

Person person = new Person (); 

person.setId(new Integer(rs.getInt("id"))); 

person.setUsername(rs.getString("username")); 

person.setAge(rs.getString("age")); 

return user; 

} 

});

4、query()方法执行查询

User user = new User();

jdbcTemplate.query("SELECT * FROM USER WHERE user_id = ?",new Object[] {id},

new RowCallbackHandler() {

public void processRow(ResultSet rs) throws SQLException {

user.setId(rs.getString("user_id"));

user.setName(rs.getString("name"));

user.setSex(rs.getString("sex").charAt(0));

user.setAge(rs.getInt("age"));

}

});

final User user = new User();
jdbcTemplate.query("SELECT * FROM USER WHERE user_id = ?",
                    new Object[] {id},
                    new RowCallbackHandler() {
                        public void processRow(ResultSet rs) throws SQLException {
                            user.setId(rs.getString("user_id"));
                            user.setName(rs.getString("name"));
                            user.setSex(rs.getString("sex").charAt(0));
                            user.setAge(rs.getInt("age"));
                        }
                    });

List<Map> lst = new ArrayList<Map>();
lst = jdbcTemplate.query("SELECT * FROM USER WHERE user_id = ?",new Object[] {id},

new RowMapper(){ 

      public Map mapRow(ResultSet rs, int arg1) throws SQLException {

Map m = new HashMap(); 

m.put("user_id", rs.getObject("user_id"));

m.put("name", rs.getObject("name")); 

m.put("sex", rs.getObject("sex"));

m.put("age", rs.getObject("age"));

  return m; 

    }

      });