This article describes the Java implementation of transactions, bank transfers and goods in and out of warehouses based on JDBC. Share it for your reference, as follows:
1. Transfer business
When the transfer must execute 2 SQL statements (update updates) and both of them are successful, the transaction is submitted. If one fails, both of them will roll back the transaction.
2. A transaction should have 4 attributes : atomicity, consistency, isolation, and persistence. These four attributes are usually called ACID attributes.
① Atomicity. A transaction is an inseparable unit of work, and all operations included in the transaction are either done or not.
② Consistency. A transaction must be to change the database from one consistent state to another. Consistency is closely related to atomicity.
③ Isolation. The execution of a transaction cannot be interfered with by other transactions. That is, the operations within a transaction and the data used are isolated from other concurrent transactions, and the transactions executed concurrently cannot interfere with each other.
④ Durability. Permanence is also called permanence, which means that once a transaction is committed, its changes to the data in the database should be permanent. Other subsequent operations or failures should not have any impact on them.
3. Here is a demo description using the transfer operation as a case, and the points of attention are as follows:
(1) A SQL statement operation does not require manual transaction submission
(2) The query operation does not require manual transaction submission.
(3) However, update, insert, and delete require manual transaction submission
(4) If the balance is negative, you can query the results first and then make a judgment, and then operate
(5) con.setAutoCommit(false); manually start the transaction; con.commit(); manually commit the transaction; con.rollback(); transaction rollback operation
4. First create a data table or two data tables. Here, create two data tables for clarity of demonstration.
5. Create a database and data table, and then write the tool class BaseDao.java
package com.bie.utils;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/*** @author BieHongLi* @version Created: February 27, 2017 at 10:09:00 am* Tool class for connecting to database*/public class BaseDao { private static String driver="com.mysql.jdbc.Driver"; private static String url="jdbc:mysql:///test"; private static String user="root"; private static String password="123456"; /*** * Method to connect to the database* @return * @throws ClassNotFoundException * @throws SQLException */ public static Connection getCon() throws ClassNotFoundException, SQLException{ Class.forName(driver);//Load the database driver System.out.println("Test loading the database successfully"); Connection con=DriverManager.getConnection(url, user, password); System.out.println("Test database link successfully"); return con; } /*** * Method to close the database* @param con * @param ps * @param rs */ public static void close(Connection con,PreparedStatement ps,ResultSet rs){ if(rs!=null){// Close the resource to avoid exception try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(ps!=null){ try { ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(con!=null){ try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /*** * Agree to the method of adding, deleting and modifying* @param sql * @param arr * @return */ public static boolean addUpdateDelete(String sql,Object[] arr){ Connection con=null; PreparedStatement ps=null; try { con=BaseDao.getCon();//Step 1: Operation to connect to the database ps=con.prepareStatement(sql);//Step 2: Precompile//Step 3: Set the value if(arr!=null && arr.length!=0){ for(int i=0;i<arr.length;i++){ ps.setObject(i+1, arr[i]); } } int count=ps.executeUpdate();//Step 4: Execute the sql statement if(count>0){ return true; }else{ return false; } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return false; } /*public static void main(String[] args) { try { BaseDao.getCon(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }*/}6. The test is directly carried out in the main method, so I wrote UserDao for testing and just look at the effect.
package com.bie.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import com.bie.utils.BaseDao;/*** @author BieHongLi* @version Creation time: February 27, 2017 at 3:19:49 pm**/public class UserDao { /*** * Note: * (1) An SQL statement operation does not require manual transaction submission* (2) Query operation does not require manual transaction submission, * (3) However, update, insert, and delete requires manual transaction submission* (4) If the balance is negative, you can query the result first and then judge it, and then operate it* @param args */ public static void main(String[] args) { Connection con=null; PreparedStatement ps1=null; PreparedStatement ps2=null; try { //Step 1: The operation of connecting to the database con=BaseDao.getCon(); //The transaction is automatically submitted by default, so the default is true. Now change true to false, and the default automatic transaction is prohibited //con.setAutoCommit(true); //Transaction 1: If set to false, the transaction needs to be submitted manually con.setAutoCommit(false); //Step 2: The updated SQL statement String sql="update bank_a set usera_rmb=usera_rmb-500 where usera_id=1 "; String sql2="update bank_b set userb_rmb=userb_rmb+500 where userb_id=1 "; //Step 3: Precompile sql ps1=con.prepareStatement(sql); //Step 4: Execute the sql statement. Although SQL has been executed, it has not been persisted and updated to the database ps1.executeUpdate(); //Step 3: Precompile sql2 ps2=con.prepareStatement(sql2); //Step 4: Execute sql2. Although SQL2 is executed, it has not been persisted and updated to the database ps2.executeUpdate(); System.out.println("Transfer successful..."); //Transaction 2: manually commit the transaction. If both executions are successful, then commit the transaction con.commit(); } catch (ClassNotFoundException e) { e.printStackTrace(); try { //Transaction 3: If an exception is thrown, then roll back the transaction con.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } catch (SQLException e) { e.printStackTrace(); } finally { //Close the resource to avoid exceptions BaseDao.close(con, ps1, null); BaseDao.close(null, ps2, null); } }}The demo effect is as follows:
For more information about Java related content, please check out the topics of this site: "Summary of Java's skills to operate databases using JDBC", "Summary of Java+MySQL database programming", "Tutorial on Java Data Structure and Algorithm", "Summary of Java File and Directory Operation Skills", "Summary of Java Operation DOM Node Skills" and "Summary of Java Cache Operation Skills"
I hope this article will be helpful to everyone's Java programming.