Add capability to refresh multiple beans at once

[AntoineDuComptoirDesPharmacies]

Hi everyone,

Some of our entities have a column generated/populated by the database on INSERT.
Thus, after calling save we call refresh on the bean to get the value generated by database for this column.

Now, we want to reproduce the same for batch INSERT. Inserstion is going well as there is a saveAll method available.
However the refresh method do not seems to allow passing multiple beans at once.
Thus, we end up doing a for loop , calling refresh on each bean, resulting in a large amount of database query sent to the server.

It could be interesting to have a refreshAll method that take a list of beans and SELECT all of them in a unique server query.

What do you think of this feature ?

Yours faithfully,
LCDP

[jnehlmeier]

As a workaround you could create a list of ids after saveAll() has been called, because your entities now have an id. Then select the entities again using id IN (..). Not nice, but at least you do not have hundreds of queries.

I am not completely sure but if you do it in the same transaction chances are that your custom select would return the same instances Ebean has just saved but with updated fields because of the persistence context.

[rPraml]

I am not completely sure but if you do it in the same transaction

I am also not 100% sure, but an IN(ids) query in the same transaction will not hit the DB at all, if all IDs are in the persistence context.

I would make a small DTO bean with ID and the missing field(s) . Then do a DTO-in query and distribute the results to the currently saved beans. This would fetch only the necessary data from the DB.

[AntoineDuComptoirDesPharmacies]

Hi @jnehlmeier and @rPraml, that are great workaround ideas.
I do not seems to reach L2 cache, but going through a DTO-in-query may allow to ensure this point.

Here is a potential workaround following your advices :

try (Transaction transaction = DB.beginTransaction()) {
            transaction.setBatchSize(1_000);

            DB.saveAll(orders);

            List<Long> ids = orders
                    .stream().map(Order::getId)
                    .collect(Collectors.toList());

            Map<Long, Order> orderReferences = DB.find(Order.class)
                            .select("reference")
                            .where()
                            .idIn(ids)
                            .findMap();

            for (Order order : orders) {
                order.setReference(orderReferences.get(order.getId()).getReference());
                // setLoaded is usualy called by Ebean after save and set all fields and the bean non-dirty
                DB.beanState(order).setLoaded();
            }


            transaction.commit();
}

Seems good to you ?

[rbygrave]

have a column generated/populated by the database on INSERT

What is the database(s) being used and exactly what is this column, what is this use case? Generally this is going to be far from ideal (to do a lot of refresh calls). It might be worth looking at other options depending on the database but first we should understand exactly what the use case is.

[AntoineDuComptoirDesPharmacies]

Hi @rbygrave,

Our generated value from database is a DB sequence which is following current timestamp.
Something that would look like 20230907121510356 for the 07th of septembre 2023, 12h15m10s356ms.
It is generated by a DB sequence instead of Java side as it avoid having conflict with same number generated at same instant in database by multiple instance of the same service.
Additional details of our use case here : https://groups.google.com/g/ebean/c/yWlhXNKs1aY/m/8dhipyhlAgAJ

[rbygrave]

Yeah but what does the model look like exactly, as in is this the ONLY generated column and hence potentially retrievable via GetGeneratedKeys.

So again, what does the model look like exactly and what is the database being used?

[AntoineDuComptoirDesPharmacies]

We are currently using PostgreSQL as database.
In theory we could have put @GeneratedValue on this business DB generated column which will be retrieved by Ebean on insert.
But for legacy reasons, this table was created with a classic DB generated ID in addition to our business generated reference.
Moreover, tons of tables are referencing this enitity using the 'useless' primary key, making it difficult to rework now.

Here is the model :

public class Order extends Model {

    @Id
    @GeneratedValue
    public Long id;
    
    @Column(unique = true, nullable = false, insertable = false, updatable = false)
    private String reference; // This is the business DB generated value using timestamp
    
    [...]

Yours faithfully,
LCDP

[rbygrave]

I think you could use postgres specific returning clause on the INSERT to return both columns.