JDBC Insight transaction, batch processing, setting primary key

1. The concept of a business

• a transaction refers to a logically set of operations that comprise each unit of this group of operations, either completely or unsuccessfully.

• For example: a--b transfer, corresponding to the following two SQL statements

update from account set money=money+100 where name= ' B ';

update from account set money=money-100 where name= ' a ';

L Database open transaction Command

• start transaction Open Business

• Rollback ROLLBACK TRANSACTION

• COMMIT Transaction

2. Detailed JDBC connection to database transaction

when the JDBC program obtains a connection object to the database, by default the connection object automatically submits the SQL statement sent on it to the database. If you want to turn off this default submission so that multiple SQL executes in a single transaction, you can use the following statement:

L JDBC Control TRANSACTION Statement

• Connection.setautocommit (false); start Transaction

• connection.rollback (); rollback

• connection.commit (); Commit

L SET Transaction rollback point

• SavePoint sp = Conn.setsavepoint ();

• Conn.rollback (sp);

• Conn.commit (); Must be submitted after rollback

3. When multiple threads open the data in the respective transaction operations database, the database system is responsible for isolating operations to ensure the accuracy of each thread in obtaining the data.

If you do not consider isolation, the following issues may be raised:

dirty reads: A transaction reads data that is not committed by another transaction.

This is very dangerous, assuming a transfer to B 100 yuan, the corresponding SQL statement as follows:

1.updateaccount set money=money+100 while name= ' B ';

2.updateaccount set money=money-100 while Name= ' a ';

when the 1th SQL finished, 2nd has not been implemented (a did not submit), if the B query their own account, you will find that they have more than 100 yuan of money. If a waits for B to go and then rolls back, B will lose 100 yuan.

L Non-repeatable reads: read a row of data in a transaction and read the results multiple times differently.

For example, the bank would like to query a account balance, the first query a account for 200 yuan, at this time a to the account to deposit 100 yuan and submitted, the bank then proceeded to a query, at this time a account for 300 yuan. The Bank two times query inconsistent, may be very confused, do not know which query is accurate.

• the difference between dirty reads is that dirty reads are dirty data that is not committed before a transaction is read, and that non-repeatable reads re-read the previous transaction's committed data.

Many people think that this situation is right, do not need to be confused, of course, the back of the prevail. We can consider such a situation, for example, the bank program needs to output the results of the query to the computer screen and write to the file, the result in a transaction for the output of the destination, the two query inconsistent, resulting in the file and screen results inconsistent, the bank staff do not know which is whichever.

• Virtual reading (phantom reading)

• refers to data that is inserted into another transaction within a transaction, resulting in inconsistent read and backward reading.

• such as C deposit 100 yuan has not been submitted, when the Bank makes statement statistics Account table All users of the total of 500 yuan, and then C submitted, then the bank to find accounts for more than 600 yuan, resulting in false reading will also make the bank at a loss, in the end is whichever.

4. The database has a total of four isolation levels defined:

• Serializable: Can avoid dirty read, not repeatable read, the occurrence of virtual reading. (serializable) (serialized )

• REPEATABLE READ: Prevents dirty reads, non-repeatable reads from occurring. (Repeatable Read)

• Read committed: Prevents dirty reads from occurring (read submitted).

• Read UNCOMMITTED: The lowest level, the above situation can not be guaranteed. (read not submitted)

• Set transactionisolation level setting transaction isolation Levels

• SELECT @ @tx_isolation Query the current transaction isolation level

5. The creation of JDBC transactions is primarily divided into the following steps

1. Set the transaction to be committed in a non automatic commit:

Conn.setautocommit (false);

2. Put the code that needs to add the transaction into the Try,catch block.

3. Add a commit operation for a transaction within a try block, indicating that the operation is no exception and commits the transaction.

conn.commit ();

4. Add a ROLLBACK transaction within a catch block, indicating an exception to the operation, revoking the transaction:

Conn.rollback ();

5. Set transaction submission to Autocommit:

Conn.setautocommit (true);

6. Batch processing

Business Scenario: When you need to send a batch of SQL statements to the database, you should avoid sending execution to the database, instead, use the JDBC batch mechanism to improve execution efficiency.

There are two ways to implement batch processing, the first way:

• statement.addbatch (SQL)

• Advantages: You can send multiple different SQL statements to the database.

• Disadvantages: (1) SQL statements are not precompiled.

(2) When you send multiple statements to the database, but only the SQL statements with different parameters, you need to write a number of SQL statements repeatedly

L Execute Batch SQL statements

• ExecuteBatch () Method: Execute Batch Command

• Clearbatch () Method: Clear Batch Command

Related code:

Connection conn = null;

Statement st = null;

ResultSet rs = null;

try {

conn = Jdbcutil.getconnection ();

String SQL1 = "INSERT into user (Name,password,email,birthday)

values (' KKK ', ' 123 ', ' abc@sina.com ', ' 1978-08-08 ');

String sql2 = "Update user set password= ' 123456 ' where id=3";

St =conn.createstatement ();

St.addbatch (SQL1);//Add SQL statements to the batch command

St.addbatch (SQL2);//Add SQL statements to the batch command

St.executebatch ();

} finally{

Jdbcutil.free (Conn, St, RS);

}

the second way to implement batch processing:

• Preparedstatement.addbatch ()

• Advantages: Sent is precompiled SQL statements, the implementation of high efficiency.

• Disadvantages: Can only be applied in batches with the same SQL statement but with different parameters. Therefore, this form of batch processing is often used to bulk insert data in the same table, or to bulk Update table data.

Related code:

conn = Jdbcutil.getconnection ();

String sql = "INSERT into user (Name,password,email,birthday) VALUES (?,?,?,?)";

St =conn.preparestatement (SQL);

For (inti=0;i<50000;i++) {

st.setstring (1, "AAA" + i);

st.setstring (2, "123" + i);

st.setstring (3, "AAA" + i + "@sina. com");

St.setdate (4,newdate (1980, Ten));

St.addbatch ();

if (i%1000==0) {

St.executebatch ();

St.clearbatch ();

}

}

St.executebatch ();

7. Get the primary key that the database automatically generates

Example:

Connection Conn =jdbcutil.getconnection ();

String sql = "INSERT into user (Name,password,email,birthday)

VALUES (' abc ', ' 123 ', ' abc@sina.com ', ' 1978-08-08 ');

PreparedStatement St =conn.

preparestatement (Sql,statement.return_generated_keys);

st.executeupdate ();

ResultSet rs =st.getgeneratedkeys ();//Get primary key for insert row

if (Rs.next ())

System.out.println (Rs.getobject (1));

L Note: This parameter is valid only for insert operations