Как оптимизировать SQL-запрос в Jooq для возврата табличного объекта

Я использую код ниже, чтобы выполнить запрос с Присоединиться

@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;

}

Теперь возможно, как-то этот запрос вернет меня AccountBillFee таблица весь объект, а также значения функций? Как я видел в JOOQ, если я сам создаю объект, он всегда вставляет новую запись, а не обновляет ее.

Источник

2 ответа

Решение

Вы можете добавить все AccountBillFeeколонны к вашему SELECT утверждение за один раз:

selectQuery.addSelect(AccountBillFee.fields());

Так как вы хотите хранить извлеченный (обогащенный) AccountBillFeeRecord запись снова в базу данных, я предлагаю вам попробовать что-то вроде:

// 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();
Источник

Благодаря @Lukas вот решение, которое я искал

     @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;

        }
Источник
Другие вопросы по тегам