Summary of database transactions

Java database Development using JDBC, its development steps are: 1. Registration drive; 2. access to links; 3. Execute sql;4. Release resources.

The transaction of a database refers to a logically indivisible set of operations, either full success or failure.

How MySQL transactions are opened :

Start transaction; --open transaction commit; --Submission of transaction rollback; --Transaction rollback

The JDBC design has a transaction savepoint, which enables the rollback operation to this savepoint.

SavePoint savepoint = Conn.setsavepoint ();

properties of the firm : 1. atomicity, 2. Consistency, 3. Isolation, 4. Persistence.

1. Stresses the indivisibility of matters;

2. Consistency of data integrity at the time of transaction execution;

3. The implementation of a single transaction should not be affected by another transaction;

4. After execution of the transaction, the data becomes persistent.

Because of the isolation of the transaction, the database transaction produces three kinds of read problems: Dirty read, non-repeatable read, Phantom read.

Dirty reads: One transaction reads uncommitted data from another transaction.

When the transaction isolation level is READ UNCOMMITTED , one transaction execution is not committed, the other transaction reads the data, and the previous thing rolls back, so that the second transaction reads untrue data.

Non-REPEATABLE READ: One transaction reads data that has been committed by another transaction (typically performed update operation)

When the transaction is read for the first time, it needs to make two queries at this time, while the update of the other transaction is between two queries, and the data is different when the other transaction is updated and then queried. At this point their isolation level is Read Committed.

　　Phantom read: One transaction reads data that has already been committed to another transaction (typically performed insert operation)

The phantom reading of MySQL database is probabilistic and not easy to demonstrate.

four isolation levels for the database:

READ UNCOMMITTED is  not submitted for reading, so dirty read, non-repeatable read, Phantom read is likely to occur read Committed  has been committed to reading, can avoid dirty read, can not avoid non-repeatable read and Phantom read REPEATABLE read  repeat read, Avoid dirty read, non-repeatable read, but Phantom reading is still possible to generate serializable  serial read, to avoid all abnormal read generation

Their security is incremental and efficiency is diminishing.

The default for MySQL is read committed in repeatable read;orical.

To view the isolation level of a transaction by command: * SELECT @ @tx_isolation; Set DATABASE Isolation Level: * Set session TRANSACTION isolation levels isolation level;

database connection pooling : Because database linked objects are time-consuming to create, you can save server memory and improve efficiency if you use pre-created linked objects and reuse them.

The Close method of linking the connection pool is designed to return the link to the connection pool instead of destroying it.

Open Source database connection pool: Dbcp,c3p0,tomcat built-in connection pooling (Jndi technology).

ThreadLocal: A thread package that can store some objects in a thread as a global object of this thread. Its principle is to maintain a map collection, put the K,v object is this thread object and need to save the global object.

Class Mythreadlocal<t> {private map<thread,t> Map = new hashmap<thread,t> ();p ublic void Set (T value) {m Ap.put (Thread.CurrentThread (), value);} public void Remove () {Map.Remove (Thread.CurrentThread ());} Public T get () {return map.get (Thread.CurrentThread ());}}

service layer and transaction : The DAO layer performs a single operation on the data source, and the service layer is better suited as the storage layer for the transaction. Multiple methods of the DAO layer are called in the service layer, and the same linked object is used.

Example: Use C3P0 to implement a transfer transaction. Service Layer-dao Layer-model layer

Model JavaBean

Package Jdbcconectionpools;public class Account {private int id;private String name;private float money;public account () { }public account (int ID, String name, float money) {super (); this.id = Id;this.name = Name;this.money = money;} public int getId () {return ID;} public void setId (int id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} public float Getmoney () {return money;} public void Setmoney (float money) {This.money = money;}}

DAO layer

Package Jdbcconectionpools;import Java.sql.connection;import Java.sql.preparedstatement;import java.sql.ResultSet; Import Java.sql.sqlexception;import java.sql.statement;import Java.util.arraylist;import java.util.List;public        Class Accountdao {public list<account> selectallaccount (Connection conn) {String sql = ' SELECT * from account ';        Statement Statement;        ResultSet rs = null; List<account> list =new arraylist<account> (); try {statement = Conn.createstatement (); rs = Statement.executequery (SQL), while (Rs.next ()) {int id = rs.getint ("id"); String name = rs.getstring ("name"), Float money = rs.getfloat ("Money"), account A = new account (Id,name,money); List.add (a) ; SYSTEM.OUT.PRINTLN ("ID:" +id+ "Name:" +name+ "money:" +money);}}          catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();} return list;} Transfer out account public void Decaccount (Connection conn,string name,float number) {String sql = "Update accounts set money = Money-?" WHERE name =? "; PReparedstatement stmt = null; ResultSet rs = null;try {stmt = conn.preparestatement (sql); Stmt.setfloat (1, number); Stmt.setstring (2, name); Stmt.executeupdate ();} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();}} Transfer account public void Incaccount (Connection conn,string name,float number) {String sql = "Update accounts set money = Money +?" WHERE name =? "; PreparedStatement stmt = null; ResultSet rs = null;try {stmt = conn.preparestatement (sql); Stmt.setfloat (1, number); Stmt.setstring (2, name); Stmt.executeupdate ();} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();}}        Single Search public account Selectuser (Connection conn,string name) {String sql = ' SELECT * from account where name =? ';        PreparedStatement statement;        ResultSet rs = null;        Account A = null; try {statement = conn.preparestatement (SQL); statement.setstring (1, name); rs = Statement.executequery (); if (Rs.next ()) {a = new account (Rs.getint ("id"), rs.getstring ("name"), RS.getfloat ("money"));}} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();} return A;}}

Service Layer

Package Jdbcconectionpools;import Java.sql.connection;import Java.sql.sqlexception;import java.util.List;import Org.junit.test;public class Accountserivce {//Search transaction public list<account> Selectaccountservice () {Connection conn = Jdbcutils.getconnection (); Accountdao dao = new Accountdao (); List<account> list = null;try {jdbcutils.begintransaction (); list = Dao.selectallaccount (conn); Jdbcutils.committransaction ();} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace (); try {jdbcutils.rollbacktransaction ();} catch (SQLException E1) {//TODO auto-generated catch Blocke1.printstacktrace ();}} return list;} Make money service public void transferaccounts (String from,string to,float number) {Connection conn = jdbcutils.getconnection (); Accountdao dao = new Accountdao (), try {jdbcutils.begintransaction ();d Ao.decaccount (conn, from, number);d Ao.incaccount (conn, to, number); Account account = Dao.selectuser (conn, from); if (account! = null) if (Account.getmoney () < 0) { System.err.println ("YuInsufficient amount! Can't transfer money! "); Jdbcutils.rollbacktransaction ();} else {jdbcutils.committransaction ();} Elsejdbcutils.rollbacktransaction ();} catch (SQLException e) {e.printstacktrace (); try {jdbcutils.rollbacktransaction ();} catch (SQLException E1) { E1.printstacktrace ();}}} @Testpublic void Test () {Accountserivce service = new Accountserivce (); Service.transferaccounts ("Meimei", "Nozomi", 10000); list<account> list = Service.selectaccountservice (); for (int i =0;i<list.size (); i++) System.out.println ( List.get (i). GetId () + "" +list.get (i). GetName () + "+list.get (i). Getmoney ());}}

Tool class

Package Jdbcconectionpools;import Java.sql.connection;import Java.sql.resultset;import java.sql.SQLException; Import Java.sql.statement;import Com.mchange.v2.c3p0.combopooleddatasource;public class Jdbcutils {private static Combopooleddatasource CPDs = null;private static threadlocal<connection> tl = new threadlocal<connection> () static {//Here is a good, write the configuration file, want to change the database, simple CPDs = new Combopooleddatasource ("MySQL");//This is the MySQL database} public static Connection get Connection () {Connection conn = Tl.get (); try {if (conn = = NULL) {conn = Cpds.getconnection (); Tl.set (conn);}} catch (Sqlex Ception E1) {//TODO auto-generated catch Blocke1.printstacktrace ();}           Return conn; }//transaction about public static void BeginTransaction () throws Sqlexception{connection conn = Tl.get (); if (conn = = NULL) {conn = CP Ds.getconnection (); Tl.set (conn);} Conn.setautocommit (false);} public static void CommitTransaction () throws Sqlexception{connection conn = Tl.get (); if (conn = = NULL) {conn = Cpds.getconn Ection (); Tl.set (COnn);} Conn.commit (); Tl.remove ();} public static void RollbackTransaction () throws Sqlexception{connection conn = Tl.get (); if (conn = = NULL) {conn = Cpds.getco Nnection (); Tl.set (conn);} Conn.rollback (); Tl.remove ();} Method of releasing resources public static void release (ResultSet RS, Statement stmt, Connection conn) {if (rs! = null) {try {rs.close ();} CA TCH (SQLException e) {e.printstacktrace ();} rs = null;} if (stmt! = null) {try {stmt.close ();} catch (SQLException e) {e.printstacktrace ();} stmt = null;} IF (conn! = null) {try {conn.close ();} catch (SQLException e) {e.printstacktrace ();} conn = null;}} public static void Release (ResultSet rs,statement stmt) {if (rs! = null) {try {rs.close ();} catch (SQLException e) {E.prin Tstacktrace ();} rs = null;} if (stmt! = null) {try {stmt.close ();} catch (SQLException e) {e.printstacktrace ();} stmt = null;}}  public static void Release (Statement stmt, Connection conn) {if (stmt! = null) {try {stmt.close ();} catch (SQLException e) {E.printstacktrace ();} stmt = null;} IF (conn! = null) {try {conn.close ();} catch (SQLException e) {e.printstacktrace ();} conn = null;}}}

Configuration file C3p0-config.xml

<?XML version= "1.0" encoding= "UTF-8"?><C3p0-config>      <!--This is the default config! -      <Default-config>          < Propertyname= "Initialpoolsize">10</ Property>          < Propertyname= "MaxIdleTime">30</ Property>          < Propertyname= "Maxpoolsize">100</ Property>          < Propertyname= "Minpoolsize">10</ Property>          < Propertyname= "Maxstatements">200</ Property>      </Default-config>        <!--This is my config for MySQL -      <Named-configname= "MySQL">          < Propertyname= "Driverclass">Com.mysql.jdbc.Driver</ Property>          < Propertyname= "Jdbcurl">Jdbc:mysql://localhost:3306/mydata?characterencoding=utf-8</ Property>          < Propertyname= "User">Root</ Property>          < Propertyname= "Password">Qwert123</ Property>          < Propertyname= "Initialpoolsize">10</ Property>          < Propertyname= "MaxIdleTime">30</ Property>          < Propertyname= "Maxpoolsize">100</ Property>          < Propertyname= "Minpoolsize">10</ Property>          < Propertyname= "Maxstatements">200</ Property>      </Named-config>  </C3p0-config>

Encountered two exceptions: 1. The query is suitable for USE statement cannot be set? 2. When using Chinese query SQL command line can be checked, JDBC cannot find, set utf-8 after the found.

Design Summary: 1. The toolkit provides creation and recycling of connection objects, and uses Threadlocal to save the linked object, removing the linked object from the thread after the commit of the transaction.

Only DAO layer operations are performed in the 2.service layer, not JDBC programming.

The 3.dao layer does not have to get the link object, execute SQL with the parameter link object.

Summary of database transactions