Testing Pure JDBC DAO method with AbstractTransactionalJUnit4SpringContextTests: changes in DB not reverted after test method

Question

I am playing around with pure JDBC and testing. I have written simple DAO, which allow perform CRUD operations:

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) {
                }
            }
        }

    }
}

Here is my spring configuration:

And my test:

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

In my test class I want to avoid any custom "cleanup" methods, which revert all changes made during test, so I extend AbstractTransactionalJUnit4SpringContextTests. 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.
At the beginning rollback doesn't work, for example if testCheckIfTestRollbackWorks() was run as first, and second was testCRUD, testCRUD fails because of thrown SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL131026082556940' defined on 'VEHICLE'.
After some research, I found information that autoCommit could cause such behaviour, then I set defaultAutoCommit property to false.
But then, testCRUD does not work, because despite the fact that I call vehicleDao.insert(vehicle); I cannot find this object when I use: vehicleDao.findByVehicleNo(vehicleNo);
I presume that reason was simple: my DAO did not perform any commit. So I add to insert, update and delete method "conn.commit()".
But now, changes from one test are again visible in another test :( I assume that test rollback it own's tranasaction, but not this one which is commited in DAO method.
Any tips how to approach this problem?

M. Deinum · Accepted Answer

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.

Well yes BUT that is only true for code that uses a single and/or spring managed transactions. Your code doesn't, you are getting a connection yourself and doing a commit on that connection. So the data is already committed, committed data cannot be rolled back. Next to that the transaction isn't visible to spring (or under springs control) so nothing spring can do about that.

If you want Spring to influence/drive your transactions either rewrite your code to use a JdbcTemplate or use a TransactionAwareDataSourceProxy. More information can be found in the Spring Reference Guide.

Links:

JdbcTemplate javadoc | reference
TransactionAwareDataSourceProxy javadoc | reference
JDBC Section of the Reference Guide

Testing Pure JDBC DAO method with AbstractTransactionalJUnit4SpringContextTests: changes in DB not reverted after test method

Answers (2)

Related Questions