Hibernate./JPA native bulk insert with on duplicate key update issue

Question

I have used this function to generate bulk insert to MySQL 5.7 with insert or update

private String bulkInsertQuery(List<ProducDetails> productDetailsList){
    StringBuilder prodBatchInsert=new StringBuilder("INSERT INTO product_details(prod_id, prod) VALUES ");
    StringBuilder prodBatchInsertOnDuplicateKey=new StringBuilder(" ON DUPLICATE KEY UPDATE prod=VALUES(prod)");
    productDetailsList.forEach((ProductDetails row)->{
        prodBatchInsert.append("(");
        prodBatchInsert.append("\""+row.getProdId()+"\"");
        prodBatchInsert.append(",");
        prodBatchInsert.append("\""+row.getProd()+"\"");
        prodBatchInsert.append(")");
        prodBatchInsert.append(",");
    });
    String insertSql=prodBatchInsert.toString();
    return insertSql.substring(0,insertSql.length()-1).concat(prodBatchInsertOnDuplicateKey.toString());
}

Query query=entityManager.createQuery(bulkInsertQuery(productDetailsList));
            query.executeUpdate();

initially, I used single quotes but it fails due to this type of data product names like ABC'S FOOD which contained single quotes, then I switched to the above double quotes

I want to know if there is a way to resolve this or use parameterized queries to bulk insert on update duplicate keys.

Hibernate does support batch inserts. Please look here https://www.baeldung.com/jpa-hibernate-batch-insert-update — jms, Mar 18 '21 at 06:50
yes but I want on duplicate key update too...it doesn't work that way — wink_lol_coder, Mar 18 '21 at 07:00

score 1 · Answer 1 · answered Mar 18 '21 at 10:34

1

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

answered Mar 18 '21 at 10:34

Christian Beikov

6,925
1
26
48

Check the example in the other stack overflow post :) – Christian Beikov Mar 18 '21 at 16:38
i'm getting this issue now javax.persistence.EntityExistsException: A different object with the same identifier value was already associated with the session – wink_lol_coder Mar 19 '21 at 05:11
Why are you trying to persist the same object multiple times with the same persistence context? Try to de-duplicate your list first before running this process. – Christian Beikov Mar 22 '21 at 14:52

Hibernate./JPA native bulk insert with on duplicate key update issue

1 Answers1