CODEWITHSUNDEEP
javamysqljooq
yaswanth
yaswanth

Reputation: 2477

Using a java method in select query jooq

I have a mysql query which is in the following format

        dslContext
            .select(
                ITEMDATA.ITEMID,
                ITEMDATA.COST,
                ITEMNAMES.ITEMNAME
            )
            .from(ITEMDATA)
            .join(ITEMNAMES)
            .on(ITEMDATA.ITEMID=ITEMNAMES.ITEMID)
            .where(conditions);

The above query joins ITEMDATA with ITEMNAMES table to select ITEMNAME in the result. I am caching ITEMNAMES table in-memory and want to avoid the join with ITEMNAMES table. This would speed up the query and would simplify the query since the actual query is much more complex.

I would like to use it something similar to the following. I want to call itemNamesCache.getItemName in the select params list which gives the ITEMNAME and returns a part of the select result. getItemName should take the ITEMID returned in the response as a parameter and give the ITEMNAME.

    dslContext.
        select(
            ITEMDATA.ITEMID,
            ITEMDATA.COST,
            itemNamesCache.getItemName(valueOfItemId)
        )
        .from(ITEMDATA)
        .where(conditions);

P.S: I can iterate the results and call the itemNamesCache.getItemName. But I would like to use something embedded in the query if it's possible

Upvotes: 2

Views: 1344

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

You cannot have a callback from a SQL query back into some Java logic, even if the fact that you're constructing the SQL query with jOOQ (and thus Java) makes it look like that were feasible.

However, you could post-process the jOOQ result by patching records using a previously built cache:

A Java solution

In case you're working with a database that really can't handle this simple join (and you've checked that you have all proper indexes and constraints in place!) then you could try the following solution:

// Assuming this import:
import static org.jooq.impl.DSL.*;

write...

Map<Integer, String> itemNamesCache =
dslContext.selectDistinct(ITEMNAMES.ITEMID, ITEMNAMES.NAME)
          .from(ITEMNAMES)
          .fetchMap(ITEMNAMES.ITEMID, ITEMNAMES.NAME);

dslContext
    .select(
        ITEMDATA.ITEMID,
        ITEMDATA.COST,
        // create an empty column here
        inline(null, String.class).as(ITEMNAMES.NAME))
    .from(ITEMDATA)
    .where(conditions)
    // fill the empty column with cached values
    .fetch(r -> r.value3(itemNamesCache.get(r.value1())));

A SQL-based solution

The SQL way to do that would be to write a correlated subquery.

SELECT
  itemdata.itemid,
  itemdata.cost,
  (SELECT itemnames.name FROM itemnames WHERE itemnames.itemid = itemdata.itemid)
FROM 
  itemdata
WHERE
  ...

With jOOQ

// Assuming this import:
import static org.jooq.impl.DSL.*;

... write:

dslContext
    .select(
        ITEMDATA.ITEMID,
        ITEMDATA.COST,
        field(select(ITEMNAMES.NAME)
           .from(ITEMNAMES)
           .where(ITEMDATA.ITEMID.eq(ITEMNAMES.ITEMID)))
        .as(ITEMNAMES.NAME)
    )
    .from(ITEMDATA)
    .where(conditions)
    .fetch();

In theory, both queries should run at exactly the same speed, because they're equivalent (if you have a foreign key on ITEMDATA.ITEMID).

In practice, most databases will probably have better performance for the JOIN query, unless they implement scalar subquery caching (e.g. like Oracle), which can drastically speed up the second query, depending on the number of distinct ITEMIDs (the smaller, the better).

Upvotes: 1

Related Questions