[Crazy Java] JDBC: Transaction management, Intermediate point, Bulk Update

1. The concept of database transactions:

1) The purpose of the transaction is to ensure the integrity of the data in the database;

2) Imagine a bank transfer process, if two steps, the first step is a's account-1000, the second step is B's account +1000, the two actions must be coherent, if the middle break (failure, etc.) after the first step after the execution of an exception and terminate the operation, then a white buckle 1000, and B's account also did not have the money to increase, this has had the very serious mistake;

！！ The above example can be seen:

A. These two steps must be coherent and synthesized together and should be implemented as an integral logical execution unit;

B. If the two steps are completed successfully then the data is complete, if the middle is broken, then the data is incomplete (wrong);

！！ The above can be summed up, the above two steps must be all done, or not all do, otherwise it will lead to data errors!!

3) In order to solve the above problems, as long as the above two steps packaged into a transaction, then what are the characteristics of the transaction?

I. First, the transaction must contain a number of database operations (it is certainly the operation of the database modification), because only the modification (update) can lead to incomplete data, and the transaction is to solve the incomplete problem;

II. The most important feature of a transaction is either not to do it, to do it all, and how does it fulfill this requirement? The problem is in two ways:

A. The first is to do it all done, this is of course the best, if every step of the implementation is smooth without any accident, then naturally all done, this nothing to say;

B. How do you do "or not"? You can not predict whether the execution of this event will be abnormal! Many anomalies are caused by some random factors!

C. Obviously, to do or to do, the key is to do half of the occurrence of the exception should be how to solve?

D. In fact, the principle is very simple, once there is an exception, the changes have just been made to undo it is not OK? In fact, the business is to achieve "or do not do";

E. This operation is called rollback: when the transaction executes, it will update the underlying data directly, and if there is an exception that is temporarily unresolved, it will immediately terminate the transaction and undo all the updates just performed, restoring the database to the state before the transaction was executed, which is the rollback of the transaction!

4) The transaction must be submitted in order to be effective:

I. Even if the complete execution of the transaction has been completed, it must be submitted in order to make changes to the data really take effect;

II. If the transaction is terminated without submission, it will all be rolled back even if all the previous executions have been completed;

III. Commit action is commit, after the completion of the transaction must remember commit to make it really effective oh!

！！ In fact, before committing to the data modification is only in the memory (cache) image, the previous modification after the use of the select query will find that the results of the data is actually updated, but that is only an illusion, because the update is only in-memory image, if you do not commit to disconnect the reconnection, Re-enter after select the following results found not modified;

！！ Changes made to the memory image are permanently written to the physical storage device only after committing a commit!!

！！ After the commit is disconnected and re-connected, select will find that the data is really modified;

2. Theory-level concepts of transactions:

1) from the example above, you can see that the transaction has the following 4 characteristics (synthetic acid):

I. atomicity (atomicity): A transaction is the smallest execution unit, indivisible, and must be executed once as a whole;

！ This is obvious, the above example of the transfer, the partition of the execution of the result will inevitably lead to the location of data inconsistency;

！！ In fact, the above-mentioned bank transfer is a special case, business you can arbitrarily define, do not cause inconsistencies in the two operations can also form a transaction, but the execution of the time will be in accordance with the nature of the transaction;

Ii. Consistency (consistency): If a transaction is fragmented, it can result in inconsistent data, so the ultimate purpose of the transaction is to ensure the integrity and consistency of the data, which is guaranteed by atomicity;

Iii. Isolation (Isolation): Concurrent transactions cannot interfere with each other (concurrent transaction contention data must be not monitored!). ), the reason is very simple, that is the atomic nature! You cannot see the middle state of the other side between concurrent transactions!

！！ The visible atomicity is the fundamental attribute of the transaction, and the other characteristics are guaranteed by atomicity;

Iv. Persistence (Durability): Also known as persistence, refers to the fact that once a transaction is committed, modifications to the data are permanently saved to the physical memory! Just make changes in the memory image before committing it!

2) The contents and submission of the transaction:

I. A transaction must consist of a DML statement: It is obvious that the transaction is to prevent data inconsistencies when data is modified!

！！ But the SELECT statement is allowed in the middle, but the SELECT statement is not part of the transaction, because the SELECT statement does not modify the data, just temporarily view the following results;

II. Only one DDL or DCL statement can appear, and must be the last sentence: The DDL and DCL will automatically trigger the commit action by default! The presence of a DDL or DCL means that the transaction is terminated!

III. Explicit commit and implicit commit: Explicit commit is the manual explicit execution of the commit command, implicit commit is the execution of DML or DCL statement, in JDBC programming smoothly from the method of normal exit will also be implicitly auto-commit!

3) Rollback:

I. Explicit rollback: Manually explicitly execute the rollback command;

II. Implicit rollback: Throws an unhandled exception and actively forces the exit from a method in JDBC programming (strong fallback!). Exit, etc.) will also trigger an implicit rollback;

！！ The above commit and rollback are SQL commands that can be entered and executed directly on the SQL command line!

3. Turn off autocommit to turn on transaction--mysql all (All) are transactions:

1) In fact, by default, MySQL will treat each input SQL command as a separate transaction, for example, if you enter an INSERT INTO (DML) command, it executes immediately and updates the changes directly to the physical memory. This is a MySQL. By default, each SQL command is treated as a separate transaction, and a single command is executed to automatically commit;

2) Then there is no transaction function, and a MySQL default will automatically open the auto-commit function (that is, each input command will be treated as a separate transaction and immediately submit!) ), so, to turn on the transaction function, you must turn off the auto-commit feature!

3) Switch command: Set autocommit = 0 |    1; 0 means turn off autocommit (that is, turn on the transaction function), 1 means turn on autocommit (that is, turn off the transaction function);

！！ Once the transaction function is turned on, the DML statements can be executed sequentially, and once executed to DCL/DDL or commit commits a transaction consisting of the previous successive DML, and the subsequent statement opens a new transaction! Of course, you can also use the rollback command to roll back the transaction;

4) Open a temporary transaction:

I. When you are working on a database at the command line, you may not want set autocommit =% to turn off autocommit, but to temporarily execute a transaction, which is a common requirement;

II. You can enter a begin or start Transaction command (with a semicolon ending) to indicate that a temporary transaction has been opened;

Iii. Next, the DML statement that executes the transaction is done;

Iv. when a commit or DDL/DCL is encountered, the temporary transaction is submitted, and then the temporary transaction ends and returns to the auto-commit state, and if you want to perform the transaction again, you must use begin or start transaction to open a temporary transaction!

V. If a rollback occurs (whether explicitly entering the rollback command or another cause of an exception rollback), it is assumed that the end of the Backstab temporary transaction, back to the autocommit state, must be started again by begin or Strat transaction to start the execution of the transaction!

5) Turn on the effect of multiple command lines on Autocommit mode: Because each SQL command-line window is a separate connection session, it is not affected by each other, and setting autocommit mode in a command-line window does not affect other open command-line windows, which is obvious!

4. Middle point:

1) SQL provides an intermediate point, which allows rollback to be rolled back to the middle point instead of having to roll it all back;

2) The syntax for setting the middle point is: savepoint the name of the custom intermediate point;

3) The middle point is definitely added in the middle of the DML statement of the transaction!

4) Considering that the middle point can be set a lot, so the rollback must be specified to roll back to which intermediate point, the syntax is: rollback to the middle point of the name;

5. JDBC Support for transactions:

1) The management of the JDBC to the transaction is connection, which is implemented by the connection object method;

2) First turn off autocommit on transaction function: void Connection.setautocommit (Boolean autocommit); False to turn off Autocommit turn on transaction feature

3) Of course you can also check whether the auto-commit function is on: Boolean connection.getautocommit (); True indicates that autocommit is turned on

4) Open the transaction function is to execute the transaction, the transaction is a DML statement, so is a stmt.executeupdate statement (stmt or as usual after going to statement, preparedstement);

5) Submit the task:

I. Automatically commits when you execute to the first DDL/DCL (executeupdate a DDL/DCL statement);

ii. void Connection.commit (); Explicit commit

6) Rollback:

I. If an exception is thrown during the execution of the transaction, it is automatically rolled back;

Ii. explicit rollback: void rollback ();

7) Middle point:

Set Middle Point

I. SavePoint connection.setsavepoint (); Set an intermediate point at a location in the transaction that is not named, using the system default naming

II. SavePoint setsavepoint (String name); Give the middle point a name

Iii. rollback to the specified intermediate point: void Connection.rollback (SavePoint savepoint); Roll back to the specified middle point

！！ Rolling back to the middle point of the API just so, the rollback position is specified by the SavePoint object, not by the middle point name, so the general name of the Setsavepoint method is not very good, but the name is still available, That is, the command line that must get the database uses the rollback to command to access the name of the middle point;

8) Example: A transaction will automatically roll back when it encounters an unhandled exception during execution

public class Test {private string Driver;private string Url;private string user;private string pass;public void Initparam ( ) throws FileNotFoundException, IOException {Properties props = new Properties ();p rops.load (New FileInputStream (" Mysql.ini "));d river = Props.getproperty (" driver "); URL = props.getproperty (" url "); user = Props.getproperty (" user "); pass = Props.getproperty ("Pass");} public void init (string[] sqls) throws FileNotFoundException, IOException, ClassNotFoundException, SQLException { Initparam (); Class.forName (driver); try (Connection conn = drivermanager.getconnection (URL, user, pass)) {Conn.setautocommit (false) ; Open transaction Function Try (Statement stmt = Conn.createstatement ()) {for (String sql:sqls) {stmt.executeupdate (SQL);}} Conn.commit ();}} public static void Main (string[] args) throws FileNotFoundException, ClassNotFoundException, IOException, SQLException { String[] Sqls = {"INSERT into student_table values (null, ' AAA ', 1) '," Insert to student_table values (null, ' BBB ', 1) ', "in SERT into Student_table values (NULL, ' CCC ', 1) ', ' insert into student table values (NULL, ' CCC ', 7717) '//to throw an exception for violation of FOREIGN key constraints, to test whether this exception causes self- Rollback};new Test (). Init (SQLS);}}

6. Batch update:

1) is equivalent to batch processing, that is, a one-time execution of a large number of SQL updates (DML), the use of a batch mechanism is obviously more than a single execution of all UPDATE statements faster, obviously in the case of batch needs to adopt this mechanism is very necessary;

2) JDBC Batch processing support:

1) statement must be used;

2) First Call Statement's Addbatch method to join the batch queue of SQL updates: void Statement.addbatch (String sql);

！！ You can only use statement instead of PreparedStatement, because SQL statements have been identified when you get PreparedStatement, and here we need to dynamically add SQL statements to the batch queue;

3) After all statements to be batched are inserted into the queue, call Statement's ExecuteBatch method to feed the batch queue one time into the database execution: int[] Statement.executebatch ();

！！ Because each DML statement returns how many rows were updated this time, all update batches will return an array, representing how many rows each DML statement would have

[Crazy Java] JDBC: Transaction management, Intermediate point, Bulk Update