I. Isolation Level of Transactions
Multiple transactions run simultaneously in the database. When they access the same data in the database, if the isolation mechanism is not adopted, some concurrency problems will occur. There are several concurrency problems in the database.
(1) Reading "dirty" data refers to the fact that transaction T1 does not submit a data after modifying it, and transaction T2 reads this data again (at this time, transaction T2 reads uncommitted data after modification of T1), and then causes T1 to be revoked (rollback) for some reason. At this time, the data read by transaction T2 is still uncommitted data modified before rollback of transaction T1, so it is called moral data read by transaction T2. For "dirty" data
(2) Phantom phenomenon. Transaction T1 reads some data records in the database according to certain conditions, while transaction T2 deletes some of them or inserts some records. When T1 reads the data again, it finds that some records disappear or some more records. This phenomenon is called phantom phenomenon.
(3) Non-repeatable reading means that transaction T2 modifies the data after transaction T1 reads the data, so that the next time transaction T1 reads the data again, it is inconsistent with the data read the previous time. This is called non-repeatable reading.
In order to solve these problems, a variety of transaction isolation levels are stipulated in the database. Different isolation levels have different anti-interference levels. The higher isolation level, the better data consistency, but the performance of the corresponding database is worse. Therefore, isolation level should be selected according to the specific situation. There are four isolation levels in MySQL: the following four:
(1) Reading uncommitted data. READ UNCOMMITTED allows transactions to read uncommitted data from other transactions. These three concurrency problems can occur
(2) Reading submitted data READ COMMITED only allows transactions to read data submitted by other transactions, avoiding reading "dirty data", but two other problems still arise.
(3) REPEATABLE READ ensures that a transaction can read the same data multiple times. During the operation of the transaction, other transactions can access the data delicately, avoiding reading "dirty" data and non-repeatable reading.
(4) Serialization SERIALIZABLE ensures that transactions can read the same row from a table. During the operation of this transaction, other transactions are prohibited from modifying the table. All concurrency problems can be avoided, but the performance of the database is poor.
In addition, the default transaction isolation level in MySQL is REPEATABLE READ, while Oracle supports only two transaction isolation levels, READ COMMITED and SERIALIZABLE, with READ COMMITED as the default transaction isolation level.
Therefore, in order to avoid concurrency when we use JDBC to perform operations on databases, we can set the isolation level by using the setTransaction Isolation () method in the Connection object. The parameters of this method are a constant, namely TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ and T. RANSACTION_SERIALIZABLE corresponds to the isolation level mentioned above.
Here is a concrete example to test the isolation level of transactions:
Now there is a table in the local database:
Here's the test
//Test classes are classes used to test the isolation level of transactions
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
public class TestTransaction {//Multiple operations of transactions
DAO dao = new DAO();
@Test
public void testTransactionIsolation(){//Transaction 1 performs update operations
Connection con = null;
try {
con = JDBCTools.getConnection();
con.setAutoCommit(false);
String sql = "UPDATE customer SET money=money-500 WHERE id=1";
dao.update(con, sql);
con.commit();//Add a breakpoint here for testing, stop here for debug, and run transaction 2 to see the results.
} catch (Exception e) {
e.printStackTrace();
}finally{
dao.release(null, con, null);
}
}
@Test
public void testTransactionIsolation1(){//Transaction 2 Query Operation
Connection con = null;
try {
con = JDBCTools.getConnection();
String sql = "SELECT money FROM customer WHERE id=1";
Integer value = getvalues(con, sql);
System.out.println(value);
} catch (Exception e) {
e.printStackTrace();
}finally{
dao.release(null, con, null);
}
}
/**
* Returns the value of a field in a record
* @param sql
* @param args
* @return
*/
public <E> E getvalues(Connection con,String sql,Object ... args){
PreparedStatement ps = null;
ResultSet rs = null;//The resulting set is only one row, one column
try {
con.setTransactionIsolation(con.TRANSACTION_READ_COMMITTED);//Set isolation level to read submitted
ps = con.prepareStatement(sql);
for(int i = 0;i < args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if(rs.next()){
return (E)rs.getObject(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(rs, null, ps);
}
return null;
}
}
//The DAO class contains methods for performing a series of operations on the database
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.commons.beanutils.BeanUtils;
public class DAO {
/**
* Update of database
* @param sql
* @param args
*/
public void update(Connection con,String sql,Object ... args){//Updating operation of database can realize the operation of adding, deleting and altering
PreparedStatement ps = null;
try {
ps = con.prepareStatement(sql);
for(int i = 0;i<args.length;i++){//Fill in placeholders
ps.setObject(i + 1, args[i]);
}
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(null, null, ps);
}
}
/**
* Returns the value of a field in a record
* @param sql
* @param args
* @return
*/
public <E> E getvalues(Connection con,String sql,Object ... args){
PreparedStatement ps = null;
ResultSet rs = null;//The resulting set is only one row, one column
try {
ps = con.prepareStatement(sql);
for(int i = 0;i < args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if(rs.next()){
return (E)rs.getObject(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(rs, null, ps);
}
return null;
}
/**
* Single query operation of database
* @param clazz
* @param sql
* @param args
* @return
*/
public <T> T getForOne(Connection con,Class<T> clazz,String sql,Object ... args){//Query operation of database
T entity = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = con.prepareStatement(sql);
for(int i = 0;i < args.length;i++){//Fill in placeholders
ps.setObject(i + 1,args[i]);
}
rs = ps.executeQuery();//Get the result set and use it to know the specific value of a column in a row
if(rs.next()){
ResultSetMetaData rsmd = rs.getMetaData();//This object knows how many columns the result set has, and the aliases corresponding to each column.
Map<String,Object> valueMap = new HashMap<>();//Create a Map set to store column names and corresponding attribute values in the result set
for(int i = 0;i < rsmd.getColumnCount();i++){
String ColumnLabel = rsmd.getColumnLabel(i + 1);//Get the column names for each column queried
Object ColumnValue = rs.getObject(i + 1);//Get the corresponding values for each column
valueMap.put(ColumnLabel, ColumnValue);
}
if(valueMap.size() > 0){
entity = clazz.newInstance();
for(Map.Entry<String, Object> entry : valueMap.entrySet()){//Apportionment of attributes by reflection
String fieldName = entry.getKey();
Object fieldvalue = entry.getValue();
// Field f1 = clazz.getDeclaredField(fieldName);
// f1.setAccessible(true);
// f1.set(entity, fieldvalue);
BeanUtils.setProperty(entity, fieldName, fieldvalue);//Assignment of Java class attributes
}
}
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(rs, null, ps);
}
return entity;
}
/**
* Operation of Multiple Queries Returning Result Sets in a Database
* @param clazz
* @param sql
* @param args
* @return
*/
public <T> List<T> getForList(Connection con,Class<T> clazz,String sql,Object ... args){
List<T> list = new ArrayList<>();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = con.prepareStatement(sql);
for(int i = 0;i < args.length;i++){
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();//Get the result set
List<Map<String,Object>> valueList = new ArrayList<>();//List collection for storing multiple records
ResultSetMetaData rsmd = rs.getMetaData();
Map<String,Object> map = null;//Map collection for storing a record
while(rs.next()){//Processing result sets
map = new HashMap<String,Object>();
for(int i = 0;i < rsmd.getColumnCount();i++){
String columLabel = rsmd.getColumnLabel(i + 1);
Object value = rs.getObject(i + 1);
//Store a record in the mao collection
map.put(columLabel, value);
}
valueList.add(map);
}
//Determine whether the valueList is empty or not, then traverse the set of valueList, get a Map object, and convert it to the object corresponding to the Class parameter.
T bean = null;
if(valueList.size() > 0){
for(Map<String,Object> each : valueList){
for(Map.Entry<String, Object> e : each.entrySet()){
String fieldname = e.getKey();
Object fieldvalue = e.getValue();
//Assigning values to corresponding Java class attributes
bean = clazz.newInstance();
BeanUtils.setProperty(bean, fieldname, fieldvalue);
}
//Put the T object in the list
list.add(bean);
}
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(rs, null, ps);
}
return list;
}
}
//JDBC Tool Class includes database connection, update, shutdown and other functions.
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
//JDBC Tool Class for Closing Database Connection Operations, Updating Operations and Query Operations
public class JDBCTools {
public static Connection getConnection() throws Exception{//Connect to the database
String driverClass = null;
String url = null;
String user = null;
String password = null;
Properties properties = new Properties();
InputStream in = Review.class.getClassLoader().getResourceAsStream("jdbc.properties");
properties.load(in);
driverClass = properties.getProperty("driver");
url = properties.getProperty("jdbcurl");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(driverClass);
return DriverManager.getConnection(url, user, password);
}
public static void release(Connection con , Statement state){//Close database connection
if(state != null){
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void release(ResultSet rs , Connection con , Statement state){//Close database connection
if(rs != null)
{
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(state != null){
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}The final result is
