CODEWITHSUNDEEP
javamysqlsqljooq
Fisher Coder
Fisher Coder

Reputation: 3576

Translate SQL query into JOOQ

I have a SQL query like this:

select 
  e.event_type as eventType, 
  e.app_name as appName, 
  e.from_number as appNumber, 
  e.timestamp as messageTime, 
  b.created_at as blockTime 
from event_log e use index (idx_event_type_timestamp) 
  join blocked_phone_numbers b 
    on b.app_name = e.app_name
    and b.number = e.to_number 
where e.event_type = 1 
  and e.timestamp > 2018-09-08
  and b.created_at < e.timestamp 
limit 10;

and I need to refactor it into a JOOQ query, here's where I reached:

SelectLimitPercentStep<Record> selectLimitPercentStep = context
                .select(EVENT_LOG.EVENT_TYPE, EVENT_LOG.APP_NAME, EVENT_LOG.FROM_NUMBER, EVENT_LOG.TIMESTAMP)
                .select(BLOCKED_PHONE_NUMBERS.CREATED_AT)
                //.use_index//todo: use index
                .from(EVENT_LOG)
                .join(BLOCKED_PHONE_NUMBERS)
                .on(EVENT_LOG.APP_NAME.eq(EVENT_LOG.APP_NAME).and(EVENT_LOG.TO_NUMBER.eq(BLOCKED_PHONE_NUMBERS.NUMBER)))
                .where(EVENT_LOG.EVENT_TYPE.eq((byte) EventType.OUTBOUND_SENT.ordinal())
                    .and(EVENT_LOG.TIMESTAMP.gt(new Timestamp(earliestTime.getMillis())))
                    .and(BLOCKED_PHONE_NUMBERS.CREATED_AT.lt(EVENT_LOG.TIMESTAMP)))
                .limit(1000);

But I didn't find any methods to translate this part use index (idx_event_type_timestamp) into JOOQ, anyone could shed any light please?

Upvotes: 2

Views: 240

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221195

jOOQ has a Table.useIndex() method, just use it.

EVENT_LOG.useIndex("idx_event_type_timestamp")

Upvotes: 2

Related Questions