Introduce bulk updaters/inserters/deleters

[minborg]

Currently, the Manager::updater, Manager::inserter and Manager::deleter handle operations on a one-by-one case. It would be beneficial to introduce bulk update methods that could handle multiple operations in a single SQL operation.

[minborg]

One way of implementing this feature would be to introduce an API like this:

try (BulkInserter<Film> bulkInserter = films.createBulkInserter()) {   
    createFilmStream().forEach(bulkInserter);
   // Films are buffered by the bulkInserter and purged to the database as the buffer
  // is full.
}
// When the bulkInserter is closed, the last inserts are flushed to the database.

Another way would be:

    films.bulkInsert(createFilmStream());

[minborg]

Benchmark of different solutions: https://stackoverflow.com/questions/11389449/performance-of-mysql-insert-statements-in-java-batch-mode-prepared-statements-v

[Martin-H2]

Hello,
as person who proposed this feature, you have my upvote :)

One addition:
You may add a convenience method like:
EntityManager.persistAll(Collection, BulkOptions)

BulkOptions may be optional ... and may hold (future) settings for bulk insertion tuning.
For VERY big bulk inserts, there is a second option besides "prepared statements" or "multivalue":
LOAD DATA INFILE
https://dev.mysql.com/doc/refman/5.7/en/load-data.html

You could transparently switch to this mode, when:

• the number of inserted items ( .size() of provided collection) is > threshold N, where N needs to be evaluated and is the point where LOAD DATA INFILE would be faster
• the underlying DBMS supports LOAD DATA INFILE
• the speedment running JVM is able to write a CSV-file (on the DBMS machine ?)

[ractoc]

wouldn't it be a lot cleaner to use a sort of transactions setup for this?
Then, you would just start the transaction, perform all update / insert / delete statements, commit the transaction.

This is, if I remember correctly, the way JPA and Hibernate do it. It gets the same results, the statements are only passed to the database when all statements are done. But it seems more in line with how these sorts of things work when you look at the database itself.

Using the transaction setup means that the actual database actions can be done in different locations in your code. which makes for cleaner code in my opinion.

When using transactions, we could even keep track of all updates / inserts / deletes performed so we could reverse them in case one of the later ones fails to commit. But this would be a nice to have feature in the short term.

very basic example:
EntityManager.startTransaction();
FilmService.updateFilm(Film1);
FilmService.createFilem(Film2);
FilmService.removeFilm(Film3);
DirectorService.createDirector(director);
...
...
EntityManager.commitTransaction();
of in case of problems:
EntityManager.rollbackTransaction();

[minborg]

@ractoc interestingly the imminent version of Speedment will support transactions in a similar way you describe it. We could ask @Rei-Sundance to test the performance with a transaction based insert once 3.0.17 is released.

Example:

    final LanguageManager languages = app.getOrThrow(LanguageManager.class);

    final TransactionComponent txComponent = app.getOrThrow(TransactionComponent.class);
    final TransactionHandler txHandler = txComponent.createTransactionHandler();

    txHandler.createAndAccept(tx -> {
        Language german = new LanguageImpl().setLanguageId((short)2).setName("German");
        languages.persist(german);
        Language swedish = new LanguageImpl().setLanguageId((short)3).setName("Swedish");
        languages.persist(german);
        tx.commit();
    });

[ractoc]

cool, looking forward to seeing that. A feature like this brings us one step closer to actually being able to use speedment in high end production environments