javasqlitejooq

jOOQ 3.19: Force Record column type for columns matching name format when performing .fetch()

I'm using jOOQ version 3.19 (unable to upgrade to 3.20 as currently on Java 17) and not the codegen library, just the plain org.jooq lib.

I'm reading from a SQLite database which has some Integer fields holding values bigger than 32 bits. When I perform my DSLContext.fetch() using plain SQL, the record which comes back is putting my SQLite Integers into a Java int which only holds 32 bits so I'm getting the wrong value back. I'd like it to put those values into a long instead - ideally I would be able to provide a column name format and for any matching [SQLite]Integer columns, the value would be returned as a long.

How can I configure jOOQ to do this? I don't think I can use the forcedType because I think that's for jOOQ codegen only. I've tried having a play around with a RecordMapper but I think that happens after the value has already been converted into a Record and I've also looked at Converter, Binding and MetaProvider but I couldn't figure it out using any of those / or they didn't seem correct for what I'm trying to do. Please keep in mind I'm using version 3.19 as I believe there are additions in 3.20 and 3.21 which would have made this easier.

Appreciate any help or guidance! Thank you!

Solution

  • If you're creating fields from identifiers manually, you can pass any data type to it, explicitly:

    Field<Long> field = field(name("my_field"), SQLDataType.BIGINT);

    If you're just executing a native SQL query, you can avoid jOOQ's auto-discovering field types from JDBC DatabaseMetaData by using ResultQuery.coerce(), e.g.

    Field<Long> field1 = field(name("field1"), SQLDataType.BIGINT);
Field<String> field2 = field(name("field2"), SQLDataType.VARCHAR);

Result<Record2<Long, String>> result = ctx
    .resultQuery(
        """
        SELECT field1, field2
        FROM my_table
        """
    )
    .coerce(field1, field2)
    .fetch();

    As of jOOQ 3.20, it's currently not possible to override the data type registry of the internal result metadata lookup, see:

    You could, however, proxy your JDBC driver and create a proxy for your ResultSet and ResultSetMetaData to achieve this without jOOQ knowing.