For the source code of this tutorial, please visit: tutorial_demo
In the previous tutorial, we used pure annotation and Apache Commons DbUtils to implement CRUD operation of single table, but the operation in this tutorial does not support transaction. In this tutorial, we changed it to transaction support version according to the existing knowledge, so as to prepare for the follow-up study.
1, Analysis of transfer operation problems
Next, we implement a transfer operation to analyze the existing problems.
1.1. Add corresponding methods to IAccountService
//New transfer method void transfer(Integer srcId, Integer dstId, Float money);
1.2. Implement the newly added method in the business layer implementation class AccountServiceImpl
//Transfer operation
@Override
public void transfer(Integer srcId, Integer dstId, Float money) {
//Query users who need to transfer according to Id
Account src = accountDao.findById(srcId);
Account dst = accountDao.findById(dstId);
if(src == null) {
throw new RuntimeException("Transfer out user does not exist");
}
if(dst == null) {
throw new RuntimeException("Transfer in user does not exist");
}
if(src.getMoney() < money) {
throw new RuntimeException("Insufficient balance of transfer out account");
}
//One decrease, one increase
src.setMoney(src.getMoney() - money);
dst.setMoney(dst.getMoney() + money);
//Update in database
accountDao.update(src);
accountDao.update(dst);
}
1.3 add test method to test class
@Test
public void testTrans() {
accountService.transfer(1, 2, 10F);
}
Run test method, test successful. There seems to be no problem.
1.4 modification of transfer method at Service level
//Transfer operation
@Override
public void transfer(Integer srcId, Integer dstId, Float money) {
//Query users who need to transfer according to Id
Account src = accountDao.findById(srcId);
Account dst = accountDao.findById(dstId);
if(src == null) {
throw new RuntimeException("Transfer out user does not exist");
}
if(dst == null) {
throw new RuntimeException("Transfer in user does not exist");
}
if(src.getMoney() < money) {
throw new RuntimeException("Insufficient balance of transfer out account");
}
//One decrease, one increase
src.setMoney(src.getMoney() - money);
dst.setMoney(dst.getMoney() + money);
//Update in database
accountDao.update(src);
//The newly added content makes an exception artificially
int i = 100/0；
//Update in database
accountDao.update(dst);
}
Running the test method, the program is abnormal, but src has less money in the database.
Transfer process analysis:
- The principle of large transfer process is to reduce the source account money and increase the target account money;
- The operation of reducing the source account money and increasing the target account money either succeeds at the same time or fails at the same time;
- In order to achieve simultaneous success, related operations should be wrapped in the same transaction in case of simultaneous failure;
- The same Connection should be used for the same transaction operation.
Current problems: no transaction is opened in the current transfer process, and all operations use independent connections.
2, Code upgrade
According to the previous analysis, we need to modify the project to support transactions. The following principles should be met:
- Transaction processing in the Service layer (start transaction, commit, rollback);
- Dao layer is only responsible for database operation and not for business, that is to say, each Dao operation only performs the most fine-grained CRUD operation;
- The Dao layer does not handle exceptions, and the exceptions are thrown to the Service layer.
2.1. Create a tool class to support transactions
package org.codeaction.util;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
@Component
public class JdbcUtils {
private static DataSource dataSource;
//Used to save the connection of the current thread
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
//Get connection pool object
public static DataSource getDataSource() {
return dataSource;
}
//Pay attention to this position
@Autowired
public void setDataSource(DataSource dataSource) {
JdbcUtils.dataSource = dataSource;
}
//Get connection
public static Connection getConnection() throws SQLException {
System.out.println(dataSource);
Connection conn = tl.get();
if(conn == null) {
return dataSource.getConnection();
}
return conn;
}
//Open transaction
public static void beginTransaction() throws SQLException {
Connection conn = tl.get();
if(conn != null) {
throw new SQLException("Transaction has been opened, cannot be opened repeatedly");
}
conn = getConnection();
conn.setAutoCommit(false);
tl.set(conn);
}
//Commit transaction
public static void commitTransaction() throws SQLException {
Connection conn = tl.get();
if(conn == null) {
throw new SQLException("Connection is empty, cannot commit transaction");
}
conn.commit();
conn.close();
tl.remove();
}
//Rollback transaction
public static void rollbackTransaction() throws SQLException {
Connection conn = tl.get();
if (conn == null) {
throw new SQLException("Connection is empty, transaction cannot be rolled back");
}
conn.rollback();
conn.close();
tl.remove();
}
}
explain:
- This project uses ThreadLocal to ensure that it can be used in multi-threaded environment;
- Instead of using @ AutoWired on it, the dataSource property uses @ AutoWired on its set method. The reason is that dataSource is of static type, static type variable creation is earlier than Spring container creation, and Spring context has not been loaded when class loader loads static variables. So the classloader will not inject the static class correctly into the bean, and it will fail.
2.2. Modify the implementation class of Dao
package org.codeaction.dao.impl;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.codeaction.dao.IAccountDao;
import org.codeaction.domain.Account;
import org.codeaction.util.JdbcUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
@Repository("accountDao")
public class AccountDaoImpl implements IAccountDao {
@Autowired
private QueryRunner queryRunner;
public void setQueryRunner(QueryRunner queryRunner) {
this.queryRunner = queryRunner;
}
@Override
public List<Account> findAll() throws SQLException {
Connection conn = JdbcUtils.getConnection();
String sql = "select * from account";
List<Account> list = queryRunner.query(conn, sql, new BeanListHandler<Account>(Account.class));
return list;
}
@Override
public Account findById(Integer id) throws SQLException {
Connection conn = JdbcUtils.getConnection();
String sql = "select * from account where id = ?";
Account account = queryRunner.query(conn, sql, new BeanHandler<Account>(Account.class), id);
return account;
}
@Override
public void save(Account account) throws SQLException {
Object[] params = {account.getName(), account.getMoney()};
Connection conn = JdbcUtils.getConnection();
String sql = "insert into account(name, money) values(?, ?)";
queryRunner.update(conn, sql, params);
}
@Override
public void update(Account account) throws SQLException {
Object[] params = {account.getName(), account.getMoney(), account.getId()};
Connection conn = JdbcUtils.getConnection();
String sql = "update account set name=?, money=? where id=?";
queryRunner.update(conn, sql, params);
}
@Override
public void delete(Integer id) throws SQLException {
Object[] params = {id};
Connection conn = JdbcUtils.getConnection();
String sql = "delete from account where id=?";
queryRunner.update(conn, sql, id);
}
}
2.3 modify the Service layer implementation class
package org.codeaction.service.impl;
import org.codeaction.dao.IAccountDao;
import org.codeaction.domain.Account;
import org.codeaction.service.IAccountService;
import org.codeaction.util.JdbcUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.sql.SQLException;
import java.util.List;
@Service("accountService")
public class AccountServiceImpl implements IAccountService {
@Autowired
private IAccountDao accountDao;
public void setAccountDao(IAccountDao accountDao) {
this.accountDao = accountDao;
}
@Override
public List<Account> findAll() {
List<Account> list = null;
try {
//1. Open transaction
JdbcUtils.beginTransaction();
//2. Operation
list = accountDao.findAll();
//3. Submission
JdbcUtils.commitTransaction();
} catch (Exception e) {
e.printStackTrace();
try {
//Rollback transaction
JdbcUtils.rollbackTransaction();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
return list;
}
@Override
public Account findById(Integer id) {
Account account = null;
try {
//1. Open transaction
JdbcUtils.beginTransaction();
//2. Operation
account = accountDao.findById(id);
//3. Submission
JdbcUtils.commitTransaction();
} catch (Exception e) {
e.printStackTrace();
try {
//Rollback transaction
JdbcUtils.rollbackTransaction();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
return account;
}
@Override
public void save(Account account) {
try {
//1. Open transaction
JdbcUtils.beginTransaction();
//2. Operation
accountDao.save(account);
//3. Submission
JdbcUtils.commitTransaction();
} catch (Exception e) {
e.printStackTrace();
try {
//Rollback transaction
JdbcUtils.rollbackTransaction();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
@Override
public void update(Account account) {
try {
//1. Open transaction
JdbcUtils.beginTransaction();
//2. Operation
accountDao.update(account);
//3. Submission
JdbcUtils.commitTransaction();
} catch (Exception e) {
e.printStackTrace();
try {
//Rollback transaction
JdbcUtils.rollbackTransaction();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
@Override
public void delete(Integer id) {
try {
//1. Open transaction
JdbcUtils.beginTransaction();
//2. Operation
accountDao.delete(id);
//3. Submission
JdbcUtils.commitTransaction();
} catch (Exception e) {
e.printStackTrace();
try {
//Rollback transaction
JdbcUtils.rollbackTransaction();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
@Override
public void transfer(Integer srcId, Integer dstId, Float money) {
try {
//1. Open transaction
JdbcUtils.beginTransaction();
//2. Operation
Account src = accountDao.findById(srcId);
Account dst = accountDao.findById(dstId);
if(src == null) {
throw new RuntimeException("Transfer out user does not exist");
}
if(dst == null) {
throw new RuntimeException("Transfer in user does not exist");
}
if(src.getMoney() < money) {
throw new RuntimeException("Insufficient balance of transfer out account");
}
src.setMoney(src.getMoney() - money);
dst.setMoney(dst.getMoney() + money);
accountDao.update(src);
//int x = 1/0; / / note here
accountDao.update(dst);
//3. Submission
JdbcUtils.commitTransaction();
} catch (Exception e) {
e.printStackTrace();
try {
//Rollback transaction
JdbcUtils.rollbackTransaction();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
}
2.4. Modify the JdbcConfig configuration class
package org.codeaction.config;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.codeaction.util.JdbcUtils;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class JdbcConfig {
@Value("${jdbc.driverClass}")
private String driverClass;
@Value("${jdbc.jdbcUrl}")
private String jdbcUrl;
@Value("${jdbc.user}")
private String user;
@Value("${jdbc.password}")
private String password;
@Bean("queryRunner")
public QueryRunner queryRunner() {
return new QueryRunner();
}
@Bean("dataSource")
public DataSource dataSource() {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
try {
dataSource.setDriverClass(driverClass);
dataSource.setJdbcUrl(jdbcUrl);
dataSource.setUser(user);
dataSource.setPassword(password);
} catch (Exception e) {
e.printStackTrace();
}
return dataSource;
}
}
2.5 operation test method
@Test
public void testTrans() {
accountService.transfer(1, 2, 10F);
}
The test method can be run successfully;
Cancel the comment of int i = 1/0; in 2.4, run the test method, and if there is an exception, it can roll back normally.
2.6 current code problems
The current code has been able to support transactions, but there are still some problems, as follows:
- The code is too cumbersome. Compared with the previous version, the Service layer implementation class is too cumbersome. It is not only responsible for business but also responsible for transaction operations;
- If there is a change in the method related to transaction operation in the JdbcUtils class, the corresponding call location of the Service layer code will be changed. If the project is very large, it is a nightmare for developers.
In the following sections, we will learn more about Spring AOP to solve the above problems.