SQL Caches

One thing everybody knows about SQL is that it's slow. Caching is the obvious solution, but can be annoying and full of boilerplate, with mistakes that are easy to make and could cause major issues.

SQLCache is the simple solution for caching individual values in singular columns in your tables. Let's say you have tables with this schema:

CREATE TABLE teams (name STRING PRIMARY KEY, points INT);
CREATE TABLE players (id STRING PRIMARY KEY, team STRING, FOREIGN KEY (team) REFERENCES teams(name) ON UPDATE CASCADE ON DELETE CASCADE);

This is a simple table layout for a team system where players are on teams, and each team has points. Let's see how you could leverage SQLCache to avoid having to send requests for these values.

Firstly, you want to be able to check how many points a team has. To handle that, you should first create your SQLHelper. In this example, let's assume it has already been created and is called sql. Next, use it to create a SQLCache:

SQLCache pointsCache = sql.createCache("teams", "points", "name");

The first parameter you pass it is the name of the table. The second parameter is the name of the column you're caching. Lastly, pass a vararg of strings representing the column names for this table's primary key, which will be used to get and set the values.

Now you want to be able to get the number of points a team has. The cache makes this incredibly simple. Say you want to get how many points the team named "red" has:

int points = pointsCache.select("red");

Simply pass the values for the primary key columns you specified earlier. If the value has not been cached yet, it will be queried and cached. If it's already in the cache, it will be returned quickly.

Note that this will return null and cause a NullPointerException if the associated column does not exist.

If you want to update the value, it's equally easy:

pointsCache.update(35, "red");

This will update the cached value for the red team, setting them to 35 points. This value is not updated in SQL until you do one of two things:

• Call flush()
• Call commit() on the SQLHelper which owns the cache

Calling flush() will use update queries to update all of the cached values that have been modified. Calling commit() on the SQLHelper will flush all of the caches it owns.

You can set a commit interval on a SQLHelper to automatically commit it every X ticks, using sql.setCommitInterval(X).

You can also call clear() on the cache to clear its cached values, but you should use this sparingly, as it will not save any of the changes that were made but not flushed.

If the name of a team is changed, you'll notice that the players table will automatically update the foreign key. However, the cache cannot detect this change, so you'll need to call a method on the helper to fix this. Before you run the query to update the team name, you can flush and clear the specific cache entry for that team like this:

sql.flushAndRemoveFromMatchingCaches("*.name|team", "red");

The first argument is a pattern to match caches based on the name of the table they're for and their primary keys. The format is tableName.primaryKeyName. You can use * to match all of a specific type, or specify multiple separated with a |. This pattern will match all caches on any table which use a primary key with the name name or team. It will flush, then remove values keyed with the primary key "red". This prevents you from having to flush and clear all your caches every time something like this happens, which could start to defeat the purpose of caching to begin with.