Using jooq with @TestTransaction

[Gabor79]

Jooq is used mainly in our application. Additionally we use the oubox package which uses eintityManager.
An interesting thing happens when we want to test our solution for the oubox-pattern.

The test cases are annotated with @TestTransaction and of course somewhere in the application, there is an @Transactional annotation to ensure the single transaction is done because of the outbox-pattern. ​

If the oubox table is queried with native query the tests give the expected result.
If the oubox table is queried with jooq the test give not the expected result. Quite precisely, it looks like the outbox table is empty. The result of the query does not return any records.

Why is there a difference between native query and jooq?

Of course, remove is turned off during testing.
%test.quarkus.debezium-outbox.remove-after-insert = false

Sample code can be found here. Test cases should be examined.

https://github.com/Gabor79/jooq-demo

[Gabor79]

Has anyone managed to look at the sample code I attached?

[Gabor79]

It was suggested in quarkus community quarkusio/quarkus#21944 that I should open an issue in quarkus-jooq.

[lukaseder]

Hi @Gabor79. I'm not maintaining this third party extension here, but I might still be able to help? This extension wires the agroal data source into jOOQ's DSLContext, so in my opinion, jOOQ should already participate in transactions. After all, your jOOQ INSERT statement worked, because otherwise, the EntityManager could not have picked up its results.

But perhaps something goes wrong with the @TestTransaction, and that isn't correctly communicated to jOOQ, transitively? I don't think jOOQ does anything wrong here. Some wiring might not work. Have you tried reading the data from within the @Transaction, instead of from outside of it?

How can I reproduce the problem from your repository? I tried running mvn clean install and mvn clean integration-test

[Gabor79]

HI @lukaseder
Thank you for your answer.
This way you can reproduce and compare.
It works as expected.: mvn -Dtest=AServiceWithEntityManagerIT.class test
It does not work as expected.: mvn -Dtest=AServiceWithJooqIT.class test

[lukaseder]

OK, thanks @Gabor79. I can reproduce it this way. Funny, why wouldn't the tests run if I don't list them explicitly?

Anyway. I would still assume there's a bug in @TestTransaction. Try this, to remove jOOQ from the equation:

diff --git a/src/test/java/com/example/AServiceWithJooqIT.java b/src/test/java/com/example/AServiceWithJooqIT.java
index f256f5d..68806ef 100644
--- a/src/test/java/com/example/AServiceWithJooqIT.java
+++ b/src/test/java/com/example/AServiceWithJooqIT.java
@@ -10,6 +10,11 @@ import org.jooq.Result;
 import org.junit.jupiter.api.Test;

 import javax.inject.Inject;
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
 import java.util.ArrayList;
 import java.util.List;

@@ -23,10 +28,12 @@ class AServiceWithJooqIT {
     AService underTest;
     @Inject
     DSLContext jooq;
+    @Inject
+    DataSource ds;

     @Test
     @TestTransaction
-    void test1(){
+    void test1() throws SQLException {
         underTest.addHost(new AJooq(1L, "name1"));
         List<OutboxEventDto> outboxEvents = getOutboxEvents();
         assertThat(outboxEvents).hasSize(1);
@@ -34,14 +41,23 @@ class AServiceWithJooqIT {

     @Test
     @TestTransaction
-    void test2(){
+    void test2() throws SQLException {
         underTest.addHost(new AJooq(2L, "name2"));
         List<OutboxEventDto> outboxEvents = getOutboxEvents();
         assertThat(outboxEvents).hasSize(1);
     }

     @SuppressWarnings("rawtypes")
-    private List<OutboxEventDto> getOutboxEvents() {
+    private List<OutboxEventDto> getOutboxEvents() throws SQLException {
+        try (Connection c = ds.getConnection();
+             Statement s = c.createStatement();
+             ResultSet rs = s.executeQuery("select * from outbox_event")) {
+
+            System.out.println("Results: ");
+            while (rs.next()) {
+                System.out.println(rs.getString(1));
+            }
+        }
         Result<Record4<String, String, String, String>> outboxEvents =
                 jooq.select(OUTBOX_EVENT.AGGREGATE_ID, OUTBOX_EVENT.AGGREGATE_TYPE, OUTBOX_EVENT.TYPE,
                             OUTBOX_EVENT.PAYLOAD)

The purely JDBC based code doesn't see your inserted records either.

[lukaseder]

I think this is generally a good idea with jOOQ. jOOQ doesn't do anything magic, especially not with transactions, so if something transaction related isn't working, try removing jOOQ from the equation, try again with JDBC, and you're probably going to see that it still isn't working...

[Gabor79]

You're right, it doesn't work with either JDBC or JOOQ.

[famod]

A transaction started by @TestTransaction will never commit, only roll back. Because of this and because of the new connection that is opened in the jooq-free example, I fail to see how that is supposed to work.

[lukaseder]

All the code in question is running within the @TestTransaction