How To Update Existing Records While BatchInsert Using Jooq

[Debapriya Patra]

Hi Lukas,

I am designing a DB where I have the columns below.

There is a nightly build which is basically pulling data from another system and entering records to this DB. I am doing a batch insert of records. 

public boolean createTopicPages(List<TopicPage> topicPages) {
    List<TopicPageRecord> topicPageRecords = new ArrayList<>(topicPages.size());
    topicPages.stream().forEach(topicPage -> {
        TopicPageRecord topicPageRecord = new TopicPageRecord();
        topicPageRecord.setTaxonomyId(topicPage.getTaxonomyId());
        topicPageRecord.setCmsName(topicPage.getCmsName());
        topicPageRecord.setSubject(topicPage.getSubject());
        topicPageRecord.setClassification(topicPage.getClassification());
        topicPageRecord.setEntryName(topicPage.getEntryName());
        topicPageRecord.setStatus(topicPage.getStatus());
        topicPageRecord.setEntryUpdated(topicPage.getEntryUpdated());
        topicPageRecord.setEntryPublished(topicPage.getEntryPublished());
        topicPageRecords.add(topicPageRecord);
    });
    int[] execute = dsl.batchInsert(topicPageRecords).execute();
    return execute.length > 0;
}

Now the problem is, when every time pull records and trying to insert into the DB , there will be exiting entries already in the DB. I wanted to either ignore or update the existing records. 

If there is a change to either entry_updated OR status  for any of the existing records in DB then I wanted to update that record with the new status and entry_updated value. Otherwise ignore those existing records and just insert the new ones.

In my DB cms_name, taxonomy_id & subject are unique columns. 

Can you please let me know how will I solve this problem using Jooq query ? I was reading lot of blogs where people are talking about upsert solutions which you have provided but in my case its a batch insert I am doing so in that case how Jooq will help me here.

NOTE: I use Postgres DB for my application.

Thanks,

Deba

[Lukas Eder]

Hi Deba,

You might be interested in our loader API. The CSV example shows how you can do what you want to achieve:

https://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-csv/

So, just specify the onDuplicateKeyUpdate() flag, which checks all your unique constraints for constraint validation errors, and then updates the row instead of inserting it. 

It supports importing records:

https://www.jooq.org/doc/latest/manual/sql-execution/importing/importing-records/ 

Hope this helps

Lukas

 

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/126dbb7e-4108-4c69-b6b1-ffab2006682e%40googlegroups.com.

[Debapriya Patra]

Hi Lukas,

I was more interested on how I can update an existing record while inserting in a batch. I tried the way you have asked but I am getting an error now.

    public boolean createSubjectPages(List<SubjectPage> subjectPages) {
        List<SubjectPageRecord> subjectPageRecords = new ArrayList<>(subjectPages.size());
        subjectPages.stream().forEach(topicPage -> {
            SubjectPageRecord subjectPageRecord = new SubjectPageRecord();
            subjectPageRecord.setTaxonomyId(topicPage.getTaxonomyId());
            subjectPageRecord.setCmsName(topicPage.getCmsName());
            subjectPageRecord.setSubject(topicPage.getSubject());
            subjectPageRecord.setStatus(topicPage.getStatus());
            subjectPageRecord.setEntryUpdated(topicPage.getEntryUpdated());
            subjectPageRecord.setEntryPublished(topicPage.getEntryPublished());
            subjectPageRecords.add(subjectPageRecord);
        });

        try {
            Loader<SubjectPageRecord> result = dsl.loadInto(SUBJECT_PAGE)
                    .onDuplicateKeyUpdate()
                    .loadRecords(subjectPageRecords)
                    .fields(SUBJECT_PAGE.TAXONOMY_ID, SUBJECT_PAGE.CMS_NAME, SUBJECT_PAGE.SUBJECT,
                            SUBJECT_PAGE.STATUS, SUBJECT_PAGE.ENTRY_UPDATED, SUBJECT_PAGE.ENTRY_PUBLISHED,
                            SUBJECT_PAGE.CREATED, SUBJECT_PAGE.UPDATED)
                    .execute();
        }
        catch (IOException e) {
            log.error("Error while saving data. {}", e);
        }

        return true;
    }

03-01-2020 18:50:36.530 | http-nio-8080-exec-1 | requestId:6b309d68-b0e9-49fc-8a17-2edf9faeff3c | ERROR | GraphQLErrorHandler - ON DUPLICATE KEY UPDATE only works on tables with explicit primary keys. Table is not updatable : "public"."subject_page"
java.lang.IllegalStateException: ON DUPLICATE KEY UPDATE only works on tables with explicit primary keys. Table is not updatable : "public"."subject_page"
at org.jooq.impl.LoaderImpl.onDuplicateKeyUpdate(LoaderImpl.java:207)
at org.jooq.impl.LoaderImpl.onDuplicateKeyUpdate(LoaderImpl.java:100)

Basically we provide the public schema, which holds the VIEWs for all the tables, for Jooq/flyway configuration. Not sure why its complaining when I use this load API with .onDuplicateKeyUpdate() .

Any help will be greatly appreciated.

Cheers,

Debapriya Patra
650.933.6852

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO69ryP6O4NdkGmSYiSy6anz6uLfyKN3cJ04m-sCSepozQ%40mail.gmail.com.

[Debapriya Patra]

In my case the PrimaryKey is generated by the DB, which is a UUID. So to avoid this error because PrimaryKey will be null and this error is because of that I believe.

Thanks,

Deba

[Lukas Eder]

Hi Deba,

The primary key meta information is required to emulate ON DUPLICATE KEY UPDATE on PostgreSQL.

jOOQ does not (yet) know the underlying primary key(s) of an updatable view. You can tell jOOQ's code generator by specifying "synthetic primary keys", though:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-synthetic-primary-keys/  

This will add primary key information in your generated code, and turn all TableRecords into UpdatableRecords.

I hope this helps,

Lukas

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/5b15619b-8937-43d5-a52d-55d0b4caf651%40googlegroups.com.

[Debapriya Patra]

Hi Lukas,

I am kinda confused and wanted to make sure I understand the whole thing about synthetic primary keys and implement properly.

I am attaching my groovy file which is used int eh gradle file as. Jooq code generator. Also my schema definition file as well. 

Can you please help me understand what changes I have to do here to make it work in my case ?

Cheers,

Debapriya Patra
650.933.6852

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6zGY9HTckY1yPRfCnUupXsnZ61HicgPsOfSumnj%2Bhi1Q%40mail.gmail.com.

[Lukas Eder]

Replace this:

syntheticPrimaryKeys = 'SCHEMA\.TABLE\.COLUMN(1)'

By a meaningful regular expression, such as

syntheticPrimaryKeys = 'public\.subject_page\.id'

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAM2NkrQ9U1oHoXbBgtn7roF%2BBrbLUGedahQtEH01vYgwXJjEaw%40mail.gmail.com.