Merge multiple objects with Hibernate-CodePudding

I'm using JPA with Hibernate and spring-boot-starter-data-jpa. I want to merge/update a set of Items. If it's a new item, I want to persist, if it's an already existsing item i want to update it.

    @PersistenceContext(unitName = "itemEntityManager")
    private EntityManager em;

    @Transactional
    public void saveItems(Set<Item>> items) {
        items.forEach(em::merge);
    }

When I try it like this, every Item creates a new HQL statment and it's inperformant. So I'm looking for a way to save all Items in one time (if it's possible), to save calls and time.

I found this:

        EntityTransaction transaction = em.getTransaction();
        transaction.begin();
        items.forEach(em::merge);
        transaction.commit();

but i can't use this transaction because i use the @Transactional. Is there a way with native SQL?

CodePudding user response：

You could use @SQLInsert for this purpose to use batch inserts. See Hibernate Transactions and Concurrency Using attachDirty (saveOrUpdate)

CodePudding user response：

I created a native SQL statement:

I joined all items as a list of values in sql format

String sqlValues = String.join(",", items.stream().map(this::toSqlEntry).collect(Collectors.toSet()));

Then i called a native query

    em.createNativeQuery("INSERT INTO config_item"
                  "( id, eaid, name, type, road, offs, created, deleted )"
                  " VALUES "   sqlValues
                  " ON CONFLICT (id) DO UPDATE "
                  " SET "
                      "eaid=excluded.eaid,\n"
                      "name=excluded.name,\n"
                      "type=excluded.type,\n"
                      "road=excluded.road,\n"
                      "offs=excluded.offs,\n"
                      "created=excluded.created,\n"
                      "deleted=excluded.deleted;"
        ).executeUpdate();

That’s a lot faster and works