Java 04javaweb-05 transactions and connection pooling

One, the business Part 1. Introduction to Transactions

To do one thing, this one thing has multiple constituent units, which are either successful at the same time or fail at the same time. A account transferred to B account money, the operation of the transfer of a account and B account transfer money into a transaction, or the two actions at the same time success, on behalf of the successful transfer, or two actions failed at the same time, on behalf of the transfer failed.

2. MySQL's transaction control

MySQL default transaction is automatically committed, a SQL is a transaction

Open transaction manually: Start transaction

Auto-commit of database default transactions is temporarily invalidated when transaction is opened manually

Commit TRANSACTION: Commit

All SQL statements between the COMMIT transaction and the open transaction are in effect

ROLLBACK TRANSACTION: Rollback

All SQL operations from ROLLBACK TRANSACTION to open transaction are invalid

3. Transactional control of the JDBC API

You can control transactions by connection objects

In JDBC, the way to control transactions is to control the JDBC Update database API method---executeupdate

Open transaction: Connection.setautocommit (FALSE);

Commit TRANSACTION: Connection.commit ();

ROLLBACK TRANSACTION: Connection.rollback ();

4. Four characteristics of the business acid

Atomicity: The smallest unit of operation of a database is a transaction

Consistency: Result data for multiple operations in one transaction is consistent, both successful and simultaneous failures

Isolation: Operations between multiple transactions do not affect each other

Persistence: The update operation is persisted to disk after a transaction is committed

What are the implications of not considering isolation?

Simulating two transaction a transaction B transactions

Dirty read: b transaction read to transaction not committed by a transaction

Non-repeatable READ: The content of data read two times in a transaction is inconsistent

Virtual read/Phantom read: The number of data read two times in a transaction is inconsistent

5. Isolation level of a transaction

Resolve the above issues by setting the isolation level of the database:

READ UNCOMMITTED: Read a transaction that has not yet been committed and nothing can be resolved

Read Committed: Reads the content that has been submitted, can resolve the dirty read

REPEATABLE READ: Repeat read, can resolve dirty read and non-repeatable read

Serializable: serialization, can solve all

Default isolation level of the database

Default isolation level for MySQL: Repeatable read

Default Isolation level for Oracle: Read Committed

To query the isolation level of the database: SELECT @ @tx_isolation

Manually modify the default isolation level of the database:

Set session transaction ISOLATION level setting transaction isolation Levels

Simulate dirty read steps:

1) Open two clients to enter the database separately

2) Set two client database isolation level to READ UNCOMMITTED

3) Two clients open the transaction separately

4) A client modifies data but has not yet committed

5) B client Query data discovery data has been modified

6) A client rollback

Isolation level performance issues: Serializable<repeatable read<read committed<read Uncommitted

Isolation level Security Issue: Serializable>repeatable read>read Committed>read Uncommitted

Note: Transaction control must be in the service layer

ThreadLocal: Represents a map that is dedicated to storing data for the current thread

Gets the data for the current thread: get ();

Sets the data bound to the current thread: set (value)

Delete the data bound to the current thread: remove ()

Two, the connection pool part 1. Introduction to connection pooling (1) What is a connection pool

Pool that holds database connection resources (Connection)

(2) Why connection pooling is required

1) Save the connection resources to improve the performance of the program

2) Prevent database server connection resource overflow

(3) The principle of the inner connection pool

1) connection Pooling Initializes some connection resources as soon as they are created

2) When using connection, instead of creating a resource from the pool

3) When the resource is used, it is not destroyed but the resource is returned to the pool

(4) Custom connection pooling

1  Public classmyDataSource {2     //1. Create a pool3     Private StaticLinkedlist<connection> DataSource =NewLinkedlist<connection>();4     //2. Initialize some connection resources for the pool when creating datasource5      PublicmyDataSource (intcount) {6          for(inti=0;i<count;i++){7             Try {8                 //Conn is the object provided by the MySQL driver package9Connection conn =jdbcutils.getconnection ();Ten                 //How to strengthen a method in an object One                 //BufferedReader reader = new BufferedReader (New FileReader ("")); AConnectionwrapper wrapper =NewConnectionwrapper (conn); -                 //some enhancement to Conn before placing the connnection resource in the pool (the main enhancement is the Close method) - Datasource.add (wrapper); the}Catch(Exception e) { - e.printstacktrace (); -             } -         } +     } -     //ways to get connection +      Public StaticConnection getconnection () { A         returnDatasource.removefirst (); at     } -     //put the finished resource back in the pool -      Public Static voidGiveback (Connection conn) { -System.out.println ("Quantity before return:" +datasource.size ()); - Datasource.addlast (conn); -SYSTEM.OUT.PRINTLN ("number returned:" +datasource.size ()); in     } -      Public Static voidMain (string[] args)throwsSQLException { tomyDataSource DataSource =NewmyDataSource (10); +Connection conn =datasource.getconnection (); - SYSTEM.OUT.PRINTLN (conn); the         //calling the PreparedStatement object *PreparedStatement pstmt = conn.preparestatement ("SELECT * From Account"); $ System.out.println (pstmt);Panax Notoginseng         ///datasource.giveback (conn); -         //problem: What you want to do is to put Conn back in the pool when Conn calls the Close method instead of destroying it . the conn.close (); +     } A}

Decorator Mode:

1) The enhanced class must implement the same interface as the strengthened class

2) to pass the strengthened class as a constructor parameter

3) To strengthen the method of rewriting the logic, do not need to strengthen the method called by the enhanced

2. Common open source connection pooling technology

Dbcp:apache Foundation's Open source connection pooling Technology Commons Project Neutron project

C3P0: Open source connection Pooling technology

(1) DBCP Connection Pooling Technology

Download the jar Package

Importing JAR Packages

1  Public classDbcpdemo {2 @Test3      Public voidTest1 ()throwssqlexception{4         //1. Get a connection pool5Basicdatasource DataSource =NewBasicdatasource ();6         //set up information for a database7Datasource.setdriverclassname ("Com.mysql.jdbc.Driver");8Datasource.seturl ("Jdbc:mysql:///database");9Datasource.setusername ("root");TenDatasource.setpassword ("root"); One         //2. Get a connection resource AConnection conn =datasource.getconnection (); - SYSTEM.OUT.PRINTLN (conn); -         //3. Return the resources the conn.close (); -     } - @Test -      Public voidTest2 ()throwsexception{ +InputStream in = Dbcpdemo.class. getClassLoader (). getResourceAsStream ("Dbcp.properties"); -Properties Pro =NewProperties (); + pro.load (in); ADataSource DataSource =Basicdatasourcefactory.createdatasource (pro); atConnection conn =datasource.getconnection (); - SYSTEM.OUT.PRINTLN (conn); - conn.close (); -     } -}

(2) C3P0 Connection Pool

Download the jar Package

Importing JAR Packages

1  Public classC3p0demo {2 @Test3      Public voidTest1 ()throwsexception{4         //Create a connection pool5Combopooleddatasource DataSource =NewCombopooleddatasource ();6         //set four basic parameters7Datasource.setdriverclass ("Com.mysql.jdbc.Driver");8Datasource.setjdbcurl ("Jdbc:mysql:///database");9Datasource.setuser ("root");TenDatasource.setpassword ("root"); One         //Get Connections AConnection conn =datasource.getconnection (); - SYSTEM.OUT.PRINTLN (conn); - conn.close (); the     } - @Test -      Public voidTest2 ()throwssqlexception{ -         //Create a connection pool +         //Combopooleddatasource dataSource = new Combopooleddatasource ("Haohao"); -Combopooleddatasource DataSource =NewCombopooleddatasource (); +Connection conn =datasource.getconnection (); A SYSTEM.OUT.PRINTLN (conn); at conn.close (); -     } -}

Java 04javaweb-05 transactions and connection pooling