java - 使用 AbstractTransactionalJUnit4SpringContextTests : changes in DB not reverted after test method 测试纯 JDBC DAO 方法

Question

我正在玩弄纯 JDBC 和测试。我编写了简单的 DAO，它允许执行 CRUD 操作:

package pl.aszecowka;


import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class JdbcVehicleDao implements VehicleDao {
    private DataSource dataSource;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public void insert(Vehicle vehicle) {
        String sql = "insert into vehicle(vehicle_no,color, wheel, seat) values(?,?,?,?)";
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, vehicle.getVehicleNo());
            ps.setString(2, vehicle.getColor());
            ps.setInt(3, vehicle.getWheel());
            ps.setInt(4, vehicle.getSeat());
            ps.executeUpdate();
            ps.close();
            conn.commit();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    @Override
    public void update(Vehicle vehicle) {
        String sql = "update vehicle set color=?, wheel=?, seat=? where vehicle_no = ?";
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, vehicle.getColor());
            ps.setString(4, vehicle.getVehicleNo());
            ps.setInt(2, vehicle.getWheel());
            ps.setInt(3, vehicle.getSeat());
            ps.executeUpdate();
            ps.close();
            conn.commit();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                }
            }
        }
    }

    @Override
    public void delete(Vehicle vehicle) {
        String sql = "delete from vehicle where vehicle_no = ? ";
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, vehicle.getVehicleNo());
            int i = ps.executeUpdate();
            System.out.println(i);
            ps.close();
            conn.commit();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                }
            }
        }
    }

    @Override
    public Vehicle findByVehicleNo(String vehicleNo) {
        String sql = "select * from vehicle where vehicle_no = ?";
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, vehicleNo);
            Vehicle vehicle = null;
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                vehicle = new Vehicle(rs.getString("vehicle_no"), rs.getString("color"), rs.getInt("wheel"), rs.getInt("seat"));
            }
            rs.close();
            ps.close();
            return vehicle;
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                }
            }
        }

    }
}

这是我的 Spring 配置:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
            <property name="driverClassName" value="org.apache.derby.jdbc.ClientDriver"/>
            <property name="url" value="jdbc:derby://localhost:1527/vehicle;create=true"/>
            <property name="username" value="app"/>
            <property name="password" value="app"/>
            <property name="initialSize" value="2"/>
            <property name="maxActive" value="5"/>
           <property name="defaultAutoCommit" value="false" />
        </bean>

        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource"/>
        </bean>

        <bean id="vehicleDao" class="pl.aszecowka.JdbcVehicleDao">
            <property name="dataSource" ref="dataSource"/>
        </bean>

还有我的测试:

@ContextConfiguration("/beans.xml")
public class JdbcVehicleDaoTest extends AbstractTransactionalJUnit4SpringContextTests {

    @Resource
    private VehicleDao vehicleDao;


    @Test
    public void testCRUD()
    {
        String vehicleNo = "ABCDEF";
        String color = "blue";
        int wheel = 4;
        int seat = 4;
        Vehicle vehicle = new Vehicle(vehicleNo, color, wheel, seat);
        vehicleDao.insert(vehicle);
        Vehicle fromDB = vehicleDao.findByVehicleNo(vehicleNo);
        Assert.assertNotNull(fromDB);
        Assert.assertEquals(vehicleNo, fromDB.getVehicleNo());
        Assert.assertEquals(color, fromDB.getColor());
        Assert.assertEquals(wheel, fromDB.getWheel());
        Assert.assertEquals(seat, fromDB.getSeat());

        color = "blue";
        seat = 5;
        wheel = 12;

        fromDB.setColor(color);
        fromDB.setSeat(seat);
        fromDB.setWheel(wheel);
        vehicleDao.update(fromDB);
        fromDB = vehicleDao.findByVehicleNo(fromDB.getVehicleNo());
        Assert.assertNotNull(fromDB);
        Assert.assertEquals(vehicleNo, fromDB.getVehicleNo());
        Assert.assertEquals(color, fromDB.getColor());
        Assert.assertEquals(wheel, fromDB.getWheel());
        Assert.assertEquals(seat, fromDB.getSeat());

        vehicleDao.delete(fromDB);
        fromDB = vehicleDao.findByVehicleNo(fromDB.getVehicleNo());
        Assert.assertNull(fromDB);
    }

    @Test
    public void testCheckIfTestRollbackWorks()
    {
        Vehicle vehicle = new Vehicle("ABCDEF", "blue", 4, 4);
        vehicleDao.insert(vehicle);
    }
}

在我的测试类中，我想避免任何自定义“清理”方法，这些方法会恢复测试期间所做的所有更改，因此我扩展了 AbstractTransactionalJUnit4SpringContextTests。这个类用@Transactional注解，据我所知这意味着所有测试方法的事务最后都会回滚。
一开始回滚不起作用，例如，如果 testCheckIfTestRollbackWorks() 作为第一个运行，第二个是 testCRUD，testCRUD 失败是因为抛出 SQLIntegrityConstraintViolationException: The statement was aborted because it would have cause a duplicate “VEHICLE”上定义的“SQL131026082556940”标识的唯一或主键约束或唯一索引中的键值。
经过一些研究，我发现 autoCommit 可能导致此类行为的信息，然后我将 defaultAutoCommit 属性设置为 false。
但是，testCRUD 不起作用，因为尽管我调用vehicleDao.insert(车辆)；我在使用时找不到这个对象:vehicleDao.findByVehicleNo(vehicleNo);
我认为原因很简单:我的 DAO 没有执行任何提交。所以我添加了插入、更新和删除方法“conn.commit()”。
但是现在，一个测试的更改在另一个测试中再次可见:(我假设测试回滚它自己的交易，但不是这个在 DAO 方法中提交的交易。
有什么提示可以解决这个问题吗？

Answer 1

This class is annotated with @Transactional, and as far as I know it means that transactions for all test methods will rolled back at the end.

是的，但是这仅适用于使用单个和/或 spring 管理事务的代码。您的代码没有，您正在自己建立连接并对该连接进行提交。所以数据已经提交，提交的数据不能回滚。除此之外，事务对 spring 不可见(或在 springs 控制下)，因此 spring 对此无能为力。

如果您希望 Spring 影响/驱动您的事务，请重写您的代码以使用 JdbcTemplate 或使用 TransactionAwareDataSourceProxy。更多信息可以在 Spring Reference Guide 中找到.

链接:

1. JdbcTemplate javadoc | reference
2. TransactionAwareDataSourceProxy javadoc | reference
3. JDBC Section引用指南的