Easier mapping with SimpleFlatMapper

Question

Can jOOQ automatically add an alias prefix for all columns of a table in the select clause?

Can jOOQ also help with the 128 byte/character name length limitation of databases?

The reason for the questions are that SimpleFlatMapper is used for the mapping. SimpleFlatMapper requires the fetched database column names to map to the model.

Model example (really getter/setter are used):

class Head {
    public Integer id;
    public List<Position> positions;
    ...
}

class Position {
    public Integer id;
    public Integer headId;
    ...
}

The naming can be done individually:

ResultSet resultSet = dsl.select(..., POSITION.ID.as("positions_id"), ...)
    .from(HEAD)
    .join(POSITION.as("positions")).onKey()
    .fetchResultSet();
    
List<Head> headers = JdbcMapperFactory.newInstance()
                                      .ignorePropertyNotFound()
                                      .newMapper(Head.class)
                                      .stream(resultSet)
                                      .collect(Collectors.toList())

However if it is a complicated model with several joins/columns it is a bit tedious. The only solution I found was to program a function manually. Have I however maybe overlooked something and jOOQ can do it by itself or help?

Something the likes of this would be very nice (probably not the best naming, I couldn't come up with something better on the spot):

ResultSet resultSet = dsl.select()
    .from(HEAD)
    .join(POSITION.as("positions").prefixColumns()).onKey()
    .fetchResultSet();

Or:

ResultSet resultSet = dsl.selectWithTableAlias()
    .from(HEAD)
    .join(POSITION.as("positions")).onKey()
    .fetchResultSet();

Resulting in the following SQL:

SELECT head.id, head.***, positions.id AS positions_id, positions.headid AS positions_headid, positions.***
FROM head JOIN position AS positions ON head.id = positions.headid

Furthermore databases like Oracle and MSSQL have a limitation of 128 bytes/characters for names. In very rare complex scenarios the alias names might reach that limit because of the needed nesting.

jOOQ cannot offer a workaround for this in some form or can it? So basically define a name that is used in the SQL and a name for the actual resulting object.

I know, very niche requirements. But they would help a lot with the mapping.

Lukas Eder · Accepted Answer · 2021-03-01T08:04:01.603

Can jOOQ automatically add an alias prefix for all columns of a table in the select clause?

There's no such feature, because the possible sets of desired auto-prefix algorithms is quite big, and jOOQ ultimately wouldn't do exactly what you want. Maybe, there's room for an SPI to help you do this, in the future. But there isn't one available yet: https://github.com/jOOQ/jOOQ/issues/11545

However, you can easily do this yourself, because every jOOQ query is just a dynamically constructed expression tree, even if you're not always using jOOQ for dynamic SQL.

You can easily write a utility and use that everywhere:

public static List<Field<?>> autoPrefix(Field<?>... fields) {
    return autoprefix(Arrays.asList(fields));
}

public static List<Field<?>> autoPrefix(Field<?>... fields) {
    Stream.of(fields).map(f -> f.as(myPrefixLogic(f))).collect(toList());
}

This can now be used explicitly on all DSLContext.select() calls, e.g.

dsl.select(autoPrefix(..., POSITION.ID.as("positions_id"), ...))
    .from(HEAD)
    .join(POSITION.as("positions")).onKey()
    .fetchResultSet();

Alternatively, you can wrap your query in a derived table:

public ResultSet fetchResultSetWithPrefix(Select<?> select) {
    Table<?> table = select.asTable("t");

    return
    dsl.select(autoPrefix(table.fields()))
       .from(table)
       .fetchResultSet();
}

And now, instead of calling fetchResultSet(), you call your auxiliary function:

try (ResultSet rs = fetchResultSetWithPrefix(
    dsl.select(..., POSITION.ID.as("positions_id"), ...)
       .from(HEAD)
       .join(POSITION.as("positions")).onKey()
)) {
    ...
}

Another, more complex option would be to do this with a VisitListener.

Remember, irrespective of what your query looks like (because jOOQ's DSL mimicks SQL, syntactically), all your jOOQ queries are dynamic SQL queries, so you can relatively easily transform them to whatever you want, automatically.

Can jOOQ also help with the 128 byte/character name length limitation of databases?

I'll have the same reservations about this automatism as before. Where would you see jOOQ provide automatic help here? Given your naming scheme (table_column) would you like a fair distribution of 63 characters per object type? Or is the table less important than the column, and you'll truncate table names at 42 characters, leaving 85 characters for the column?

What if you wanted to fully qualify identifiers, including the schema name, as in schema_table_column? What if you project UDTs, meaning you'll get schema_table_column_attribute1_attribute2

I don't think any automation can be provided by jOOQ which would suit all needs. However, as shown above, it is very simple to implement this only once for your entire application, making sure the naming is always applied correctly. And if an SPI were available, you could implement your abbreviation logic there.

Easier mapping with SimpleFlatMapper

1 Answers1