Spring 对 JDBC 的支持 (十)
Spring 对象 Jdbc 的支持:
-
Spring 对 jdbc 的操作进行了简化!
-
提供了 JdbcTemplate 模板工具类,简化 jdbc 的操作!
如果想用 spring jdbc 功能,先引入 jar 文件:
spring-jdbc-3.2.5.RELEASE.jar 工具类包
spring-tx-3.2.5.RELEASE.jar 事务支持依赖包
连接池、数据库驱动包!
开发步骤:
-
原始的 jdbc 操作代码
-
对连接管理
-
对 jdbc 操作进行简化
JdbcTemplate 工具类 Api
JdbcTemplate 与 DataSource 依赖关系
<?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:aop="http://www.springframework.org/schema/aop"
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
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 加载Proerties配置文件 -->
<context:property-placeholder location="classpath:com/matrix42/h_jdbc/db.properties"/>
<!-- 1. 实例化连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${driverClass}"></property>
<property name="jdbcUrl" value="${jdbcUrl}"></property>
<property name="user" value="${user}"></property>
<property name="password" value="${password}"></property>
<property name="initialPoolSize" value="${initialPoolSize}"></property>
<property name="maxPoolSize" value="${maxPoolSize}"></property>
<property name="acquireIncrement" value="${acquireIncrement}"></property>
</bean>
<!-- 2. 创建JdbcTemplate对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 实例化dao -->
<bean id="deptDao" class="com.matrix42.h_jdbc.DeptDao">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
</beans>
import java.io.Serializable;
import java.util.List;
// 接口
public interface IDeptDao {
public void save(Dept dept);
public void update(Dept dept);
public void delete(Serializable id);
public Dept findById(Serializable id);
public List<Dept> getAll();
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DeptDao_1 {
// 1. 原始jdbc代码
public void save(Dept dept) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql:///hib_demo","root","root");
PreparedStatement pstmt = con.prepareStatement("insert into t_dept(deptName) values('test')");
pstmt.executeUpdate();
// 关闭
pstmt.close();
con.close();
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.sql.DataSource;
public class DeptDao_2 {
// 接收容器注入的DataSource对象
private DataSource dataSource;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
// 1. 原始jdbc代码
public void save(Dept dept) throws Exception {
Connection con = dataSource.getConnection();
PreparedStatement pstmt = con.prepareStatement("insert into t_dept(deptName) values('test')");
pstmt.executeUpdate();
// 关闭
pstmt.close();
con.close();
}
}
public class Dept {
private int id;
private String name;
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;
}
}
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
public class DeptDao implements IDeptDao {
// 接收容器注入的JdbcTemplate对象
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
// 1. 原始jdbc代码
public void save(Dept dept) {
jdbcTemplate.update("insert into t_dept(deptName) values(?)", dept.getName());
}
@Override
public void delete(Serializable id) {
jdbcTemplate.update("delete from t_dept where id=?", id);
}
@Override
public void update(Dept dept) {
jdbcTemplate.update("update t_dept set deptName=? where id=?", dept.getName(),dept.getId());
}
@Override
public Dept findById(Serializable id) {
// queryForList 把每一行都封装为map对象,再添加到list中
// List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from t_dept");
// 传入类型参数,表示查询的列的类型; 这里只能查询一列
// List<String> list = jdbcTemplate.queryForList("select deptName from t_dept", String.class);
List<Dept> list = jdbcTemplate.query("select * from t_dept where id=?", new MyRowMapper(), id);
return (list!=null&&list.size()>0)?list.get(0):null;
}
@Override
public List<Dept> getAll() {
List<Dept> list = jdbcTemplate.query("select * from t_dept", new MyRowMapper());
return list;
}
// 封装Springjdbc查询的结果集
class MyRowMapper implements RowMapper<Dept>{
// 如何解析一行
@Override
public Dept mapRow(ResultSet rs, int rowNum) throws SQLException {
Dept dept = new Dept();
dept.setId(rs.getInt("id"));
dept.setName(rs.getString("deptName"));
return dept;
}
}
}
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class App {
private ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml",App.class);
@Test
public void testApp() throws Exception {
IDeptDao deptDao = (IDeptDao) ac.getBean("deptDao");
// deptDao.save(new Dept());
// deptDao.delete(9);
// Dept dept = new Dept();
// dept.setId(3);
// dept.setName("人事部");
// deptDao.update(dept);
// 查询方法
Dept dept = deptDao.findById(1);
System.out.println(dept);
System.out.println(dept.getName());
// System.out.println(deptDao.getAll());
}
}
db.properties
driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql:///hib_demo
user=root
password=root
initialPoolSize=3
maxPoolSize=6
acquireIncrement=2