Spring与DAO
4.1 Spring 与 JDBC 模板
除了引入必要的jar包,还要导入sql数据库文件。
09-JDBC-Template
学生类 主要赋值三种属性
public class Student {
private Integer id;
private String name;
private int age;
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(String name, int age) {
super();
this.name = name;
this.age = age;
}
public Integer getId() {
return id;
}
public String getName() {
return name;
}
public int getAge() {
return age;
}
public void setId(Integer id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
学生方法的接口类
public interface IStudentService {
void addStudent(Student student);
void removeById(int id);
void modifyStudent(Student student);
List<String> findAllStudentsNames();
String findStudentNameById(int id);
List<Student> findAllStudents();
Student findStudentById(int id);
}
学生方法的实现类
创建了DAO类对象,调用其方法返回值
public class StudentServiceImpl implements IStudentService {
private IStudentDao dao;
public void setDao(IStudentDao dao) {
this.dao = dao;
}
@Override
public void addStudent(Student student) {
dao.insertStudent(student);
}
@Override
public void removeById(int id) {
dao.deleteById(id);
}
@Override
public void modifyStudent(Student student) {
dao.updateStudent(student);
}
@Override
public List<String> findAllStudentsNames() {
return dao.selectAllStudentsNames();
}
@Override
public String findStudentNameById(int id) {
return dao.selectStudentNameById(id);
}
@Override
public List<Student> findAllStudents() {
return dao.selectAllStudents();
}
@Override
public Student findStudentById(int id) {
return dao.selectStudentById(id);
}
}
DAO接口类,各种查找方法
public interface IStudentDao {
void insertStudent(Student student);
void deleteById(int id);
void updateStudent(Student student);
List<String> selectAllStudentsNames();
String selectStudentNameById(int id);
List<Student> selectAllStudents();
Student selectStudentById(int id);
}
DAO实现类并继承了JdbcDaoSupport来将sql语句写入jdbctemplate
实现了增删改查方法
public class StudentDaoImpl extends JdbcDaoSupport implements IStudentDao {
@Override
public void insertStudent(Student student) {
String sql = "insert into student(name,age) values(?,?)";
this.getJdbcTemplate().update(sql, student.getName(), student.getAge());
}
@Override
public void deleteById(int id) {
String sql = "delete from student where id = ?";
this.getJdbcTemplate().update(sql, id);
}
@Override
public void updateStudent(Student student) {
String sql = "update student set name=?, age=? where id=?";
this.getJdbcTemplate().update(sql, student.getName(), student.getAge(), student.getId());
}
//只查询所有名字
@Override
public List<String> selectAllStudentsNames() {
String sql = "select name from student";
return this.getJdbcTemplate().queryForList(sql, String.class);
}
//只查询指定id的名字
@Override
public String selectStudentNameById(int id) {
String sql = "select name from student where id=?";
return this.getJdbcTemplate().queryForObject(sql, String.class, id);
}
//查询多个目标
@Override
public List<Student> selectAllStudents() {
String sql = "select id,name,age from student";
return this.getJdbcTemplate().query(sql, new StudentRowMapper());
}
@Override
public Student selectStudentById(int id) {
String sql = "select id,name,age from student where id=?";
return this.getJdbcTemplate().queryForObject(sql, new StudentRowMapper(), id);
}
}
因为查询的方法中需要将多个属性赋给一个对象,所以我们用
pubic T queryForObject (String sql, RowMapper m , Object… args)
pubic List query (String sql, RowMapper m, Object… args)
注意,RowMapper 为记录映射接口,用于将查询结果集中每一条记录包装为指定对象。
该接口中有一个方法需要实现:
public Object mapRow(ResultSet rs, int rowNum)
参数 rowNum 表示总的结果集中当前行的行号,但参数 rs 并不表示总的结果集,而是
表示 rowNum 所代表的当前行的记录所定义的结果集,仅仅是当前行的结果。
一般,该方法体中就是实现将查询结果中当前行的数据包装为一个指定对象。
StudentRowMapper类 将多个属性赋给一个对象
public class StudentRowMapper implements RowMapper<Student> {
//rs:当查询出总的结果集后,框架会自动遍历这个结果集,每一次遍历的一行数据,都会被存放到
// 这个方法的rs参数中。也就是说,这里的rs代表的是一行数据,并非所有查询结果。换个角度
// 来说,只要能执行到这个方法,就说明这里的rs不会是空的
@Override
public Student mapRow(ResultSet rs, int arg1) throws SQLException {
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
return student;
}
}
配置文件
1、C3P0数据源找到注册驱动driver、url定位符、用户名、密码四个连接属性。
2、从属性文件读取数据库连接信息,使用context:property-placeholder/ 方式导入属性配置文件,要求在 Spring 配置文件头部加入 context 的约束,在属性文件中给出四个属性的值。
3、把数据源赋给DAO类,会自动生成JDBC 模板对象。
4、把DAO类再给学生实现方法类
JDBC 模板类 JdbcTemplate 从其父类 JdbcAccessor 继承了一个属性 dataSource,用于接收
数据源。
<!-- 注册数据源:C3P0 -->
<bean id="myDataSource" 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>
这里不用//<!-- 注册属性文件:方式一
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:jdbc.properties"/>
</bean>-->
<!-- 注册属性文件:方式二 -->
<context:property-placeholder location="classpath:jdbc.properties"/>
这里不用//<!-- 注册JdbcTemplate和Dao
<bean id="myJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="myDataSource"/>
//</bean>
//<bean id="studentDao" class="com.bjpowernode.dao.StudentDaoImpl">
//<property name="jdbcTemplate" ref="myJdbcTemplate"/>
//</bean> -->
<!-- 注册Dao -->
<bean id="studentDao" class="com.bjpowernode.dao.StudentDaoImpl">
<property name="dataSource" ref="myDataSource"/>
</bean>
<!-- 注册Service -->
<bean id="studentService" class="com.bjpwernode.service.StudentServiceImpl">
<property name="dao" ref="studentDao"/>
</bean>
jdbc.properties配置文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test
jdbc.user=root
jdbc.password=333
测试代码:
public class MyTest {
private IStudentService service;
@Before
public void before() {
String resource = "applicationContext.xml";
ApplicationContext ac = new ClassPathXmlApplicationContext(resource);
service = (IStudentService) ac.getBean("studentService");
}
//增
@Test
public void test01() {
Student student = new Student("张三", 23);
service.addStudent(student);
}
//删
@Test
public void test02() {
service.removeById(2);
}
//修改
@Test
public void test03() {
Student student = new Student("张三", 23);
student.setId(3);
service.modifyStudent(student);
}
//查询
@Test
public void test04() {
List<String> names = service.findAllStudentsNames();
System.out.println(names);
}
@Test
public void test05() {
String name = service.findStudentNameById(3);
System.out.println(name);
}
@Test
public void test06() {
List<Student> students = service.findAllStudents();
for(Student student : students) {
System.out.println(student);
}
}
@Test
public void test07() {
Student student = service.findStudentById(3);
System.out.println(student);
}
}
可以使用Navicat查看结果
4.2 Spring的事务管理
举例:购买股票—transaction_buystock 项目
本例要实现模拟购买股票。存在两个实体:银行账户 Account 与股票账户 Stock。当要
购买股票时,需要从 Account 中扣除相应金额的存款,然后在 Stock 中增加相应的股票数量。
而在这个过程中,可能会抛出一个用户自定义的异常。异常的抛出,将会使两个操作回滚。
先建表
定义实体类账户
public class Account {
private Integer aid;
private String aname;
private double balance; //余额
public Account() {
super();
// TODO Auto-generated constructor stub
}
public Account(String aname, double balance) {
super();
this.aname = aname;
this.balance = balance;
}
public Integer getAid() {
return aid;
}
public String getAname() {
return aname;
}
public double getBalance() {
return balance;
}
public void setAid(Integer aid) {
this.aid = aid;
}
public void setAname(String aname) {
this.aname = aname;
}
public void setBalance(double balance) {
this.balance = balance;
}
@Override public String toString() { return "Account [aid=" + aid +
", aname=" + aname + ", balance=" + balance + "]"; }
}
定义实体类股票
public class Stock {
private Integer sid;
private String sname; // 股票名称
private int count; // 股票数量
public Stock() {
super();
// TODO Auto-generated constructor stub
}
public Stock(String sname, int count) {
super();
this.sname = sname;
this.count = count;
}
public Integer getSid() {
return sid;
}
public String getSname() {
return sname;
}
public int getCount() {
return count;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public void setSname(String sname) {
this.sname = sname;
}
public void setCount(int count) {
this.count = count;
}
@Override
public String toString() {
return "Stock [sid=" + sid + ", sname=" + sname + ", count=" + count + "]";
}
}
定义Dao层实现股票和账户的增和改,这里接口类就省略了
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {
@Override
public void insertAccount(String aname, double money) {
String sql = "insert into account(aname, balance) values(?,?)";
this.getJdbcTemplate().update(sql, aname, money);
}
@Override
public void updateAccount(String aname, double money, boolean isBuy) {
String sql = "update account set balance=balance+? where aname=?";
if (isBuy) {
sql = "update account set balance=balance-? where aname=?";
}
this.getJdbcTemplate().update(sql, money, aname);
}
}
public class StockDaoImpl extends JdbcDaoSupport implements IStockDao {
@Override
public void insertStock(String sname, int amount) {
String sql = "insert into stock(sname, count) values(?,?)";
this.getJdbcTemplate().update(sql, sname, amount);
}
@Override
public void updateStock(String sname, int amount, boolean isBuy) {
String sql = "update stock set count=count-? where sname=?";
if (isBuy) {
sql = "update stock set count=count+? where sname=?";
}
this.getJdbcTemplate().update(sql, amount, sname);
}
}
定义service实现类,这里同样省略了接口。调用了Dao类的方法,并设置了一个购买异常。
public class BuyStockServiceImpl implements IBuyStockService {
private IAccountDao adao;
private IStockDao sdao;
public void setAdao(IAccountDao adao) {
this.adao = adao;
}
public void setSdao(IStockDao sdao) {
this.sdao = sdao;
}
@Override
public void openAccount(String aname, double money) {
adao.insertAccount(aname, money);
}
@Override
public void openStock(String sname, int amount) {
sdao.insertStock(sname, amount);
}
@Override
public void buyStock(String aname, double money, String sname, int amount) throws BuyStockException{
boolean isBuy = true;
adao.updateAccount(aname, money, isBuy);
if (1 == 1) {
throw new BuyStockException("购买股票异常");
}
sdao.updateStock(sname, amount, isBuy);
}
定义异常类
public class BuyStockException extends Exception {
public BuyStockException() {
super();
// TODO Auto-generated constructor stub
}
public BuyStockException(String message) {
super(message);
// TODO Auto-generated constructor stub
}
}
jdbc配置文件同上
Spring配置文件:约束、xml注入、事务管理、AOP配置
<?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"
xmlns:tx="http://www.springframework.org/schema/tx"
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/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- ======================= IoC ============================ -->
<!-- 注册数据源:C3P0 -->
<bean id="myDataSource" 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>
<!-- 注册属性文件 -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!-- 注册Dao -->
<bean id="accountDao" class="com.bjpowernode.dao.AccountDaoImpl">
<property name="dataSource" ref="myDataSource"/>
</bean>
<!-- 注册Dao -->
<bean id="stockDao" class="com.bjpowernode.dao.StockDaoImpl">
<property name="dataSource" ref="myDataSource"/>
</bean>
<!-- 注册Service -->
<bean id="buyStockService" class="com.bjpowernode.service.BuyStockServiceImpl">
<property name="adao" ref="accountDao"/>
<property name="sdao" ref="stockDao"/>
</bean>
<!-- ======================= AOP ============================ -->
<!-- 注册事务管理器 -->
<bean id="myTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="myDataSource"/>
</bean>
<tx:advice id="txAdvice" transaction-manager="myTransactionManager">
<tx:attributes>
<!-- 这里指定的是:为每一个连接点指定所要应用的事务属性 -->
<tx:method name="open*" isolation="DEFAULT" propagation="REQUIRED"/>
<tx:method name="buyStock" isolation="DEFAULT" propagation="REQUIRED" rollback-for="BuyStockException"/>
</tx:attributes>
</tx:advice>
<!-- AOP配置 -->
<aop:config>
<!-- 这里指定的是切入点 -->
<aop:pointcut expression="execution(* *..service.*.*(..))" id="myPointcut"/>
<aop:advisor advice-ref="txAdvice" pointcut-ref="myPointcut"/>
</aop:config>
</beans>
测试类
public class MyTest {
private IBuyStockService service;
@Before
public void before() {
String resource = "applicationContext.xml";
ApplicationContext ac = new ClassPathXmlApplicationContext(resource);
service = (IBuyStockService) ac.getBean("buyStockService");
}
@Test
public void test01() {
service.openAccount("张三", 10000);
service.openStock("北京动力节点", 0);
}
@Test
public void test02() throws BuyStockException {
service.buyStock("张三", 2000, "北京动力节点", 5);
}
}