Java Web (v)--Transaction & Database connection pool & Dbutiles

#事务 & Database Connection pool &dbutils# #事务 > Transaction actually refers to a set of actions that contain many single logic. As long as there is a logic that does not execute successfully, it is a failure. All data is returned to the original state (rollback) * Why there is a transaction?> in order to ensure the success of the logic. Example: Bank transfer. # # #使用命令行方式演示事务. * Open transaction start transaction;* commit or ROLLBACK TRANSACTION commit;  Commit the transaction and the data will be written to the database on disk rollback; Data is rolled back to its original state. 1. Turn off the auto-submit feature. [Icon] (img/img01.png) 2. Demo Transaction! [Icon] (img/img02.png) # # #使用代码方式演示事务 > code inside the transaction, mainly for the connection.  >>>1. Turn off autocommit settings by Conn.setautocommit (false). >2. Commit Transaction Conn.commit (); >3. Rollback TRANSACTION conn.rollback (); @Testpublic void Testtransaction () {Connection conn = null; PreparedStatement PS = null; ResultSet rs = null;try {conn = Jdbcutil.getconn ();//connection, transaction is automatically committed by default. Turn off auto-commit. Conn.setautocommit (FALSE); String sql = "Update account Set money = money-?" WHERE id =? "; PS = conn.preparestatement (SQL);//deduction of the money, the deduction ID 1 of 100 dollars ps.setint (1, 1);p the S.setint (2); int a = 10/0;//Plus Money, give ID 2 plus 100 bucks ps.setint (1, -100);p s.setint (2, 2);p s.executeupdate ();//SUCCESS: Commit the transaction. Conn.commit ();} catch (SQLException e) {try {//incident: ROLLBACK TRANSACTION conn.rollback ();} catch (Sqlexception E1) {e1.printstacktrace ();} E.printstacktrace ();} finally {jdbcutil.release (conn, PS, RS);}} # # #事务的特性 * atomicity > refers to the logic contained in the transaction and is indivisible. * Consistency > refers to before and after the execution of a transaction. Data integrity * Isolation > refers to the fact that transactions should not be affected by other transactions during execution * persistence > refers to a successful transaction execution, then the data should persist to disk. # # #事务的安全隐患 > Regardless of isolation level settings, the following issues occur. * Read > Dirty read unreadable reading. * Dirty Read > One transaction read to another transaction uncommitted data * Non-REPEATABLE READ > One transaction reads the data submitted by another transaction, resulting in inconsistencies between the previous and two query results. # # # #读未提交 Demo 1. Set the isolation level of a window to READ UNCOMMITTED! [Icon] (img/img03.png) 2. Two windows open a transaction separately! [Icon] (img/img04.png) * Write > Lose update # # # Read submitted demo 1. Set the isolation level of a window to read Committed! [Icon] (img/img05.png) 2. A b Two windows open the transaction, perform the update operation in the b window. [Icon] (img/img06.png) 3. The query results performed in the a window are inconsistent. One time is before the b window commits the transaction, once after the b window commits the transaction. [Icon] (img/img07.png) > This isolation level is capable of masking dirty reads, but it raises another issue that cannot be read repeatedly.  # # #可串行化 > If there is a connection isolation level set in order to serialize, then who first opened the transaction, who has the first execution of the right, who opened the transaction, who can only get, and so on the front of that transaction, commit or rollback, before execution. However, this isolation level is generally less used. Easy to cause performance problems. Low efficiency. * by efficiency, from high to low > READ UNCOMMITTED > Read submitted > Repeatable READ > Serializable * by interception level, from high to bottom > Serializable > Repeatable READ > Read submitted > READ UNCOMMITTED # #事务总结 # # #需要掌握的1 . Transaction Conn.setautocommit (FALSE) is used in the Code, Conn.commit (); Conn.rollback (); 2. TransactionJust for the connection connection object, if another connection object is opened, then that is the default commit. 3. The transaction is automatically committed. # # # #需要了解的 # # # #安全隐患读脏读一个事务读到了另一个事务未提交的数据不可重复读一个事务读到了另一个事务已提交的数据, resulting in inconsistencies between the previous two query results a transaction reads the data of another transaction insert, causing inconsistent query results before and after. Write lost updates. # # # #隔离级别读未提交 > Problem: Dirty Read Committed > Resolved: Dirty Read, raised: Non-repeatable read REPEATABLE READ > Resolution: Dirty Read, non-repeatable read, unresolved: Phantom read serializable > Resolution: Dirty Read, non-repeatable read, Phantom read. MYSQL default Isolation level is REPEATABLE read Oracle The default isolation level is read Committed # # #丢失更新! [Icon] (img/img08.png) # # #解决丢失更新 * Pessimistic lock > can join for update! when querying [Icon] (img/img09.png) * Optimistic locking > requires programmers to control themselves. [Icon] (img/img10.png) # #数据库连接池 >1. Database Connection object creation works to compare consumption performance.  >2. At first, a space (collection) is opened in memory, and a open first is placed into the pool to place multiple connection objects. If you need to connect back, go straight from the pool.  Do not go to create a connection yourself. After use, remember to return the connection. Make sure the Connection object is recycled. [Icon] (img/img11.png) # # #自定义数据库连接池 * Code Implementation * Problems occur: 1. You need to remember Addback Method 2 extra.  Single case.   3. Interface-oriented programming is not possible. Userdao dao = new Userdaoimpl ();   Dao.insert ();  DataSource DataSource = new myDataSource ();   Because there is no Addback method defined inside the interface.   4. How to solve? Take Addback as the starting point. # # #解决自定义数据库连接池出现的问题. > Because there is a Addback method, the place where this connection pool is used requires extra memory of this method and cannot be programmed for interfaces.  > We are going to modify that close method in the interface. The Close method of the original connection object is to really close the connection. > intends to modify this close method, and later in calling close, does notis actually closed, but the connection object is returned. # # # #如何扩展某一个方法?> The original method logic, not what we want. Want to modify your own logic 1. Direct changes to the source code cannot be achieved. 2. Inheritance, you have to know who the specific implementation of this interface is. 3. Use decorator mode. # #开源连接池 # # # DBCP1. Import Jar file 2. Do not use configuration file: public void testDBCP01 () {Connection conn = null; PreparedStatement PS = null;try {//1. Building the data source object Basicdatasource DataSource = new Basicdatasource ();//What type of database is accessed and which database , user name, password. Jdbc:mysql://localhost/bank Master Protocol: Sub-Protocol://local/Database Datasource.setdriverclassname ("Com.mysql.jdbc.Driver"); Datasource.seturl ("Jdbc:mysql://localhost/bank");d atasource.setusername ("root");d Atasource.setpassword ("root") ;//2. Get the Connection Object conn = Datasource.getconnection (); String sql = "INSERT into account values (null,?,?)"; PS = conn.preparestatement (sql);p s.setstring (1, "admin");p s.setint (2, +);p s.executeupdate ();} catch (SQLException e) {e.printstacktrace ();} finally {jdbcutil.release (conn, PS);}} 2. How to use the configuration file: Connection conn = null; PreparedStatement PS = null;try {basicdatasourcefactory factory = new Basicdatasourcefactory (); Properties Properties = new properties (); inputstrEAM is = new FileInputStream ("Src//dbcpconfig.properties");p roperties.load (IS);D atasource DataSource = Factory.createdatasource (properties);//2. Get the Connection Object conn = Datasource.getconnection (); String sql = "INSERT into account values (null,?,?)"; PS = conn.preparestatement (sql);p s.setstring (1, "Liangchaowei");p s.setint (2, p);p s.executeupdate ();} catch (Exception e) {e.printstacktrace ();} finally {jdbcutil.release (conn, PS);} * c3p0> copy jar file to lib directory # # #不使用配置文件方式Connection conn = null; PreparedStatement PS = null;try {//1. Create Datasourcecombopooleddatasource DataSource = new Combopooleddatasource ();//2. Set the connection data information Datasource.setdriverclass ("Com.mysql.jdbc.Driver");//Forget---> Go to previous code---> The JDBC document Datasource.setjdbcurl ("Jdbc:mysql://localhost/bank");d atasource.setuser ("root");d Atasource.setpassword ( "Root");//2. Get the Connection Object conn = Datasource.getconnection (); String sql = "INSERT into account values (null,?,?)"; PS = conn.preparestatement (sql);p s.setstring (1, "admi234n");p S.setint (2, 103200);p S.executeUpdate ();} catch (Exception e) {e.printstacktrace ();} finally {jdbcutil.release (conn, PS);} # # #使用配置文件方式//The Default-config branch in XML is found by default. Combopooleddatasource DataSource = new Combopooleddatasource ();//2. Set the connection data information Datasource.setdriverclass ("Com.mysql.jdbc.Driver");//Forget---> Go to previous code---> The JDBC document Datasource.setjdbcurl ("Jdbc:mysql://localhost/bank");d atasource.setuser ("root");d Atasource.setpassword ( "Root");//2. Get the Connection Object conn = Datasource.getconnection (); String sql = "INSERT into account values (null,?,?)"; PS = conn.preparestatement (sql);p s.setstring (1, "admi234n");p S.setint (2, 103200); # #DBUtils # # #增删改//dbutils It just helped us simplify the CRUD code, but the creation of the connection and the job of getting it. Not in his consideration queryrunner Queryrunner = new Queryrunner (new Combopooleddatasource ());//Add//queryrunner.update ("INSERT INTO Account values (null,?,?) "," AA ", 1000);//delete//queryrunner.update (" Delete from account where id =? ", 5);//Update//queryru Nner.update ("Update account set money =?") WHERE id =? ", 10000000, 6); # # #查询1. The anonymous implementation class of the direct new interface Queryrunner QueryrunnER = new Queryrunner (new Combopooleddatasource ()); Account account = Queryrunner.query ("SELECT * from account where id =?", new Resultsethandler<account> () {@Overrid  Epublic Account Handle (ResultSet Rs) throws SQLException {Account account = new account (); while (Rs.next ()) {String name = Rs.getstring ("name"), int money = Rs.getint ("Money"), Account.setname (name); Account.setmoney (money);}  return account;} }, 6); System.out.println (Account.tostring ()); 2. Directly use the implementation class that the framework has written. * Query Single object Queryrunner queryrunner = new Queryrunner (new Combopooleddatasource ());//Query single object Account account = Queryrunner.query ("SELECT * from account where id =?", new Beanhandler<account> (Account.class), 8); * Querying multiple Objects Queryru Nner Queryrunner = new Queryrunner (new Combopooleddatasource ()); list<account> list = Queryrunner.query ("SELECT * from Account", New Beanlisthandler<account> (Account.class ); # # #ResultSetHandler Common Implementation class The following two are the most frequently used Beanhandler, the single data being queried is encapsulated into an object Beanlisthandler, and the multiple data being queried is encapsulated into a list< object > ------------------------------------------Arrayhandler, the single data that is queried is encapsulated into an array arraylisthandler, and the multiple data being queried is encapsulated into a collection, The elements inside the collection are arrays. Maphandler, the single data that is queried is encapsulated into a mapmaplisthandler, and the multiple data that is queried is encapsulated into a collection, and the elements inside the collection are map. columnlisthandlerkeyedhandlerscalarhandler# Summary # #事务使用命令行演示使用代码演示脏读, non-repeatable read, Phantom read lost update pessimistic lock optimistic lock 4 isolation level READ UNCOMMITTED Read committed repeatable read Serializable # # Data Connection Pool * DBCP do not use configuration using configurations * C3P0 do not use configuration using configurations (must master) * Custom Connection Pool decorator Mode # #DBUtils > simplifies our crud, which defines a generic crud approach. Queryrunner.update (); queryrunner.query

Java Web (v)--Transaction & Database connection pool & Dbutiles