JDBC事务处理【代码实现】

概述

代码实现

package cn.tedu.transaction;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TransDemo1 {
    public static void main(String[] args) {
//        double p=0x3p4;
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        //创建连接池对象
        ComboPooledDataSource source=new ComboPooledDataSource();
        try {
            //1.获取数据库连接
            conn = source.getConnection();
            //2.开启事务
            //默认布尔值为true，true代表会自动提交每一条sql语句（每条sql都是单独的事务）
            //false则不会自动提交每一条sql语句当前语句以及之后的sql语句都在一次事务中
            conn.setAutoCommit(false);
            //3.进行数据库操作
            ps = conn.prepareStatement("update user set money=money-100 where name=?");
            ps.setString(1,"a");

            //异常语句
//            int i=1/0;

            //执行sql语句
            ps.executeUpdate();
            ps = conn.prepareStatement("update user set money=money+100 where name=?");
            ps.setString(1,"b");
            //执行sql语句
            ps.executeUpdate();

            //4.提交事务
            conn.commit();

        } catch (Exception e) {
            e.printStackTrace();
            //回滚事务
            if(conn!=null)
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }finally {
            if(ps!=null)
                try {
                    ps.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    ps=null;
                }
            if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    conn=null;
                }
        }
    }
}

带保存点的事务

package cn.tedu.transaction;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.*;

public class TransDemo2 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        //声明保存点的对象
        Savepoint sp=null;
        //创建连接池对象
        ComboPooledDataSource source=new ComboPooledDataSource();
        try {
            conn = source.getConnection();
            //开启事务
            //默认布尔值为true，true代表会自动提交每一条sql语句（每条sql都是单独的事务）
            //false则不会自动提交每一条sql语句当前语句以及之后的sql语句都在一次事务中
            conn.setAutoCommit(false);

            ps = conn.prepareStatement("update user set money=money-100 where name=?");
            ps.setString(1,"a");
            //
            //int i=1/0;
            //执行sql语句
            ps.executeUpdate();
            ps = conn.prepareStatement("update user set money=money+100 where name=?");
            ps.setString(1,"b");
            //执行sql语句
            ps.executeUpdate();

            //添加保存点
            sp = conn.setSavepoint();
            //
            int i=1/0;


            ps = conn.prepareStatement("update user set money=money-100 where name=?");
            ps.setString(1,"a");
            //
            //int i=1/0;
            //执行sql语句
            ps.executeUpdate();
            ps = conn.prepareStatement("update user set money=money+100 where name=?");
            ps.setString(1,"b");
            //执行sql语句
            ps.executeUpdate();

            //提交事务
            conn.commit();

        } catch (Exception e) {
            e.printStackTrace();
            //回滚事务
            if(conn!=null)
                try {
                    //判断保存点的对象是否为null
                    if(sp!=null){
                        //回滚到保存点
                        conn.rollback(sp);
                        //继续提交回滚点之前的代码
                        conn.commit();
                    }else{
                        //没有保存点全部回滚
                        conn.rollback();
                    }
                    conn.rollback();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
        }finally {
            if(ps!=null)
                try {
                    ps.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    ps=null;
                }
            if(conn!=null)
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    conn=null;
                }
        }
    }
}