JDBC编程之事务处理

来源：互联网发布：java agent premain 编辑：程序博客网时间：2024/06/05 23:55

当插入数据遇到唯一性约束时，数据插入失败，如下列操作，插入tbl_user成功，但是插入tbl_address失败，因为id为1已经存在。

package com.djx.jdbc;import java.sql.DriverManager;import java.sql.SQLException;import com.mysql.jdbc.Connection;import com.mysql.jdbc.Statement;public class TransactionTest {public static Connection getConnection() {Connection conn=null;try {Class.forName("com.mysql.jdbc.Driver");conn=(Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db?autoReconnect=true&useSSL=false","root","1234");} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return conn;}public static void insertTbUserData() {Connection conn=getConnection();String sql="insert into tbl_user(id,name,password,email) values(10,'Tom','1234','tom@163.com')";int count;try {Statement statement=(Statement)conn.createStatement();count = statement.executeUpdate(sql);System.out.println("向用户表中插入"+count+"条记录");    statement.close();    conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static void insertTbAddressData() {Connection conn=getConnection();String sql="insert into tbl_address(id,city,country,user_id) values(1,'深圳','中国',10)";int count;try {Statement statement=(Statement)conn.createStatement();count = statement.executeUpdate(sql);System.out.println("向用户表中插入"+count+"条记录");    statement.close();    conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static void main(String[] args) {// TODO Auto-generated method stubinsertTbUserData();insertTbAddressData();}}

利用事务回滚就可以防止错误的插入，如下代码

package com.djx.jdbc;import java.sql.DriverManager;import java.sql.SQLException;import com.mysql.jdbc.Connection;import com.mysql.jdbc.Statement;public class TransactionTest {public static Connection getConnection() {Connection conn=null;try {Class.forName("com.mysql.jdbc.Driver");conn=(Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db?autoReconnect=true&useSSL=false","root","1234");} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return conn;}public static void insertTbUserData(Connection conn) throws SQLException {String sql="insert into tbl_user(id,name,password,email) values(10,'Tom','1234','tom@163.com')";int count;Statement statement=(Statement)conn.createStatement();count = statement.executeUpdate(sql);System.out.println("向用户表中插入"+count+"条记录");    statement.close();}public static void insertTbAddressData(Connection conn) throws SQLException{String sql="insert into tbl_address(id,city,country,user_id) values(1,'深圳','中国',10)";int count;Statement statement=(Statement)conn.createStatement();count = statement.executeUpdate(sql);System.out.println("向用户表中插入"+count+"条记录");    statement.close();}public static void main(String[] args) {// TODO Auto-generated method stubConnection conn=null;try {conn=getConnection();conn.setAutoCommit(false);insertTbUserData(conn);insertTbAddressData(conn);conn.commit();//提交事务} catch (SQLException e) {System.out.println("======捕获到SQL异常======");e.printStackTrace();try {conn.rollback();//事务回滚System.out.println("======事务回滚成功======");} catch (Exception e2) {// TODO: handle exceptione2.printStackTrace();}}finally {try {if (conn!=null) {conn.close();}} catch (Exception e3) {// TODO: handle exception}}}}

利用事务回滚，如果下面插入错误，则上面插入也将不成功。

0 0