How to Optimize the SQL Query in Jooq to return Table Object

Question

I am using below code run a query with Join

@Transactional
public Result<Record> getFolderFeeList(int argFolderRSN, String argOrderBy) {

    Transaction transaction = Transaction.current();
    // List<ValidAccountFeeRow> validSiteOptionList = transaction.daos().getValidAccountFeeDao().findAll();

    SelectQuery<Record> selectQuery = transaction.selectQuery();
    selectQuery.addSelect(
            Routines.fFoldername(argFolderRSN).as("FolderName"),
            AccountBillFee.ACCOUNT_BILL_FEE.FOLDER_RSN,
            AccountBillFee.ACCOUNT_BILL_FEE.PARENT_ACCOUNT_BILL_FEE_RSN,
            AccountBillFee.ACCOUNT_BILL_FEE.FEE_CODE,
            AccountBillFee.ACCOUNT_BILL_FEE.PROCESS_RSN,
            AccountBillFee.ACCOUNT_BILL_FEE.SECURITY_CODE,
            AccountBillFee.ACCOUNT_BILL_FEE.FEE_AMOUNT,
            Routines.fGetpaidinfullflag(AccountBillFee.ACCOUNT_BILL_FEE.BILL_NUMBER, AccountBill.ACCOUNT_BILL.PAYMENT_OPTION,
                    AccountBillFee.ACCOUNT_BILL_FEE.FEE_LEFT.cast(Double.class), AccountBill.ACCOUNT_BILL.PAID_IN_FULL_FLAG.cast(String.class))
                    .as("PaidInFullFlag"), AccountBillFee.ACCOUNT_BILL_FEE.MANDATORY_FLAG, AccountBill.ACCOUNT_BILL.DUE_DATE,
            AccountBillFee.ACCOUNT_BILL_FEE.BILL_NUMBER, AccountBillFee.ACCOUNT_BILL_FEE.ACCOUNT_BILL_FEE_RSN,
            AccountBillFee.ACCOUNT_BILL_FEE.FEE_COMMENT);
    selectQuery.addFrom(AccountBillFee.ACCOUNT_BILL_FEE);
    selectQuery.addJoin(AccountBill.ACCOUNT_BILL, JoinType.LEFT_OUTER_JOIN,
            AccountBill.ACCOUNT_BILL.BILL_NUMBER.eq(AccountBillFee.ACCOUNT_BILL_FEE.BILL_NUMBER));
    selectQuery.addJoin(ValidAccountFee.VALID_ACCOUNT_FEE,
            AccountBillFee.ACCOUNT_BILL_FEE.FEE_CODE.eq(ValidAccountFee.VALID_ACCOUNT_FEE.FEE_CODE));
    selectQuery.addConditions(AccountBillFee.ACCOUNT_BILL_FEE.FOLDER_RSN.eq(argFolderRSN));
    if (argOrderBy == null) {
        selectQuery.addOrderBy(AccountBillFee.ACCOUNT_BILL_FEE.FOLDER_RSN.asc(), AccountBillFee.ACCOUNT_BILL_FEE.BILL_NUMBER.asc(),
                AccountBillFee.ACCOUNT_BILL_FEE.FEE_CODE.asc());
    } else {
        // To Do selectQuery.addOrderBy(argOrderBy);
    }
    Result<Record> result = selectQuery.fetch();
    return result;

}

Now is it pssible somehow this query will return me AccountBillFee table whole object as well as functions values? As i saw in JOOQ if i am creating a object by my self its always inserting a new record rather than updating it.

Lukas Eder · Accepted Answer · 2013-12-15 19:44:17Z

2

You can add all of AccountBillFee's columns to your SELECT statement in one go:

selectQuery.addSelect(AccountBillFee.fields());

Since you want to store the fetched (enriched) AccountBillFeeRecord record again to the database, I suggest you try something like:

// This is the "weakly typed", generic record with the additional columns
Record record =
DSL.using(configuration)
   .select(...)
   .from(ACCOUNT_BILL_FEE)
   .fetchOne();

// This is one way to transform the above record into a
// "table-typed" record that you can store
AccountBillFeeRecord r1 = record.into(ACCOUNT_BILL_FEE);
r1.update();

edited Dec 15, 2013 at 19:44

answered Dec 13, 2013 at 13:39

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Subodh Joshi Over a year ago

So it wiil return Object of AccountBillFee? or ever column independently?

Subodh Joshi Over a year ago

So it Will return Object of AccountBillFee? or every column independently? If you will check i want a Whole object of this table so that i can work with dirtychecking and update which is very complex in current approach. Secodnt his if you will check my code SelectQuery<Record> selectQuery = transaction.selectQuery(); i Used SelectQuery Object and it is not allowing AccountBillFee.ACCOUNT_BILL_FEE.fields().

Lukas Eder Over a year ago

There is currently no way to add additional columns to a "table-typed" AccountBillFeeRecord in order to be able to store it again. Of course, you can create a new AccountBillFeeRecord and update that with the values from your ad-hoc record

Subodh Joshi Over a year ago

So we have to use three different query to get the table record

Lukas Eder Over a year ago

No, I think you didn't understand. You can fetch the record the way you did, and store it explicitly with an update, or with a new AccountBillFeeRecord

|

Sitansu · Accepted Answer · 2013-12-19 17:02:48Z

Thanks to @Lukas here is Solution which i was looking

     @Transactional
    public Result<Record> getFolderFeeList(int argFolderRSN, String argOrderBy) {

   Transaction transaction = Transaction.current();
 // List<ValidAccountFeeRow> validSiteOptionList     transaction.daos().getValidAccountFeeDao().findAll();

  SelectQuery<Record> selectQuery = transaction.selectQuery();
  selectQuery.addSelect(Routines.fFoldername(argFolderRSN).as("FolderName"),
                    Routines.fGetpaidinfullflag(AccountBillFee.ACCOUNT_BILL_FEE.BILL_NUMBER, AccountBill.ACCOUNT_BILL.PAYMENT_OPTION,
                            AccountBillFee.ACCOUNT_BILL_FEE.FEE_LEFT.cast(Double.class), AccountBill.ACCOUNT_BILL.PAID_IN_FULL_FLAG.cast(String.class))
                            .as("PaidInFullFlag"),AccountBill.ACCOUNT_BILL.DUE_DATE);
 selectQuery.addSelect(AccountBillFee.ACCOUNT_BILL_FEE.fields());
selectQuery.addFrom(AccountBillFee.ACCOUNT_BILL_FEE);
selectQuery.addJoin(AccountBill.ACCOUNT_BILL, JoinType.LEFT_OUTER_JOIN,
                    AccountBill.ACCOUNT_BILL.BILL_NUMBER.eq(AccountBillFee.ACCOUNT_BILL_FEE.BILL_NUMBER));
 selectQuery.addJoin(ValidAccountFee.VALID_ACCOUNT_FEE,
   AccountBillFee.ACCOUNT_BILL_FEE.FEE_CODE.eq(ValidAccountFee.VALID_ACCOUNT_FEE.FEE_CODE));
            selectQuery.addConditions(AccountBillFee.ACCOUNT_BILL_FEE.FOLDER_RSN.eq(argFolderRSN));
            if (argOrderBy == null) {
                selectQuery.addOrderBy(AccountBillFee.ACCOUNT_BILL_FEE.FOLDER_RSN.asc(), AccountBillFee.ACCOUNT_BILL_FEE.BILL_NUMBER.asc(),
                        AccountBillFee.ACCOUNT_BILL_FEE.FEE_CODE.asc());
            } else {
                // To Do selectQuery.addOrderBy(argOrderBy);
            }
            Result<Record> result = selectQuery.fetch();
            for(Record record : result){
            AccountBillFeeRecord r1 = record.into(AccountBillFee.ACCOUNT_BILL_FEE);
            System.out.println(record.getValue("FolderName"));
            }
            return result;

        }

Collectives™ on Stack Overflow

How to Optimize the SQL Query in Jooq to return Table Object

2 Answers 2

13 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related