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
On Fri, Feb 28, 2020 at 5:58 AM Lukas Eder <[email protected]> wrote:
> 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
>
>
> On Thu, Feb 27, 2020 at 9:43 PM Debapriya Patra <[email protected]>
> wrote:
>
>> Hi Lukas,
>>
>> I am designing a DB where I have the columns below.
>>
>> [image: Screen Shot 2020-02-27 at 12.34.51 PM.png]
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> 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
>>
>> --
>> 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 [email protected].
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/jooq-user/126dbb7e-4108-4c69-b6b1-ffab2006682e%40googlegroups.com
>> <https://groups.google.com/d/msgid/jooq-user/126dbb7e-4108-4c69-b6b1-ffab2006682e%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
> --
> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jooq-user/CAB4ELO69ryP6O4NdkGmSYiSy6anz6uLfyKN3cJ04m-sCSepozQ%40mail.gmail.com
> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO69ryP6O4NdkGmSYiSy6anz6uLfyKN3cJ04m-sCSepozQ%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAM2NkrSnfswe2r4-xAE9CAq6MO71rVy9p9cam4kkRDoSZcfdeA%40mail.gmail.com.
