JDBC 和 Oracle conn.commit 和 conn.setAutocommit 无法正常工作 [英] JDBC and Oracle conn.commit and conn.setAutocommit not working properly

问题描述

我创建了一个如下所示的 DBManager 类

I have made a DBManager class as shown below

public class DBManager {


      public static String DRIVER = "oracle.jdbc.driver.OracleDriver";
      public static String URL = "jdbc:oracle:thin:@//localhost:1521/DB";
      public static String USERNAME = "afsweb";
      public static String PASSWORD = "afsweb";
      public static String DOCDBUSERNAME = "docdb";
      public static String DOCDBPASSWORD = "docdb";
      public static int PORT = 1521;

    //static Logger log = Logger.getLogger(ExcelDBManager.class.getName());
    public static Connection getConnection(String url ,String username, String password){
    try {
        Class.forName(DRIVER);
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    Connection con = null;
    try {
        con = DriverManager.getConnection(url,username,password);
        con.setAutoCommit(false);
        } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();

    }
    return con;
}

我有方法截断表中的行

public static void truncate() throws SQLException{
        conn = DBManager.getConnection(DBManager.URL, DBManager.USERNAME, DBManager.PASSWORD);
        System.out.println(conn.getAutoCommit()  +"");
        Statement pstmnt = null;
        ResultSet rs = null;
        try{    
            pstmnt = conn.createStatement();
            pstmnt.executeQuery("truncate table bd_vehicles_temp_1");
            System.out.println("Query Executed");
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally{
            try{
            if(rs !=null){
                rs.close();
            }
            if(pstmnt != null){
                pstmnt.close();
            }
            if(conn != null){
                conn.close();
            }
            }
            catch(SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

现在我还没有在我的 truncate() 方法中写 conn.commit.此外，我已将自动提交设置为 false.即使这样，更改也会反映在数据库中.

Now i have not written conn.commit inside my truncate() method. Also i have setAutocommit to false. Even then the changes are reflected in database.

在执行上述方法时，我得到的输出为

On Executing the above method i get output as

false
Query Executed

这意味着我的连接自动提交模式是假的.truncate 方法所做的更改仍然反映在数据库中.可能的原因是什么??我正在使用 Oracle 数据库.

Which means my connections autocommit mode is false. Still the changes made by truncate method is reflected in database. What could be the possible reason ?? I am using Oracle Database.

提前致谢！

推荐答案

TRUNCATE 是隐式提交的数据定义语言 (DDL) 命令.如果您改用 DELETE 语句，它就不会提交任何内容.

TRUNCATE is a Data Definition Language (DDL) command which commits implicitly. It wouldn't have committed anything, had you used the DELETE statement instead.

// Deletes ALL Rows; No WHERE Clause
pstmnt.executeQuery("DELETE FROM bd_vehicles_temp_");

TRUNCATE 是 DDL 语句的原因是它直接删除表数据，而不将其复制到回滚表空间.这就是 TRUNCATE 更快但无法回滚的原因.

The reason TRUNCATE is a DDL statement is that it removes the table data directly without copying it into the Rollback Tablespace. That's why TRUNCATE is faster but cannot be rolled back.

编辑:(为什么我的 INSERT 也在提交?)

EDIT : (Why my INSERTs are committing as well?)

那是因为您关闭了连接而没有调用 Connection#rollback().

That's because you're closing your Connection without calling Connection#rollback().

如果连接在没有明确提交或回滚的情况下关闭；JDBC 在这里没有特别要求任何东西，因此行为取决于数据库供应商.在 Oracle 的情况下，会发出一个隐含的提交.

If a Connection is closed without an explicit commit or a rollback; JDBC does not mandate anything in particular here and hence the behaviour is dependent on the database vendor. In case of Oracle, an implict commit is issued.

强烈建议应用程序在调用 close 方法之前明确提交或回滚活动事务.如果调用 close 方法并且存在活动事务，则结果是实现定义的.

It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the close method. If the close method is called and there is an active transaction, the results are implementation-defined.

所以，在 finally 块

pstmnt = conn.createStatement();

pstmnt.executeQuery("DELETE FROM bd_vehicles_temp_1");
System.out.println("Query Executed");

conn.rollback();
System.out.println("Changes rolled back");

这篇关于JDBC 和 Oracle conn.commit 和 conn.setAutocommit 无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！