mock jooq select request

Question

I have my service with jooq select in myMethod()

Result<MyRecord> records = dsl.selectFrom(MyTable).fetch();

And I try to write unit-test using mockito. I want to mock my dsl and get 2 different result when I call service.myMethod()

Mockito.when(dsl.selectFrom(MyTable)).thenReturn(result)

But it doesn't work. How can I mock my select request?

Answer 1

Mocking the jOOQ API

If you want to mock the jOOQ API, the interesting methods for you to mock are the various fetch() methods, or execute() methods, as they act like the "terminal" methods of the jOOQ DSL API. All the intermediate methods should produce new mocks, not actual results.

However, as jOOQ's DSL is vast, you might be overlooking a variety of edge cases, so I think that mocking it entirely might not work too well.

Mocking the JDBC API

If you really want to mock the jOOQ API, I would rather mock it on the JDBC level. jOOQ implements a mock JDBC Connection, allowing to mock the entirety of JDBC using a single lambda expression, where you can do something like this (from the manual):

MockDataProvider provider = new MyProvider();
MockConnection connection = new MockConnection(provider);
DSLContext ctx = DSL.using(connection, SQLDialect.ORACLE);
Result<BookRecord> result = ctx.selectFrom(BOOK).where(BOOK.ID.eq(5)).fetch();

And then (simplified):

public class MyProvider implements MockDataProvider {

    @Override
    public MockResult[] execute(MockExecuteContext ctx) throws SQLException {
        DSLContext ctx = DSL.using(SQLDialect.ORACLE);
        MockResult[] mock = new MockResult[1];
        String sql = ctx.sql();

        if (sql.toUpperCase().startsWith("DROP")) {
            throw new SQLException("Statement not supported: " + sql);
        }
        else if (sql.toUpperCase().startsWith("SELECT")) {
            Result<Record2<Integer, String>> result =
                ctx.newResult(AUTHOR.ID, AUTHOR.LAST_NAME);
            result.add(cctx
                .newRecord(AUTHOR.ID, AUTHOR.LAST_NAME)
                .values(1, "Orwell"));
            mock[0] = new MockResult(1, result);
        }

        return mock;
    }
}

There's also an out of the box, file-based version of this mock implementation, the MockFileDatabase, which uses regular expressions to match SQL strings and provides a text based syntax to construct result sets for your queries.

Mocking databases in general

Please beware that mocking is not a good way to test database interaction - it only gets you so far, in simple cases. A much better, thorough, approach are integration tests. If you must, using an in-memory database, but ideally using your production database product (e.g. Oracle, PostgreSQL, etc.) via something like testcontainers.