Spring:JdbcTemplate使用指南
前言:
本文指在介绍Spring框架中的JdbcTemplate类的使用方法,涉及基本的Spring反转控制的使用方法和JDBC的基本概念。目标是使读者能够对JdbcTemplate快速地掌握和使用。
准备:
1. Spring的基本概念
Spring框架核心的思想就是建立一个Java对象的大工厂,用户只要给工厂一个指令,工厂就能将用户需要的对象根据配置文件组装好返还给用户。用户需要做的许多工作则可以写成简单的配置文件。
2. 丑陋的JDBC代码
Connection con= null; PreparedStatement pStmt=null; ResultSet rs = null; try{ con = ods.getConnection(); String sql = "select * from admin"; pStmt=con.prepareStatement(sql); rs=pStmt.executeQuery(); while(rs.next()) { } } catch(Exception ex) { try{ con.rollback(); }catch(SQLException sqlex){ sqlex.printStackTrace(System.out); } ex.printStackTrace(); }finally{ try{ rs.close(); pStmt.close(); con.close(); }catch(Exception e){e.printStackTrace();} }
|
以上是常见的JDBC代码,简单的select语句也需要冗长的出错处理,并且每个函数都不断地重复同样的代码。
3. JdbcTemplate的作用
JdbcTemplate正是为了减少上述繁琐的代码而设计出来的。它是对JDBC的一种封装,抽象我们常用的一些方法。Simple and Stupid就是它的目标。下面是完成了刚才JDBC代码同样功能的JdbcTemplate的代码:
String sql = "select * from admin"; jdbcTemplate.query(sql,new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { } } ); |
环境搭建:
1. 数据库的配置
本文使用Oracle数据库,新建表admin:
create table admin ( |
2. Spring配置
JdbcTemplate的使用需要有DataSource的支持,所以在配置文件中,我们首先要配置一个OracleDataSource,然后在将这个DataSource配置到JdbcTemplate里。接着将JdbcTemplate配置进DAO层,最后将DAO配置进Model层。简要的关系如下:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"> <beans> <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource"> <property name="URL"> <value>jdbc:oracle:thin:root/123@localhost:1521/XE</value> </property> </bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource"><ref bean="dataSource"/></property> </bean>
<bean id="userDAO" class="DAO.Imp.UserDAOImp"> <property name="jdbcTemplate"> <ref bean="jdbcTemplate" /> </property> </bean>
<bean id="user" class="Model.User"> <property name="dao"><ref bean="userDAO"/></property> </bean> </beans> |
3. 环境配置, 如图:
使用方法:
1. 查找
多行查询:
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; }
|
List最终返回的是满足条件的User队列。
单行查询:
public User selectById(String id){ String sql = "select * from admin where id=?"; final User u = new User(); final Object[] params = new Object[] {id}; jdbcTemplate.query(sql, params, new RowCallbackHandler(){ public void processRow(ResultSet rs) throws SQLException { u.setId(rs.getString("ID")); u.setName(rs.getString("NAME")); u.setPassword(rs.getString("PASSWORD")); } }); return u; }
|
2. 插入
public void insert(User u) { String sql = "insert into admin (ID,NAME,PASSWORD) values (admin_id_seq.nextval,?,?)"; Object[] params = new Object[] { u.getName(), u.getPassword() }; jdbcTemplate.update(sql,params); } |
admin_id_seq.nextval为Oracle设置好的序列,问号“?”被params里的数据依次替代,最终执行sql。
3. 修改
非常简单:
public void update(String how) { jdbcTemplate.update(how); } |
源代码:
User.class:
package Model;
import java.util.List; import DAO.UserDAO; /** * Model层 * * * @author 李嘉陵 * @since 2006-4-30 12:10:30 * @version 0.10a **/
public class User { private String name; private String id; private String password; private UserDAO dao;
public User() {
}
public User(String name, String password) { this.name = name; this.password = password; }
public void setDao(UserDAO dao) { this.dao = dao; } public String getId() { return id; }
public void setId(String id) { this.id = id; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public String getPassword() { return password; }
public void setPassword(String password) { this.password = password; }
public void getInfo(String id) { List list = dao.select("where id="+id); User u = (User) list.get(0);
this.id=id; this.name = u.getName(); this.password = u.getPassword();
}
public void insert() { dao.insert(this); }
public void update(String how) { dao.update(how); }
public void update() { dao.update("update admin set name='"+name+"', password='"+password+"' where id="+id); }
public List selectWithTemp(String where) { return dao.select(where); }
public void selectWithTemp() { dao.selectWithTemp(); }
public User selectById(String id) { return dao.selectById(id); }
public void insertUsers(List users) { dao.insertUsers(users); } } |
UserDAO.class :
package DAO;
import java.util.List;
import Model.User;
/** * DAO层接口 * * * @author 李嘉陵 * @since 2006-4-30 8:40:56 * @version 0.10a **/
public interface UserDAO { public void select(); public void test(); public void selectWithTemp(); public List select(String where); public void update(String how); public void insert(User u); public User selectById(String id); public int[] insertUsers(final List users);
} |
UserDAOImp.class:
package DAO.Imp;
import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List;
import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowCallbackHandler; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.RowMapperResultReader;
import DAO.UserDAO; import Model.User;
/** * DAO层的实现 * * * @author 李嘉陵 * @since 2006-4-30 8:41:26 * @version 0.10a **/
public class UserDAOImp implements UserDAO{
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; }
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 void selectWithTemp() { String sql = "select * from admin";
jdbcTemplate.query(sql,new RowCallbackHandler() { public void processRow(ResultSet rs) throws SQLException { System.out.println("ID: "+rs.getString("ID")+" Name: "+rs.getString("name")); } } );
}
public List select(String where) { List list; String sql = "select * from admin "+where; list = jdbcTemplate.query(sql,new RowMapperResultReader(new UserRowMapper())); return list; }
public User selectById(String id) { String sql = "select * from admin where id=?"; final User u = new User(); final Object[] params = new Object[] {id};
jdbcTemplate.query(sql,params, new RowCallbackHandler(){ public void processRow(ResultSet rs) throws SQLException { u.setId(rs.getString("ID")); u.setName(rs.getString("NAME")); u.setPassword(rs.getString("PASSWORD")); } });
return u; }
public void update(String how) { String sql = how; jdbcTemplate.update(sql); }
public void insert(User u) { String sql = "insert into admin (ID,NAME,PASSWORD) values (admin_id_seq.nextval,?,?)"; Object[] params = new Object[] { u.getName(), u.getPassword()}; jdbcTemplate.update(sql,params); }
} |
UserAction.class:
//测试类 public class UserAction { public static void main(String[] args) { Resource resource=new ClassPathResource("beans.xml"); BeanFactory factory = new XmlBeanFactory(resource); User user = (User) factory.getBean("user");
user.selectWithTemp(); } } |