In Jooq, how to fetch into LinkedHashMap?

Question

I know that to fetch into Map I can use fetchMap, but it doesn't support LinkedHashMap, where I want to maintain the insertion order created by orderBy.

I tried

.
.
.
.orderBy(...)
.collect(
    Collectors.toMap(
        record -> record.field1(), 
        record -> record.field2(), 
        LinkedHashMap.class));

but the field1 and field2 parts gave me the following error.

Cannot resolve method 'field1' in 'T'

Hope someone can help me~

Thanks!

Solution:

Based on @Lukas' answer, I tried several times and found the following points must be done:

1. In the select method, all fields must have a type, except those calculated, where adding a type will throw field not found in field list error.

2. To add a type to a field, you have to use DSL.field(fieldname, type) method instead of DSL.field(fieldname).cast(type). The latter will throw field not found in field list error.

3. In collect method's collector object, when getting each field of a record, they must have a type as well. Like

record -> record.get(DSL.field("fieldname", Float.class)),

The complete query will be something like:

Table<Record2<Long, Integer>> table1 = ...;
Table<Record2<Long, Integer>> table2 = ...;
.
.
.
return DSL.
        .select(table1.field("table1_field1", Long.class),
                DSL.ifnull(
                        table1.field("table1_field2", Float.class)
                                .div(table2.field("table2_field1", Float.class)),
                        0)
                        .as("result_table_field1"))
        .from(table1)
        .join(table2)
            .on(table1.field("table1_field1", Long.class)
                    .eq(table2.field("table2_field2", Long.class)))
        .orderBy(DSL.field("result_table_field1"))
        .collect(Collectors.toMap(
                record -> record.get(table1.field("table1_field1", Long.class)),
                record -> record.get(DSL.field("result_table_field1", Float.class)),
                (v1, v2) -> { throw new InvalidResultException("Duplicate key: " + v1); },
                LinkedHashMap::new
        ));

Answer 1

Both of these approaches are assuming you're using the jOOQ code generator

fetchMap() already returns a LinkedHashMap

Check out the fetchMap() Javadoc. It says:

The resulting map is iteration order preserving.

It's effectively a LinkedHashMap, though it doesn't guarantee this exact type, so you'd have to downcast at your own risk.

Using collect()

If you write a query that produces a Record2 type, then you can use a type safe collector like this:

LinkedHashMap<T1, T2> map =
ctx.select(T.COL1, T.COL2)
   .from(T)
   .orderBy(...)
   .collect(Collectors.toMap(
       Record2::value1,
       Record2::value2,
       (v1, v2) -> { throw new InvalidResultException("Duplicate key: " + v1); },
       LinkedHashMap::new
   ));

The reason your attempt didn't compile was:

1. You were missing the mandatory mergeFunction argument of toMap()
2. You should be using value1() and value2(), instead of field1(), field2(), but that only works if you have a type safe Record2 in your result set
3. If you don't have Record2, but Record instead, then you can work without type safety:

LinkedHashMap<T1, T2> map =
ctx.select(...)
   .from(T)
   .orderBy(...)
   .collect(Collectors.toMap(
       r -> r.get(T.COL1),
       r -> r.get(T.COL2),
       (v1, v2) -> { throw new InvalidResultException("Duplicate key: " + v1); },
       LinkedHashMap::new
   ));