1. Framework Setup (Java with JUnit 5 and Testcontainers)
Configure build.gradle (or pom.xml) with Testcontainers and PostgreSQL driver dependencies.
```java // Example Test Base Class @Testcontainers public abstract class AbstractRepositoryTest {
@Container public static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:13.3") .withDatabaseName("testdb") .withUsername("test") .withPassword("test");
protected JdbcTemplate jdbcTemplate; protected DataSource dataSource; protected Connection connection;
@BeforeAll static void startContainer() { postgres.start(); }
@BeforeEach void setup() throws SQLException { dataSource = new DriverManagerDataSource(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword()); connection = dataSource.getConnection(); connection.setAutoCommit(false); // Start transaction jdbcTemplate = new JdbcTemplate(dataSource); // Load SQL fixtures jdbcTemplate.execute("TRUNCATE TABLE users RESTART IDENTITY CASCADE;"); // Clean before fixtures jdbcTemplate.execute(new ClassPathResource("sql/users_fixture.sql").getFile()); }
@AfterEach void teardown() throws SQLException { if (connection != null) { connection.rollback(); // Rollback transaction connection.close(); } } } ```
2. Test Files (Example UserRepositoryTest)
```java public class UserRepositoryTest extends AbstractRepositoryTest {
private UserRepository userRepository;
@BeforeEach void initRepository() { userRepository = new UserRepository(jdbcTemplate); // Assuming UserRepository uses JdbcTemplate }
@Test void testCreateUser() { User newUser = new User("john.doe@example.com", "John Doe"); User createdUser = userRepository.save(newUser); assertNotNull(createdUser.getId()); assertEquals("john.doe@example.com", createdUser.getEmail()); }
@Test void testFindUserById() { // Fixture already loaded a user with ID 1 Optional<User> user = userRepository.findById(1L); assertTrue(user.isPresent()); assertEquals("jane.doe@example.com", user.get().getEmail()); }
@Test void testUpdateUserEmail() { userRepository.updateEmail(1L, "jane.updated@example.com"); Optional<User> updatedUser = userRepository.findById(1L); assertTrue(updatedUser.isPresent()); assertEquals("jane.updated@example.com", updatedUser.get().getEmail()); }
@Test void testDeleteUser() { userRepository.deleteById(2L); // Fixture has user with ID 2 Optional<User> deletedUser = userRepository.findById(2L); assertFalse(deletedUser.isPresent()); } } ```
3. SQL Fixtures
src/test/resources/sql/users_fixture.sql:
``sql INSERT INTO users (id, email, name) VALUES (1, 'jane.doe@example.com', 'Jane Doe'); INSERT INTO users (id, email, name) VALUES (2, 'bob.smith@example.com', 'Bob Smith'); ``
This script populates the users table with known data before each test method, ensuring a consistent starting state. The TRUNCATE in setup() clears previous data.
4. Coverage Notes
This approach provides high confidence in the repository layer by verifying actual SQL interactions against a real PostgreSQL instance. It effectively catches: SQL syntax errors specific to PostgreSQL, correctness of ORM mappings or JdbcTemplate queries, database schema mismatches (e.g., wrong column names, types), and constraint violations (unique, foreign key, NOT NULL) under realistic conditions. Each test's isolation guarantees that failures are due to the specific test case, not residual data from prior tests.
5. CI Hook
Integration into a CI pipeline (e.g., Jenkins, GitHub Actions) is straightforward. The CI environment must have Docker installed and accessible to the test runner. Testcontainers automatically manages the lifecycle of the ephemeral Postgres container. The build tool (Maven/Gradle) executes the tests as part of the standard build command (./gradlew test or mvn test). Testcontainers handles spinning up the container before tests run and tearing it down afterwards, requiring no explicit docker run or docker stop commands in the CI script itself beyond ensuring Docker is available.
