Java Study Notes-JDBC4 (19), study notes jdbc4

Java Study Notes-JDBC4 (19), study notes jdbc4

16Database transactions

16.1 bank deposits

# Create table account (accountid varchar (18), # account balance double () # balance) # deposit table create table inaccount (accountid varchar (18 ), # account inbalance double (100) # deposit balance): Insert into inaccount (accountid, inbalance) values ('123', 500612345 ); insert into account (accountid, balance) values ('201312', 500612345 );

L in databases, transactions refer to a series of operations performed as a single logical unit of work.

L to ensure data in the databaseConsistencyData manipulation should be a discrete group logical unit: when it is complete, Data Consistency can be maintained, and when some operations in this unit fail, the entire transaction should be regarded as an error, and all operations after the starting point should be rolled back to the starting state.

Transaction operation: first define to start a transaction and then modify the data. IfSubmit(COMMIT), these modifications are permanently saved, ifRollback(ROLLBACK), the database management system will discard all your modifications and return to the state at the start of the transaction

LACID of the transactionAttribute

• 1. Atomicity)
  Atomicity means that a transaction is an inseparable unit of work. Operations in a transaction either occur or do not occur.
• 2. Consistency)
  The transaction must change the database from a consistent state to another consistent state. (Data is not damaged)
• 3. Isolation)
  The isolation of transactions means that the execution of a transaction cannot be disturbed by other transactions. That is, the operations and data used in a transaction are isolated from other concurrent transactions, transactions executed concurrently cannot interfere with each other.
• 4. Durability)
  Durability means that once a transaction is committed, its changes to data in the database are permanent, and other operations and database faults should not have any impact on it.

16.2 JDBC Transaction Processing

L transaction processing: ensure that all transactions are executed as a unit of work. Even if a fault occurs, this execution method cannot be changed. When multiple operations are performed in a transaction, either all the transactions are committed or the entire transaction is rolled back to the original state.

L in JDBC, transactions are automatically committed by default. Each time an SQL statement is executed, if the execution succeeds, the transaction will be automatically submitted to the database, rather than rolled back.

L to execute multiple SQL statements as a transaction:

• Call setAutoCommit (false) of the Connection object to cancel Automatic transaction commit.
• After all SQL statements are successfully executed, call the commit (); Method to submit the transaction.
• When an exception occurs, the rollback () method is called to roll back the transaction.

L you can use the getAutoCommit () method of Connection to obtain the commit method of the current transaction.

16.3 transaction processing: command line implementation

1. view the submission Mode

Select @ autocommit;

2. Use begin, rollback, and commit to implement
Begin // start a transaction

Rollback // transaction rollback

Commit // submit the transaction

For example:

Mysql> begin;

Mysql> insert into test values (9, 'dd', 'dd ');

Mysql> commit;

3. directly use set to change the automatic mysql submission Mode

MYSQL is automatically submitted by default, that is, when you submit a QUERY, it will directly execute
Set autocommit = 0 disable automatic submission
Set autocommit = 1 enable automatic commit to process transactions.
* When setautocommit = 0 is used, all your SQL statements will be processed as transactions.

You can use commit to confirm or roll back the end.

* Note: a new transaction is started when you end the transaction.

16.4 understand the transaction isolation level

L transaction isolation level (Transaction isolationlevels): The isolation level is the four levels of transaction concurrency control. Divided

SERIALIZABLE)

2. REPEATABLE READ)

3 READ committed)

4. READ uncommitted)

17Batch Processing ---Increase processing speed

L when batch insert or update records are required. You can use Java batchUpdateThis mechanism allows multiple statements to be submitted to the database for batch processing at a time. Generally, it is more efficient than submitting data separately.

L JDBC batch processing statements include the following two methods:

• AddBatch (String): Add SQL statements or parameters to be processed in batches;
• ExecuteBatch (); execute batch processing statements;

L we usually encounter two SQL statements in batch:

• Batch Processing of Multiple SQL statements;
• Batch passing parameters of an SQL statement;

Test:

/*** Test batch saving * statement * PreparedStatement * // *** use Statement to batch save 5000 records * mysql statement batch time = 3047 // The time is 3047 milliseconds */@ test public void testStatementBatch () {Connection conn = null; Statement stmt = null; long time = System. currentTimeMillis (); // record start time conn = JDBCUtils. getConnection (); try {stmt = conn. createStatement (); for (int I = 0; I <5000; I ++) {String SQL = "insert into B _user (id, NAME, PASSWORD, age) VALUES (1, 'T', 't', 1) "; stmt. addBatch (SQL); // batch insert} stmt.exe cuteBatch (); // execute the batch processing statement System. out. println ("mysql statement batch time =" + (System. currentTimeMillis ()-time);} catch (SQLException e) {e. printStackTrace ();} finally {JDBCUtils. closeResource (conn, stmt, null );}}

PreparedStatement:

/*** Use PreparedStatement to batch save 5000 records * mysql preparedstatement batch time = 3094 */@ Test public void testPreparedStatementBatch () {Connection conn = null; PreparedStatement pstmt = null; long time = System. currentTimeMillis (); conn = JDBCUtils. getConnection (); String SQL = "insert into B _user (id, NAME, PASSWORD, age) VALUES (?,?,?,?) "; Try {pstmt = conn. prepareStatement (SQL); for (int I = 0; I <5000; I ++) {pstmt. setInt (1, 1); pstmt. setString (2, "t"); pstmt. setString (3, "t"); pstmt. setInt (4, 1); pstmt. addBatch ();} pstmt.exe cuteBatch (); System. out. println ("mysql preparedstatement batch time =" + (System. currentTimeMillis ()-time);} catch (SQLException e) {e. printStackTrace ();} finally {JDBCUtils. closeResource (conn, pstmt, null );}}

18. MySQL BLOB type Introduction

L in MySQL, BLOB is a large binary object and a container that can store a large amount of data. It can hold data of different sizes. The BLOB type is actually a type series (TinyBlob, Blob, MediumBlob, and LongBlob). They are equivalent except for the maximum amount of information stored.

L four BLOB types of MySQL

/*** Save data with images */@ Test public void saveImage () {Connection conn = null; PreparedStatement pstmt = null; conn = JDBCUtils. getConnection (); String SQL = "INSERT INTO bt_user (NAME, headimage) VALUES (?,?) "; Try {pstmt = conn. prepareStatement (SQL); pstmt. setString (1, "tom"); InputStream inputStream = new FileInputStream ("D: \ ...........mm.jpg"); pstmt. setBinaryStream (2, inputStream, inputStream. available (); // mysql implements all the methods, but some methods cannot be executed and pstmt.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace ();} catch (FileNotFoundException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace ();}}

 

/*** Read a record with an image in the database */@ Test public void getImage () {Connection conn = null; Statement stmt = null; ResultSet rs = null; conn = JDBCUtils. getConnection (); try {stmt = conn. createStatement (); String SQL = "select * from bt_user where id = 1"; rs = stmt.exe cuteQuery (SQL); if (rs. next () {Blob blob = rs. getBlob ("headimage"); InputStream is = blob. getBinaryStream (); String path = "D: \ work \ Workspaces \ \ Day14_jdbc \ src \ cn \ itcast \ mysql \ bt \ mm2.jpg "; OutputStream OS = new FileOutputStream (path ); byte [] buffer = new byte [1024]; int len =-1; while (len = is. read (buffer ))! =-1) {OS. write (buffer, 0, len);} // OS. flush (); OS. close (); // There is flush is in close. close () ;}} catch (SQLException e) {e. printStackTrace ();} catch (FileNotFoundException e) {e. printStackTrace ();} catch (IOException e) {e. printStackTrace ();} finally {JDBCUtils. closeResource (conn, stmt, rs );}}