CODEWITHSUNDEEP
javasqljooq
Stefan Falk
Stefan Falk

Reputation: 25517

Can this fetch be done more "elegantly"?

I am using jOOQ and I am not quite sure if I am using it the way I am supposed to. I was wondering if my code below can be written more elegantly. By "elegantly" I mean that it is quite annoying to change basically anything regarding the columns. If I add, remove or change the order of the columns I'd have to change all result and Table<> objects etc. I am quite sure that I'll encounter such requests like below a few more times in the future thus I'd like to know if I can simplify this.

In addition, I noticed that the result of on.fetch() and similar is a Result<Record6<..>>. I took a look at those RecordX classes. There are 22 of them. I don't think that I'll ever need that but what if I wanted to read 23 columns?

public List<StoreItemDTO> getItems(Long storeId) {

  // Get all item_ids for the store

  SelectConditionStep<Record1<Long>> where = this.ctx
    .select(STORE_ITEM.ID)
    .from(STORE_ITEM)
    .where(STORE_ITEM.STORE_ID.eq(storeId));

  // Get all store_item_details (all languages) according to the fetched item_ids

  Table<Record5<Long, Long, String, String, Long>> storeItemDetails = this.ctx
    .select(
        STORE_ITEM_DETAILS.ID,
        STORE_ITEM_DETAILS.STORE_ITEM_ID,
        STORE_ITEM_DETAILS.NAME,
        STORE_ITEM_DETAILS.DESCRIPTION,
        STORE_ITEM_DETAILS.STORE_LANGUAGE_ID
        )
    .from(STORE_ITEM_DETAILS)
    .where(STORE_ITEM_DETAILS.STORE_ITEM_ID.in(where))
    .asTable("storeItemDetails");

  // Join the result and get the items for the store in all languages

  SelectOnConditionStep<Record6<Long, Long, Long, String, String, Long>> on = this.ctx
    .select(
        STORE_ITEM.ID, 
        STORE_ITEM.STORE_ID,
        storeItemDetails.field(STORE_ITEM_DETAILS.ID),
        storeItemDetails.field(STORE_ITEM_DETAILS.NAME),
        storeItemDetails.field(STORE_ITEM_DETAILS.DESCRIPTION),
        storeItemDetails.field(STORE_ITEM_DETAILS.STORE_LANGUAGE_ID)          
        )
    .from(STORE_ITEM)
    .join(storeItemDetails)
    .on(storeItemDetails.field(STORE_ITEM_DETAILS.STORE_ITEM_ID).eq(STORE_ITEM.ID));

  Result<Record6<Long, Long, Long, String, String, Long>> fetch = on.fetch();

  // ..

  return null;
}

Upvotes: 1

Views: 248

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221265

If you don't need the type safety, don't use it. E.g. the following works just fine, too

// Get all item_ids for the store
SelectConditionStep<?> where = this.ctx...

// Get all store_item_details (all languages) according to the fetched item_ids
Table<?> storeItemDetails = this.ctx...

// Join the result and get the items for the store in all languages
SelectOnConditionStep<Record6<Long, Long, Long, String, String, Long>> on = this.ctx...

The extended type safety is very nice for the compiler when it can perform type inference, e.g. when you're writing:

  • UNIONs
  • IN predicates
  • When you fetch your results into lambda expressions
  • etc.

You're probably writing all these types down because you're using IDE auto-completion, and your IDE makes the most specific suggestion first. But using wildcards will work just as well, in the above case like this e.g.:

Result<?> fetch = this.ctx 
                     // .. 
                     .fetch();

for(Record record : fetch) {            
    String value = record.getValue(STORE_ITEM_DETAILS.NAME);            
    System.out.println(value);
}

I don't think that I'll ever need that but what if I wanted to read 23 columns?

Nothing significant changes. See also the manual: http://www.jooq.org/doc/latest/manual/sql-execution/fetching/record-n

You can still choose whether to use explicit record typing (no degree on the record):

Result<Record> result = query.fetch();

... or whether you use a wildcard:

Result<?> result = query.fetch();

Upvotes: 2

Related Questions