Is there a way to force jOOQ to only reference columns by name and never by index, and not generate any functions or properties that would rely on index instead of name?
I took a lap through the advanced code generation page on jooq.org and did not immediately see anything. Hoping I just missed it.
Background
I'm starting jOOQ on a new project with an existing production database than has suffered some hand DDL modification, specifically some ALTER TABLE ADD COLUMN baggage. Thus my liquibase table definition does not match the actual production definition in terms of order.
I want to be able to create a dev database from scratch according to schema changes, and not have any risk a developer referencing columns by index, which are invalid.
When I generate code against the dev database, I notice the current generated objects (based on the production database) have numerous diffs around index values and property order. I'm nearly certain this is because the databases, while they have the same column names, do not have them in the same order.
jOOQ internally works with column indexes rather than names for a few simple reasons:
ResultSet, but all of them supporte indexed columnsObject[] by index is much faster in a hot loop than looking up that value in a Map<?, ?>Map<?, ?>? String? org.jooq.Name? There isn't really a good answer to that question. When you look up a field by name, e.g. using Record.field(Name), we will take both the qualified and unqualified name into consideration (qualified matches being better than unqualified ones). This is a really useful feature in some cases, when you want to treat A.ID as the same column as B.ID. But that also means that names tend to be ambiguous. In fact, they are. You can write valid SQL where two columns in a top level SELECT statement are identical by name, and can be distinguished only by index.For these reasons, it would be unwise to base jOOQ records on field names, rather than field indexes. This also translates to the code generator. When projecting SELECT *, the order of columns may be relevant to some use cases, e.g. to the UNION operator. The SQL standard specifies UNION CORRESPONDING, where a union is based on column names of the subqueries, rather than their index. But no RDBMS I'm aware of supports this syntax. You can ORDER BY <column index>. And there are other SQL features that are index based. So, it is important that we reproduce (by default) the order of columns as they are defined in the dictionary, by the code generator.
You can easily override the column order produced by the code generator to something more stable, if you don't rely on the above, using the <orderProvider>
https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-order-provider
It has to implement Comparator. So, for a simple case of ordering things alphabetically, write:
public class AlphabeticOrderProvider implements Comparator<Definition> {
@Override
public int compare(Definition o1, Definition o2) {
return o1.getQualifiedInputName().compareTo(o2.getQualifiedInputName());
}
}
And supply that to your code generation configuration:
<configuration>
<generator>
<database>
<orderProvider>com.example.AlphabeticOrderProvider</orderProvider>
</database>
</generator>
</configuration>
You could also base your code generation directly on your Liquibase migration, which should produce the same column order every time you run it on a fresh schema. Starting with jOOQ 3.13 (due around Q1 2020), we'll support the following LiquibaseDatabase out of the box:
The way this works is jOOQ spins up an H2 in-memory database and runs your entire Liquibase migration on that, instead of on your actual database, with Liquibase taking care of dialect agnosticity. We'll also be able to parse and translate any native SQL migrations that you may have.
This approach obviously assumes you're going to check in generated sources, instead of generating them afresh on each environment.