Unit test for a large SELECT query with jOOQ

user_1928515 0
Viewed 690

I am using jOOQ for working with a relational database. I have a SELECT query for which I need to write unit tests with mocking. Based on this doc and this post, I need to define my own data provider, which should look something like this:

class MyProvider implements MockDataProvider {

    DSLContext create = DSL.using(SQLDialect.MYSQL);

    @Override
    public MockResult[] execute(MockExecuteContext mockExecuteContext) throws SQLException {
        MockResult[] mock = new MockResult[1];
        String sql = mockExecuteContext.sql();

        if (sql.startsWith("select")) {
            Result<Record2<String, String>> result = create.newResult(COL_1, COL_2);
            result.add(create.newRecord(COL_1, COL_2)
                             .values("val1", "val2"));

            mock[0] = new MockResult(1, result);
        }

        return mock;
    }
}

where COL_1 and COL_2 are defined as follows:

Field<String> COL_1 = field("Column1", String.class);
Field<String> COL_2 = field("Column2", String.class);

It's quite simple and straightforward when SELECT is a small one (as in the above example, just 2 columns). I am wondering how it should be done in case of complex and large selects. For instance I have a SELECT statement which selects 30+ columns from multiple table joins. Seems the same approach of

Result<Record_X<String, ...>> result = create.newResult(COL_1, ...);
result.add(create.newRecord(COL_1, ...)
                 .values("val1", ...));

does not work in case of more than 22 columns.

Any help is appreciated.

jooq java
1 Answers

Answering your question

There is no such limitation as a maximum of 22 columns. As documented here:

Higher-degree records

jOOQ chose to explicitly support degrees up to 22 to match Scala's typesafe tuple, function and product support. Unlike Scala, however, jOOQ also supports higher degrees without the additional typesafety.

You can still construct a record with more than 22 fields using DSLContext.newRecord(Field...). Now, there is no values(Object...) method on the Record type, because the Record type is the super type of all the Record1 - Record22 types. If such an overload were present, then the type safety on the sub types would be lost, because the values(Object...) method is applicable for all types of arguments. This might be fixed in the future by introducing a new RecordN subtype.

But you can load data into your record with other means, e.g. by calling Record.fromArray(Object...):

Record record = create.newRecord(COL_1, ...);
record.fromArray("val1", ...);
result.add(record);

The values() method being mere convenience (adding type safety) for fromArray().

Disclaimer:

I'm assuming you read the disclaimer on the documentation page you've linked. I'm posting it here anyway for other readers of this question, who might not have read the disclaimer:

Disclaimer: The general idea of mocking a JDBC connection with this jOOQ API is to provide quick workarounds, injection points, etc. using a very simple JDBC abstraction. It is NOT RECOMMENDED to emulate an entire database (including complex state transitions, transactions, locking, etc.) using this mock API. Once you have this requirement, please consider using an actual database instead for integration testing, rather than implementing your test database inside of a MockDataProvider.

It seems you're about to re-implement a database which can "run" any type of query, including a query with 23+ columns, and every time you change the query under test, you will also change this test here. I still recommend you do integration testing instead, using testcontainers or even with H2, which will help cover many more queries than any such unit test approach. Here's a quick example showing how to do that: https://github.com/jOOQ/jOOQ/tree/main/jOOQ-examples/jOOQ-testcontainers-example

Also, integration tests will help test query correctness. Unit tests like these will only provide dummy results, irrespective of the actual query. It is likely that such mocks can be implemented much more easily on a higher level than the SQL level, i.e. by mocking the DAO, or repository, or whatever methods, instead.

user_5217990
Related