CODEWITHSUNDEEP
javajooq
Jose Martinez
Jose Martinez

Reputation: 11992

fields in JOOQ record not lining up when fetchInto

This is a weird problem that keeps happening across versions 3.10 and 3.11 of JOOQ (didn't try earlier versions). I fetch a record using fetchInto, and when I print the record (or have debug on) the fields all print correctly. That is to say the value for field_x is correct.

The problem is that when I go to retrieve field_x using the record's getX method, it returns the value from another field.

Below is some of the code we used.

        return jooqDslContext.select(CL_USERS.fields())
            .from(CL_USERS)
            .join(THIRDPARTY_USER_XREF)
            .on(THIRDPARTY_USER_XREF.USER_ID.eq(CL_USERS.CL_USER_ID))
            .join(THIRDPARTY_USER_ID)
            .on(THIRDPARTY_USER_ID.ID.eq(THIRDPARTY_USER_XREF.THIRDPARTY_ID))
            .where(THIRDPARTY_USER_ID.EID.eq(eid))
            .fetchOptionalInto(CL_USERS);

Here is the print of the record....

+--------------------------------------------------+----------+------+-----------+------------------+------------+----------------+------------------------+ etc... 
|remember_token                                    |cl_user_id|ria_id|ria_user_id|ria_user_id_backup|cl_user_type|user_description|first_name              | etc...
+--------------------------------------------------+----------+------+-----------+------------------+------------+----------------+------------------------+ etc...
|xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx...| 777777777|   777|      77777|            {null}|      {null}|{null}          |xxxxxxxxxxxxxxxxxxxxxxxx| etc...
+--------------------------------------------------+----------+------+-----------+------------------+------------+----------------+------------------------

notice the first_name is populated. but when i print the first_name using the getFirstName method I get NULL.

cur.getFirstName() = null

From the looks of it seems that there is a mismatch between the index number of the field.... the generated code thinks firstName is field 6, in this case, but in reality it is field 8 (the 8th field in the toString() output.

    public byte[] getFirstName() {
        return (byte[]) get(6);
    }

EDIT: Adding more info.

How can this be reproduced?

Here is another example using a different code base from the example above. In this case I set the amount field on the record object. But when I go to save it or print it out, the address field is set instead.

    TransactionsRecord tr = serviceDslContext.newRecord(Transactions.TRANSACTIONS);
    tr.setAmount(new BigDecimal(10));
    logger.debug("tr = {}", tr);

prints..

+------+-------+-------+...
|amount|account|address|...
+------+-------+-------+...
|{null}|{null} |*10    |...
+------+-------+-------+...

Link to the JOOQ generated code.

Can you post the DDL of the table? 

CREATE TABLE `transactions` (
    `account` VARCHAR(50) NOT NULL,
    `address` VARCHAR(50) NOT NULL,
    `amount` DECIMAL(42,10) NOT NULL,
    `bip125-replaceable` VARCHAR(64) NOT NULL,
    `blockhash` VARCHAR(64) NOT NULL,
    `blockindex` INT(11) NOT NULL,
    `blocktime` TIMESTAMP NULL DEFAULT NULL,
    `category` VARCHAR(50) NOT NULL,
    `confirmation` INT(11) NOT NULL,
    `generated` TINYINT(1) NOT NULL,
    `instantlock` TINYINT(4) NOT NULL,
    `involvesWatchonly` TINYINT(4) NOT NULL,
    `label` VARCHAR(50) NOT NULL,
    `time` TIMESTAMP NULL DEFAULT NULL,
    `timereceived` TIMESTAMP NULL DEFAULT NULL,
    `txid` VARCHAR(64) NOT NULL,
    `vout` INT(11) NOT NULL,
    `walletconflicts` TEXT NULL,
    `amount_in_usd` DECIMAL(42,10) NULL DEFAULT NULL,
    `usd_exchange_rate` DECIMAL(42,10) NULL DEFAULT NULL,
    PRIMARY KEY (`txid`),
    INDEX `account_generated` (`account`, `generated`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

What database product and version are you using?

In the above case I am using MySQL 5.7.23 on AWS RDS. For the first example it was a variant of MySQL 5.6 on AWS RDS. Also, for the latter example, it has occurred on 5.7.14-google, we migrated recently to AWS.

Upvotes: 2

Views: 916

Answers (2)

Jose Martinez
Jose Martinez

Reputation: 11992

After doing some testing it looks like the Rearrange code feature in IntelliJ is messing up the JOOQ generated code. This can have dire consequences for code running in production. The Rearrange code feature is displayed as an option in the Commit Changes window.

enter image description here

The testing consisted of running a test before and after the Rearrange code feature. Below is the code.

    TransactionsRecord tr = serviceDslContext.newRecord(TRANSACTIONS);
    tr.setAmount(new BigDecimal(10));
    logger.debug("tr = {}", tr);

If this code is executed before the Rearrange code feature, that is right after JOOQ has generated the code, then the following is displayed...

 +-------+-------+------+...
|account|address|amount|...
+-------+-------+------+...
|{null} |{null} |   *10|...
+-------+-------+------+...

After IntelliJ executes Rearrange codeon the JOOQ generated code, this is the output...

+------+-------+-------+...
|amount|account|address|...
+------+-------+-------+...
|{null}|{null} |*10    |...
+------+-------+-------+...

It seems like the values stay in the right spot, but the fields that those values represent have shifted around.

Upvotes: 2

Lukas Eder
Lukas Eder

Reputation: 220842

It looks like the generated record you've posted was modified manually or using some post processors, or it was generated by a custom generator. Clearly, the generated table (correct order):

public class Transactions extends TableImpl<TransactionsRecord> {

    ...

    public final TableField<TransactionsRecord, BigDecimal> AMOUNT = 
      createField("amount", org.jooq.impl.SQLDataType.DECIMAL(42, 10).nullable(false), this, "");

    public final TableField<TransactionsRecord, String> ACCOUNT = 
      createField("account", org.jooq.impl.SQLDataType.VARCHAR(50).nullable(false), this, "");

    public final TableField<TransactionsRecord, String> ADDRESS = 
      createField("address", org.jooq.impl.SQLDataType.VARCHAR(50).nullable(false), this, "");

    ...

... and the generated record:

public class TransactionsRecord extends UpdatableRecordImpl<TransactionsRecord> 
    implements Record20<...> {

    public void setAccount(String value) {
        set(0, value);
    }

    public String getAccount() {
        return (String) get(0);
    }

    public void setAddress(String value) {
        set(1, value);
    }

    public TransactionsRecord() {
        super(Transactions.TRANSACTIONS);
    }

    public TransactionsRecord(String account, String address, BigDecimal amount, ...) {
        super(Transactions.TRANSACTIONS);

        set(0, account);
        set(1, address);
        set(2, amount);
        ...
    }

    public BigDecimal getAmount() {
        return (BigDecimal) get(2);
    }

    public String getAddress() {
        return (String) get(1);
    }

    public void setAmount(BigDecimal value) {
        set(2, value);
    }

    public void setBlockhash(String value) {
        set(4, value);
    }

    ...

... don't match. Look at how there are getters and setters before and after the constructor. Also, they are not in order! The official JavaGenerator provided by jOOQ wouldn't generate such a class.

Notice, if you do want to modify generated code, you should either override JavaGenerator or implement your own generator class.

Upvotes: 1

Related Questions