[Spring+SpringMVC+Mybatis]框架学习笔记（三）：Spring实现JDBC

上一章：[Spring+SpringMVC+Mybatis]框架学习笔记（二）：Spring-IOC-DI
下一章：[Spring+SpringMVC+Mybatis]框架学习笔记（四）：Spring实现AOP

第3章 Spring实现JDBC

利用springJdbcTemplate实现一个表的简单的增删改查。

事先建表：

CREATE DATABASE STUDENT;

USE STUDENT;

CREATE TABLE STUDENT(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL,
	AGE INT NOT NULL,
	SEX TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL
)AUTO_INCREMENT = 100000;

INSERT INTO STUDENT(NAME,AGE,SEX) VALUES("STEVEN",31,1);
INSERT INTO STUDENT(NAME,AGE) VALUES("LUCY",28);

注入数据库连接的四要素（数据库类型、地址、用户名、密码）:

引入数据库驱动包
mysql-connector-java-5.1.39-bin.jar
ojdbc5.jar
引入spring-jdbc的jar包
spring-jdbc-4.2.1.RELEASE.jar
spring-tx-4.2.1.RELEASE.jar（跟事务相关）

3.1 实现方式1：配置文件方式

3.1.1 实体类

package com.steven.spring.sysmgr.entity;

import java.io.Serializable;

/**
 * 实体类
 * @author chenyang
 *
 */
public class Student implements Serializable{
	/**
	 * 
	 */
	private static final long serialVersionUID = -3587920774423955014L;

	public Student(){
		
	}
	
	private int id;
	private String name;
	private int age;
	private int sex;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public int getSex() {
		return sex;
	}
	public void setSex(int sex) {
		this.sex = sex;
	}
	
}

3.1.2 DAO层的接口和实现类

1）DAO接口：

package com.steven.spring.sysmgr.dao;

import java.util.List;
import java.util.Map;

import com.steven.spring.sysmgr.entity.Student;

public interface IStudentDao {
	
	public boolean addStudent(Student student);
	
	public boolean delStudent(Integer studentId);
	
	public boolean updateStudent(Student student);
	
	public List<Map<String, Object>> getStudentMap();
	
	public List<Student> getStudentList();
}

2）DAO接口的实现：

package com.steven.spring.sysmgr.dao.impl;

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

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

import com.steven.spring.sysmgr.dao.IStudentDao;
import com.steven.spring.sysmgr.entity.Student;

public class StudentDao implements IStudentDao{
	
	private JdbcTemplate jdbcTemplate;
	
 	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public boolean addStudent(Student student) {
		boolean flag = false;
		String addSql = "INSERT INTO STUDENT(NAME,AGE,SEX) VALUES(?,?,?)";
		int rows = this.jdbcTemplate.update(addSql, student.getName(), student.getAge(), student.getSex());
		if(rows > 0){
			flag = true;
		}
		return flag;
	}

	@Override
	public boolean delStudent(Integer studentId) {
		boolean flag = false;
		String delSql = "DELETE FROM STUDENT WHERE ID = ?";
		int rows = this.jdbcTemplate.update(delSql, studentId);
		if(rows > 0){
			flag = true;
		}
		
		return flag;
	}

	@Override
	public boolean updateStudent(Student student) {
		boolean flag = false;
		String updateSql = "UPDATE STUDENT SET NAME=?, AGE=?, SEX=? WHERE ID=? ";
		int rows = this.jdbcTemplate.update(updateSql, student.getName(), student.getAge(), student.getSex(), student.getId());
		if(rows > 0){
			flag = true;
		}
		return flag;
	}

	@Override
	public List<Map<String, Object>> getStudentMap() {
		return this.jdbcTemplate.queryForList("SELECT ID,NAME,AGE,SEX FROM STUDENT");
	}

	@Override
	public List<Student> getStudentList() {
		String querySql = "SELECT ID, NAME, AGE, SEX FROM STUDENT";
		//第1种方式：用接口的匿名内部类来实现，优点：可以根据需求做二次改造
		/*return this.jdbcTemplate.query(querySql, new RowMapper<Student>(){

			@Override
			public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
				Student student = new Student();
				student.setId(rs.getInt("ID"));
				student.setName(rs.getString("NAME"));
				student.setAge(rs.getInt("AGE"));
				student.setSex(rs.getInt("SEX"));
				return student;
			}
			
		});*/
		
		//第2种方式：直接将行结果集映射到实体类中，优点：简便
		return this.jdbcTemplate.query(querySql, new BeanPropertyRowMapper<Student>(Student.class));
		
	}
}

3.1.3 服务层的接口和实现类

1）服务类接口：

package com.steven.spring.sysmgr.service;

import java.util.List;
import java.util.Map;

import com.steven.spring.sysmgr.entity.Student;

public interface IStudentService {
	
	public boolean addStudent(Student student);
	
	public boolean delStudent(Integer studentId);
	
	public boolean updateStudent(Student student);
	
	public List<Map<String, Object>> getStudentMap();
	
	public List<Student> getStudentList();
}

2）服务类接口的实现，它依赖于IStudentDao：

package com.steven.spring.sysmgr.service.impl;

import java.util.List;
import java.util.Map;

import com.steven.spring.sysmgr.dao.IStudentDao;
import com.steven.spring.sysmgr.entity.Student;
import com.steven.spring.sysmgr.service.IStudentService;

public class StudentService implements IStudentService{

	private IStudentDao studentDao;
	
	public void setStudentDao(IStudentDao studentDao) {
		this.studentDao = studentDao;
	}

	@Override
	public boolean addStudent(Student student) {
		// TODO Auto-generated method stub
		return this.studentDao.addStudent(student);
	}

	@Override
	public boolean delStudent(Integer studentId) {
		// TODO Auto-generated method stub
		return this.studentDao.delStudent(studentId);
	}

	@Override
	public boolean updateStudent(Student student) {
		// TODO Auto-generated method stub
		return this.studentDao.updateStudent(student);
	}

	@Override
	public List<Map<String, Object>> getStudentMap() {
		// TODO Auto-generated method stub
		return this.studentDao.getStudentMap();
	}

	@Override
	public List<Student> getStudentList() {
		// TODO Auto-generated method stub
		return this.studentDao.getStudentList();
	}

}

3.1.4 spring-IOC的配置文件

spring-IOC的配置文件applicationContext.xml：利用SpringIOC实现服务类和DAO类的依赖

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="
	http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans.xsd">

	<!-- 注册服务类，并描述依赖关系 -->
	<bean id="studentService" class="com.steven.spring.sysmgr.service.impl.StudentService">
		<property name="studentDao" ref="studentDao"/>
	</bean>
	
	<!-- 注册DAO类 -->
	<bean id="studentDao" class="com.steven.spring.sysmgr.dao.impl.StudentDao">
		<property name="jdbcTemplate" ref="jdbcTemplate"/>
	</bean>
	
	<!-- 注册springJdbc查询模板，模板依赖于数据源 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"/>
	</bean>
	
	<!-- 注册spring自带的管理数据库连接的数据源 -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://127.0.0.1:3306/student"/>
		<property name="username" value="root"/>
		<property name="password" value="123"/>
	</bean>
	
</beans>

3.1.5 测试类

package com.steven.spring.sysmgr.test;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.steven.spring.sysmgr.entity.Student;
import com.steven.spring.sysmgr.service.IStudentService;

public class SpringJdbcTest {
	private ApplicationContext ac;
	
	@Before
	public void init(){
		ac = new ClassPathXmlApplicationContext("applicationContext.xml");
	}
	
	//模拟增加功能
	@Test
	public void testAddStudent(){
		IStudentService studentService = (IStudentService) ac.getBean("studentService");

		Student student = new Student();
		student.setName("Jacky");
		student.setAge(35);
		student.setSex(1);
		
		System.out.println(studentService.addStudent(student));
		
	}
	
	//模拟删除功能
	@Test
	public void testDelStudent(){
		IStudentService studentService = (IStudentService) ac.getBean("studentService");
		
		int delStudentId = 100000;
		
		System.out.println(studentService.delStudent(delStudentId));
		
	}
	
	//模拟更改功能
	@Test
	public void testUpdateStudent(){
		IStudentService studentService = (IStudentService) ac.getBean("studentService");
		
		Student student = new Student();
		student.setId(100001);
		student.setName("Lily");
		student.setAge(26);
		student.setSex(0);
		
		System.out.println(studentService.updateStudent(student));
		
	}
	
	//模拟查询功能，返回List<Student>
	@Test
	public void testGetStudentList(){
		IStudentService studentService = (IStudentService) ac.getBean("studentService");
		System.out.println(studentService.getStudentList());
	}
	
	//模拟查询功能，返回List<Map<String, object>>
	@Test
	public void testGetStudentMap(){
		IStudentService studentService = (IStudentService) ac.getBean("studentService");
		System.out.println(studentService.getStudentMap());
	}
}

3.2 实现方式2：注解方式

1、在服务层接口实现类(@Service(...))和Dao层接口实现类(@Repository(...))中分别加入注解表示符，并声明依赖关系（@Autowired）

1）DAO的实现类

package com.steven.spring.sysmgr.dao.impl;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.steven.spring.sysmgr.dao.IStudentDao;
import com.steven.spring.sysmgr.entity.Student;

@Repository("studentDao")
public class StudentDao implements IStudentDao{
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
 	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public boolean addStudent(Student student) {
		boolean flag = false;
		String addSql = "INSERT INTO STUDENT(NAME,AGE,SEX) VALUES(?,?,?)";
		int rows = this.jdbcTemplate.update(addSql, student.getName(), student.getAge(), student.getSex());
		if(rows > 0){
			flag = true;
		}
		return flag;
	}

	@Override
	public boolean delStudent(Integer studentId) {
		boolean flag = false;
		String delSql = "DELETE FROM STUDENT WHERE ID = ?";
		int rows = this.jdbcTemplate.update(delSql, studentId);
		if(rows > 0){
			flag = true;
		}
		
		return flag;
	}

	@Override
	public boolean updateStudent(Student student) {
		boolean flag = false;
		String updateSql = "UPDATE STUDENT SET NAME=?, AGE=?, SEX=? WHERE ID=? ";
		int rows = this.jdbcTemplate.update(updateSql, student.getName(), student.getAge(), student.getSex(), student.getId());
		if(rows > 0){
			flag = true;
		}
		return flag;
	}

	@Override
	public List<Map<String, Object>> getStudentMap() {
		return this.jdbcTemplate.queryForList("SELECT ID,NAME,AGE,SEX FROM STUDENT");
	}

	@Override
	public List<Student> getStudentList() {
		String querySql = "SELECT ID, NAME, AGE, SEX FROM STUDENT";
		//第1种方式：用接口的匿名内部类来实现，优点：可以根据需求做二次改造
		/*return this.jdbcTemplate.query(querySql, new RowMapper<Student>(){

			@Override
			public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
				Student student = new Student();
				student.setId(rs.getInt("ID"));
				student.setName(rs.getString("NAME"));
				student.setAge(rs.getInt("AGE"));
				student.setSex(rs.getInt("SEX"));
				return student;
			}
			
		});*/
		
		//第2种方式：直接将行结果集映射到实体类中，优点：简便
		return this.jdbcTemplate.query(querySql, new BeanPropertyRowMapper<Student>(Student.class));
		
	}
}

2）服务层的实现类

package com.steven.spring.sysmgr.service.impl;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.steven.spring.sysmgr.dao.IStudentDao;
import com.steven.spring.sysmgr.entity.Student;
import com.steven.spring.sysmgr.service.IStudentService;

@Service("studentService")
public class StudentService implements IStudentService{
	@Autowired
	private IStudentDao studentDao;
	
	public void setStudentDao(IStudentDao studentDao) {
		this.studentDao = studentDao;
	}

	@Override
	public boolean addStudent(Student student) {
		// TODO Auto-generated method stub
		return this.studentDao.addStudent(student);
	}

	@Override
	public boolean delStudent(Integer studentId) {
		// TODO Auto-generated method stub
		return this.studentDao.delStudent(studentId);
	}

	@Override
	public boolean updateStudent(Student student) {
		// TODO Auto-generated method stub
		return this.studentDao.updateStudent(student);
	}

	@Override
	public List<Map<String, Object>> getStudentMap() {
		// TODO Auto-generated method stub
		return this.studentDao.getStudentMap();
	}

	@Override
	public List<Student> getStudentList() {
		// TODO Auto-generated method stub
		return this.studentDao.getStudentList();
	}

}

2、在配置文件中进行加入约束和扫描包

<?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"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
		http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context
		http://www.springframework.org/schema/context/spring-context.xsd">

	<!-- 扫描包 -->
	<context:component-scan base-package="com.steven.spring.sysmgr"></context:component-scan>
	
	<!-- 注册springJdbc查询模板，模板依赖于数据源 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"/>
	</bean>
	
	<!-- 注册spring自带的管理数据库连接的数据源 -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://127.0.0.1:3306/student"/>
		<property name="username" value="root"/>
		<property name="password" value="123"/>
	</bean>
</beans>

3.3 实现方式3：Dao实现类继承JdbcDaoSupport

在Dao接口实现里，不用引入JdbcTemplate的接口，而是继承JdbcDaoSupport。因为JdbcDaoSupport类引入了JdbcTemlate。

使用这种方式时，可以不用注入jdbcTemplate，即dao实现类直接依赖于dataSource。因为在类JdbcDaoSupport中会实例化JdbcTemplate。

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

	<!-- 注册服务类，并描述依赖关系 -->
	<bean id="studentService" class="com.steven.spring.sysmgr.service.impl.StudentService">
		<property name="studentDao" ref="studentDao"/>
	</bean>
	
	<!-- 注册DAO类: 当Dao实现类继承了JdbcDaoSupport，可以直接注入dataSource-->
	<bean id="studentDao" class="com.steven.spring.sysmgr.dao.impl.StudentDao">
		<property name="dataSource" ref="dataSource"/>
	</bean>
	
	<!-- 注册spring自带的管理数据库连接的数据源 -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://127.0.0.1:3306/student"/>
		<property name="username" value="root"/>
		<property name="password" value="123"/>
	</bean>
</beans>

3.4 实现方式4：利用properties配置文件方式

1、在src根目录下新建一个配置文件jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/student
jdbc.user=root
jdbc.password=123

2、让spring加载配置文件

1）加载properties配置文件内容

方式1：

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
	<property name="location" value="classpath:jdbc.properties"/>
</bean>

方式2：

首先在xml配置文件中引入context头文件标签。

	<context:property-placeholder location="classpath:jdbc.properties"/>

2）将dataSource的bean标签更改为：

<!-- 注册spring自带的管理数据库连接的数据源 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="${jdbc.driver}"/>
	<property name="url" value="${jdbc.url}"/>
	<property name="username" value="${jdbc.user}"/>
	<property name="password" value="${jdbc.password}"/>
</bean>

3.5 实现方式5：c3p0数据源连接池

1）引入c3p0数据连接池的jar包:c3p0-0.9.0.jar

2）xml配置文件数据源连接部分改为（注意不同点）：

<!-- 注册c3p0数据源连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	<property name="driverClass" value="${jdbc.driver}"/>
	<property name="jdbcUrl" value="${jdbc.url}"/>
	<property name="user" value="${jdbc.user}"/>
	<property name="password" value="${jdbc.password}"/>
</bean>

3.6 实现方式6：dbcp数据源连接池

1）引入dbcp数据源连接池（Apache公司）需要的jar包
commons-dbcp-1.4.jar
commons-pool-1.6.jar