Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Mock File Database
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
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.
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.
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 # Statements producing specific exceptions can indicate them as such select * from t; @ exception: ACCESS TO TABLE T FORBIDDEN
The above syntax consists of the following elements to define an individual statement:
-
MockFileDatabasecomments 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 byDSLContext.fetchFromTXT(),DSLContext.fetchFromJSON(), orDSLContext.fetchFromXML()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:
MockFileDatabase provider = 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.
Matching statements using regular expressions
Alternatively, regular expressions can be used to match statements in order of definition in the file.
# Regardless of the number of columns, if selecting 'A' as the first column, # always return a single row containing columns A and B select 'A', .*; > A B > - - > A B @ rows: 1 # All other select statements are assumed to return only a column A select .*; > A > - > A @ rows: 1
The same rules apply as before. The first matching statement will be applied for any given input statement.
This feature is "opt-in", so it has to be configured appropriately:
// Initialise your data provider:
MockFileDatabase provider = new MockFileDatabase(new MockFileDatabaseConfiguration()
.source(new File("/path/to/db.txt"))
.patterns(true) // Turn on regular expressions here
);
MockConnection connection = new MockConnection(provider);
// Pass the mock connection to a jOOQ DSLContext:
DSLContext create = DSL.using(connection, SQLDialect.POSTGRES);
// This returns a column A only
Result<?> result = create.select(inline("X")).fetch();
// This returns columns A and B
Result<?> result = create.select(inline("A"), inline("B"), inline("C")).fetch();
Feedback
Do you have any feedback about this page? We'd love to hear it!