JAVA解决JDBC的自动提交带来的数据事务出错问题-----JDBC

旧约Alatus

已于 2023-11-10 15:17:22 修改

阅读量364

点赞数 1

分类专栏： JAVA 文章标签： java 数据库后端 mysql tomcat spring maven

于 2023-08-08 16:39:21 首次发布

本文链接：https://blog.csdn.net/2201_75960169/article/details/132170300

版权

JAVA 专栏收录该内容

533 篇文章 3 订阅

订阅专栏

package com.powernode.jdbc;

//DROP TABLE IF EXISTS t_act;
//CREATE TABLE t_act(actno BIGINT,balance DOUBLE(7,2));//7表示有效数字的个数,2表示小数位的个数
//INSERT INTO t_act(actno,balance) VALUES(111,20000);
//INSERT INTO t_act(actno,balance) VALUES(222,20000);
//COMMIT;
//SELECT * FROM t_act;
import java.sql.*;
import java.util.ResourceBundle;

public class JDBCTest04
{
    public static void main(String[] args)
    {
        //JDBC的事务都是自动提交的,什么叫自动提交(只要执行任意一条DML语句就会自动提交一次),这是JDBC默认的行为
        //在业务流程中我们通常是多条SQL语句(DML)同时执行,同时成功,同时失败
        //JDBC的事务是自动提交机制的,JDBC只要执行容易DML语句就提交一次
        PreparedStatement preparedStatement = null;
        Connection connection = null;
        ResourceBundle resourceBundle = ResourceBundle.getBundle("JDBC");
        String url = resourceBundle.getString("url");
        String user = resourceBundle.getString("user");
        String password = resourceBundle.getString("password");
        try
        {
            Class.forName(resourceBundle.getString("Driver"));
            connection = DriverManager.getConnection(url,user,password);
            //将自动提交机制修改为手动提交,开启事务
            connection.setAutoCommit(false);
            String sql = "update t_act set balance = ? where actno = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"0.0");
            preparedStatement.setString(2,"222");
            int count = preparedStatement.executeUpdate();
            //终止程序的空指针异常
//            String s = null;
//            s.toString();

            preparedStatement.setString(1,"10000.0");
            preparedStatement.setString(2,"222");
            count = preparedStatement.executeUpdate() + count;
            System.out.println(count == 2 ? "转账成功" : "转账失败");
            //能走到这就是没有异常,手动提交数据
            connection.commit();
        }
        catch(Exception e)
        {
            if (connection != null)
            {
                try
                {
                    //回滚事务
                    connection.rollback();
                } catch (SQLException ex)
                {
                    throw new RuntimeException(ex);
                }
            }
            e.printStackTrace();
        }
        finally
        {
            if (preparedStatement != null)
            {
                try
                {
                    preparedStatement.close();
                }
                catch (SQLException e)
                {
                    throw new RuntimeException(e);
                }
            }
            if (connection != null)
            {
                try
                {
                    connection.close();
                }
                catch (SQLException e)
                {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}
package com.powernode.jdbc;

//DROP TABLE IF EXISTS t_act;
//CREATE TABLE t_act(actno BIGINT,balance DOUBLE(7,2));//7表示有效数字的个数,2表示小数位的个数
//INSERT INTO t_act(actno,balance) VALUES(111,20000);
//INSERT INTO t_act(actno,balance) VALUES(222,20000);
//COMMIT;
//SELECT * FROM t_act;
import java.sql.*;
import java.util.ResourceBundle;

public class JDBCTest04
{
public static void main(String[] args)
{
//JDBC的事务都是自动提交的,什么叫自动提交(只要执行任意一条DML语句就会自动提交一次),这是JDBC默认的行为
//在业务流程中我们通常是多条SQL语句(DML)同时执行,同时成功,同时失败
//JDBC的事务是自动提交机制的,JDBC只要执行容易DML语句就提交一次
PreparedStatement preparedStatement = null;
Connection connection = null;
ResourceBundle resourceBundle = ResourceBundle.getBundle("JDBC");
String url = resourceBundle.getString("url");
String user = resourceBundle.getString("user");
String password = resourceBundle.getString("password");
try
{
Class.forName(resourceBundle.getString("Driver"));
connection = DriverManager.getConnection(url,user,password);
//将自动提交机制修改为手动提交,开启事务
connection.setAutoCommit(false);
String sql = "update t_act set balance = ? where actno = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"0.0");
preparedStatement.setString(2,"222");
int count = preparedStatement.executeUpdate();
//终止程序的空指针异常
// String s = null;
// s.toString();

preparedStatement.setString(1,"10000.0");
preparedStatement.setString(2,"222");
count = preparedStatement.executeUpdate() + count;
System.out.println(count == 2 ? "转账成功" : "转账失败");
//能走到这就是没有异常,手动提交数据
connection.commit();
}
catch(Exception e)
{
if (connection != null)
{
try
{
//回滚事务
connection.rollback();
} catch (SQLException ex)
{
throw new RuntimeException(ex);
}
}
e.printStackTrace();
}
finally
{
if (preparedStatement != null)
{
try
{
preparedStatement.close();
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
}
if (connection != null)
{
try
{
connection.close();
}
catch (SQLException e)
{
throw new RuntimeException(e);
}
}
}
}
}