Spring AOP实现数据库事务控制

声明：本栏目所使用的素材都是凯哥学堂VIP学员所写，学员有权匿名，对文章有最终解释权；凯哥学堂旨在促进VIP学员互相学习的基础上公开笔记。

利用Spring AOP实现简单的数据库事务控制：

1.需求：

现在有一个school库，里面有一张student表以及一张studentLog表，student表用于记录学生信息，studentLog表则用于记录student表的日志信息。要求对student表进行操作时，将操作信息记录日志到studentLog表里，并且要有事务控制，当用户对student表操作失败或程序出现异常时，事务需要进行回滚，两张表都不能写入数据，必须保持两张表的数据一致。

student表结构如下：

studentLog表结构如下：

2.编写两张表格字段的封装类：

package org.zero01.pojo;
import org.springframework.stereotype.Component;
@Component("stu")
public class Student {
    private int sid;
    private String sname;
    private int age;
    private String sex;
    private String address;
    public int getSid() {
        return sid;
    }
    public void setSid(int sid) {
        this.sid = sid;
    }
    public String getSname() {
        return sname;
    }
    public void setSname(String sname) {
        this.sname = sname;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
}

package org.zero01.pojo;
import java.util.Date;
public class StudentLog {
    private int log_id;
    private int sid;
    private String sname;
    private int age;
    private String sex;
    private String address;
    private String operation_type;
    private Date log_time;
    public int getLog_id() {
        return log_id;
    }
    public void setLog_id(int log_id) {
        this.log_id = log_id;
    }
    public int getSid() {
        return sid;
    }
    public void setSid(int sid) {
        this.sid = sid;
    }
    public String getSname() {
        return sname;
    }
    public void setSname(String sname) {
        this.sname = sname;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public String getOperation_type() {
        return operation_type;
    }
    public void setOperation_type(String operation_type) {
        this.operation_type = operation_type;
    }
    public Date getLog_time() {
        return log_time;
    }
    public void setLog_time(Date log_time) {
        this.log_time = log_time;
    }
}

3.编写数据层以及逻辑层的接口：

package org.zero01.dao;
import org.zero01.pojo.Student;
import java.util.List;
public interface DAO {
    public int insert(Student student) throws Exception;
    public int delete(int sid) throws Exception;
    public Student selectById(int sid) throws Exception;
    public List<Student> selectAll() throws Exception;
    public int update(Student student) throws Exception;
}
package org.zero01.dao;
import org.zero01.pojo.StudentLog;
import java.util.List;
public interface LogDAO {
    public int insert(StudentLog studentLog)throws Exception;
    public int delete(int log_id)throws Exception;
    public List<StudentLog> selectAll()throws Exception;
    public int update(StudentLog studentLog)throws Exception;
}
package org.zero01.service;
import org.zero01.pojo.Student;
import java.util.List;
public interface School {
    public int enterSchool(Student student) throws Exception;
    public int deleteStudentData(int sid) throws Exception;
    public Student searchStudentData(int sid) throws Exception;
    public List<Student> searchStudentsData() throws Exception;
    public int alterStudentData(Student student) throws Exception;
}

4.编写切面类，控制数据库事务：

package org.zero01.aop;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
@Aspect
@Component("tranAOP")
public class TransactionAOP {
    private final DataSource dataSource;
    @Autowired
    public TransactionAOP(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    // 保存连接对象的池子
    private ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
    public ThreadLocal<Connection> getThreadLocal() {
        return threadLocal;
    }
    @Pointcut("execution(* org.zero01.service.*.*(..))")
    private void dao() {
    }
    /**
    * @Description: 控制数据库事务
    * @Param: 
    * @return: 
    * @Author: 01
    * @Date: 2018/3/6
    */ 
    @Around("dao()")
    public Object tranController(ProceedingJoinPoint proceedingJoinPoint) throws SQLException {
        Connection connection = null;
        Object result = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            threadLocal.set(connection);
            // 把调用传递到目标方法上
            result = proceedingJoinPoint.proceed();
            connection.commit();
        } catch (Throwable t) {
            if (connection != null) {
                connection.rollback();
                t.printStackTrace();
            }
        } finally {
            if (connection != null) {
                connection.setAutoCommit(true);
                connection.close();
            }
        }
        return result;
    }
}

5.编写数据层的实现类：

package org.zero01.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.zero01.aop.TransactionAOP;
import org.zero01.pojo.Student;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@Component("stuDAO")
public class StudentDAO implements DAO {
    @Autowired
    private TransactionAOP trabAOP;
    /**
     * @Description: 添加学生数据
     * @Param: 表格的字段封装对象
     * @return: 返回插入行的id
     * @Author: 01
     * @Date: 2018/3/6
     */
    public int insert(Student student) throws SQLException {
        Connection connection = trabAOP.getThreadLocal().get();
        String sql = "INSERT INTO student(sname,age,sex,address) VALUES (?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, student.getSname());
        preparedStatement.setInt(2, student.getAge());
        preparedStatement.setString(3, student.getSex());
        preparedStatement.setString(4, student.getAddress());
        preparedStatement.executeUpdate();
        ResultSet resultSet = connection.createStatement().executeQuery("SELECT LAST_INSERT_ID()");
        if (resultSet.next()) {
            return resultSet.getInt(1);
        }
        return 0;
    }
    /**
     * @Description: 删除某个学生数据
     * @Param:  要删除行的id
     * @return: 返回影响的行数
     * @Author: 01
     * @Date: 2018/3/6
     */
    public int delete(int sid) throws SQLException {
        Connection connection = trabAOP.getThreadLocal().get();
        String sql = "DELETE FROM student WHERE sid=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, sid);
        return preparedStatement.executeUpdate();
    }
    /**
    * @Description: 按id查找某个学生的数据
    * @Param: 要查询行的id
    * @return: 返回查询出来的学生数据
    * @Author: 01
    * @Date: 2018/3/6
    */
    public Student selectById(int sid) throws SQLException {
        Connection connection = trabAOP.getThreadLocal().get();
        String sql = "SELECT * FROM student WHERE sid=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, sid);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            Student student = new Student();
            student.setSid(resultSet.getInt("sid"));
            student.setSname(resultSet.getString("sname"));
            student.setAge(resultSet.getInt("age"));
            student.setSex(resultSet.getString("sex"));
            student.setAddress(resultSet.getString("address"));
            return student;
        }
        return null;
    }
    /**
    * @Description: 查询全部学生的数据
    * @return: 返回查询出来的数据集合
    * @Author: 01
    * @Date: 2018/3/6
    */
    public List<Student> selectAll() throws Exception {
        Connection connection = trabAOP.getThreadLocal().get();
        String sql = "SELECT * FROM student";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Student> logList = new ArrayList<Student>();
        while (resultSet.next()) {
            Student student = new Student();
            student.setSid(resultSet.getInt("sid"));
            student.setSname(resultSet.getString("sname"));
            student.setAge(resultSet.getInt("age"));
            student.setSex(resultSet.getString("sex"));
            student.setAddress(resultSet.getString("address"));
            logList.add(student);
        }
        return logList;
    }
    /**
    * @Description: 修改某个学生的数据
    * @Param: 表格的字段封装对象
    * @return: 返回影响行数
    * @Author: 01
    * @Date: 2018/3/6
    */
    public int update(Student student) throws SQLException {
        Connection connection = trabAOP.getThreadLocal().get();
        String sql = "UPDATE student SET sname=?,age=?,sex=?,address=? WHERE sid=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, student.getSname());
        preparedStatement.setInt(2, student.getAge());
        preparedStatement.setString(3, student.getSex());
        preparedStatement.setString(4, student.getAddress());
        preparedStatement.setInt(5, student.getSid());
        return preparedStatement.executeUpdate();
    }
}

package org.zero01.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.zero01.aop.TransactionAOP;
import org.zero01.pojo.StudentLog;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
@Component("stuLogDAO")
public class StudentLogDAO implements LogDAO {
    @Autowired
    private TransactionAOP trabAOP;
    /**
    * @Description: 添加日志记录
    * @Param: 表格的字段封装对象
    * @return: 返回影响行数
    * @Author: 01
    * @Date: 2018/3/6
    */
    public int insert(StudentLog studentLog) throws Exception {
        Connection connection = trabAOP.getThreadLocal().get();
        String sql;
        PreparedStatement preparedStatement;
        if (studentLog.getOperation_type().equals("selectAll")) {
            sql = "INSERT INTO studentlog(operation_type,log_time) VALUES ('selectAll',sysdate())";
            preparedStatement = connection.prepareStatement(sql);
            return preparedStatement.executeUpdate();
        }
        sql = "INSERT INTO studentlog(sid,sname,age,sex,address,operation_type,log_time) VALUES (?,?,?,?,?,?,sysdate())";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, studentLog.getSid());
        preparedStatement.setString(2, studentLog.getSname());
        preparedStatement.setInt(3, studentLog.getAge());
        preparedStatement.setString(4, studentLog.getSex());
        preparedStatement.setString(5, studentLog.getAddress());
        preparedStatement.setString(6, studentLog.getOperation_type());
        return preparedStatement.executeUpdate();
    }
    /**
     * @Description: 删除日志记录
     * @Param: 要删除行的id
     * @return: 返回影响行数
     * @Author: 01
     * @Date: 2018/3/6
     */
    public int delete(int log_id) throws Exception {
        Connection connection = trabAOP.getThreadLocal().get();
        String sql = "DELETE FROM studentlog WHERE log_id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, log_id);
        return preparedStatement.executeUpdate();
    }
    /**
     * @Description: 查询全部日志记录
     * @return: 返回查询出来的数据集合
     * @Author: 01
     * @Date: 2018/3/6
     */
    public List<StudentLog> selectAll() throws Exception {
        Connection connection = trabAOP.getThreadLocal().get();
        String sql = "SELECT * FROM studentlog";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<StudentLog> logList = new ArrayList<StudentLog>();
        while (resultSet.next()) {
            StudentLog studentLog = new StudentLog();
            studentLog.setLog_id(resultSet.getInt("log_id"));
            studentLog.setSid(resultSet.getInt("sid"));
            studentLog.setSname(resultSet.getString("sname"));
            studentLog.setAge(resultSet.getInt("age"));
            studentLog.setSex(resultSet.getString("sex"));
            studentLog.setAddress(resultSet.getString("address"));
            studentLog.setOperation_type(resultSet.getString("operation_type"));
            studentLog.setLog_time(resultSet.getTimestamp("log_time"));
            logList.add(studentLog);
        }
        return logList;
    }
    /**
     * @Description: 修改某条日志记录
     * @Param: 表格的字段封装对象
     * @return: 返回影响行数
     * @Author: 01
     * @Date: 2018/3/6
     */
    public int update(StudentLog studentLog) throws Exception {
        Connection connection = trabAOP.getThreadLocal().get();
        String sql = "UPDATE student SET sid=?,sname=?,age=?,sex=?,address=?,operation_type=?,log_time=? WHERE log_id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, studentLog.getSid());
        preparedStatement.setString(2, studentLog.getSname());
        preparedStatement.setInt(3, studentLog.getAge());
        preparedStatement.setString(4, studentLog.getSex());
        preparedStatement.setString(5, studentLog.getAddress());
        preparedStatement.setString(6, studentLog.getOperation_type());
        preparedStatement.setDate(7, (Date) studentLog.getLog_time());
        preparedStatement.setInt(8, studentLog.getLog_id());
        return preparedStatement.executeUpdate();
    }
}

6.编写逻辑层的实现类：

package org.zero01.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.zero01.dao.DAO;
import org.zero01.dao.LogDAO;
import org.zero01.pojo.Student;
import org.zero01.pojo.StudentLog;
import java.util.List;
@Component("schoolService")
public class SchoolService implements School {
    @Autowired
    private DAO dao;
    @Autowired
    private LogDAO logDAO;
    /**
     * @Description: 映射两张表格中相同的字段
     * @Author: 01
     * @Date: 2018/3/6
     */
    public StudentLog stuMap(Student student, String operation_type) {
        StudentLog studentLog = new StudentLog();
        if (student != null) {
            studentLog.setSid(student.getSid());
            studentLog.setSname(student.getSname());
            studentLog.setAge(student.getAge());
            studentLog.setSex(student.getSex());
            studentLog.setAddress(student.getAddress());
        }
        studentLog.setOperation_type(operation_type);
        return studentLog;
    }
    /**
     * @Description: 入学
     * @Param:
     * @return:
     * @Author: 01
     * @Date: 2018/3/6
     */
    public int enterSchool(Student student) throws Exception {
        int sid = dao.insert(student);
        student.setSid(sid);
        return logDAO.insert(stuMap(student, "add"));
    }
    /**
     * @Description: 删除学生数据
     * @Param:
     * @return:
     * @Author: 01
     * @Date: 2018/3/6
     */
    public int deleteStudentData(int sid) throws Exception {
        Student student = dao.selectById(sid);
        if (student != null) {
            student.setSid(sid);
            dao.delete(sid);
        } else {
            return 0;
        }
        return logDAO.insert(stuMap(student, "delete"));
    }
    /**
     * @Description: 搜索某个学生的资料
     * @Param:
     * @return:
     * @Author: 01
     * @Date: 2018/3/6
     */
    public Student searchStudentData(int sid) throws Exception {
        Student student = dao.selectById(sid);
        if (student != null) {
            logDAO.insert(stuMap(student, "selectById"));
        } else {
            return null;
        }
        return student;
    }
    /**
     * @Description: 搜索全部学生的资料
     * @Param:
     * @return:
     * @Author: 01
     * @Date: 2018/3/6
     */
    public List<Student> searchStudentsData() throws Exception {
        List<Student> students = dao.selectAll();
        logDAO.insert(stuMap(null, "selectAll"));
        return students;
    }
    /**
     * @Description: 修改某个学生的资料
     * @Param:
     * @return:
     * @Author: 01
     * @Date: 2018/3/6
     */
    public int alterStudentData(Student studentNew) throws Exception {
        Student studentOld = dao.selectById(studentNew.getSid());
        int row = dao.update(studentNew);
        logDAO.insert(stuMap(studentOld, "alter"));
        return row;
    }
}

经过测试后两张表格的内容如下：

小结：

在以上代码中，我们通过Spring AOP编写了一个切面类，完成了一个简单的事务控制。事务控制与数据库连接对象的开关都交给切面类去完成，这样我们的JDBC代码里就不需要去控制事务了，只需要关注核心的SQL语句即可，也减少了很多重复的代码。
从这个例子里，我们认识到了AOP技术如何应用在事务管理上，也知道了要将一些非核心关注点，但是又很多地方需要使用的功能交给切面去完成，并且需要把切面模块化，这样才能提高切面的复用性。