Detailed explanation of JDBC sample code for Java transaction management learning

This article mainly introduces the relevant information of JDBC for Java transaction management learning. The introduction in the article is very detailed. I believe it has certain reference value for everyone. Friends who need it can take a look below.

What is a Java transaction

The usual concept is that transactions are only related to the database.

Transactions must comply with the ACID principles established by ISO/IEC. ACID is the abbreviation for atomicity, consistency, isolation and durability. The atomicity of a transaction means that any failure during transaction execution will cause any modifications made by the transaction to become invalid. Consistency means that when a transaction fails, all data affected by the transaction should be restored to the state before the transaction was executed. Isolation means that modifications to data during transaction execution are not visible to other transactions before the transaction is committed. Persistence means that the status of the submitted data should be correct when the transaction execution fails.

In layman’s terms, a transaction is a set of atomic operation units. From a database perspective, it is a set of SQL instructions. Either all of them are executed successfully. If one of the instructions is executed incorrectly for some reason, then Undo all previously executed instructions. The simpler answer is: either all executions are successful, or they are canceled and not executed.

Since the concept of transaction comes from the database, what is a Java transaction? What's the connection?

In fact, if a Java application system wants to operate a database, it is implemented through JDBC. Addition, modification, and deletion are all implemented indirectly through corresponding methods, and transaction control is also transferred to the Java program code accordingly. Therefore, database operation transactions are customarily called Java transactions.

Characteristics of transactions:

1) Atomicity: A transaction is a logical unit of work in the database, and it must be an atomic work Unit, for its data modifications, either all or none will be executed.

2) Consistency: When the transaction is completed, all data must be consistent. In the relevant database, all rules must be applied to transaction modifications to maintain the integrity of all data.

3) Isolation: The execution of a transaction cannot be affected by other transactions.

4) Durability: Once a transaction is submitted, the operation of the transaction is permanently stored in the DB. Even if you perform a rollback operation at this time, the changes will not be undone.

Transaction: It is a unit of concurrency control and a user-defined sequence of operations. Either all of these operations are done, or none of them are done, and they are an integral unit of work. Through transactions, SQL Server can bind a set of logically related operations together so that the server maintains data integrity. A transaction usually starts with begin transaction and ends with commit or rollback. Commint means submission, that is, all operations of committing a transaction. Specifically, all updates to the data in the transaction are written back to the physical database on the disk, and the transaction ends normally. Rollback means rollback, that is, some kind of failure occurs while the transaction is running and the transaction cannot continue. The system undoes all completed operations on the database in the transaction and rolls back to the state where the transaction started.

Auto-commit transactions: Each separate statement is a transaction. There is an implicit commit after each statement. (Default)

Explicit transaction: starts with begin transaction display and ends with commit or rollback.

Implicit transactions: When the connection operates in implicit transaction mode, the sql server database engine instance will automatically start a new transaction after committing or rolling back the current transaction. There is no need to describe the beginning of things, just commit or rollback each transaction. But each transaction still ends explicitly with commit or rollback. After the connection sets the implicit transaction mode to open, an implicit transaction will be automatically started when the database engine instance executes any of the following statements for the first time: alter table, insert, create, open, delete, revoke, drop , select, fetch, truncate table, grant, update, the transaction will remain valid until a commit or rollback statement is issued. After the first transaction is committed or rolled back, the database engine instance will automatically start a new transaction the next time the connection executes any of the above statements. This instance will continue to generate implicit transaction chains until implicit transaction mode is turned off.

JDBC transaction management

How to manage transactions when using JDBC. Take a look at the code directly

Sample code

/** 
 * @Title: JDBCTrans.java 
 * @Package com.oscar999.trans 
 * @Description: 
 * @author XM 
 * @date Feb 14, 2017 4:38:27 PM 
 * @version V1.0 
 */ 
package com.oscar999.trans; 
 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
import java.sql.Statement; 
 
/** 
 * @author 
 * 
 */ 
public class JDBCTrans { 
 
 public JDBCTrans() { 
 
 } 
 
 /** 
 * 
 * @param sHostName 
 * @param sPortNumber 
 * @param sSid 
 * @param userName 
 * @param password 
 * @return 
 * @throws SQLException 
 */ 
 public Connection getConnection(String sHostName, String sPortNumber, String sSid, String userName, String password) throws SQLException { 
 Connection conn = null; 
 String url = getOraclURL(sHostName, sPortNumber, sSid); 
 conn = DriverManager.getConnection(url,userName,password); 
 return conn; 
 } 
 
 /** 
 * 
 * @param conn 
 * @param sql 
 * @throws SQLException 
 */ 
 public void add(Connection conn, String sql) throws SQLException { 
 Statement stmt = null; 
 try { 
  stmt = conn.createStatement(); 
  stmt.execute(sql); 
 } catch (SQLException e) { 
  // TODO Auto-generated catch block 
  e.printStackTrace(); 
 } finally { 
  if (stmt != null) 
  stmt.close(); 
 } 
 } 
 
 /** 
 * @param args 
 */ 
 public static void main(String[] args) { 
 // TODO Auto-generated method stub 
 String sHostName = ""; 
 String sPortNumber = ""; 
 String sSid = ""; 
 String userName = ""; 
 String password = ""; 
 
 sHostName = ""; 
 sPortNumber = ""; 
 sSid = ""; 
 userName = ""; 
 password = ""; 
 
 try { 
  Class.forName("oracle.jdbc.driver.OracleDriver"); 
 } catch (ClassNotFoundException e1) { 
  // TODO Auto-generated catch block 
  e1.printStackTrace(); 
 } 
  
 JDBCTrans jdbcTrans = new JDBCTrans(); 
 Connection conn = null; 
 try {  
  conn = jdbcTrans.getConnection(sHostName, sPortNumber, sSid, userName, password); 
  conn.setAutoCommit(false);// can't insert, update 
  
  //1. add SQL 
  String addSQL = "insert into TEST_TABLE values('name1','value1')"; 
  jdbcTrans.add(conn,addSQL); 
  
 } catch (SQLException e) { 
  // TODO Auto-generated catch block 
  e.printStackTrace(); 
 } finally { 
  /*if (conn != null) 
  { 
  try { 
   conn.close(); 
  } catch (SQLException e) { 
   // TODO Auto-generated catch block 
   e.printStackTrace(); 
  } 
  }*/ 
 } 
 
 } 
 
 private String getOraclURL(String sHostName, String sPortNumber, String sSid) { 
 String url = "jdbc:oracle:thin:@" + sHostName + ":" + sPortNumber + ":" + sSid; 
 return url; 
 } 
 
}

For the above code, the description is as follows:

There are several explanations for the above code Part:

1. conn.setAutoCommit(false) Do not commit the transaction after execution.

It has no effect on Select, but for Insert and Update, the data will not be modified if it is not submitted

2.  conn.close(); 关闭Connection的代码有被Mark掉， 是想呈现conn.setAutoCommit(false)的效果。

原因是在 Connection Close的时候会执行一次Commit.

而如果Connection是在应用服务器中使用连接池的话， Connection就不会被Close, 也就不会执行Commit.

3. setAutoCommit(false) 用法大多数是在要执行多条语句才提交。

所以针对以上第三点， 更接近实际的状况的代码如示例代码2

示例代码2

/** 
 * @Title: JDBCTrans.java 
 * @Package com.oscar999.trans 
 * @Description: 
 * @author XM 
 * @date Feb 14, 2017 4:38:27 PM 
 * @version V1.0 
 */ 
package com.oscar999.trans; 
 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
import java.sql.Statement; 
 
/** 
 * @author 
 * 
 */ 
public class JDBCTrans { 
 
 public JDBCTrans() { 
 
 } 
 
 /** 
 * 
 * @param sHostName 
 * @param sPortNumber 
 * @param sSid 
 * @param userName 
 * @param password 
 * @return 
 * @throws SQLException 
 */ 
 public Connection getConnection(String sHostName, String sPortNumber, String sSid, String userName, String password) throws SQLException { 
 Connection conn = null; 
 String url = getOraclURL(sHostName, sPortNumber, sSid); 
 conn = DriverManager.getConnection(url, userName, password); 
 return conn; 
 } 
 
 /** 
 * 
 * @param conn 
 * @param sql 
 * @throws SQLException 
 */ 
 public void add(Connection conn, String sql) throws SQLException { 
 Statement stmt = null; 
 try { 
  stmt = conn.createStatement(); 
  stmt.execute(sql); 
 } catch (SQLException e) { 
  // TODO Auto-generated catch block 
  e.printStackTrace(); 
 } finally { 
  if (stmt != null) 
  stmt.close(); 
 } 
 } 
 
 /** 
 * @param args 
 */ 
 public static void main(String[] args) { 
 // TODO Auto-generated method stub 
 String sHostName = ""; 
 String sPortNumber = ""; 
 String sSid = ""; 
 String userName = ""; 
 String password = ""; 
 
 sHostName = ""; 
 sPortNumber = ""; 
 sSid = ""; 
 userName = ""; 
 password = ""; 
 
 try { 
  Class.forName("oracle.jdbc.driver.OracleDriver"); 
 } catch (ClassNotFoundException e1) { 
  // TODO Auto-generated catch block 
  e1.printStackTrace(); 
 } 
 
 JDBCTrans jdbcTrans = new JDBCTrans(); 
 Connection conn = null; 
 try { 
  conn = jdbcTrans.getConnection(sHostName, sPortNumber, sSid, userName, password); 
  conn.setAutoCommit(false);// can't insert, update 
 
  // 1. add SQL 1 
  String addSQL = "insert into TEST_TABLE values('name1','value1')"; 
  jdbcTrans.add(conn, addSQL); 
 
  //2. add SQL 2 
  addSQL = "insert into TEST_TABLE values('name2','value2')"; 
  jdbcTrans.add(conn, addSQL); 
  
  conn.commit(); 
 } catch (SQLException e) { 
  // TODO Auto-generated catch block 
  if(conn!=null){ 
   try { 
   conn.rollback(); 
   } catch (SQLException e1) { 
   e1.printStackTrace(); 
   } 
  }  
  e.printStackTrace(); 
 } finally { 
  if (conn != null) { 
  try { 
   conn.close(); 
  } catch (SQLException e) { 
   // TODO Auto-generated catch block 
   e.printStackTrace(); 
  } 
  } 
 } 
 
 } 
 
 private String getOraclURL(String sHostName, String sPortNumber, String sSid) { 
 String url = "jdbc:oracle:thin:@" + sHostName + ":" + sPortNumber + ":" + sSid; 
 return url; 
 } 
 
}

这里需要说明的是： conn.rollback(); 

只要执行有异常，就要rollback , 这一步必不可少

如果没有在执行出现异常的时候进行回滚。如果在执行第一条语句之后出现异常，con既没有提交也没有回滚，表就会被锁住(如果oracle数据库就是行锁)，而这个锁却没有机会释放。

可能在执行con.close()的时候会释放锁，但还是如果应用服务器使用了数据库连接池，连接不会被断开。

总结

The above is the detailed content of Detailed explanation of JDBC sample code for Java transaction management learning. For more information, please follow other related articles on the PHP Chinese website!