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()});
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(?,?,?)",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"));
});
浙公网安备 33010602011771号