Mocking JDBC Using a Set of SQL String / Result Pairs

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.

Hack up a Simple JDBC ResultSet Cache Using jOOQ’s MockDataProvider

Some queries shouldn’t hit the database all the time. When you query for master data (such as system settings, languages, translations, etc.), for instance, you may want to avoid sending the same silly query (and the results) over the wire all the time. For example:

SELECT * FROM languages

Most databases maintain buffer caches to accelerate these queries, so you don’t always hit the disk. Some databases maintain result set caches per cursor, or their JDBC drivers might even implement result set caches directly in the driver – a little known feature in Oracle, for instance:

SELECT /*+ RESULT_CACHE */ * FROM languages

But you may not be using Oracle, and because patching JDBC is a pain, you might have resorted to implementing the cache one or two layers up in the data access or service layer:

class LanguageService {
    private Cache cache;

    List<Language> getLanguages() {
        List<Language> result = cache.get();

        if (result == null) {
            result = doGetLanguages();
            cache.put(result);
        }

        return result;
    }
}

Doing it in the JDBC layer, instead

While this might work fine on a per-service-and-method level, it might quickly become tedious when you query only parts of those results. E.g. what happens when you add an additional filter? Should you cache that query as well? Should you perform the filter on the cache, or hit the database at least once per filter?

class LanguageService {
    private Cache cache;

    List<Language> getLanguages() { ... }
    List<Language> getLanguages(Country country) {
        // Another cache?
        // Query the cache only and delegate to
        //     getLanguages()?
        // Or don't cache this at all?
    }
}

wouldn’t it be nice if we had a cache of the form:

Map<String, ResultSet> cache;

… which caches re-usable JDBC ResultSets (or better: jOOQ Results) and returns the same results every time an identical query string is encountered.

Use jOOQ’s MockDataProvider for this

jOOQ ships with a MockConnection, which implements the JDBC Connection API for you, mocking all other objects, such as PreparedStatement, ResultSet, etc. We’ve already introduced this useful tool for unit testing in a previous blog post.

But you can “mock” your connection also in order to implement a cache! Consider the following, very simple MockDataProvider:

class ResultCache implements MockDataProvider {
    final Map<String, Result<?>> cache = 
        new ConcurrentHashMap<>();
    final Connection connection;

    ResultCache(Connection connection) {
        this.connection = connection;
    }

    @Override
    public MockResult[] execute(MockExecuteContext ctx)
    throws SQLException {
        Result<?> result;

        // Add more sophisticated caching criteria
        if (ctx.sql().contains("from language")) {

            // We're using this very useful new Java 8
            // API for atomic cache value calculation
            result = cache.computeIfAbsent(
                ctx.sql(),
                sql -> DSL.using(connection).fetch(
                    ctx.sql(),
                    ctx.bindings()
                )
            );
        }

        // All other queries go to the database
        else {
            result = DSL.using(connection).fetch(
                ctx.sql(), 
                ctx.bindings()
            );
        }

        return new MockResult[] { 
            new MockResult(result.size(), result)
        };
    }
}

Obviously, this is a very simplistic example. A real cache would involve invalidation (time-based, update-based, etc.) as well as more selective caching criteria than just matching on from language.

But the fact is that using the above ResultCache, we can now wrap all JDBC connections and prevent hitting the database more than once for all queries that query from the language table! An example using jOOQ API:

DSLContext normal = DSL.using(connection);
DSLContext cached = DSL.using(
    new MockConnection(new ResultCache(connection))
);

// This executs a select count(*) from language query
assertEquals(4, cached.fetchCount(LANGUAGE));
assertEquals(4, normal.fetchCount(LANGUAGE));

// Let's add another language (using normal config):
LanguageRecord lang = normal.newRecord(LANGUAGE);
lang.setName("German");
lang.store();

// Checking again on the language table:
assertEquals(4, cached.fetchCount(LANGUAGE));
assertEquals(5, normal.fetchCount(LANGUAGE));

The cache works like a charm! Note that the current cache implementation is merely SQL string based (as it should be). If you modify the SQL string even only slightly, you’ll experience another cache miss and the query goes back to the database:

// This query is not the same as the cached one, it
// fetches two count(*) expressions. Thus we go back
// to the database and get the latest result.
assertEquals(5, (int) cached
    .select(
        count(),
        count())
    .from(LANGUAGE)
    .fetchOne()
    .value1());

// This still has the "stale" previous result
assertEquals(4, cached.fetchCount(LANGUAGE));

Conclusion

Caching is hard. Very hard. Apart from concurrency, naming things and off-by-one errors, it’s one of the three hardest problems in software.

This article doesn’t recommend to implement a cache at the JDBC level. You may or may not make that decision yourself. But when you do, then you can see how easy it is to implement such a cache using jOOQ.

jOOQ is the best way to write SQL in Java

And the best thing is that you don’t have to use jOOQ in all of your application. You can use it just for this particular use-case (and for mocking JDBC), and continue using JDBC, MyBatis, Hibernate, etc, as long as you patch other framework’s JDBC Connections with the jOOQ MockConnection.