Oracle Transaction Processing and example demo JDBC Operations Bulk Delete

Transactions

As the basic unit of logical processing, it is composed of one or more SQL statements for database operations . Of course, there is a good application for non-database operations, such as a restore point set on a computer.

The basic nature of the transaction is described in another article: SQL Transactions and example demonstrations

Oracle and SQL Server differ on transactions

transactions in SQL Server are generally divided into implicit transactions, explicit transactions, and autocommit transactions.

Automatic transactions: For SQL Server , when a client submits an SQL statement, SQL Server automatically starts a transaction, and for such a transaction, after executing the SQL Statement is automatically submitted after the

Show transactions: This is also a more common use of transactions, in fact, in essence, in the automatic transaction, add a begintran,conn.commit,end Tran.

Implicit transactions: When connection is required to open a transaction , the implicit transaction is the start of the default open transaction and the database connection. Of course, there is still a commit or rollback operation to follow.

Oracle 's transactions are not so rich, similar to SQL Server 's implicit transactions; no need to open conn and Begin, as long as the subsequent operations commit or rollback operation.

mechanism for transaction submission

Start by understanding some basic concepts,data buffer cache: A high-speed read-only cache that connects hard disk files and Oracle data operations.

SGA: After starting an Oracle instance, a piece of memory space is opened up in memory to hold the server's control information and data.

Data blocks: The basic unit of data storage.

When connected to a database,Oracle creates a separate process -shadow process for connected users, which accompanies the user's entire operation;

1. Check the data block

2. constructing the undo data Block

To roll the data back and forth

3. Generate Redo logs

Logs for re-operation are stored in the log buffer cache .

4.LGWR process Start, commit transaction and write all log files

Java transaction processing

Testdemo: Bulk Delete with interface PreparedStatement and Oracle transaction implemented in java.sql

Common methods:

Int[]executebatch():

A batch of commands is submitted to the database for execution, and an array of update counts is returned if all command execution succeeds.

voidsetString(int Parameterindex,
String x) :

Sets the specified parameter to the given Java String value. When this value is sent to the database, the driver converts it to a SQL VARCHAR or LongVarChar value.

Demo

[Java] View PlainCopyPrint?

2. /** Delete a user-August 11, 2014 18:19:04
4. * @userId array of user IDs
6. */
8. Publicboolean deleteuser (string[] userId) {
10. A thread-safe mutable string
12. stringbuffersb=new StringBuffer ();
14. Sb.append ("Deletefrom t_user where user_id =?");
16. connectionconn=null;
18. PREPAREDSTATEMENTPSMT = null;
20. Booleanflag=false;
24. Conn=dbutil.getconnection ();
26. try {
28. Turn off auto-commit transactions
30. Conn.setautocommit (false);
32. Create a PreparedStatement object to send the parameterized SQL statement to the database.
34. psmt= conn.preparestatement (sb.tostring ());
36. Adds a set of parameters to the batch command for this PreparedStatement object.
38. for (inti =0; i<userid.length;i++) {
40. Psmt.setstring (1,userid[i].trim ());
42. Psmt.addbatch ();
44. }
50. //Perform batch updates
52. Psmt.executebatch ();
54. //Statement execution complete, commit this transaction
56. Conn.commit ();
60. Flag=true;
62. }catch (SQLException e) {
64. TODO auto-generated Catch block
66. E.printstacktrace ();
68. try{
70. Conn.rollback ();
72. }catch (SQLException E1) {
74. TODO auto-generated Catch block
76. E1.printstacktrace ();
78. }
80. }
84. Returnflag;
86. }