JdbcTemplate简单的方法执行sql

1、使用JdbcTemplate的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或INSERT,可以用update()方法

jdbcTemplate.update("INSERT INTO USER VALUES('"  
    
+ user.getId() + "', '"  
    
+ user.getName() + "', '"  
    
+ user.getSex() + "', '"  
    
+ user.getAge() + "')");  
    
jdbcTemplate.update("INSERT INTO USER VALUES('"  
    
+ user.getId() + "', '"  
    
+ user.getName() + "', '"  
    
+ user.getSex() + "', '"  
    
+ user.getAge() + "')");

2-1)带参数的更新

jdbcTemplate.update("UPDATE USER SET name = ? WHERE user_id = ?", new Object[] {name, id});    
    
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");    
        
    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);    
        
    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");  



        
    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");  
        
    }  
        
      
        
    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");
        
        
    }

/** 
使用三种Callback接口作为参数的query方法的返回值不同:  
以ResultSetExtractor作为方法参数的query方法返回Object型结果，要使用查询结果，我们需要对其进行强制转型；  
以RowMapper接口作为方法参数的query方法直接返回List型的结果；  
以RowCallbackHandler作为方法参数的query方法，返回值为void； 
RowCallbackHandler和RowMapper才是我们最常用的选择  
 * @author Administrator 
 *  
 */ 
public class SpringTest {  
 /** 
  * 返回结果是List里装Map,使用参数,使用回调 RowMapperResultSetExtractor用于处理单行记录， 
  * 它内部持有一个RowMapper实例的引用，当处理结果集的时候， 会将单行数据的处理委派给其所持有的RowMapper实例，而其余工作它负责 
  */ 
 public void getListRowMapperResultSetExtractor() {  
  ApplicationContext context = new FileSystemXmlApplicationContext(  
    "src/database_config.xml");  
  // E:/demoworkspace/spring 为工程主目录  
  JdbcTemplate jt = new JdbcTemplate((DataSource) context  
    .getBean("oracleDataSourceTest")); // 测试用的方法  
  Object[] arg = new Object[] { 10 };  
  List list = (ArrayList) jt.query("select * from region where rownum<?",  
    arg, new RowMapperResultSetExtractor(new RowMapper() {  
     public Object mapRow(ResultSet rs, int index)  
       throws SQLException {  
      Map u = new HashMap(); //可以是自己的JavaBean值对象(简单Java对象POJO)  
      u.put("region_id", rs.getString("region_id"));  
      u.put("region_name", rs.getString("region_name"));  
      return u;  
     }  
    }));  
  Iterator it = list.iterator();  
  while (it.hasNext()) {  
   Map map = (Map) it.next();  
   System.out.println(map.toString());  
  }  
 }  
 
 
 /**返回结果是List里装Map,不使用参数,使用回调 
  使用RowMapper比直接使用ResultSetExtractor要方便的多，只负责处理单行结果就行，现在，我们只需要将单行的结果组装后返回就行， 
  剩下的工作，全部都是JdbcTemplate内部的事情了。 实际上，JdbcTemplae内部会使用一个ResultSetExtractor实现类来做其余的工作， 
  毕竟，该做的工作还得有人做不是？！   
  */ 
 public void getListRowMapper() {  
  ApplicationContext context = new FileSystemXmlApplicationContext(  
    "src/database_config.xml");  
  JdbcTemplate jt = new JdbcTemplate((DataSource) context  
    .getBean("oracleDataSourceTest"));  
  List list = jt.query(  
    "select * from region where rownum<10", new RowMapper() {  
     public Object mapRow(ResultSet rs, int index)  
       throws SQLException {  
      Map u = new HashMap();  
      u.put("region_id", rs.getString("region_id"));  
      u.put("region_name", rs.getString("region_name"));  
      return u;  
     }  
    });  
  Iterator it = list.iterator();  
  while (it.hasNext()) {  
   Map map = (Map) it.next();  
   System.out.println(map.toString());  
  }  
 }  
 
 // 返回记录集  
 /** 
  RowCallbackHandler虽然与RowMapper同是处理单行数据，不过，除了要处理单行结果，它还得负责最终结果的组装和获取工作， 
  在这里我们是使用当前上下文声明的List取得最终查询结果， 不过，我们也可以单独声明一个RowCallbackHandler实现类， 
  在其中声明相应的集合类，这样，我们可以通过该RowCallbackHandler实现类取得最终查询结果  
  */ 
 public void getListRowCallbackHandler() {  
  ApplicationContext context = new FileSystemXmlApplicationContext(  
    "src/database_config.xml");  
 
  JdbcTemplate jt = new JdbcTemplate((DataSource) context  
    .getBean("oracleDataSourceTest"));  
  String sql = "select * from region  where region_id>?";  
  final List<Map> list=new ArrayList<Map>(); //一定要用final定义  
  Object[] params = new Object[] { 0 };  
  jt.query(sql, params, new RowCallbackHandler() {  
   public void processRow(ResultSet rs) throws SQLException {  
    Map u = new HashMap();    
    u.put("region_id", rs.getString("region_id"));  
    u.put("region_name", rs.getString("region_name"));  
    list.add(u);  
   }  
  });  
    
  Iterator it = list.iterator();  
  while (it.hasNext()) {  
   Map map = (Map) it.next();  
   System.out.println(map.toString());  
  }  
 }

JdbcTemplate将我们使用的JDBC的流程封装起来，包括了异常的捕捉、SQL的执行、查询结果的转换等等。spring大量使用Template Method模式来封装固定流程的动作，XXXTemplate等类别都是基于这种方式的实现。

除了大量使用Template Method来封装一些底层的操作细节，spring也大量使用callback方式类回调相关类别的方法以提供JDBC相关类别的功能，使传统的JDBC的使用者也能清楚了解spring所提供的相关封装类别方法的使用。

5.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 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);      
        
    }    
        
    });         

Java代码

        
    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"));    
        
    }  
        
    });  
        
      
        
    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"));    
        
    }    
        
    });  

Java代码

        
    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()));  
        
      
        
    }  
        
      
        
    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);    
    
}
    
  
    
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);    
    
}  
    
  
    
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {    
    
return con.prepareStatement(sql);    
    
}

//.增删改

//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);    
    
});  
    
  
    
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);    
    
}  
    
  
    
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);    
    
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"));    
    
});

优质内容筛选与推荐>>
1、使用C#判断数据库服务器是否已经启动
2、培养你的核心竞争能力
3、添加SQL字段
4、EV: 关于min-width样式的使用
5、JS比较好用的一些方法搜集

0 │ 收藏 │ 举报

朋友将在看一看看到

分享想法到看一看