时间戳

首页 > 解决方案 > Insert values in many to many relationship tables with JOOQ

java - Insert values in many to many relationship tables with JOOQ

问题描述

I have three tables in my database, SUBSCRIPTION, USER_ID, and an association table called SUBSCRIPTION_USER_ID.

My strategy is to use JOOQ batch with three queries, the first one to insert on row into SUBSCRIPTION, the second query to insert multiple rows into USER_ID, and finally, I need to insert the association IDs into SUBSCRIPTION_USER_ID, so I did the following:

InsertValuesStep2 insertUserIds = insertInto(
    USER_ID, USER_ID.USER_ID_TYPE, USER_ID.USER_ID_VALUE);

for (String userId : subscriptionDTO.getUserId())
    insertUserIds = insertUserIds.values(getValue(0, userId), getValue(1, userId));

InsertReturningStep insertReturningUserIds = insertUserIds.onConflictDoNothing();

InsertResultStep insertReturningSubscription = insertInto(SUBSCRIPTION)
        .set(SUBSCRIPTION.CHANNEL_ID, subscriptionDTO.getChannel())
        .set(SUBSCRIPTION.SENDER_ID, subscriptionDTO.getSenderId())
        .set(SUBSCRIPTION.CATEGORY_ID, subscriptionDTO.getCategory())
        .set(SUBSCRIPTION.TOKEN, subscriptionDTO.getToken())
        .onConflictDoNothing()
        .returningResult(SUBSCRIPTION.ID);

Unfortunately, to insert values into the association table, I tried many ways but nothing works for me, finally, I tried to insert values in SUBSCRIPTION_USER_IDusing with select but It doesn't work:

InsertValuesStep insertValuesSubscriptionUserIds = insertInto(
        SUBSCRIPTION_USER_ID, 
        SUBSCRIPTION_USER_ID.SUBSCRIPTION_ID, 
        SUBSCRIPTION_USER_ID.USER_ID_ID)
    .select(select(SUBSCRIPTION.ID, USER_ID.ID)
        .from(SUBSCRIPTION)
        .innerJoin(USER_ID)
        .on(concat(USER_ID.USER_ID_TYPE, 
                val(CATEGORY_USER_ID_DELIMITER), 
                USER_ID.USER_ID_VALUE).in(subscriptionDTO.getUserId())
        .and(SUBSCRIPTION.SENDER_ID.equal(subscriptionDTO.getSenderId()))
        .and(SUBSCRIPTION.CHANNEL_ID.equal(subscriptionDTO.getChannel()))
        .and(SUBSCRIPTION.CATEGORY.equal(subscriptionDTO.getCategory()))
        .and(SUBSCRIPTION.TOKEN.equal(subscriptionDTO.getToken()))));

Am I missing something above? Is there a better way using JOOQ to insert many-to-many relationship values or to use queries results as parameters for other queries?

标签： javamany-to-manyjooq

解决方案

如上所述，我为SUBSCRIPTION和USER_ID表插入了值。并获取关联表，我需要从上述两个表中获取已插入值的 ID，因此为了解决我使用此查询插入的问题SUBSCRIPTION_USER_ID：

InsertReturningStep insertReturningSubscriptionUserId = insertInto(
    SUBSCRIPTION_USER_ID, 
    SUBSCRIPTION_USER_ID.SUBSCRIPTION_ID, 
    SUBSCRIPTION_USER_ID.USER_ID_ID)
    .select(select(SUBSCRIPTION.ID, USER_ID.ID).from(SUBSCRIPTION
            .where(concat(USER_ID.USER_ID_TYPE, val(CATEGORY_USER_ID_DELIMITER), USER_ID.USER_ID_VALUE).in(subscriptionDTO.getUserId()))
            .and(SUBSCRIPTION.SENDER_ID.equal(subscriptionDTO.getSenderId()))
            .and(SUBSCRIPTION.CHANNEL_ID.equal(subscriptionDTO.getChannel()))
            .and(SUBSCRIPTION.CATEGORY.equal(subscriptionDTO.getCategory()))
            .and(SUBSCRIPTION.TOKEN.equal(subscriptionDTO.getToken()))).onConflictDoNothing();

最后，我使用以下命令执行了所有查询batch：

using(configuration).batch(insertReturningSubscription,
 insertReturningUserIds,
 insertReturningSubscriptionUserId).execute()

推荐阅读