Oracle transaction processing and instance demonstration JDBC operation batch Deletion

Oracle transaction processing and instance demonstration JDBC operation batch Deletion

Transactions

As the basic unit of logical processing, database operations are composed of one or more SQL statements. Of course, there are also non-database operations, such as the Restore Point Set in the computer is a good application.

The basic nature of transactions is described in another article: SQL transactions and instance demonstrations.

Transaction differences between Oracle and SQL server

Transactions in SQL server are generally classified into implicit transactions, explicit transactions, and automatically committed transactions.

Automatic transaction: for SQL server, when the client submits an SQL statement, SQL server automatically starts a transaction. For such a transaction, it is automatically submitted after the SQL statement is executed.

Display transaction: this is also a common transaction. In fact, in the automatic transaction, add a Begintran, conn. commit, end tran.

Implicit transactions: Compared to transactions that require enabling connection, implicit transactions enable the Begin of transactions and database connections by default. Of course, you still need to perform the commit or rollback operation later.

Oracle transactions are not so rich, similar to SQL server's implicit transactions. You do not need to enable conn and Begin, as long as you perform commit or rollback operations in subsequent operations.

Transaction commit Mechanism

First, I want to understand some basic concepts: data buffer cache: A High-Speed Read-Only cache that connects a hard disk file to oracle data operations.

SGA: A memory space opened in the memory after the oracle instance is started, used to store server control information and data.

Data Block: The basic unit of data storage.

After connecting to the database, oracle creates an independent process-shadow process for the connected user, which is accompanied by the user's entire operation;

1. Check data blocks

2. Construct an undo data block

To roll back and forth data

3. Generate redo logs

Logs used for Reoperation are stored in the log buffer cache.

4. The lgwr process starts, commits transactions, and writes all log files.

Java Transaction Processing

TestDemo: combined with the PreparedStatement interface under java. SQL and oracle transactions, batch deletion is achieved.

Common Methods:

Int [] executeBatch ():

Submit a batch of commands to the database for execution. If all commands are successfully executed, an array consisting of update counts is returned.

VoidsetString (int parameterIndex,

String x ):

Set the specified parameter to a given Java String value. When you send this value to a database, the driver converts it to an SQL VARCHAR or LONGVARCHAR value.

Demo

/** Delete a user-August 11, 2014 18:19:04
* @ UserId: User ID Array
*/
PublicBoolean DeleteUser (String [] userId ){
// A thread-safe variable string
StringBuffersb = new StringBuffer ();
Sb. append ("deletefrom t_user where user_id =? ");
Connectionconn = null;
PreparedStatementpsmt = null;
Booleanflag = false;
 
Conn = DButil. getConnection ();
Try {
// Disable automatic transaction commit
Conn. setAutoCommit (false );
// Create a PreparedStatement object to send parameterized SQL statements to the database.
Psmt = conn. prepareStatement (sb. toString ());
// Add a set of parameters to the batch processing command of this PreparedStatement object.
For (inti = 0; I <userId. length; I ++ ){
Psmt. setString (1, userId [I]. trim ());
Psmt. addBatch ();
}
 

// Execute batch update
Psmt.exe cuteBatch ();
// After the statement is executed, submit the transaction
Conn. commit ();

Flag = true;
} Catch (SQLException e ){
// TODO Auto-generated catch block
E. printStackTrace ();
Try {
Conn. rollback ();
} Catch (SQLException e1 ){
// TODO Auto-generated catch block
E1.printStackTrace ();
}
}
 
Returnflag;
}

Summary

In general, I still have a limited understanding of many internal oracle mechanisms. The design model lectures a few days ago also reminded me that some of the things here had this blog. It can only be said that it is very simple to understand that oracle's transaction processing here is essentially consistent with the transaction processing.

Oracle 11g installation manual on RedHat Linux 5.8 _ x64 Platform

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian