CODEWITHSUNDEEP
javamysqljoinsubqueryjooq
william.eyidi
william.eyidi

Reputation: 2365

Update multiple tables with JOOQ

the problem here with jooq is that it doesn't support join and updates, moreover updates on multiple tables.

I find a way to transform this query:

   String query =   "UPDATE knowCRM.customer_did cd ";
        query +=    " LEFT JOIN knowCRM.know_service ks";
        query +=    " ON ks.id = cd.customer_service_id";
        query +=    " LEFT JOIN knowCRM.customer_flags cf";
        query +=    " ON ks.service_owner_id = cf.account_number";
        query +=    " SET cd.is_cli_number= 1, cf.is_cli_number = '0'";
        query +=    " WHERE ks.service_owner_id = " + accountNumber;
        query +=    " AND cd.did_number= " + cliNumber;

into these two:

        int count2 = wrapper.getCreate().update(CUSTOMER_DID)
                        .set(CUSTOMER_DID.IS_CLI_NUMBER, Byte.parseByte("1"))
                        .where(CUSTOMER_DID.CUSTOMER_SERVICE_ID.equal(
                                    wrapper.getCreate().select(KNOW_SERVICE.ID)
                                    .from(CUSTOMER_FLAGS, KNOW_SERVICE)
                                    .where(KNOW_SERVICE.ID.equal(CUSTOMER_DID.CUSTOMER_SERVICE_ID))
                                    .and(KNOW_SERVICE.SERVICE_OWNER_ID.equal(CUSTOMER_FLAGS.ACCOUNT_NUMBER))
                                    .and(KNOW_SERVICE.SERVICE_OWNER_ID.equal(accountNumber))
                                    .and(CUSTOMER_DID.DID_NUMBER.equal(cliNumber))
                                ))
                        .execute();

and 

        int count3 = wrapper.getCreate().update(CUSTOMER_FLAGS)
                .set(CUSTOMER_FLAGS.IS_CLI_NUMBER, Byte.parseByte("0"))
                .where(CUSTOMER_FLAGS.ACCOUNT_NUMBER.equal(
                            wrapper.getCreate().select(KNOW_SERVICE.SERVICE_OWNER_ID)
                            .from(CUSTOMER_DID, KNOW_SERVICE)
                            .where(KNOW_SERVICE.ID.equal(CUSTOMER_DID.CUSTOMER_SERVICE_ID))
                            .and(KNOW_SERVICE.SERVICE_OWNER_ID.equal(CUSTOMER_FLAGS.ACCOUNT_NUMBER))
                            .and(KNOW_SERVICE.SERVICE_OWNER_ID.equal(accountNumber))
                            .and(CUSTOMER_DID.DID_NUMBER.equal(cliNumber))
                        ))
                .execute();

I would like a more clever way to refactor this query with jooq whithout having to split it into two massive queries.

Upvotes: 1

Views: 3360

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221175

In principle, the JOIN operations are specified on an org.jooq.Table. There's a pending feature request to add "join convenience methods" also to UPDATE, just as they exist also on SELECT: #3266

Your original query can be written as such in jOOQ:

CustomerDid cd = CUSTOMER_DID.as("cd");
KnowService ks = KNOW_SERVICE.as("ks");
CustomerFlags cf = CUSTOMER_FLAGS.as("cf");

ctx.update(cd.leftJoin(kd)
             .on(ks.ID.eq(cd.CUSTOMER_SERVICE_ID))
             .leftJoin(cf)
             .on(ks.SERVICE_OWNER_ID.eq(cf.ACCOUNT_NUMBER)))
   .set(cd.IS_CLI_NUMBER, 1)
   .set(cf.IS_CLI_NUMBER, "0")
   .where(ks.SERVICE_OWNER_ID.eq(accountNumber))
   .and(cd.DID_NUMBER.eq(cliNumber))
   .execute();

Upvotes: 5

Related Questions