声明：本栏目所使用的素材都是凯哥学堂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() {
}
@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;
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;
}
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();
}
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;
}
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;
}
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();
}
}
| 
湘公网安备 43102102000065号