事务的概念
一个事务里包含多步骤数据库操作,比如查询、插入、删除等等。
操作这些时候,难免会遇到一些错误。如果遇到错误了,我们就进行回滚,返回到事务执行前的状态。
代码框架示意理解事务
try{
insert();
update();
delete();
//进行提交
commit();
}catch(){
//出错时进行回滚
rollback();
System.out.println("出错了,已经回滚了");
}
接下来就是代码的解读了
在操作数据库之前,我给大家一个数据库。
也就是SQL语句,运行下就有数据库了
/*
SQLyog Ultimate v11.24 (32 bit)
MySQL - 5.6.39 : Database - af_school
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`af_school` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `af_school`;
/*Table structure for table `admin` */
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`username` varchar(32) DEFAULT NULL,
`password` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `admin` */
insert into `admin`(`username`,`password`) values ('shaofa','123456');
/*Table structure for table `book` */
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`author` varchar(32) DEFAULT NULL,
`title` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `book` */
insert into `book`(`author`,`title`) values ('小张','非常好\'零基础\'的入门教程'),('小王','美丽\'人\'生'),('小王','美丽\'人\'生'),('邵发','谁与争\'风\''),('小张','非常好\'零基础\'的入门教程');
/*Table structure for table `exam` */
DROP TABLE IF EXISTS `exam`;
CREATE TABLE `exam` (
`id` int(11) NOT NULL COMMENT '学号',
`chinese` int(11) DEFAULT NULL COMMENT '语文成绩',
`english` int(11) DEFAULT NULL COMMENT '英语成绩',
`math` int(11) DEFAULT NULL COMMENT '数学成线',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `exam` */
insert into `exam`(`id`,`chinese`,`english`,`math`) values (20180001,89,90,98),(20180002,78,82,93),(20180003,90,73,95),(20180004,88,98,83),(20180005,96,79,75),(20180006,77,98,82);
/*Table structure for table `leave_event` */
DROP TABLE IF EXISTS `leave_event`;
CREATE TABLE `leave_event` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '请假记录ID',
`stuId` int(11) NOT NULL COMMENT '学生ID',
`daysFrom` date DEFAULT NULL COMMENT '哪天开始',
`daysTo` date DEFAULT NULL COMMENT '哪天结束',
`type` tinyint(4) DEFAULT NULL COMMENT '类型,0病假,1事假',
`reason` varchar(256) DEFAULT NULL COMMENT '事由',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*Data for the table `leave_event` */
insert into `leave_event`(`id`,`stuId`,`daysFrom`,`daysTo`,`type`,`reason`) values (1,20180001,'2018-01-02','2018-01-03',0,'感冒'),(2,20180001,'2018-03-06','2018-03-08',0,'发烧'),(3,20180003,'2018-03-01','2018-03-07',1,'出国旅游'),(4,20180005,'2018-03-07','2018-03-07',1,'家里有事'),(5,20180003,'2018-03-17','2018-03-18',0,'不舒服'),(7,20190001,'2019-07-05','2019-07-10',1,'休息'),(8,20190001,'2019-07-05','2019-07-10',1,'休息');
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL COMMENT '学号',
`name` varchar(32) NOT NULL COMMENT '姓名',
`sex` tinyint(1) DEFAULT NULL COMMENT '性别',
`phone` varchar(16) DEFAULT '13800000000' COMMENT '手机号',
`birthday` date DEFAULT NULL COMMENT '生日',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `student` */
insert into `student`(`id`,`name`,`sex`,`phone`,`birthday`) values (20180001,'盖聂',1,'1409900089','1982-03-09'),(20180002,'卫庄',1,'1282399999','1993-10-01'),(20180003,'张良',NULL,NULL,'1996-03-11'),(20180004,'伏念',1,NULL,'1982-09-24'),(20180005,'颜路',1,'13699292899','1983-11-21'),(20180006,'赤练',0,'13819289890','1998-03-12'),(20180007,'端木蓉',0,'13800000000','1978-05-12'),(20180008,'盗跖',1,'13410012908','1993-09-10'),(20180009,'白凤',1,'13509890090','1994-04-20'),(20180010,'天明',1,'18799891829','2002-04-19'),(20180011,'月儿',0,'13882938990','2003-06-10');
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(40) DEFAULT NULL,
`password` varchar(40) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user` */
/*Table structure for table `user_profile` */
DROP TABLE IF EXISTS `user_profile`;
CREATE TABLE `user_profile` (
`id` int(11) NOT NULL,
`bankcard` varchar(40) DEFAULT NULL COMMENT '关联账号',
`address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user_profile` */
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
我们最新的C3P0与JDBC框架可以支持事务的使用。
使用方法参考我的博客JDBC与C3P0基本使用
了解更多框架请参考我的博客JDBC框架详解
事务应用代码:
1.没有出现错误,但是忘记提交
效果:回滚,没有更新数据
package my;
import mysql.AfSqlConnection;
import mysql.c3p0.AfSimpleDB;
import my.db.User;
import my.db.UserProfile;
public class Test
{
public static void testTrasaction() throws Exception
{
AfSqlConnection conn = AfSimpleDB.getConnection();
conn.beginTransaction();
try {
// SQL 1
User user = new User();
user.setUsername("shaofa");
user.setPassword("123");
user.setEmail("shaofa@afanihao.cn");
conn.insert( user );
// SQL 2
UserProfile profile = new UserProfile();
profile.setId( user.getId());
profile.setBankcard("1823898918388888");
profile.setAddress("X市X区X街道某小区");
conn.insert(profile);
// 提交
// conn.commit();
}catch(Exception e)
{
conn.rollback();
throw e ; // 出错
}
conn.close();
}
public static void main(String[] args)
{
try
{
testTrasaction();
} catch (Exception e)
{
e.printStackTrace();
}
}
}
2.SQL语句出错(没有插入主键)
效果:回滚没有执行
package my;
import mysql.AfSqlConnection;
import mysql.c3p0.AfSimpleDB;
import my.db.User;
import my.db.UserProfile;
public class Test
{
public static void testTrasaction() throws Exception
{
AfSqlConnection conn = AfSimpleDB.getConnection();
conn.beginTransaction();
try {
// SQL 1
User user = new User();
user.setUsername("shaofa");
user.setPassword("123");
user.setEmail("shaofa@afanihao.cn");
conn.insert( user );
// SQL 2
UserProfile profile = new UserProfile();
// 不插入主键的出错
// profile.setId( user.getId());
profile.setBankcard("1823898918388888");
profile.setAddress("X市X区X街道某小区");
conn.insert(profile);
// 提交
conn.commit();
}catch(Exception e)
{
conn.rollback();
throw e ; // 出错
}
conn.close();
}
public static void main(String[] args)
{
try
{
testTrasaction();
} catch (Exception e)
{
e.printStackTrace();
}
}
}
3.SQL语句执行成功
效果:执行事务(不回滚)
package my;
import mysql.AfSqlConnection;
import mysql.c3p0.AfSimpleDB;
import my.db.User;
import my.db.UserProfile;
public class Test
{
public static void testTrasaction() throws Exception
{
AfSqlConnection conn = AfSimpleDB.getConnection();
conn.beginTransaction();
try {
// SQL 1
User user = new User();
user.setUsername("shaofa");
user.setPassword("123");
user.setEmail("shaofa@afanihao.cn");
conn.insert( user );
// SQL 2
UserProfile profile = new UserProfile();
profile.setId( user.getId());
profile.setBankcard("1823898918388888");
profile.setAddress("X市X区X街道某小区");
conn.insert(profile);
// 提交
conn.commit();
}catch(Exception e)
{
conn.rollback();
throw e ; // 出错
}
conn.close();
}
public static void main(String[] args)
{
try
{
testTrasaction();
} catch (Exception e)
{
e.printStackTrace();
}
}
}