I’ve set up a testcontainer for use during integration testing. Whilst it works and the test data is upserted to the test container, I cannot accurately check that my repository function, using JOOq, actually upserts any data at all because it seems to go nowehere.
Here is the code for my test-data.sql
file:
CREATE TABLE IF NOT EXISTS users
(uuid UUID NOT NULL, first_name VARCHAR(255),
last_name VARCHAR(255), email_address VARCHAR(255));
INSERT INTO users (uuid, first_name, last_name, email_address)
VALUES (etc)
here is the code for my UsersRepositoryTest.java
file:
package users;
import cache.RedisBackedCache;
import com.example.config.JooqTestConfig;
import org.example.Application;
import org.jooq.DSLContext;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Order;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.flyway.FlywayProperties;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.DynamicPropertyRegistry;
import org.springframework.test.context.DynamicPropertySource;
import org.springframework.test.context.junit.jupiter.SpringExtension;
import org.testcontainers.containers.GenericContainer;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;
import org.testcontainers.utility.DockerImageName;
import java.util.List;
import java.util.UUID;
import static org.jooq.codegen.maven.example.Tables.USERS;
import static org.junit.jupiter.api.Assertions.*;
@SpringBootTest(classes = {Application.class, JooqTestConfig.class})
@Testcontainers
@ExtendWith(SpringExtension.class)
@ActiveProfiles("test")
public class UsersRepositoryTest {
private UsersRepository usersRepository;
@Autowired
private DSLContext dslContext;
@Container
private static final PostgreSQLContainer<?> postgreSQLContainer = new PostgreSQLContainer<>("postgres:11.1")
.withDatabaseName("integration-tests-db").withUsername("username").withPassword("password")
.withInitScript("db/test-data.sql");
static {
postgreSQLContainer.start();
}
UsersResource user = new UsersResource(UUID.randomUUID(), "Tom", "Campbell", "[email protected]");
@BeforeEach
void setUp() {
usersRepository = new UsersRepository(dslContext);
}
private UsersResource upsertedUser(String emailAddress) {
return dslContext.select(USERS.asterisk())
.from(USERS)
.where(USERS.EMAIL_ADDRESS.eq(emailAddress))
.fetchAnyInto(UsersResource.class);
}
@Test
void testConnectionToDatabase() {
Assertions.assertNotNull(usersRepository);
}
@Test
public void addUserSuccess(){
usersRepository.upsertUsers(user);
List<UsersResource> users = usersRepository.getAllUsers();
assertTrue(users.contains(user));
}
@Test
void addUserAlreadyExists(){
usersRepository.upsertUsers(user);
Throwable exception = assertThrows(IllegalStateException.class, () -> usersRepository.upsertUsers(user));
assertEquals("User could not be created", exception.getMessage());
}
@Test
void updateUser(){
usersRepository.upsertUsers(user);
UUID userUUID = dslContext.select(USERS.UUID)
.from(USERS)
.where(USERS.EMAIL_ADDRESS.eq(user.emailAddress))
.fetchSingleInto(UUID.class);
UsersResource updatedUser = new UsersResource(userUUID
usersRepository.updateUserName(updatedUser);
assertEquals(upsertedUser.uuid, updatedUser.uuid);
assertEquals(upsertedUser.firstName, updatedUser.firstName);
assertEquals(upsertedUser.lastName, updatedUser.lastName);
assertEquals(upsertedUser.emailAddress, updatedUser.emailAddress);
}
@Test
void deleteUserSuccess(){
usersRepository.upsertUsers(user);
UUID userUUID = dslContext.select(USERS.UUID)
.from(USERS)
.where(USERS.EMAIL_ADDRESS.eq(user.emailAddress))
.fetchSingleInto(UUID.class);
usersRepository.deleteUser(userUUID);
boolean userRecordExists = dslContext.fetchExists(USERS, USERS.EMAIL_ADDRESS.eq(user.emailAddress));
assertFalse(userRecordExists);
}
}
and finally, here is the pom file:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-3.9.7.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>gown-qub-app</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>22</maven.compiler.source>
<maven.compiler.target>22</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.3.0</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jooq</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
<version>3.2.4</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>5.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>junit-jupiter</artifactId>
<version>1.19.8</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>postgresql</artifactId>
<version>1.19.3</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.2.224</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-postgres-extensions</artifactId>
<version>3.19.9</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>3.19.9</version>
</dependency>
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>2.7.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-testcontainers</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.junit.platform</groupId>
<artifactId>junit-platform-commons</artifactId>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
</dependency>
<dependency>
<groupId>redis.clients</groupId>
<artifactId>jedis</artifactId>
<version>4.0.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>3.3.0</version>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>3.19.9</version>
<executions>
<execution>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
</dependencies>
<configuration>
<jdbc>
<driver>org.postgresql.Driver</driver>
<url>jdbc:postgresql:postgres</url>
</jdbc>
<generator>
<database>
<name>org.jooq.meta.postgres.PostgresDatabase</name>
<includes>.*</includes>
<excludes></excludes>
<inputSchema>public</inputSchema>
</database>
<target>
<packageName>org.jooq.codegen.maven.example</packageName>
<directory>target/generated-sources/jooq</directory>
</target>
</generator>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-jar-plugin</artifactId>
<version>3.4.1</version>
<configuration>
<archive>
<manifest>
<addClasspath>true</addClasspath>
<classpathPrefix>lib/</classpathPrefix>
<mainClass>org.example.Application</mainClass>
</manifest>
</archive>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>22</source>
<target>22</target>
<release>22</release>
<compilerArgs>--enable-preview</compilerArgs>
</configuration>
</plugin>
<plugin>
<groupId>org.testcontainers</groupId>
<artifactId>testcontainers-jooq-codegen-maven-plugin</artifactId>
<dependencies>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>postgresql</artifactId>
<version>1.19.3</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>
</dependencies>
<executions>
<execution>
<id>generate-jooq-sources</id>
<goals>
<goal>generate</goal>
</goals>
<phase>generate-sources</phase>
<configuration>
<database>
<type>POSTGRES</type>
<containerImage>postgres:15.3-alpine</containerImage>
</database>
<flyway>
<locations>
filesystem:src/main/resources/db/migration
</locations>
</flyway>
<jooq>
<generator>
<database>
<includes>.*</includes>
<excludes>flyway_schema_history</excludes>
<inputSchema>public</inputSchema>
</database>
<target>
<packageName>example.micronaut.jooq</packageName>
<directory>target/generated-sources/jooq</directory>
</target>
</generator>
</jooq>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
The function ‘upsertUsers’ looks like this:
public void upsertUsers(UsersResource users) {
boolean emailExists = dslContext.fetchExists(
dslContext.selectOne().from(USERS).where(USERS.EMAIL_ADDRESS.eq(users.emailAddress))
);
try {
if(emailExists) {
throw new IllegalStateException("This user already exists");
} else {
dslContext.insertInto(USERS)
.set(USERS.UUID, UUID.randomUUID())
.set(USERS.FIRST_NAME, users.firstName)
.set(USERS.LAST_NAME, users.lastName)
.set(USERS.EMAIL_ADDRESS, users.emailAddress)
.onConflict(USERS.EMAIL_ADDRESS)
.doNothing()
.execute();
}
} catch(Exception e) {
// logger.info("User could not be created", e);
}
I believe it’s because my JOOq and then testcontainers don’t align therefore it cannot be checked.
I created the users table through the dbeaver UI.
2
Answers
The issue was that there was no dynamic property source given. I believe that the spring datasource still pointed to my local database as opposed to the testcontainer I had set up. Using this code fixed the issue:
So, your observed problem is:
And the query you wrote is using:
As in
ON CONFLICT .. DO NOTHING
I’d say, everything works as expected? (Except you should probably use
doUpdate()
instead…)