程序员人生 网站导航

JDBC编程-事务编程(四)

栏目:数据库应用时间:2015-06-01 09:04:44

事务的概念

事务的概念在我看来是指的是1组sql序列,这个序列是1块履行的单位,要末全部履行,要不全部履行,这样可以很好的对数据库进行并发控制。
由于数据库是多个用户都可以同时操作的,如果多个用户同时操作1个数据,就容易造成数据的不1致,所以事务作为并发控制的1个基本单位很有必要。

事务的特性

1.原子性:事务是1个完全的整体,所有的操作和数据都是1个整体。
2.1致性:事务的操作是1致性的。
3.隔离性:事务之间的操作是相互隔离的。
4.持久性:事务的操作是持久的,即便出现了毛病也会完成。

事务的语句

BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION

实验代码

public static Statement getStatement(){ Statement st = null; try { Class.forName("com.mysql.jdbc.Driver"); Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_dbb", "root", ""); st = (Statement) conn.createStatement(); } catch (Exception e) { // TODO: handle exception } return st; } public static void insertUserData(){ try { String sql = "INSERT INTO tbl_user(id,name,password,email)" + "VALUES(10,'Tom','123456','tom@qq.com')"; Statement st = getStatement(); int count = st.executeUpdate(sql); System.out.println("插入了"+count+"行用户数据"); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } } public static void insertAddressData(){ try { String sql = "INSERT INTO tbl_address(id,city,country,user_id)" + "VALUES(1,'shanghai','china,'10')"; Statement st = getStatement(); int count = st.executeUpdate(sql); System.out.println("插入了"+count+"行地址数据"); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } } public static void main(String[] args) { insertUserData(); insertAddressData(); }

这段代码会报错,由于在tbl_address中已有id = 1的数据了,具体以下图:

插入了1行用户数据 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10')' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) at com.mysql.jdbc.Util.getInstance(Util.java:360) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1618) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1549) at liu.peng.jdbc.TransactionTest.insertAddressData(TransactionTest.java:37) at liu.peng.jdbc.TransactionTest.main(TransactionTest.java:46)

检查发现只是插入user表的数据,没有插入address表数据,造成数据不完全。

事务处理的利用

事务处理的核心代码就是
conn.setAutoCommit(false);
在履行完以后的提交。
conn.commit();
和在捕获异常时候的回滚。
具体代码以下:

public static Connection getConnection(){ Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_dbb", "root", ""); } catch (Exception e) { // TODO: handle exception } return conn; } public static void insertUserData(Connection conn)throws SQLException{ String sql = "INSERT INTO tbl_user(id,name,password,email)" + "VALUES(10,'Tom','123456','tom@qq.com')"; Statement st = (Statement) conn.createStatement(); int count = st.executeUpdate(sql); System.out.println("插入了"+count+"行用户数据"); } public static void insertAddressData(Connection conn) throws SQLException{ String sql = "INSERT INTO tbl_address(id,city,country,user_id)" + "VALUES(1,'shanghai','china,'10')"; Statement st = (Statement) conn.createStatement(); int count = st.executeUpdate(sql); System.out.println("插入了"+count+"行地址数据"); } public static void main(String[] args) { Connection conn = null; try { conn = getConnection(); conn.setAutoCommit(false); insertUserData(conn); insertAddressData(conn); conn.commit(); } catch (SQLException e) { System.out.println("==========捕获SQL异常========="); e.printStackTrace(); try { conn.rollback(); System.out.println("=======s事务回滚成功======="); } catch (Exception e2) { e2.printStackTrace(); } }finally{ try { if (conn != null) { conn.close(); } } catch (Exception e3) { e3.printStackTrace(); } } }

Console界面的报错信息为:

插入了1行用户数据 ==========捕获SQL异常========= com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10')' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) at com.mysql.jdbc.Util.getInstance(Util.java:360) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1618) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1549) at liu.peng.jdbc.TransactionTest.insertAddressData(TransactionTest.java:31) at liu.peng.jdbc.TransactionTest.main(TransactionTest.java:40) =======s事务回滚成功=======

证明两条数据都没有插入进去保证了数据的完全性,而第1次只是插入user表的数据,没有插入address表数据,造成数据不完全。

------分隔线----------------------------
------分隔线----------------------------

最新技术推荐