spring中jdbctemplete的使用

前言：

本文指在介绍Spring框架中的JdbcTemplate类的使用方法，涉及基本的Spring反转控制的使用方法和JDBC的基本概念。目标是使读者能够对JdbcTemplate快速地掌握和使用。 

 

环境搭建：

1. 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> 

 

2. 环境配置， 如图： 

 

使用方法：

查找

多行查询：

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;

} 

 

插入

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。

 

修改

非常简单：

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();

}

}