Spring-JdbcTemplate学习笔记示例

spring jdbctemplate使用方法
1,建表
create table admin{
id number(10) primary key,
name varchar2(64),
password varchar2(64)
}
2,spring 配置
<?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,使用方法
查找
多行查询
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;
}
}
单行查询
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 insert(User u ){
String sql = "insert into admin(id,name,password) vlaues(admin_id_seq.nextval,?,?)";
Object[] params = new Object[]{
u.getName((),
u.getPassword()
};
jdbcTemplate.update(sql,params);
}
//admin_id_seq.nextval为oracle设置好的序列，问号“？”被params里的数据一次替代，最终执行sql。

修改
public void update(String how){
jdbc.Template.update(how);
}


源代码：
User.class
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 UserDAO getDao(){
return dao;
}

public String getId(){
return id;
}

public void setId(String id){
this.id = id;
}

public void setName (String name){
this.name = name;
}

public String getName(){
return name;
}

public void setPassword(String password){
this.password = password;
}

public String getPassword(){
return password;
}

public void getInfo(String id){
List list = dao.select("where id = " + id);
User u = (User)list.get(0);

this.id = u.getId();
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
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);
}

UserDAOImpl.class
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 UserMapper()));
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 = ClassPathResource("beans.xml");
BeanFactory factory = new XmlBeanFactory(resource);
User user = (User)factory.getBean("user");

user.selectWithTemp();
}
}