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;
}
}