I. Before Integration into SQL Helper

In particular:

1. If there is only select in a transaction, transaction control can be ignored.

2. If there are more than one dml statement (update,insert,delete) in a transaction, the transaction needs to be considered.

public class OracleDemo {
    public static void main(String[] args) {
        Connection ct = null;
        try {
            String sql0 = "insert into emp(empno,ename) values(121,'firePolice')";
            String sql = "insert into emp(empno,ename) values(122,'Nurse')";
            ct = SQLHelper.getConnection();
            ct.setAutoCommit(false);
            PreparedStatement ps = ct.prepareStatement(sql0);
            PreparedStatement ps1 = ct.prepareStatement(sql);
            ps.executeUpdate();
            int a=12/0;
            ps1.executeUpdate();
            ct.commit();

        } catch (Exception e) {
            try {
                ct.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally{

        }
    }

Integration into SQLHelper

(executeUpdateUni)

package cn.wonders.oracle.utils;

import java.io.FileInputStream;
import java.lang.management.ManagementFactory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class SQLHelper {
    // Define three variables
    private static Connection ct = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;

    // User name, password, url, driver for connecting to database
    // In actual development, we write variables to external configuration files
    // When the program starts, we read in the configuration information, java.util.Properties.
    private static String username;
    private static String password;
    private static String driver;
    private static String url;

    // Load drivers using static code blocks (drivers only need to be added once)
    static {
        // Use the Properties class to read the configuration file
        Properties pp = new Properties();
        FileInputStream fis = null;
        try {
            fis = new FileInputStream("dbinfo.properties");
            // Associate pp with dbinfo.properties file
            pp.load(fis);
            // Get the information in the dbinfo.properties file
            username = pp.getProperty("username");
            password = pp.getProperty("password");
            driver = pp.getProperty("driver");
            url = pp.getProperty("url");
            // Get Driven
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (fis != null) {
                    fis.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            fis = null;
        }
    }

    // Unified curd operation
    public static void executeUpdate(String sql, String[] parameters) {

        try {
            ct = DriverManager.getConnection(url, username, password);
            ps = ct.prepareStatement(sql);
            if (parameters != null) {
                for (int i = 0; i < parameters.length; i++) {
                    ps.setString(i + 1, parameters[i]);
                }
            }
            // implement
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            close(rs, ps, ct);
        }
    }

    /**
     * Unified curd operation, controlled as a transaction
     */
    public static void executeUpdateUni(String[] sqls, String[][] parameters) {

        try {
            ct = DriverManager.getConnection(url, username, password);
            ct.setAutoCommit(false);
            if (parameters != null) {
                for (int i = 0; i < sqls.length; i++) {
                    ps = ct.prepareStatement(sqls[i]);
                    for (int j = 0; j < parameters[i].length; j++) {
                        ps.setString(j + 1, parameters[i][j]);
                    }
                    ps.executeUpdate();
                }
            } else {
                for (int i = 0; i < sqls.length; i++) {
                    ps = ct.prepareStatement(sqls[i]);
                    ps.executeUpdate();
                }
            }
            ct.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                ct.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            close(rs, ps, ct);
        }
    }

    // Write a method for query operation
    // sql represents the sql statement to be executed
    // sql select * from emp where ename=?
    public static ResultSet executeQuery(String sql, String[] parameters) {
        try {
            // According to the actual situation, we assign value to sql statement?
            // Get Connected
            ct = DriverManager.getConnection(url, username, password);
            // Create ps object and get sql statement object
            ps = ct.prepareStatement(sql);
            // If parameters are not null, they are assigned
            if (parameters != null) {
                for (int i = 0; i < parameters.length; i++) {
                    ps.setString(i + 1, parameters[i]);
                }
            }
            rs = ps.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
            // Throw an exception
            throw new RuntimeException(e.getMessage());
        } finally {
            // close(rs, ps, ct);
        }
        return rs;
    }

    // Write a method to return a connection
    public static Connection getConnection() {

        try {
            ct = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ct;
    }

    // Write closed resources as functions
    public static void close(ResultSet rs, Statement ps, Connection ct) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            ps = null;
        }

        if (ct != null) {
            try {
                ct.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            ct = null;
        }

    }

    public static Connection getCt() {
        return ct;
    }

    public static PreparedStatement getPs() {
        return ps;
    }

}

III. Transactions in Oracle

1. What is a transaction?
Transactions are used to ensure data consistency. They consist of a set of related dml statements that either succeed or fail.

2. Transactions and locks
When performing transaction operations (dml statements), oracle locks the active tables to prevent other users from altering the structure of the tables. This is very important for our users.

3. Submission
When commit statements are used, transactions can be committed. When commit statements are executed, changes in transactions are confirmed, transactions are terminated, savepoints are deleted, locks are released. When commit statements are used to end transactions, new data can be seen in other sessions.

4. Return business
Before introducing the fallback transaction, let's first introduce the concept and function of savepoint. A savepoint is a point in a transaction that cancels part of the transaction and automatically deletes all savepoints defined by the transaction when the transaction is terminated.

5. Several Important Operations of Transactions
(1) Setting savepoints
savepoint saves the roll call;
(2) Cancellation of some transactions
rollback to save rollback;
(3) Cancellation of all transactions
rollback;

6. Transaction Processing in Oracle - Transaction Isolation Level

(1) dirty read: When one transaction reads changes that have not yet been submitted by another transaction, dirty read is generated.
There is no dirty reading problem in oracle.

(2) Non-repeatable read: The same query is repeated in the same transaction. Because of the modifications or deletions made by other submitting firms, different result sets are returned each time, which results in non-repeatable read.

(3) phantom read: The same query is repeated in the same transaction. Because of the insertion operations done by other submitting firms, phantom read occurs when different result sets are returned each time.

oracle's read committed reading has been submitted for explanation:

1. The default transaction isolation level of oracle here;
2. Guarantee not dirty reading, but non-repetitive reading and hallucination may occur.

serializable of oracle can be serialized.

1. serializable is to make transactions appear to be executed sequentially one after another (which is understandable in effect).
2. Only changes submitted by other transactions before the start of this transaction and changes made in this transaction can be seen.
3. Guarantee that dirty reading, non-repeatable reading and hallucination will not occur;
4. serializable isolation level provides read consistency (transaction read consistency) provided by read-only firms, while allowing DML(update/insert/delete) operations.

oracle's read only consistency statement:

1. Comply with transaction-level read consistency, only changes submitted by other transactions before the start of this transaction can be seen;
2. DML(insert/update/delete) operations are not allowed in this transaction.
3. Readonly is a subset of serializable. They all avoid non-repeatable reading and hallucination. The difference is that in read only, it is read-only; in serializable, it is possible to perform DML operations.