insertReturningPrimary for Collection of pojos

[Globegitter]

We have an insert for a collection of pojos so it would be nice if we could also get an insertReturningPrimary for a collection of pojos on the AbstractVertxDAO or at least an insertReturningMany on the queryExecutor to be able to easily implement oneself.

[aleksandar78]

What @Globegitter asks is a really useful feature. I was constrained to insert, make compose and then call select to obtains the primary key or some other value. JOOQ give you a possibility to get in return any value you want from an inserted record.
See JOOQ Documentation

Of course, I'm new to vertx-jooq library. Maybe there is some workaround with small effort to get this kind of feature. I'm using vertx with kotlin and I thought that using kotlin extension function I could do something. I'm aware of the intention of using QueryResult as generic and driver agnostic response is a very good thing.

Reading source code I've found insertReturning method in ReactiveClassicQueryExecutor but honestly, I'm not sure how this can be used.

Any suggestion welcome

[jklingsporn]

I agree this is a useful feature. Main issue here is, that this method's return-value needs to be parameterized on the DAO-Interface, as the return value depends on the API (Future vs CompletableFuture vs Single) and is not covered by any existing type. This is a breaking change and thus requires some caution and planning.

There is a workaround when working with the JDBC-driver. Write a helper-class like the following:

public class JooqUtil {

    public static <R extends UpdatableRecord<R>, K> CompletableFuture<List<K>> insertReturning(JDBCCompletableFutureGenericQueryExecutor queryExecutor, Collection<R> records, Table<R> table, Collection<? extends Field<?>> returnFields){
        return queryExecutor.executeAny(dslContext -> doInsertReturn(records, table, returnFields, dslContext));
    }

    public static <R extends UpdatableRecord<R>, K> CompletableFuture<List<K>> insertReturningPrimary(JDBCCompletableFutureGenericQueryExecutor queryExecutor, Collection<R> records, Table<R> table){
        return insertReturning(queryExecutor,records,table,table.getPrimaryKey().getFields());
    }

    @SuppressWarnings("unchecked")
    protected static <R extends UpdatableRecord<R>, K> List<K> doInsertReturn(Collection<R> records, Table<R> table, Collection<? extends Field<?>> returnFields, DSLContext dslContext) {
        InsertSetStep<R> insertSetStep = dslContext.insertInto(table);
        InsertValuesStepN<R> insertValuesStepN = null;
        for (R record : records) {
            insertValuesStepN = insertSetStep.values(setDefault(record).intoArray());
        }
        Result<R> fetch = insertValuesStepN.returning(returnFields).fetch();
        return fetch.stream().map(rec -> rec.key()).map(key -> {
            if (key.size() == 1) {
                return ((Record1<K>) key).value1();
            }
            return (K) key;
        }).collect(Collectors.toList());
    }

    private static Record setDefault(Record record) {
        int size = record.size();
        for (int i = 0; i < size; i++)
            if (record.get(i) == null) {
                @SuppressWarnings("unchecked")
                Field<Object> field = (Field<Object>) record.field(i);
                if (!field.getDataType().nullable() && !field.getDataType().identity())
                    record.set(field, DSL.defaultValue());
            }
        return record;
    }
}

[jklingsporn]

While thinking about integrating that feature, I realized it doesn't make sense to have it added like the existing insertReturningPrimary, because what would you do with a list of ids if you cannot connect those ids to the records you've just created?
The only useful return value would be a collection of the POJOs with the inserted ids set.

[aleksandar78]

@jklingsporn you make a good point. Returning list of IDs would be completely useless.
But, when you insert one row returning generated row ID is very useful and in many cases, a developer is constrained to make another query to get it.
A simple use case would be insert parent-child row where a child needs parent id as foreign key reference.

[jklingsporn]

So what do you suggest? Why not just using the insertReturningPrimary methods on the dao? Aleksandar <notifications@github.com> schrieb am Di., 28. Jan. 2020, 19:11:

…

@jklingsporn <https://github.com/jklingsporn> you make a good point. Returning list of IDs would be completely useless. But, when you insert one row returning generated row ID is very useful and in many cases, a developer is constrained to make another query to get it. A simple use case would be insert parent-child row where a child needs parent id as foreign key reference. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#120?email_source=notifications&email_token=ABQLZXRHLP7RLPAW2NIKNOTRABYOTA5CNFSM4IWFCJXKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEKEKZPA#issuecomment-579382460>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQLZXXQHM4FWGND7SHG2T3RABYOTANCNFSM4IWFCJXA> .

[aleksandar78]

To be honest I didn't see insertReturningPrimary methods on DAO. I did one simple project with this library and I need to deepen much more. As all our projects in the future will use this library for data access I must be sure about limitations.
Tomorrow I'll try to use that method on generated DAOs and I'll give you feedback.

Thanks

[aleksandar78]

I've tried the method on generated DAOs. That is nice to have and solves a frequent use case.
In the case of a sequential insert with passed arguments, first parent and then child row, the problem cannot be solved in that manner because the DAOs method has isolated transaction scope. If child insertion failed user must remove parent row and that isn't much convenient.
To solve this kind of use case executor with transaction method must be used but there is another difficulty. Transaction method, that deals with commits automatically, works out of the box with GenericQueryExecutor and that's ok when no specific return value is required.
If a specific value is required for sequential inserts ReactiveClassicQueryExecutor could be used, thanks to insertReturning method that requires query and field mapper as arguments. In that case, a transaction must be handled manually and honestly, code can be quite ugly.
To recap, I found solutions for some of my doubts. I think that would be very nice to have insertReturning method on GenericQueryExecutor but I don't know if that is possible due to its generic API.
DAOs are fine for isolated operations and I doubt they could be used with a shared transactional scope.
If I missing something suggestions and tips are welcome.

[Globegitter]

@jklingsporn that is a very good point you are making and I assumed that the order of the returned IDs would be matching the order of the inserted collection. But yes, returning a collection of pojos would also make sense.