Using the MockDataProvider of JOOQ, how do I set the lastId() that gets returned?

Question

Note: I am not using the code generator of Jooq

In the DAO unit test, the DAO is tested to ensure that after a object has been inserted that the DAO sets the Id as returned by the last_insert_id() from the database. No actual database is contacted since I'm using the MockConnection and MockDataProvider of JOOQ.

When the following is executed by the DAO:

DSLContext ctx = DSL.using(connection, SQLDialect.MYSQL);
//insert
//get id
BigInteger id = ctx.lastId();

JOOQ executes the following query:

select last_insert_id() from dual;

In my MockDataProvider I check when this query is executed and return a result accordingly:

import static org.jooq.impl.DSL.dual;

//other code

@Override
public MockResult[] execute(MockExecuteContext ctx) throws SQLException {
   Field<BigInteger> id = DSL.field("", BigInteger.class);
   Record record = dual().newRecord();
   record.setValue(id, BigInteger.valueOf(1234567));
   return new MockResult[] { new MockResult(record) };             
}

When the above MockResult is returned, I get the following exception

java.lang.IllegalArgumentException: Field () is not contained in Row ()

What is the correct way to populate the MockResult for the last_insert_id() query ?

Answer 1

This is a working implementation for your MockDataProvider for DSLContext.lastID():

BigInteger expected = BigInteger.valueOf(1234567);
DSLContext ctx = DSL.using(new MockConnection(c -> {
    Field<BigInteger> id = DSL.field("last_insert_id()", BigInteger.class);
    Record record = DSL.using(MYSQL).newRecord(id);
    record.setValue(id, expected);
    return new MockResult[] { new MockResult(record) };
}), SQLDialect.MYSQL);

assertEquals(expected, ctx.lastID());

There are essentially two things that went wrong in your approach:

The field name

The field name of the field you're selecting is really called last_insert_id() (at least in jOOQ 3.5.3), thus you also have to name your mock field this way:

    Field<BigInteger> id = DSL.field("last_insert_id()", BigInteger.class);

The Record

You have to create a record that already contains your field in it. The dual() table contains a different field. The fact that jOOQ generates a from dual query is irrelevant to the fact that you have to produce a Record that contains a last_insert_id() field:

    Record record = DSL.using(MYSQL).newRecord(id);

Warning

Of course, you're making a strong assumption about jOOQ's implementation as it is in 3.5.3. There is no guarantee that the exact query generated by DSLContext.lastID() will always be

select last_insert_id() from dual