1.您可以使用JdbcTemplate的execute()方法執行SQL DDL语句，例如：jdbcTemplate.execute("CREATE TABLE USER (user_id integer, name varchar(100))");使用JdbcTemplate进行查询时，可以使用queryForXXX()等方法，例如使用queryForInt()方法传回user表格中的数据数目： jdbcTemplate.quertForInt("select count(*) from user"); 也可以使用queryForObject()传回一个查询后的对象，例如传回一个String对象：String name=(String)jdbcTemplate.queryForObject( "selcet name from user where id=?", new Object[]{id}, java.lang.String.class);)单独查询某个数据并赋值给特定对象时： public BaseObj getBaseObj(final int ID) { String sql = "select * from " + DB_TABLE_NAME + " where NewsId=" + ID; BaseObj obj =(BaseObj) getJdbcTemplate().query(sql,new ResultSetExtractor(){ public Object extractData(ResultSet rs) throws SQLException,DataAccessException { if (rs.next()) { NewsObj news = new NewsObj(); news.setID(rs.getInt("NewsID")); news.setTitle(rs.getString("NewsTitle")); news.setBigClass(rs.getInt("BigClassId")); news.setNewsContent(rs.getString("NewsContent")); news.setNewsKey(rs.getString("NewsKey")); news.setNewsAuthor(rs.getString("NewsAuthor")); news.setImg(rs.getBoolean("isImg")); news.setNewsFrom(rs.getString("NewsFrom")); return news; } return null; } }); return obj; } 上面两个例子都是传回单独一笔数据，如果要传回多笔数据，则可以使用queryForList()方法，例如：List rows=jdbcTemplate().queryForList("select * from user where id="+id.intValue()); 传回的list中包括的是map对象，每个map对象代表查询结果中的一笔数据，每笔数据包括多个字段，要取得字段中的值，就要使用字段名作为key,例如： Iterator it=rows.iterator(); while(it.hasNext()){ Map result=(Map)it.next(); System.out.println(userMap.get("id")); System.out.println(userMap.get("name")); System.out.println(userMap.get("age")); } 在查询的同时，你可以在返回结果之前先进行一些处理，这就要实现RowCallbackHandler接口public User find(Integer id){ final User user=new User(); jdbcTemplate.query("select * from user where id=?",new Object[]{id}, new RowCallBackHandler(){ public void proccessRow(ResultSet rs){ user.setId(new Integer(rs.getInt("id"))); uset.setName(rs.getString("name")); } }); return user; }如果一次要返回多个查询结果对象，则可以实现RowMapper接口public class UserRowMapper implements RowMapper{ public Object MapRow(ResultSet rsmint rowNum) throws SQLException{ User user=new User(); user.setId(new Integer(rs.getInt("id"))); user.setName(rs.getString("name")); return user; }}使用RowMapper查询单笔数据public User find(Integer id){ User user=(User)jdbcTemplate.queryForObject("select * from user where id=?",new Object[]{id}, new UserRowMapper()); return user;}使用RowMapper查询多笔数据 class UserRowMapper implements RowMapper { public Object mapRow(ResultSet rs,int index) throws SQLException { User u = new User(); u.setId(rs.getString(”ID”)); u.setName(rs.getString(”Name”)); u.setPassword(rs.getString(”Password”)); return u; } }public List select(String where) { List list; String sql = “select * from admin “+where; list = jdbcTemplate.query(sql,new RowMapperResultReader(new UserRowMapper())); return list; } 传回的users对象中，包括了从数据库查询出来的结果，并已经封装成uer对象JdbcTemplate语句使用:1、使用JdbcTemplate的execute()方法执行SQL语句代码 jdbcTemplate.execute("CREATE TABLE USER (user_id integer, name varchar(100))"); 2、如果是UPDATE或INSERT,可以用update()方法。代码 jdbcTemplate.update("INSERT INTO USER VALUES('" + user.getId() + "', '" + user.getName() + "', '" + user.getSex() + "', '" + user.getAge() + "')"); 3、带参数的更新代码 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()}); 4、使用JdbcTemplate进行查询时，使用queryForXXX()等方法代码 int count = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM USER"); 代码 String name = (String) jdbcTemplate.queryForObject("SELECT name FROM USER WHERE user_id = ?", new Object[] {id}, java.lang.String.class); 代码 List rows = jdbcTemplate.queryForList("SELECT * FROM USER"); 代码 List rows = jdbcTemplate.queryForList("SELECT * FROM USER"); Iterator it = rows.iterator(); while(it.hasNext()) { Map userMap = (Map) it.next(); 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"); } JdbcTemplate将我们使用的JDBC的流程封装起来，包括了异常的捕捉、SQL的执行、查询结果的转换等等。spring大量使用Template Method模式来封装固定流程的动作，XXXTemplate等类别都是基于这种方式的实现。除了大量使用Template Method来封装一些底层的操作细节，spring也大量使用callback方式类回调相关类别的方法以提供JDBC相关类别的功能，使传统的JDBC的使用者也能清楚了解spring所提供的相关封装类别方法的使用。JDBC的PreparedStatement代码 final String id = user.getId(); final String name = user.getName(); final String sex = user.getSex() + ""; final int age = user.getAge(); jdbcTemplate.update("INSERT INTO USER VALUES(?, ?, ?, ?)", new PreparedStatementSetter() { public void setValues(PreparedStatement ps) throws SQLException { ps.setString(1, id); ps.setString(2, name); ps.setString(3, sex); ps.setInt(4, 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")); } }); 代码 class UserRowMapper implements RowMapper { public Object mapRow(ResultSet rs, int index) throws SQLException { User user = new User(); user.setId(rs.getString("user_id")); user.setName(rs.getString("name")); user.setSex(rs.getString("sex").charAt(0)); user.setAge(rs.getInt("age")); return user; } } public List findAllByRowMapperResultReader() { String sql = "SELECT * FROM USER"; return jdbcTemplate.query(sql, new RowMapperResultReader(new UserRowMapper())); } 在getUser(id)里面使用UserRowMapper代码 public User getUser(final String id) throws DataAccessException { String sql = "SELECT * FROM USER WHERE user_id=?"; final Object[] params = new Object[] { id }; List list = jdbcTemplate.query(sql, params, new RowMapperResultReader(new UserRowMapper())); return (User) list.get(0); } 网上收集org.springframework.jdbc.core.PreparedStatementCreator 返回预编译SQL 不能于Object[]一起用代码 public PreparedStatement createPreparedStatement(Connection con) throws SQLException { return con.prepareStatement(sql); } 1.增删改org.springframework.jdbc.core.JdbcTemplate 类(必须指定数据源dataSource)代码 template.update("insert into web_person values(?,?,?)",Object[]); 或代码 template.update("insert into web_person values(?,?,?)",new PreparedStatementSetter(){ 匿名内部类 只能访问外部最终局部变量 public void setValues(PreparedStatement ps) throws SQLException { ps.setInt(index++,3); }); org.springframework.jdbc.core.PreparedStatementSetter 接口 处理预编译SQL代码 public void setValues(PreparedStatement ps) throws SQLException { ps.setInt(index++,3); } 2.查询JdbcTemplate.query(String,[Object[]/PreparedStatementSetter],RowMapper/RowCallbackHandler)org.springframework.jdbc.core.RowMapper 记录映射接口 处理结果集代码 public Object mapRow(ResultSet rs, int arg1) throws SQLException { int表当前行数 person.setId(rs.getInt("id")); } List template.query("select * from web_person where id=?",Object[],RowMapper); org.springframework.jdbc.core.RowCallbackHandler 记录回调管理器接口 处理结果集代码 template.query("select * from web_person where id=?",Object[],new RowCallbackHandler(){ public void processRow(ResultSet rs) throws SQLException { person.setId(rs.getInt("id")); });
