How to mock a jOOQ result in Spock

I'm working in a Spring Boot app, I have a Groovy class:

@Component
class MyClass {

    DSLContext jooq

    MyClass(DSLContext jooq) {
        this.jooq = jooq
    }

    void execute(String value) {
        Table tbl     // assume assigned a Table
        Field<?> fld  // assume assigned a Field

        List<Record> records = jooq.select(DSL.asterisk()).from(tbl).where(fld.eq(value)).fetch()

        InsertValueStepN<Record> insertStep = jooq.insertInto(tbl)?.columns()
        // steps to complete insertStep elided
        insertStep?.execute()
    }
}

And I have a Spock unit test class:

class MyClassTest extends Specification {

    DSLContext jooq = Mock()
    MyClass tested = new MyClass(jooq)

    def "execute doesn't throw exceptions when no records found"() {
        given:
            jooq.select(_ as Field[]) >> []
        expect:
            tested.execute(null)
    }
}

My trouble is in mocking jOOQ and the result of jooq.select(). I need Spock to get passed calling jooq.select(). As shown, I get an NPE when trying to invoke from() on a null object.

I've also tried jooq.select(DSL.asterisk()) >> [], but it throws a GroovyCastException because [] as an ArrayList isn't a SelectSelectStep... but SelectSelectStep is an interface whose only implementation is SelectImpl and newing-up a SelectImpl is proving to be a challenge (and doesn't seem like the right answer anyway).

For the time being, I've decided to use the safe-navigation operator (?.), which allows my current mock to pass, but again, this doesn't seem right. I'd like to actually mock the results of the jOOQ calls. What am I missing?

Mocking the jOOQ API

The jOOQ API is vast and you will hardly be able to mock it completely. The simple roadblock you ran into should tell you that you're about to go down a long road of pain and suffering. I recommend you don't mock the jOOQ API.

The problem you ran into is shows that you can't just return an [] from jooq.select(). You have to continue mocking all the returned DSL types until you reach an operation like fetch() (and then, you have to mock also all the variants!).

Mocking JDBC

jOOQ offers a MockConnection, which allows for mocking JDBC (not the jOOQ API!) via a single lambda expression, or using a file. However, as you will notice on those manual pages, there's a 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 product instead for integration testing, rather than implementing your test database inside of a MockDataProvider.

Mocking a database is rarely a good idea, as you will be implementing your own database, rather than testing interactions with your actual database.

Better: integration testing

Better use integration tests, e.g. via testcontainers. This article illustrates why testcontainers is generally better than using a test database product, such as H2, (which is again still better than mocking). This can also be useful to generate jOOQ code.

Variant A: original code

Not because I think that it is particularly nice, but for a simple unit test without Testcontainers it would look like this with the original code, only adjusted to make the Table and Field values injectable to get it compiling and running:

package de.scrum_master.stackoverflow.q75745239

import org.jooq.*
import org.jooq.impl.DSL
import org.jooq.impl.Eq
import spock.lang.Specification

class MyClassTest extends Specification {
  DSLContext jooq = Mock(DSLContext) {
    select(_) >> Mock(SelectSelectStep) {
      from(_) >> Mock(SelectJoinStep) {
        where(_) >> Mock(SelectConditionStep) {
          fetch() >> Mock(Result)
        }
      }
    }
    insertInto(_) >> Mock(InsertSetStep)
  }
  MyClass tested = new MyClass(jooq,
    // These two mocks only exist to make the sample code work
    Mock(Table),
    Mock(Field) {
      eq(_) >> Mock(Eq)
    }
  )

  def "execute doesn't throw exceptions when no records found"() {
    when:
    tested.execute("dummy")

    then:
    noExceptionThrown()
  }
}

class MyClass {
  DSLContext jooq
  Table tbl     // assume assigned a Table
  Field<?> fld  // assume assigned a Field

  MyClass(DSLContext jooq, Table tbl, Field<?> fld) {
    this.jooq = jooq
    this.tbl = tbl
    this.fld = fld
  }

  void execute(String value) {
    List<Record> records = jooq.select(DSL.asterisk()).from(tbl).where(fld.eq(value)).fetch()
    InsertValuesStepN<Record> insertStep = jooq.insertInto(tbl)?.columns()
    // steps to complete insertStep elided
    insertStep?.execute()
  }
}

Variant B: code under test slightly refactored for testability

Now if we want to make code with chained DSL calls like JOOQ's more easily testable, we can simply extract those call chains into helper methods with clean names and then use a Spy to stub them in unit tests (not in integration tests or closer-to-integration tests using Testcontainers or an in-memory DB):

package de.scrum_master.stackoverflow.q75745239

import org.jooq.*
import org.jooq.impl.DSL
import spock.lang.Specification

class MyClassTest extends Specification {
  MyClass tested = Spy(new MyClass(Mock(DSLContext), Mock(Table), Mock(Field))) {
    fetchRecords(_) >> Mock(Result)
    createInsertStep() >> null
  }

  def "execute doesn't throw exceptions when no records found"() {
    when:
    tested.execute("dummy")

    then:
    noExceptionThrown()
  }
}

class MyClass {
  DSLContext jooq
  Table tbl     // assume assigned a Table
  Field<?> fld  // assume assigned a Field

  MyClass(DSLContext jooq, Table tbl, Field<?> fld) {
    this.jooq = jooq
    this.tbl = tbl
    this.fld = fld
  }

  void execute(String value) {
    List<Record> records = fetchRecords(value)
    InsertValuesStepN<Record> insertStep = createInsertStep()
    // steps to complete insertStep elided
    insertStep?.execute()
  }

  protected Result<Record> fetchRecords(String value) {
    return jooq.select(DSL.asterisk()).from(tbl).where(fld.eq(value)).fetch()
  }

  protected InsertValuesStepN<Record> createInsertStep() {
    return jooq.insertInto(tbl)?.columns()
  }
}

See? No mock hell, the unit test is clean and simple.

IMO, nothing replaces a unit test and nothing beats it with regard to speed. If it is difficult to write a unit test, refactor. TDD and BDD are design tools, not just means to cover code with tests. It is pretty easy to extract complex call chains into small methods.