如果我使用try with resources，如何才能不因事务而关闭连接

Question

我有一个DAO，它有一个将实体插入MySQL数据库的方法。该方法将连接和实体作为参数。在Context.xml文件中，我设置了连接将具有defaultAutoCommit="false"属性，所以我不需要在DAO方法中设置它。

defaultAutoCommit="false"

@Override
public boolean insertCarCategory(Connection connection, CarCategory carCategory) {
int rowNum = 0;
String query = "INSERT INTO car_category values(?,?,?,?);";
try (Connection con = connection;
AutoRollback autoRollback = new AutoRollback(con);
PreparedStatement statement = con.prepareStatement(query)) {
statement.setString(1, carCategory.getCarCategory());
statement.setDouble(2, carCategory.getCostPerOneKilometer());
statement.setDouble(3, carCategory.getDiscount());
statement.setBytes(4, ImageUtil.imageToByte(carCategory.getCarCategoryImage()));
rowNum = statement.executeUpdate();
//if it used as transaction dont commit and close connection
autoRollback.commit();
} catch (SQLException e) {
LOGGER.error(e);
}
return rowNum > 0;
}

将在服务层中使用的UserDao方法

@Override
public boolean insertUser(Connection connection,User user) {
int rowNum = 0;
String query = "INSERT INTO user_info(login,userPassword,userType,userEmail)values(?,?,?,?);";
ResultSet keys = null;
try(Connection con = connection;
AutoRollback autoRollback = new AutoRollback(con);
PreparedStatement statement = con.prepareStatement(query,Statement.RETURN_GENERATED_KEYS)) {
statement.setString(1, user.getLogin());
statement.setString(2, PasswordUtil.generateStrongPasswordHash(user.getPassword()));
statement.setString(3, user.getUserType());
statement.setString(4, user.getUserEmail());
rowNum = statement.executeUpdate();
keys = statement.getGeneratedKeys();
if (keys.next()) {
user.setUserId(keys.getInt(1));
}
autoRollback.commit();
} catch (SQLException e) {
LOGGER.error(e);
} finally {
if (keys != null) {
try {
keys.close();
} catch (SQLException e) {
LOGGER.error(e);
}
}
}
return rowNum > 0;
}

我使用AutoRollBack类来帮助我回滚事务。如果commit为false

public class AutoRollback implements AutoCloseable  {
private Connection conn;
private boolean committed;
public AutoRollback(Connection conn) throws SQLException {
this.conn = conn;
}
public void commit() throws SQLException {
conn.commit();
committed = true;
}
@Override
public void close() throws SQLException {
if(!committed) {
conn.rollback();
}
}
}

在服务层中，我使用DAO方法。我从连接池中获取一个连接，并将其传递给DAO方法。

private void insertCarUser(User user,CarCategory carCategory){
Connection connection = MySQLDAOFactory.getConnection();
categoryDao.insertCarCategory(connection,carCategory);
userDao.insertUser(connection,user);
}

我怎么能不关闭其中一个方法中的连接，以便在第二个方法中使用它？

Answer 1

删除各种DAO方法中的try-with-resources，而是在获得连接时立即应用try-with-resource:

private void insertCarUser(User user,CarCategory carCategory){
try (Connection connection = MySQLDAOFactory.getConnection()) {
categoryDao.insertCarCategory(connection,carCategory);
userDao.insertUser(connection,user);
}
}

类似地，如果此操作需要是原子操作，那么您将希望将事务处理转移到那里，而不是在DAO方法中。