Temporary Ids with Jooq

Sometimes one wants to construct some database records in memory such that those records form some parent-child relationship or even have a whole graph of dependencies. When programming in Java with an SQL database, I tend to use Jooq[1]. Jooq being very near the entity-relationship model of an SQL database, such dependency relationships must be constructed by assigning the foreign keys of child records or relationship tables. The problem is the allocation of ids for the primary keys of those records. It is possible of course to use a database sequence as the records are created, but that means that the simple in-memory creation of a record requires a database connection. An alternative is to use temporary ids that are created to build the records and to resolve them before storing[2].

Let’s assume that all the ‘entity’ records in the database have technical ids (Long) and that they all share the same global sequence for the generation of ids. Furthermore, the ids produced by the global sequence are positive numbers and so we can use the negative numbers for the temporary ids. We can define the following static class for the allocation of the temporary ids.

public final class TemporaryIds {
    private static final AtomicLong TEMPORARY_IDS = new AtomicLong(-1L);

    public static Long temporaryId() {
        return TEMPORARY_IDS.getAndDecrement();
    }

    public static boolean isTemporary(long id) {
        return id < 0L;
    }
}

Every time a temporary id is required, a call to temporaryId provides a unique temporary id. With these ids, one can construct whole graphs of dependent records.

When it is time to store those records in the database, permanent ids must be fetched from the database sequence and temporary ids must be resolved into permanent ones. To simplify the presentation of the code, we assume that all foreign keys are single column keys. We also assume the three following functions[3].

public Field<Long> identity(Table t) {
    return <the identity field of the table t>;
}

public Field<Long> identity(ForeignKey f) {
    return <the foreign key field of the table t>;
}

public Long id(DSLContext dsl) {
    return <the next id from a global sequence, via database connection>;
}

The algorithm to resolve a collection of records proceeds in 2 phases. In the first phase, we collect the temporary ids, fetch new permanent ids from the database, and store the relationship between the temporary ids and the permanent ids in a Guava BiMap.

DSLContext dsl = <database connection from somewhere>;
Collection<? extends TableRecord> records = <records from somewhere>;
BiMap<Long, Long> idMap = HashBiMap.create();
for(TableRecord record : records) {
    Field<Long> identity = identity(record.getTable());
    Long id = record.getValue(identity);
    if (TemporaryIds.isTemporary(id)) {
        idMap.put(id, id(dsl));
    }
}

In a second phase, we iterate through the records and replace every temporary primary id and temporary foreign id with the permanent ones for the bimap.

void replaceIfTemporary(TableRecord record, Field<Long> field, BiMap<Long, Long> idMap) {
    Long id = record.getValue(field);
    if (TemporaryIds.isTemporary(id)) {
        record.putValue(field, idMap.get(id));
    }
}

Collection<? extends TableRecord> records = <records from somewhere>;
BiMap<Long, Long> idMap = <bimap from above>;
for(TableRecord record : records) {
    Table table = record.getTable();
    Field<Long> identity = identity(table);
    replaceIfTemporary(record, identity, idMap);
    for (ForeignKey key : table.getReferences()) {
        Field<Long> fkIdentity = identity(key);
        replaceIfTemporary(record, fkIdentity, idMap);
    } 
}

The records are now ready to be sorted in insert order and batch stored in the database. The bimap can be used to reverse the resolving from permanent ids to the temporary ids if an exception occurs during storage and one needs to recover the state before the attempt to store on the database.

Footnotes

[1]: Jooq is a very good libary to program with SQL Database with Java and staying near the entity-relationship model and SQL itself.

[2]: The idea to have temporary ids with Jooq comes from the fantastic Datomic database that uses the same concept (look at tempid).

[3]: For performance reasons, one should avoid connecting to the database for every permanent id. For instance, you can allocate batches of ids from the global sequence and cache them.