JDBC中提交事务必须把Connection对象的自动提交特性关闭,改为手动提交,否则的话即使提交的是一个命令列表,DBMS也是分条处理,中间若有一条命令异常,在该条命令之前和之后的所有命令仍旧会被执行。可以理解为一个事务就是Connection对象的一次commit。
数据库表结构:
mysql> desc
-> student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
| address | varchar(50) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
这是数据库最初数据:
mysql> select * from student;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 1 | Tom | America |
| 2 | Lucy | Canada |
| 3 | Lin | China |
| 4 | Kate | Japan |
+----+------+---------+
4 rows in set (0.00 sec)
来看下面这段代码(自动提交没有关闭,提交命令列表):
Connection con;
Statement st;
......
st = con.createStatement();
st.addBatch("insert into student values(5,'A','A')");
st.addBatch("insert into student values(v,'B','B')");//注意第一个参数
st.addBatch("insert into student values(7,'C','C')");
st.executeBatch();
st.close();
con.close();
执行结果:
mysql> select * from student;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 1 | Tom | America |
| 2 | Lucy | Canada |
| 3 | Lin | China |
| 4 | Kate | Japan |
| 5 | A | A |
| 7 | C | C |
+----+------+---------+
6 rows in set (0.00 sec)
可以看到第一条,第三条命令仍旧执行了,并未作为事务提交。
然后看下面这段代码(自动提交关闭,改为手动提交,提交命令列表):
Connection con;
Statement st;
......
con.setAutoCommit(false);
st = con.createStatement();
st.addBatch("insert into student values(5,'A','A')");
st.addBatch("insert into student values(v,'B','B')");
st.addBatch("insert into student values(7,'C','C')");
st.executeBatch();
con.commit();
st.close();
con.close();
数据库数据仍是最初数据:
mysql> select * from student;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 1 | Tom | America |
| 2 | Lucy | Canada |
| 3 | Lin | China |
| 4 | Kate | Japan |
+----+------+---------+
4 rows in set (0.00 sec)
执行结果:
mysql> select * from student;
+----+------+---------+
| id | name | address |
+----+------+---------+
| 1 | Tom | America |
| 2 | Lucy | Canada |
| 3 | Lin | China |
| 4 | Kate | Japan |
+----+------+---------+
4 rows in set (0.00 sec)
可以看到执行结果跟初始数据一样,数据并未插入,当碰到异常时,DBMS自动地把事务rollback了。