JDBC thinking: transaction processing, DAO thinking

asds didn't have a thorough understanding of JDBC before. After all, most of them have been replaced by frameworks, but I firmly believe in the importance of the bottom layer. Because they have entered the life rhythm of the second year of graduate school and have heavy scientific research tasks, they don't have a deep look at the source code of JDBC. Therefore, here, they just understand the relevant notes of JDBC by Mr. Song Hongkang and pay attention to thinking!!!

JDBC transaction processing

Introduction to database transactions

sssa transaction: a set of logical operation units that transform data from one state to another.

sssa transaction processing (transaction operation): ensure that all transactions are executed as a unit of work. Even in case of failure, this execution mode cannot be changed. When multiple operations are performed in a transaction, or all transactions are committed, these modifications will be permanently saved; Or the database management system will discard all modifications made and roll back the whole transaction to the original state.

In order to ensure the consistency of data in the database, the manipulation of data should be a discrete group of logical units: when it is completed, the consistency of data can be maintained. When some operations in this unit fail, the whole transaction should be regarded as an error, and all operations from the starting point to the starting state.

JDBC transaction processing

Once sssa data is submitted, it cannot be rolled back.

When does sssa data mean submission?

ssdssa ① when a connection object is created, the transaction is automatically committed by default: each time an SQL statement is executed, if the execution is successful, it will be automatically committed to the database instead of rolling back.

ssdssa ② close the database connection and the data will be submitted automatically. If there are multiple operations, and each operation uses its own separate connection, the transaction cannot be guaranteed. That is, multiple operations of the same transaction must be under the same connection.

In the sssaJDBC program, in order to execute multiple SQL statements as one transaction:

ssdssa ① call setAutoCommit(false) of the Connection object; To cancel the auto commit transaction

ssds sa 2, after all SQL statements have been successfully executed, call commit(); Method commit transaction

ssds sa ③ call rollback() when an exception occurs; Method rolls back the transaction.

ssds sa note: if the Connection is not closed at this time and may be reused, you need to restore its auto commit state setAutoCommit(true). Especially when using database Connection pool technology, it is recommended to restore the auto commit state before executing the close() method.

Ssdsds SDA case: user AA transfers 100 to user BB

public void testJDBCTransaction() {
	Connection conn = null;
	try {
		// 1. Get database connection
		conn = JDBCUtils.getConnection();
		// 2. Start transaction
		conn.setAutoCommit(false);
		// 3. Database operation
		String sql1 = "update user_table set balance = balance - 100 where user = ?";
		update(conn, sql1, "AA");

		// Analog network exception
		//System.out.println(10 / 0);

		String sql2 = "update user_table set balance = balance + 100 where user = ?";
		update(conn, sql2, "BB");
		// 4. If there is no exception, commit the transaction
		conn.commit();
	} catch (Exception e) {
		e.printStackTrace();
		// 5. If there are exceptions, roll back the transaction
		try {
			conn.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
    } finally {
        try {
			//6. Restore the automatic submission function of each DML operation
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
        //7. Close the connection
		JDBCUtils.closeResource(conn, null, null); 
    }  
}
------------------------------------------------------------------------------------------------------------------------
//General addition, deletion and modification operations after using transactions (version 2.0)
public void update(Connection conn ,String sql, Object... args) {
	PreparedStatement ps = null;
	try {
		// 1. Get the instance of PreparedStatement (or: precompiled sql statement)
		ps = conn.prepareStatement(sql);
		// 2. Fill placeholder
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i + 1, args[i]);
		}
		// 3. Execute sql statement
		ps.execute();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		// 4. Close resources
		JDBCUtils.closeResource(null, ps);

	}
}

ACID property of the transaction

sssa Atomicity: Atomicity means that a transaction is an inseparable work unit, and operations in a transaction either occur or do not occur

sssa Consistency: transactions must change the database from one Consistency state to another.

sssa Isolation: transaction Isolation means that the execution of a transaction cannot be disturbed by other transactions, that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.

sssa persistence: persistence means that once a transaction is committed, its changes to the data in the database are permanent. Other subsequent operations and database failures should not have any impact on it.

Concurrency of database

For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not taken, various concurrency problems will be caused:

Ssdsa ① dirty reading: for two transactions T1 and T2, T1 reads the fields that have been updated by T2 but have not been committed. After that, if T2 rolls back, the content read by T1 is temporary and invalid.

sssdssa ②. Non repeatable reading: for two transactions T1 and T2, T1 reads a field, and T2 updates the field. After that, T1 reads the same field again, and the value is different.

Ssdsa ③ phantom reading: for two transactions T1 and T2, T1 reads a field from a table, and T2 inserts some new rows into the table. After that, if T1 reads the same table again, several more rows will appear.

Isolation of sssa database transactions: the database system must have the ability to isolate and run various transactions concurrently, so that they will not affect each other and avoid various concurrency problems.

sssa the degree to which a transaction is isolated from other transactions is called the isolation level. The database specifies multiple transaction isolation levels. Different isolation levels correspond to different interference levels. The higher the isolation level, the better the data consistency, but the weaker the concurrency.

Four isolation levels

sssa database provides four transaction isolation levels:

sssaOracle supports two transaction isolation levels: read committed and serial. The default transaction isolation level of Oracle is read committed

sssaMysql supports four transaction isolation levels. The default transaction isolation level of Mysql is REPEATABLE READ.

Set isolation level in MySql

Every time sssa starts a mysql program, it will get a separate database connection Each database connection has a global variable @@tx_isolation, indicating the current transaction isolation level.

DAO and related implementation classes

sssaDAO: the class and interface of Data Access Object to access data information, including CRUD (Create, regression, Update, Delete) of data without any business-related information. It is sometimes called BaseDAO

sssa function: in order to realize the modularization of functions, it is more conducive to code maintenance and upgrading.

Ssdsddsddsa the following is the embodiment of DAO used in the book city project of Java Web stage in Silicon Valley:


sssaBaseDAO.java

package com.atguigu.bookstore.dao;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;


/**
 * Define an inherited Dao that performs basic operations on the database
 * 
 * @author HanYanBing
 *
 * @param <T>
 */
public abstract class BaseDao<T> {
	private QueryRunner queryRunner = new QueryRunner();
	// Define a variable to receive generic types
	private Class<T> type;

	// Get the Class object of T, that is, get the type of generic type. The generic type is determined only when it is inherited by the subclass,
	//Here is the usage of this. It is called through a subclass. This here represents the object of the subclass
	public BaseDao() {
		// Get the type of subclass,
		Class clazz = this.getClass();
		
		// Gets the type of the parent class
		// getGenericSuperclass() is used to get the type of the parent class of the current class
		// ParameterizedType represents a type with a generic type
		ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
		
		// Get the specific generic type getActualTypeArguments get the type of the specific generic type
		// This method returns an array of types
		Type[] types = parameterizedType.getActualTypeArguments();
		
		// Gets the type of the specific generic·
		this.type = (Class<T>) types[0];
	}
	/**
	 * General addition, deletion and modification operations
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public int update(Connection conn,String sql, Object... params) {
		int count = 0;
		try {
			count = queryRunner.update(conn, sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return count;
	}

	/**
	 * Get an object
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public T getBean(Connection conn,String sql, Object... params) {
		T t = null;
		try {
			t = queryRunner.query(conn, sql, new BeanHandler<T>(type), params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return t;
	}

	/**
	 * Get all objects
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<T> getBeanList(Connection conn,String sql, Object... params) {
		List<T> list = null;
		try {
			list = queryRunner.query(conn, sql, new BeanListHandler<T>(type), params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return list;
	}

	/**
	 * Get a method that is worthy of being used to execute, such as select count(*) Such sql statements
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public Object getValue(Connection conn,String sql, Object... params) {
		Object count = null;
		try {
			// Call the query method of queryRunner to get a single value
			count = queryRunner.query(conn, sql, new ScalarHandler<>(), params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return count;
	}
}

sssaBookDAO.java

package com.atguigu.bookstore.dao;

import java.sql.Connection;
import java.util.List;

import com.atguigu.bookstore.beans.Book;
import com.atguigu.bookstore.beans.Page;

public interface BookDao {

	/**
	 * Query all records from the database
	 * 
	 * @return
	 */
	List<Book> getBooks(Connection conn);

	/**
	 * Insert a record into the database
	 * 
	 * @param book
	 */
	void saveBook(Connection conn,Book book);

	/**
	 * Delete a record from the database based on the book id
	 * 
	 * @param bookId
	 */
	void deleteBookById(Connection conn,String bookId);

	/**
	 * Query a record from the database according to the id of the book
	 * 
	 * @param bookId
	 * @return
	 */
	Book getBookById(Connection conn,String bookId);

	/**
	 * Update a record from the database according to the book id
	 * 
	 * @param book
	 */
	void updateBook(Connection conn,Book book);

	/**
	 * Get book information with pagination
	 * 
	 * @param page: Is a page object that contains only the pageNo attribute entered by the user
	 * @return The returned Page object is a Page object that contains all properties
	 */
	Page<Book> getPageBooks(Connection conn,Page<Book> page);

	/**
	 * Get book information with pagination and price range
	 * 
	 * @param page: Is a page object that contains only the pageNo attribute entered by the user
	 * @return The returned Page object is a Page object that contains all properties
	 */
	Page<Book> getPageBooksByPrice(Connection conn,Page<Book> page, double minPrice, double maxPrice);
}

sssaBookDaoImpl.java

package com.atguigu.bookstore.dao.impl;

import java.sql.Connection;
import java.util.List;

import com.atguigu.bookstore.beans.Book;
import com.atguigu.bookstore.beans.Page;
import com.atguigu.bookstore.dao.BaseDao;
import com.atguigu.bookstore.dao.BookDao;

public class BookDaoImpl extends BaseDao<Book> implements BookDao {

	@Override
	public List<Book> getBooks(Connection conn) {
		// Call the method to get a List in BaseDao
		List<Book> beanList = null;
		// Write sql statement
		String sql = "select id,title,author,price,sales,stock,img_path imgPath from books";
		beanList = getBeanList(conn,sql);
		return beanList;
	}

	@Override
	public void saveBook(Connection conn,Book book) {
		// Write sql statement
		String sql = "insert into books(title,author,price,sales,stock,img_path) values(?,?,?,?,?,?)";
		// Call the general addition, deletion and modification methods in BaseDao
		update(conn,sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(),book.getImgPath());
	}

	@Override
	public void deleteBookById(Connection conn,String bookId) {
		// Write sql statement
		String sql = "DELETE FROM books WHERE id = ?";
		// Call the general addition, deletion and modification method in BaseDao
		update(conn,sql, bookId);
			
	}

	@Override
	public Book getBookById(Connection conn,String bookId) {
		// Call the method to get an object in BaseDao
		Book book = null;
		// Write sql statement
		String sql = "select id,title,author,price,sales,stock,img_path imgPath from books where id = ?";
		book = getBean(conn,sql, bookId);
		return book;
	}

	@Override
	public void updateBook(Connection conn,Book book) {
		// Write sql statement
		String sql = "update books set title = ? , author = ? , price = ? , sales = ? , stock = ? where id = ?";
		// Call the general addition, deletion and modification methods in BaseDao
		update(conn,sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(), book.getId());
	}

	@Override
	public Page<Book> getPageBooks(Connection conn,Page<Book> page) {
		// Gets the total number of records of books in the database
		String sql = "select count(*) from books";
		// Call the method to get a single value in BaseDao
		long totalRecord = (long) getValue(conn,sql);
		// Set the total number of records in the page object
		page.setTotalRecord((int) totalRecord);

		// Get the List of records stored in the current page
		String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books limit ?,?";
		// Call the method to get a collection in BaseDao
		List<Book> beanList = getBeanList(conn,sql2, (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
		// Set the List to the page object
		page.setList(beanList);
		return page;
	}

	@Override
	public Page<Book> getPageBooksByPrice(Connection conn,Page<Book> page, double minPrice, double maxPrice) {
		// Gets the total number of records of books in the database
		String sql = "select count(*) from books where price between ? and ?";
		// Call the method to get a single value in BaseDao
		long totalRecord = (long) getValue(conn,sql,minPrice,maxPrice);
		// Set the total number of records in the page object
		page.setTotalRecord((int) totalRecord);

		// Get the List of records stored in the current page
		String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books where price between ? and ? limit ?,?";
		// Call the method to get a collection in BaseDao
		List<Book> beanList = getBeanList(conn,sql2, minPrice , maxPrice , (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
		// Set the List to the page object
		page.setList(beanList);
		
		return page;
	}

}

Keywords: JDBC

Added by srini_r_r on Wed, 22 Dec 2021 05:17:44 +0200