All versions: 3.11 | 3.10 | Development versions: 3.12

An alternative to the previous programmatic implementation of a mocking connection is the more declarative approach using a org.jooq.tools.jdbc.MockFileDatabase, which reads SQL statements and their corresponding result sets directly from a file. Assuming the following file content:

# All lines with a leading hash are ignored. This is the MockFileDatabase comment syntax
-- SQL comments are parsed and passed to the SQL statement
/* The same is true for multi-line SQL comments */
select 'A';
> A
> -
> A
@ rows: 1

select 'A', 'B' union all 'C', 'D';
> A B
> - -
> A B
> C D
@ rows: 2

# Statements without result sets just leave that section empty
update t set x = 1;
@ rows: 3

The above syntax consists of the following elements to define an individual statement:

  • MockFileDatabase comments are any line with a leading hash ("#") symbol. They are ignored when reading the file
  • SQL comments are part of the SQL statement
  • A SQL statement always starts on a new line and ends with a semi colon (;), which is the last symbol on the line (apart from whitespace)
  • If the statement has a result set, it immediately succeeds the SQL statement and is prefixed by angle brackets and a whitespace ("> "). Any format that is accepted by DSLContext.fetchFromTXT() is accepted.
  • The statement is always terminated by the row count, which is prefixed by an at symbol, the "rows" keyword, and a double colon ("@ rows:").

The above database supports exactly two statements in total, and is completely stateless (e.g. an INSERT statement cannot be made to affect the results of a subsequent SELECT statement on the same table). It can be loaded through the MockFileDatabase can be used as follows:

// Initialise your data provider (implementation further down):
MockFileDatabase db = new MockFileDatabase(new File("/path/to/db.txt"));
MockConnection connection = new MockConnection(provider);

// Pass the mock connection to a jOOQ DSLContext:
DSLContext create = DSL.using(connection, SQLDialect.POSTGRES);

// Execute queries transparently, with the above DSLContext:
Result<?> result = create.select(inline("A")).fetch();
Result<?> result = create.select(inline("A"), inline("B")).fetch();

// Queries that are not listed in the MockFileDatabase will simply fail
Result<?> result = create.select(inline("C")).fetch();

In situations where the expected set of queries are well-defined, the MockFileDatabase can offer a very effective way of mocking parts of the database engine, without offering the complete functionality of the programmatic mocking connection.

The jOOQ Logo