Mocking JDBC Using a Set of SQL String / Result Pairs

Posted on by lukaseder
In a previous blog post, I’ve shown how the programmatic MockDataProvider can be used to mock the entire JDBC API through a single functional interface: 

// context contains the SQL string and bind variables, etc.
MockDataProvider provider = context -> {

    // This defines the update counts, result sets, etc.
    // depending on the context above.
    return new MockResult[] { ... }
};
Writing the provider manually can be tedious in some cases, especially when a few static SQL strings need to be mocked and constant result sets would be OK. In that case, the MockFileDatabase is a convenient implementation that is based on a text file (or SQL string), which contains a set of SQL string / result pairs of the form:
  • SQL string
  • Result set
  • Update count
Assuming this is the content of the mocking.txt file: 
select first_name, last_name from actor;
> first_name last_name
> ---------- ---------
> GINA       DEGENERES
> WALTER     TORN     
> MARY       KEITEL   
@ rows: 3

select first_name, last_name, count(*)
from actor
join film_actor using (actor_id)
group by actor_id, first_name, last_name
order by count(*) desc;
> first_name last_name count
> ---------- --------- -----
> GINA       DEGENERES 42
> WALTER     TORN      41
> MARY       KEITEL    40
@ rows: 3
We can then easily load that file into a class and run queries against it: 

import static java.lang.System.out;
import java.sql.*;
import org.jooq.tools.jdbc.*;

public class Mocking {
    public static void main(String[] args) throws Exception {
        MockDataProvider db = new MockFileDatabase(
            Mocking.class.getResourceAsStream("/mocking.txt");

        try (Connection c = new MockConnection(db));
            Statement s = c.createStatement()) {

            out.println("Actors:");
            out.println("-------");
            try (ResultSet rs = s.executeQuery(
                "select first_name, last_name from actor")) {
                while (rs.next())
                    out.println(rs.getString(1) 
                        + " " + rs.getString(2));
            }

            out.println();
            out.println("Actors and their films:");
            out.println("-----------------------");
            try (ResultSet rs = s.executeQuery(
                "select first_name, last_name, count(*)\n" +
                "from actor\n" +
                "join film_actor using (actor_id)\n" +
                "group by actor_id, first_name, last_name\n" +
                "order by count(*) desc")) {
                while (rs.next())
                    out.println(rs.getString(1) 
                        + " " + rs.getString(2) 
                        + " (" + rs.getInt(3) + ")");
            }
        }
    }
}
The above will print: 
Actors:
-------
GINA DEGENERES
WALTER TORN
MARY KEITEL

Actors and their films:
-----------------------
GINA DEGENERES (42)
WALTER TORN (41)
MARY KEITEL (40)
Notice how we’re not really connecting to any database at all, but simply running queries against our mock database file, which contains a hard-coded set of SQL string / result pairs. While this obviously shouldn’t be used to implement / mock a full-fledged database, it is certainly very useful to intercept only a few queries and return hard-coded results to any JDBC based caller – regardless if they’re using jOOQ, Hibernate, or vanilla JDBC as in the above API.

Published by lukaseder

I made jOOQ

8 thoughts on “Mocking JDBC Using a Set of SQL String / Result Pairs”

  1. Great feature, Lukas!

    This is useful when your app is integrating with another app through database. You know, like fetching data from a different schema directly (not always it is possible to have the third-party schema locally).

    Although it’s not the best approach to integrate apps, it’s very common in industry.

    Reply

    1. Interesting, do you have a specific example of what you have in mind? Do you mean you’d fetch data from a foreign database schema via JDBC (or jOOQ / Hibernate) and would like to be able to mock that schema for test / development?

      Reply

      1. Yes, this is exactly the scenario I’m talking about! Usually during the integration test we don’t want to mock the app’s schema but only the foreign database schemas, for example Oracle EBS schema.

        Reply

        1. Very interesting, great to keep this in mind. I’m always looking for new ideas for features. Currently, the MockFileDatabase is quite limited as it has to match the exact SQL string. I’m thinking of adding some regular expressions / pattern matching, and even perhaps some parser integration to get rid of whitespace differences. What do you think? How would you like to use such a tool, if you were using it?

          Reply

          1. Yeah, working and generating Text files for each test is not that easy. Humm… regular expressions / pattern matching could help to turn the datasets less fragile.

            When I’m writing integration tests I usually use DbUnit and its XML datasets to clean up and populate my local database before each test. When my app has to integrate with a foreign schema (or database) I usually try to create their tables inside my local database (in a different schema, for example), but sometimes it’s not possible because the complexity of the schema, you know, triggers, views, FKs, packages etc. So in this case I mock the foreign schema’s DAO/Service and I live with it. The disadvantage of this mocking approach is I can’t test the SQL properly.

            But if I had some in-memory database, like HSQLDB, with a minimal generated schema from a SQL script or even from jOOQ generated code (is it possible?) I could run all my SQL queries against it without any modification. In this case, It would be necessary to use jOOQ SQL translation to bring some level of compatibility between two different databases.

            What do you think? Maybe I’m daydreaming, hehe

            Reply

            1. Thanks for sharing your use-cases, that’s very interesting.

              But if I had some in-memory database, like HSQLDB, with a minimal generated schema from a SQL script or even from jOOQ generated code (is it possible?) I could run all my SQL queries against it without any modification. In this case, It would be necessary to use jOOQ SQL translation to bring some level of compatibility between two different databases.

              Yes, that’s definitely possible. The DDLDatabase can generate a jOOQ schema from a SQL script [1], and you can recreate the schema on any database using DSLContext.ddl() [2] or simply by translating the SQL script using the parser (similar to [3])

              [1]: https://www.jooq.org/doc/latest/manual/code-generation/codegen-ddl
              [2]: https://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#ddl-org.jooq.Schema-
              [3]: https://www.jooq.org/translate

              In fact, this stuff is the main reason why jOOQ now has a parser, since version 3.9. And I’ve been working hard to improve it for 3.10 and 3.11, to support even more vendor-specific DDL. Great to know that your use-case is covered by the latest jOOQ improvements!

              Reply

  2. Hi, how about capture/simulate feature for unit test JDBC mocking, where requests with their actual results are logged during capture mode then in simulate/spy mode the recorded results could be provided, is this possible with JOOQ?

    Reply

Leave a Reply