# How to do an UPSERT with Hibernate and MySQL and avoid throwing "Duplicate entry for key" issues

**URL:** <https://discourse.hibernate.org/t/how-to-do-an-upsert-with-hibernate-and-mysql-and-avoid-throwing-duplicate-entry-for-key-issues/2349>\
**Category:** Hibernate ORM\
**Created:** [February 23, 2019, 4:34pm UTC](https://discourse.hibernate.org/t/how-to-do-an-upsert-with-hibernate-and-mysql-and-avoid-throwing-duplicate-entry-for-key-issues/2349 "2019-02-23T16:34:21Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![masber](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/masber/32/132_2.png) [@masber](https://discourse.hibernate.org/u/masber)\
**Post date:** [February 23, 2019, 4:34pm UTC](https://discourse.hibernate.org/t/how-to-do-an-upsert-with-hibernate-and-mysql-and-avoid-throwing-duplicate-entry-for-key-issues/2349/1 "2019-02-23T16:34:21Z")

</div>

Dear Hibernate community,

I have a code like this

```auto
List<?> checkRmaShipperConstraint = em.createNativeQuery("SELECT * from ereturn where rma = :rma and shipper = :shipper", Tuple.class)
    .setParameter("rma", newEr.getRma())
    .setParameter("shipper", newEr.getShipper().getId())
    .getResultList();
	        	
if (checkRmaShipperConstraint.size() > 0){
    logger.severe('unique_index VIOLATED');
    System.exit(1)
}
	        	
try {
	        		
    em.persist(newEr);
	        		
}catch(Exception ex) {
    // Here I get an Exception --> java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'XXXXXXXXX-YYYYYYYY' for key 'unique_index'
}

```

And this is the hibernate logs:

```auto
03:16:17,713 DEBUG [org.hibernate.SQL] - 
    SELECT
        * 
    from
        ereturn 
    where
        rma = ? 
        and shipper = ?
Hibernate: 
    SELECT
        * 
    from
        ereturn 
    where
        rma = ? 
        and shipper = ?
03:16:17,713 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [BIGINT] - [148375]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [1] as [VARCHAR] - [NZR1767568]
03:16:17,714 DEBUG [org.hibernate.SQL] - 
    insert 
    into
        ereturn
        (barcode, carrier, carrier_label_request_id, carrier_shipment_id, consignee, creationtime, destination, destinationHub, disabled, dispatchedDate, failedReturnPOBoxPrivateBag, globalId, groupName, height, invoice, length, modifiedtime, notes, pickupDateTime, pickupDateTimeOffset, returnMethod, rma, scanDateTime, scanDateTimeDestHub, shipper, sourceHub, trackingNumber, width) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: 
    insert 
    into
        ereturn
        (barcode, carrier, carrier_label_request_id, carrier_shipment_id, consignee, creationtime, destination, destinationHub, disabled, dispatchedDate, failedReturnPOBoxPrivateBag, globalId, groupName, height, invoice, length, modifiedtime, notes, pickupDateTime, pickupDateTimeOffset, returnMethod, rma, scanDateTime, scanDateTimeDestHub, shipper, sourceHub, trackingNumber, width) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [1] as [VARCHAR] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [BIGINT] - [148380]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [3] as [VARCHAR] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [4] as [VARCHAR] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [5] as [BIGINT] - [298757]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [6] as [TIMESTAMP] - [2017-05-17T22:56:06]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [7] as [BIGINT] - [148375]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [8] as [BIGINT] - [216219]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [9] as [BOOLEAN] - [false]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [10] as [DATE] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [11] as [VARCHAR] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [12] as [VARCHAR] - [NZR1767568]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [13] as [VARCHAR] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [14] as [DOUBLE] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [15] as [VARCHAR] - [261238592]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [16] as [DOUBLE] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [17] as [TIMESTAMP] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [18] as [VARCHAR] - [460]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [19] as [TIMESTAMP] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [20] as [INTEGER] - [null]
03:16:17,714 TRACE [org.hibernate.type.EnumType] - Binding [DROPBOX] to parameter: [21]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [22] as [VARCHAR] - [NZR1767568]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [23] as [TIMESTAMP] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [24] as [TIMESTAMP] - [null]
03:16:17,714 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [25] as [BIGINT] - [148375]
03:16:17,715 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [26] as [BIGINT] - [216218]
03:16:17,715 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [27] as [VARCHAR] - [null]
03:16:17,715 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [28] as [DOUBLE] - [null]
03:16:17,740 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - SQL Error: 1062, SQLState: 23000
03:16:17,740 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - Duplicate entry 'NZR1767568-148375' for key 'unique_index'

```

The code gives an error due to ‘unique\_index’ constraint violation. I don’t understand how I got into this situation as I am checking that the new element does not exists by the select query before persist.

Any hint?

thank you very much

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 23, 2019, 6:54pm UTC](https://discourse.hibernate.org/t/how-to-do-an-upsert-with-hibernate-and-mysql-and-avoid-throwing-duplicate-entry-for-key-issues/2349/2 "2019-02-23T18:54:13Z")

</div>

Most likely you need to do an UPSERT, which works atomically, unlike the SELECT-CHECK-INSERT anti-pattern.

Check out [this article](https://vladmihalcea.com/how-do-upsert-and-merge-work-in-oracle-sql-server-postgresql-and-mysql/) for more details.

---

<div class="post-metadata">

**Author:** ![masber](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/masber/32/132_2.png) [@masber](https://discourse.hibernate.org/u/masber)\
**Post date:** [February 23, 2019, 11:33pm UTC](https://discourse.hibernate.org/t/how-to-do-an-upsert-with-hibernate-and-mysql-and-avoid-throwing-duplicate-entry-for-key-issues/2349/3 "2019-02-23T23:33:35Z")

</div>

Hi Vlad,

thank you very much for your answer, it really helped me to understand things a little bit more but still have a couple of questions.

- Yes my code belongs to an ETL or batching process

- My environment only has one client (which runs the ETL) and one server (web server/tomcat and database).

- The client do calls to the server through a http request inside a simple loop

- I am guessing I am not doing multi-threading yet my problem looks very similar.

- Also I am using native queries to read if element already exists in the database (this should be accurate right?) but still persist fails due to a unique constraint violation.

- I also tried nativequery instead of persist but the problem did not go away.

- application managed entitymanager

I am just trying to understand a little bit deeper and I feel I still miss a few things

thank you very much

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 24, 2019, 6:39am UTC](https://discourse.hibernate.org/t/how-to-do-an-upsert-with-hibernate-and-mysql-and-avoid-throwing-duplicate-entry-for-key-issues/2349/4 "2019-02-24T06:39:58Z")

</div>

If you’re bootstrapping Hibernate natively, not via JPA, the native SQL queries won’t trigger a flush.

Check out [this article for more details](https://vladmihalcea.com/how-does-the-auto-flush-work-in-jpa-and-hibernate/) and solutions as well.