MySQL中使用触发器来记录对表的操作日志

MySQL中使用触发器来记录对表的操作日志

在项目中经常需要记录对表的操作日志，通常是写到程序中，这样无形中增加了程序的业务流程，即使采用了面向切面的编程技术，但是整个系统效率仍然不够高。本文通过对表创建触发器来记录对表的操作日志。

数据库中的表

首先准备两张用于测试的表，一张用于插入数据，另一张用于记录对表的操作日志，
表结构

1. test

字段名	类型
id	int(10)
info	varchar(20)

2. test_log

字段名	类型	备注
id	int(10)	自增列
action	text
writer	varchar(20)
update_date	timestamp

触发器

CREATE TRIGGER trigger_insert AFTER INSERT ON test FOR EACH ROW INSERT INTO test_log (
	`action`,
	`writer`,
	`update_date`
)
VALUES
	(
		'INSERT',
		CURRENT_USER(),
		CURRENT_TIMESTAMP()
	);

CREATE TRIGGER trigger_update AFTER UPDATE ON test FOR EACH ROW INSERT INTO test_log (
	`action`,
	`writer`,
	`update_date`
)
VALUES
	(
		'UPDATE',
		CURRENT_USER(),
		CURRENT_TIMESTAMP()
	);

CREATE TRIGGER trigger_delete AFTER DELETE ON test FOR EACH ROW INSERT INTO test_log (
	`action`,
	`writer`,
	`update_date`
)
VALUES
	(
		'DELETE',
		CURRENT_USER(),
		CURRENT_TIMESTAMP()
	);

测试

这里我使用SpringJDBC模板，具体代码如下所示

1. DataSourceConfiguration.java （配置类）

import java.beans.PropertyVetoException;

import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import uxdb.uxdemo4.service.TestService;
import uxdb.uxdemo4.service.impl.TestServiceImpl;

@Configuration
public class DataSourceConfiguration {
	
	@Bean
	public DataSource uxDataSource() throws PropertyVetoException {
		ComboPooledDataSource ds = new ComboPooledDataSource();
		ds.setDriverClass("com.mysql.jdbc.Driver");
		ds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
		ds.setUser("root");
		ds.setPassword("root");
		ds.setAcquireIncrement(2);
		ds.setAcquireRetryAttempts(0);
		ds.setAcquireRetryDelay(1000);
		ds.setMaxIdleTime(3600);
		ds.setMaxStatements(0);
		return ds;
		
	}
	
	@Bean 
	public JdbcTemplate jdbcTemplate(DataSource dataSource) {
		return new JdbcTemplate(dataSource);
	}
	
//	@Bean
//	public StudentService studentService() {
//		return new StudentServiceImpl();
//	}
	
	@Bean
	public TestService testService() {
		return new TestServiceImpl();
	}
}

2. TestBean.java（Bean）

public class TestBean {
	private int id;
	private String info;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getInfo() {
		return info;
	}
	public void setInfo(String info) {
		this.info = info;
	}
	@Override
	public String toString() {
		return "TestBean [id=" + id + ", info=" + info + "]";
	}
}

3. TestService.java（服务接口）

public interface TestService {
	public void save(TestBean test);

	public void update(TestBean test);

	public TestBean getTest(int id);

	public List<TestBean> getTestBean();

	public void delete(int id);

	public int getCount();
}

4. TestServiceImpl .java(服务实现类)

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import uxdb.uxdemo4.bean.TestBean;
import uxdb.uxdemo4.service.TestService;

public class TestServiceImpl implements TestService {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public void save(TestBean test) {
		jdbcTemplate.update("insert into test(id,info) values(?,?)", 
				new Object[] {test.getId(),test.getInfo()}, 
				new int[] {java.sql.Types.INTEGER,java.sql.Types.VARCHAR});

	}

	@Override
	public void update(TestBean test) {
		jdbcTemplate.update("update test set info = ? where id = ?", 
				new Object[] {test.getInfo(),test.getId()}, 
				new int[] {java.sql.Types.VARCHAR,java.sql.Types.INTEGER});

	}

	@Override
	public TestBean getTest(int id) {
		// TODO Auto-generated method stub
		return (TestBean) jdbcTemplate.queryForObject("select * from test where id = ?", new Object[] {id}, new int[] {java.sql.Types.INTEGER}, new TestRowMapper());
	}

	public class TestRowMapper implements RowMapper<TestBean>{

		@Override
		public TestBean mapRow(ResultSet rs, int rowNum) throws SQLException {
			TestBean test = new TestBean();
			test.setId(rs.getInt("id"));
			test.setInfo(rs.getString("info"));
			return test;
		}
		
	}
	@Override
	public List<TestBean> getTestBean() {
		return (List<TestBean>) jdbcTemplate.query("select * from test", new TestRowMapper());
	}

	@Override
	public void delete(int id) {
		jdbcTemplate.update("delete from test where id = ?", new Object[] {id}, new int[] {java.sql.Types.INTEGER});
	}

	@Override
	public int getCount() {
		String sql = "select count(*) from test t";
		Integer count = jdbcTemplate.queryForObject(sql, new Object[] {}, Integer.class);
		return count;
	}

}

5. MyMain.java(程序入口)

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import uxdb.uxdemo4.bean.TestBean;
import uxdb.uxdemo4.conf.DataSourceConfiguration;
import uxdb.uxdemo4.service.TestService;

public class MyMain {
	private static TestService testService;
	private static ApplicationContext act;
	
	public static void main(String[] args) {
		act = new AnnotationConfigApplicationContext(DataSourceConfiguration.class);
		//act = new ClassPathXmlApplicationContext("Application-context.xml");
		testService = (TestService) act.getBean("testService");
		List<TestBean> testList;
		for (int i = 1; i <= 10; i++) {
			TestBean test = new TestBean();
			test.setId(i);
			test.setInfo("user"+i);
			testService.save(test);
		}
		
		testList = testService.getTestBean();
		for(TestBean c : testList) {
			System.out.println(c);
		}
		
		for (int i = 1; i <= 10; i++) {
			TestBean test = new TestBean();
			test.setId(i);
			test.setInfo("userxx"+i);
			testService.update(test);
		}
		
		testList = testService.getTestBean();
		for(TestBean c : testList) {
			System.out.println(c);
		}
		
		for (int i = 1; i <= 10; i++) {
			testService.delete(i);
		}
		
		System.out.println(testService.getCount());
	}
}

运行效果

1. Console中对表的操作
   
2. test_log中记录的表的操作日志