Top

JAVA Spring MyBatis DAY06

Spring整合JDBC案例
重构资费列表

1 Spring整合JDBC案例

1.1 问题

使用Spring整合JDBC，实现对员工表的增删改查。

1.2 方案

Spring整合JDBC有2种方式：

让DAO继承与JdbcDaoSupport
给DAO注入JdbcTemplate

本案例采用第2种整合方式。

1.3 步骤

步骤一：创建项目，导入jar包

创建项目Spring06，导入jar包，如下图：

图-1

步骤二：配置applicationContext.xml

创建数据库连接参数资源文件jdbc.properties，代码如下：

url=jdbc:oracle:thin:@localhost:1521:xe
driver=oracle.jdbc.OracleDriver
user=lhh
password=123456

创建Spring配置文件applicationContext.xml，代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context" 
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"  
	xmlns:jee="http://www.springframework.org/schema/jee" 
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop" 
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xmlns:util="http://www.springframework.org/schema/util"
	xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
		http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
		http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd">
	
	<!-- 加载jdbc资源文件 -->
	<util:properties id="jdbc" location="classpath:jdbc.properties"/>
	
	<!-- 定义数据源 -->
	<bean id="ds" 
		class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="url" value="#{jdbc.url}"/>
		<property name="driverClassName" value="#{jdbc.driver}"/>
		<property name="username" value="#{jdbc.user}"/>
		<property name="password" value="#{jdbc.password}"/>
	</bean>
	
	<context:component-scan base-package="com.tarena"/>
	
	<bean class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="ds"/>
	</bean>
	
</beans>

步骤三：创建表和实体类

创建员工表，代码如下：

--员工表
create table t_emp(
	empno NUMBER(4) CONSTRAINT EMP_ID_PK PRIMARY KEY,
	ename varchar(20),
	job varchar(10),
	mgr NUMBER(11),
	hiredate date,
	sal NUMBER(11,2),
	comm NUMBER(11,2),
	deptno NUMBER(11)
);

create sequence emp_seq start with 100;

--预置的员工表数据
insert into t_emp values (1,'SMITH','CLERK',3,null,800,null,2);
insert into t_emp values (2,'ALLEN','SALESMAN',3,null,1600,300,3);
insert into t_emp values (3,'WARD','SALESMAN',4,null,1250,500,3);
insert into t_emp values (4,'JONES','MANAGER',5,null,2975,null,2);
insert into t_emp values (5,'MARTIN','SALESMAN',7,null,1250,1400,3);
insert into t_emp values (6,'BLAKE','MANAGER',9,null,2850,null,3);
insert into t_emp values (7,'CLARK','MANAGER',9,null,2450,null,1);
insert into t_emp values (8,'SCOTT','ANALYST',9,null,3000,null,2);
insert into t_emp values (9,'KING','PRESIDENT',null,null,5000,null,1);
insert into t_emp values (10,'TURNER','SALESMAN',5,null,1500,0,3);
insert into t_emp values (11,'ADAMS','CLERK',5,null,1100,null,2);
insert into t_emp values (12,'JAMES','CLERK',1,null,950,null,3);
insert into t_emp values (13,'FORD','ANALYST',1,null,3000,null,2);
insert into t_emp values (14,'MILLER','CLERK',3,null,1300,null,1);
commit;

创建员工实体类Emp，代码如下：

package com.tarena.entity;

import java.io.Serializable;
import java.sql.Date;

public class Emp implements Serializable{

	private Integer empno;
	private String ename;
	private String job;
	private Integer mgr;
	private Date hiredate;
	private Double sal;
	private Double comm;
	private Integer deptno;

	public Integer getEmpno() {
		return empno;
	}

	public void setEmpno(Integer empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public Integer getMgr() {
		return mgr;
	}

	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}

	public Date getHiredate() {
		return hiredate;
	}

	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}

	public Double getSal() {
		return sal;
	}

	public void setSal(Double sal) {
		this.sal = sal;
	}

	public Double getComm() {
		return comm;
	}

	public void setComm(Double comm) {
		this.comm = comm;
	}

	public Integer getDeptno() {
		return deptno;
	}

	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}

}

步骤四：创建DAO

创建员工数据访问接口EmpDao，代码如下：

package com.tarena.dao;

import java.util.List;

import com.tarena.entity.Emp;

public interface EmpDao {

	List<Emp> findAll();

	Emp findById(int id);

	void save(Emp emp);

	void update(Emp emp);

	void delete(int id);

}

创建该接口的实现类JdbcEmpDao，代码如下：

package com.tarena.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.tarena.entity.Emp;

@Repository
public class JdbcEmpDao implements EmpDao {

	@Resource
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public List<Emp> findAll() {
		String sql = "select * from t_emp";
		return jdbcTemplate.query(sql, new EmpRowMapper());
	}

	@Override
	public Emp findById(int id) {
		String sql = "select * from t_emp where empno=?";
		return jdbcTemplate.queryForObject(
				sql, new Object[]{id}, new EmpRowMapper());
	}

	@Override
	public void save(Emp emp) {
		String sql = "insert into t_emp values(emp_seq.nextval,?,?,?,?,?,?,?)";
		Object[] params = {emp.getEname(), emp.getJob(), 
				emp.getMgr(), emp.getHiredate(),
				emp.getSal(), emp.getComm(), 
				emp.getDeptno()};
		jdbcTemplate.update(sql, params);
	}

	@Override
	public void update(Emp emp) {
		String sql = "update t_emp set " +
				"ename=?,job=?,mgr=?,hiredate=?," +
				"sal=?,comm=?,deptno=? " +
				"where empno=?";
		Object[] params = {emp.getEname(), emp.getJob(), 
				emp.getMgr(), emp.getHiredate(),
				emp.getSal(), emp.getComm(), 
				emp.getDeptno(), emp.getEmpno()};
		jdbcTemplate.update(sql, params);
	}

	@Override
	public void delete(int id) {
		String sql = "delete from t_emp where empno=?";
		jdbcTemplate.update(sql, id);
	}
	
	class EmpRowMapper implements RowMapper<Emp> {

		@Override
		public Emp mapRow(ResultSet rs, int index) 
			throws SQLException {
			Emp e = new Emp();
			e.setEmpno(rs.getInt("empno"));
			e.setEname(rs.getString("ename"));
			e.setJob(rs.getString("job"));
			e.setMgr(rs.getInt("mgr"));
			e.setHiredate(rs.getDate("hiredate"));
			e.setSal(rs.getDouble("sal"));
			e.setComm(rs.getDouble("comm"));
			e.setDeptno(rs.getInt("deptno"));
			return e;
		}

	}
	
}

步骤五：测试

创建测试类TestCase，代码如下：

package com.tarena.test;

import java.sql.Date;
import java.util.List;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.tarena.dao.EmpDao;
import com.tarena.entity.Emp;

public class TestCase {
	
	ApplicationContext ctx = 
		new ClassPathXmlApplicationContext("applicationContext.xml");
	
	@Test
	public void testFindAll() {
		EmpDao dao = ctx.getBean("jdbcEmpDao", EmpDao.class);
		List<Emp> list = dao.findAll();
		for(Emp e : list) {
			System.out.println(
				e.getEmpno() + " " + 
				e.getEname() + " " +
				e.getSal());
		}
	}
	
	@Test
	public void testFindById() {
		EmpDao dao = ctx.getBean("jdbcEmpDao", EmpDao.class);
		Emp e = dao.findById(1);
		System.out.println(
			e.getEmpno() + " " + 
			e.getEname() + " " +
			e.getSal());
	}
	
	@Test
	public void testSave() {
		EmpDao dao = ctx.getBean("jdbcEmpDao", EmpDao.class);
		Emp e = new Emp();
		e.setEname("孙悟空");
		e.setJob("SALESMAN");
		e.setMgr(3);
		e.setHiredate(new Date(System.currentTimeMillis()));
		e.setSal(3000.0);
		e.setDeptno(30);
		dao.save(e);
	}
	
	@Test
	public void testUpdate() {
		EmpDao dao = ctx.getBean("jdbcEmpDao", EmpDao.class);
		Emp e = dao.findById(141);
		e.setEname("唐僧");
		dao.update(e);
	}
	
	@Test
	public void testDelete() {
		EmpDao dao = ctx.getBean("jdbcEmpDao", EmpDao.class);
		dao.delete(141);
	}

}

依次执行测试方法，效果如下图：

图-2

1.4 完整代码

jdbc.properties完整代码如下：

url=jdbc:oracle:thin:@localhost:1521:xe
driver=oracle.jdbc.OracleDriver
user=lhh
password=123456

applicationContext.xml完整代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context" 
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"  
	xmlns:jee="http://www.springframework.org/schema/jee" 
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop" 
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xmlns:util="http://www.springframework.org/schema/util"
	xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
		http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
		http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd">
	
	<!-- 加载jdbc资源文件 -->
	<util:properties id="jdbc" location="classpath:jdbc.properties"/>
	
	<!-- 定义数据源 -->
	<bean id="ds" 
		class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="url" value="#{jdbc.url}"/>
		<property name="driverClassName" value="#{jdbc.driver}"/>
		<property name="username" value="#{jdbc.user}"/>
		<property name="password" value="#{jdbc.password}"/>
	</bean>
	
	<context:component-scan base-package="com.tarena"/>
	
	<bean class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="ds"/>
	</bean>
	
</beans>

建表脚本完整代码如下：

--员工表
create table t_emp(
	empno NUMBER(4) CONSTRAINT EMP_ID_PK PRIMARY KEY,
	ename varchar(20),
	job varchar(10),
	mgr NUMBER(11),
	hiredate date,
	sal NUMBER(11,2),
	comm NUMBER(11,2),
	deptno NUMBER(11)
);

create sequence emp_seq start with 100;

--预置的员工表数据
insert into t_emp values (1,'SMITH','CLERK',3,null,800,null,2);
insert into t_emp values (2,'ALLEN','SALESMAN',3,null,1600,300,3);
insert into t_emp values (3,'WARD','SALESMAN',4,null,1250,500,3);
insert into t_emp values (4,'JONES','MANAGER',5,null,2975,null,2);
insert into t_emp values (5,'MARTIN','SALESMAN',7,null,1250,1400,3);
insert into t_emp values (6,'BLAKE','MANAGER',9,null,2850,null,3);
insert into t_emp values (7,'CLARK','MANAGER',9,null,2450,null,1);
insert into t_emp values (8,'SCOTT','ANALYST',9,null,3000,null,2);
insert into t_emp values (9,'KING','PRESIDENT',null,null,5000,null,1);
insert into t_emp values (10,'TURNER','SALESMAN',5,null,1500,0,3);
insert into t_emp values (11,'ADAMS','CLERK',5,null,1100,null,2);
insert into t_emp values (12,'JAMES','CLERK',1,null,950,null,3);
insert into t_emp values (13,'FORD','ANALYST',1,null,3000,null,2);
insert into t_emp values (14,'MILLER','CLERK',3,null,1300,null,1);
commit;a

Emp完整代码如下：

package com.tarena.entity;

import java.io.Serializable;
import java.sql.Date;

public class Emp implements Serializable{

	private Integer empno;
	private String ename;
	private String job;
	private Integer mgr;
	private Date hiredate;
	private Double sal;
	private Double comm;
	private Integer deptno;

	public Integer getEmpno() {
		return empno;
	}

	public void setEmpno(Integer empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public Integer getMgr() {
		return mgr;
	}

	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}

	public Date getHiredate() {
		return hiredate;
	}

	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}

	public Double getSal() {
		return sal;
	}

	public void setSal(Double sal) {
		this.sal = sal;
	}

	public Double getComm() {
		return comm;
	}

	public void setComm(Double comm) {
		this.comm = comm;
	}

	public Integer getDeptno() {
		return deptno;
	}

	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}

}

EmpDao完整代码如下：

package com.tarena.dao;

import java.util.List;

import com.tarena.entity.Emp;

public interface EmpDao {

	List<Emp> findAll();

	Emp findById(int id);

	void save(Emp emp);

	void update(Emp emp);

	void delete(int id);

}

JdbcEmpDao完整代码如下：

package com.tarena.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.tarena.entity.Emp;

@Repository
public class JdbcEmpDao implements EmpDao {

	@Resource
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public List<Emp> findAll() {
		String sql = "select * from t_emp";
		return jdbcTemplate.query(sql, new EmpRowMapper());
	}

	@Override
	public Emp findById(int id) {
		String sql = "select * from t_emp where empno=?";
		return jdbcTemplate.queryForObject(
				sql, new Object[]{id}, new EmpRowMapper());
	}

	@Override
	public void save(Emp emp) {
		String sql = "insert into t_emp values(emp_seq.nextval,?,?,?,?,?,?,?)";
		Object[] params = {emp.getEname(), emp.getJob(), 
				emp.getMgr(), emp.getHiredate(),
				emp.getSal(), emp.getComm(), 
				emp.getDeptno()};
		jdbcTemplate.update(sql, params);
	}

	@Override
	public void update(Emp emp) {
		String sql = "update t_emp set " +
				"ename=?,job=?,mgr=?,hiredate=?," +
				"sal=?,comm=?,deptno=? " +
				"where empno=?";
		Object[] params = {emp.getEname(), emp.getJob(), 
				emp.getMgr(), emp.getHiredate(),
				emp.getSal(), emp.getComm(), 
				emp.getDeptno(), emp.getEmpno()};
		jdbcTemplate.update(sql, params);
	}

	@Override
	public void delete(int id) {
		String sql = "delete from t_emp where empno=?";
		jdbcTemplate.update(sql, id);
	}
	
	class EmpRowMapper implements RowMapper<Emp> {

		@Override
		public Emp mapRow(ResultSet rs, int index) 
			throws SQLException {
			Emp e = new Emp();
			e.setEmpno(rs.getInt("empno"));
			e.setEname(rs.getString("ename"));
			e.setJob(rs.getString("job"));
			e.setMgr(rs.getInt("mgr"));
			e.setHiredate(rs.getDate("hiredate"));
			e.setSal(rs.getDouble("sal"));
			e.setComm(rs.getDouble("comm"));
			e.setDeptno(rs.getInt("deptno"));
			return e;
		}

	}
	
}

TestCase完整代码如下：

package com.tarena.test;

import java.sql.Date;
import java.util.List;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.tarena.dao.EmpDao;
import com.tarena.entity.Emp;

public class TestCase {
	
	ApplicationContext ctx = 
		new ClassPathXmlApplicationContext("applicationContext.xml");
	
	@Test
	public void testFindAll() {
		EmpDao dao = ctx.getBean("jdbcEmpDao", EmpDao.class);
		List<Emp> list = dao.findAll();
		for(Emp e : list) {
			System.out.println(
				e.getEmpno() + " " + 
				e.getEname() + " " +
				e.getSal());
		}
	}
	
	@Test
	public void testFindById() {
		EmpDao dao = ctx.getBean("jdbcEmpDao", EmpDao.class);
		Emp e = dao.findById(1);
		System.out.println(
			e.getEmpno() + " " + 
			e.getEname() + " " +
			e.getSal());
	}
	
	@Test
	public void testSave() {
		EmpDao dao = ctx.getBean("jdbcEmpDao", EmpDao.class);
		Emp e = new Emp();
		e.setEname("孙悟空");
		e.setJob("SALESMAN");
		e.setMgr(3);
		e.setHiredate(new Date(System.currentTimeMillis()));
		e.setSal(3000.0);
		e.setDeptno(30);
		dao.save(e);
	}
	
	@Test
	public void testUpdate() {
		EmpDao dao = ctx.getBean("jdbcEmpDao", EmpDao.class);
		Emp e = dao.findById(141);
		e.setEname("唐僧");
		dao.update(e);
	}
	
	@Test
	public void testDelete() {
		EmpDao dao = ctx.getBean("jdbcEmpDao", EmpDao.class);
		dao.delete(141);
	}

}

2 重构资费列表

2.1 问题

使用Spring整合JDBC，重构NETCTOSS资费列表功能。

2.2 方案

Spring整合JDBC有2种方式：

让DAO继承与JdbcDaoSupport
给DAO注入JdbcTemplate

本案例采用第2种整合方式。

2.3 步骤

步骤一：配置applicationContext.xml

在applicationContext.xml中增加一个bean，追加代码如下：

	<!-- 整合JDBC -->
	<bean class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="ds"/>
	</bean>

步骤二：创建资费数据访问组件

创建一个新的CostDao接口实现类SpringJdbcCostDao，代码如下：

package com.tarena.dao;

import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.tarena.entity.Cost;

@Repository
public class SpringJdbcCostDao implements CostDao, Serializable {

	@Resource
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public List<Cost> findAll() {
		String sql = "select * from cost order by cost_id";
		return jdbcTemplate.query(sql, new CostRowMapper());
	}
	
	class CostRowMapper implements RowMapper<Cost> {

		@Override
		public Cost mapRow(ResultSet rs, int index) 
			throws SQLException {
			Cost cost = new Cost();
			cost.setCostId(rs.getInt("cost_id"));
			cost.setName(rs.getString("name"));
			cost.setBaseDuration(rs.getInt("base_duration"));
			cost.setBaseCost(rs.getDouble("base_cost"));
			cost.setUnitCost(rs.getDouble("unit_cost"));
			cost.setStatus(rs.getString("status"));
			cost.setDescr(rs.getString("descr"));
			cost.setCreatime(rs.getTimestamp("creatime"));
			cost.setStartime(rs.getTimestamp("startime"));
			cost.setCostType(rs.getString("cost_type"));
			return cost;
		}
		
	}

}

步骤三：修改资费业务组件

修改CostService，将注入的CostDao接口实例改为SpringJdbcCostDao，代码如下：

package com.tarena.service;

import java.io.Serializable;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.tarena.dao.CostDao;
import com.tarena.entity.Cost;

@Service
public class CostService implements Serializable {
	
	@Resource(name="springJdbcCostDao")
	private CostDao costDao;
	
	public List<Cost> findAll() {
		return costDao.findAll();
	}
}

步骤四：测试

重新部署项目并启动Tomcat，登录后访问资费列表功能，浏览器显示结果如下图：

图-3

2.4 完整代码

applicationContext.xml完整代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context" 
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"  
	xmlns:jee="http://www.springframework.org/schema/jee" 
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:aop="http://www.springframework.org/schema/aop" 
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xmlns:util="http://www.springframework.org/schema/util"
	xmlns:jpa="http://www.springframework.org/schema/data/jpa"
	xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
		http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
		http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd">
	
	<util:properties id="jdbc" location="classpath:jdbc.properties"/>
	
	<!-- 定义数据源 -->
	<bean id="ds" 
		class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="url" value="#{jdbc.url}"/>
		<property name="driverClassName" value="#{jdbc.driver}"/>
		<property name="username" value="#{jdbc.user}"/>
		<property name="password" value="#{jdbc.password}"/>
	</bean>	
	
	<!-- 开启注解扫描 -->
	<context:component-scan base-package="com.tarena"/>
	
	<!-- 开启MVC注解扫描 -->
	<mvc:annotation-driven/>
	
	<!-- 定义视图解析器ViewResolver -->
	<bean id="viewResolver"
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/WEB-INF/"/>
		<property name="suffix" value=".jsp"/>
	</bean>
	
	<!-- 处理系统异常 -->
	<bean class="org.springframework.web.servlet.handler.SimpleMappingExceptionResolver">
		<property name="exceptionMappings">
	        <props>		
	             <prop key="java.lang.Exception">main/error</prop>
	        </props>
	    </property>
	</bean>	
	
	<!-- 拦截器 -->
	<mvc:interceptors>
		<!-- 登录检查拦截器 -->
		<mvc:interceptor>
			<mvc:mapping path="/**"/>
			<mvc:exclude-mapping path="/login/toLogin.do"/>
			<mvc:exclude-mapping path="/login/checkLogin.do"/>
			<bean class="com.tarena.web.LoginInterceptor"/>
		</mvc:interceptor>
	</mvc:interceptors>
	
	<!-- 整合JDBC -->
	<bean class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="ds"/>
	</bean>
	
</beans>

SpringJdbcCostDao完整代码如下：

package com.tarena.dao;

import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.tarena.entity.Cost;

@Repository
public class SpringJdbcCostDao implements CostDao, Serializable {

	@Resource
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public List<Cost> findAll() {
		String sql = "select * from cost order by cost_id";
		return jdbcTemplate.query(sql, new CostRowMapper());
	}
	
	class CostRowMapper implements RowMapper<Cost> {

		@Override
		public Cost mapRow(ResultSet rs, int index) 
			throws SQLException {
			Cost cost = new Cost();
			cost.setCostId(rs.getInt("cost_id"));
			cost.setName(rs.getString("name"));
			cost.setBaseDuration(rs.getInt("base_duration"));
			cost.setBaseCost(rs.getDouble("base_cost"));
			cost.setUnitCost(rs.getDouble("unit_cost"));
			cost.setStatus(rs.getString("status"));
			cost.setDescr(rs.getString("descr"));
			cost.setCreatime(rs.getTimestamp("creatime"));
			cost.setStartime(rs.getTimestamp("startime"));
			cost.setCostType(rs.getString("cost_type"));
			return cost;
		}
		
	}

}

CostService完整代码如下：

package com.tarena.service;

import java.io.Serializable;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.tarena.dao.CostDao;
import com.tarena.entity.Cost;

@Service
public class CostService implements Serializable {
	
	@Resource(name="springJdbcCostDao")
	private CostDao costDao;
	
	public List<Cost> findAll() {
		return costDao.findAll();
	}
}