How to create a transactional context on spring boot test classes using JDBC?

Question

When I was using hibernate, once I'd made a test, all the changes made were being rolled back once those tests are completed.

But when I use JDBC with my DAO implementations instead JpaRepositories, mutations made during tests are not being rolled back.

How can I get all the changes are being rolled back?

Here you can see how one of my test classes looks like:

package com.cemonan.bookdb2;

import com.cemonan.bookdb2.dao.BookDao;
import com.cemonan.bookdb2.domain.Book;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.context.annotation.ComponentScan;

import java.util.List;

import static org.assertj.core.api.AssertionsForClassTypes.assertThat;

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@ComponentScan(basePackages = {"com.cemonan.bookdb2.dao"})
public class BookDaoIntegrationTest {

    @Autowired
    BookDao bookDao;

    @Test
    void testCreateBook() {
        List<Book> books = bookDao.findAll();
        int countBefore = books.size();

        Book book = new Book();
        book.setTitle("A book");
        book.setIsbn("123");
        book.setPublisher("Someone");

        Book savedBook = bookDao.save(book);

        books = bookDao.findAll();
        int countAfter = books.size();

        assertThat(savedBook).isNotNull();
        assertThat(countAfter).isGreaterThan(countBefore);
    }
}


package com.cemonan.bookdb2.dao;

import com.cemonan.bookdb2.domain.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static com.cemonan.bookdb2.dao.utils.Utils.closeAll;

@Component
public class BookDaoImpl implements BookDao {

    @Autowired
    DataSource source;

    Book mapRsToBook(ResultSet rs) throws SQLException {
        Book book = new Book();
        book.setId(rs.getLong("id"));
        book.setTitle(rs.getString("title"));
        book.setIsbn(rs.getString("isbn"));
        book.setPublisher(rs.getString("publisher"));
        return book;
    }

    @Override
    public Book findById(Long id) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            connection = this.source.getConnection();
            ps = connection.prepareStatement("SELECT * FROM book WHERE id = ?");
            ps.setLong(1, id);
            rs = ps.executeQuery();

            if (rs.next()) {
                return mapRsToBook(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeAll(rs, ps, connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public Book save(Book book) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            connection = this.source.getConnection();
            ps = connection.prepareStatement("INSERT INTO book (title, isbn, publisher) VALUES (?, ?, ?)");
            ps.setString(1, book.getTitle());
            ps.setString(2, book.getIsbn());
            ps.setString(3, book.getPublisher());
            ps.execute();

            Statement stmt = connection.createStatement();
            rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");

            if (rs.next()) {
                return this.findById(rs.getLong(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeAll(rs, ps, connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public Book update(Book book) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            connection = this.source.getConnection();
            ps = connection.prepareStatement("UPDATE book SET title = ?, isbn = ?, publisher = ? WHERE id = ?");
            ps.setString(1, book.getTitle());
            ps.setString(2, book.getIsbn());
            ps.setString(3, book.getPublisher());
            ps.setLong(4, book.getId());
            ps.execute();

            if (rs.next()) {
                return this.findById(book.getId());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeAll(rs, ps, connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public void delete(Book book) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            connection = this.source.getConnection();
            ps = connection.prepareStatement("DELETE FROM book WHERE id = ?");
            ps.setLong(1, book.getId());
            ps.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeAll(rs, ps, connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Book findByTitle(String title) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            connection = this.source.getConnection();
            ps = connection.prepareStatement("SELECT * FROM book WHERE title = ?");
            ps.setString(1, title);
            rs = ps.executeQuery();

            if (rs.next()) {
                return this.mapRsToBook(rs);
            }
        } catch(SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeAll(rs, ps, connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public List<Book> findAll() {
        Connection connection = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Book> books = new ArrayList<>();

        try {
            connection = this.source.getConnection();
            stmt = connection.createStatement();
            rs = stmt.executeQuery("SELECT * FROM book");

            while(rs.next()) {
                Book book = this.mapRsToBook(rs);
                books.add(book);
            }
        } catch(SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                closeAll(rs, stmt, connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return books;
    }
}

Answer 1

The connection which is obtained from the datasource inside your DAO code does not participate in the spring-managed transaction, which was opened before by the @DataJpaTest.

And since the new DAO transaction is finally closed, it is probably committed (depending on your application code that you did not show..).

---

To fix this, you could make your DAO code participate in the spring-managed transaction by annotating it with @Transactional, so that it uses any already opened transaction when being called. This way your code will run within the transaction which is rollbacked by the test.
https://docs.spring.io/spring-framework/docs/3.0.0.M3/reference/html/ch11s05.html