tags: JDBC
1. The transaction
All updates to a SESSION either succeed or fail together
For example :A transfers money from A to B. If something goes wrong in the transfer process, the transaction can restore the data to its original state (account A has the same money, and account B has the same money).
Examples:
/* * * Let's simulate the scenario where account A transfers to account B * * * A and B both have 1000 RMB, now I ask account A to transfer 500 RMB to account B * * */
//JDBC closes transactions by default. What is wrong with closing transactions to perform transfers
// Subtract $500 from account A
String sql = "UPDATE a SET money=money-500 ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
// Account B has an extra $500
String sql2 = "UPDATE b SET money=money+500";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
Copy the codeFrom above, we can indeed see that the transfer from A to B was successful. But ** what if there is A problem in the transfer from A to B? ** Let’s simulate it
// Subtract $500 from account A
String sql = "UPDATE a SET money=money-500 ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
// There is a problem with the simulation
int a = 3 / 0;
String sql2 = "UPDATE b SET money=money+500";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
Copy the codeObviously, the above code will throw an exception, so let’s query the data again. 500 yuan is missing from Account A, and there is no increase in account B. This is clearly unreasonable.
We can solve the above problems through transactions
// If a transaction is enabled, operations on data will not take effect immediately.
connection.setAutoCommit(false);
// Subtract $500 from account A
String sql = "UPDATE a SET money=money-500 ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
// There was a problem in the transfer process
int a = 3 / 0;
// Account B is 500 yuan more
String sql2 = "UPDATE b SET money=money+500";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
// If the program can execute up to this point without throwing an exception, we commit data
connection.commit();
// Close transaction
connection.setAutoCommit(true);
} catch (SQLException e) {
try {
// If there is an exception, it will come in here and we will roll back the transaction.
connection.rollback();
// Close transaction
connection.setAutoCommit(true);
} catch (SQLException e1) {
e1.printStackTrace();
}
Copy the codeThe above program also throws an exception, account A does not decrease, account B does not increase.
Note: When Connection encounters an unhandled SQLException, the system will exit abnormally and the transaction will be rolled back automatically, but if the program catches an exception, the transaction will need to be rolled back explicitly in a catch.
savapoint
We can also use SavePoint to set intermediate points. If something goes wrong, we set an intermediate point and roll back to just before the error.
Application scenario: Now we have to solve a math problem, and we find that we made a mistake. The previous calculations are correct. It is impossible for us to rollback again. The best way is to set a save point while ensuring that the previous calculations are correct. Recalculate from the save point.
Note: SavePoint does not end the current transaction, as normal commit and rollback do
The isolation level of the transaction
The database defines four isolation levels:
- Serializable [can avoid dirty read, unrepeatable read, virtual read]
- Repeatable read【 avoid dirty read, not Repeatable read 】
- Read committed
- Read uncommitted
Each corresponds to four constants in the Connection class
- TRANSACTION_READ_UNCOMMITTED
- TRANSACTION_READ_COMMITTED
- TRANSACTION_REPEATABLE_READ
- TRANSACTION_SERIALIZABLE
Dirty read: a transaction reads uncommitted data from another transaction
Example: A transfers money to B. A executes the transfer statement, but A has not committed the transaction. B reads the data and finds that his account has more money! B says to A, I’ve already received the money. User A rolls back the transaction and waits for user B to check the amount of money in the account.
Non-repeatable reads: a transaction reads data that has been committed by another transaction, meaning that one transaction can see changes made by other transactions
Note: A queries the database to obtain data, and B modifies the database data, resulting in different results of A’s multiple queries to the database [harm: the results of A’s each query are affected by B, so the information queried by A is not interesting]
Virtual read (phantom read) : Data inserted by other transactions is read in one transaction, resulting in inconsistent reads.
Note: Similar to unrepeatable reads, virtual reads (phantom reads) can read data inserted by other transactions, resulting in inconsistent reads
To summarize: dirty reads are not tolerated. Non-repeatable reads and virtual reads are acceptable under certain circumstances.
2. The metadata
What is metadata
Metadata is the definition information of databases, tables, and columns
Why are we using metadata
Even though we wrote a simple utility class, our code was very redundant. For add, delete, and modify, only SQL and parameters are different. Why don’t we extract the same code into a method? For a query, the result set produced by different entities is not the same. We need metadata to get information about the result set before we can operate on the result set.
- ParameterMetaData — Metadata for parameters
- ResultSetMetaData – Metadata for the result set
- DataBaseMetaData – Metadata for the database
3. Modify the JDBC tool class
Problem: When we add, delete, change and check the database, we need to connect to the database, close resources, get PreparedSteatment objects, get Connection objects and other operations. Such code repetition rate is very high, so we need to enhance the tool class
Increases the deletion
// We find that only the SQL statement and the parameters passed in are unknown, so let the caller pass them in
// Since the parameters passed in are of various types and the number is indeterminate, use Object[]
public static void update(String sql, Object[] objects) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
// Set the value of the SQL placeholder based on the parameters passed in
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i + 1, objects[i]);
}
// Execute the SQL statement
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
Copy the codeThe query
/ * 1: for the query, we do not know what operation was carried out on the result set (commonly used is to data encapsulation as a Bean object, encapsulated into a List collection 】 2: we can define an interface that let the caller of the interface implementation class passed 3: this interface is the method called the caller passed in the implementation class method. 【 Policy mode 】 */
// The return value of this method is arbitrary, so it is defined as Object.
public static Object query(String sql, Object[] objects, ResultSetHandler rsh) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
// Set the value of the SQL placeholder based on the parameters passed in
if(objects ! =null) {
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i + 1, objects[i]);
}
}
resultSet = preparedStatement.executeQuery();
// Calls the methods passed in by the caller to implement the class to operate on the result set
return rsh.hanlder(resultSet);
}
Copy the codeInterface:
/* * defines the interface to operate on the result set. What does the caller want to do to the result set, as long as the interface is implemented * */
public interface ResultSetHandler {
Object hanlder(ResultSet resultSet);
}
Copy the codeImplementation class:
// Interface implementation class that encapsulates the result set into a Bean object
public class BeanHandler implements ResultSetHandler {
To encapsulate a Bean object, you first need to know what the Bean is, which is also passed in by the caller.
private Class clazz;
public BeanHandler(Class clazz) {
this.clazz = clazz;
}
@Override
public Object hanlder(ResultSet resultSet) {
try {
// Create an instantiation of the passed object
Object bean = clazz.newInstance();
if (resultSet.next()) {
// Get the result set metadata
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
// Get the column name of each column
String columnName = resultSetMetaData.getColumnName(i+1);
// Get the data for each column
String columnData = resultSet.getString(i+1);
// Set the Bean properties
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(bean,columnData);
}
// Return the Bean object
return bean;
}
Copy the code【 Strategy Mode 】 Simple understanding:
- We don’t know what the caller wants to do with the result set, so let the caller pass in the object that he wants to do, right
- As long as weEncapsulate the result set with passed objectsIt’ll be ok.
- As for what objects are passed by the caller, we can use the interface to normalize them
- What type the implementation class wants to encapsulate the result set also needs to be passed by the caller.
If you find this article helpful, give the author a little encouragement
