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.
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.
