# Native query Update not doing any changes to the database

**URL:** <https://discourse.hibernate.org/t/native-query-update-not-doing-any-changes-to-the-database/813>\
**Category:** Hibernate ORM\
**Created:** [May 22, 2018, 10:08am UTC](https://discourse.hibernate.org/t/native-query-update-not-doing-any-changes-to-the-database/813 "2018-05-22T10:08:35Z")\
**Posts on this page:** 5\
**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:** [May 22, 2018, 10:08am UTC](https://discourse.hibernate.org/t/native-query-update-not-doing-any-changes-to-the-database/813/1 "2018-05-22T10:08:35Z")

</div>

Dear Hibernate community,

I am not sure why the code below does not update my database data, however taking the query from the hibernate logs seems to work…

I am using hibernate 5.2.12.Final

This is my whole function:

```auto
		EntityManager em = Utils.initEntityManager();
		em.getTransaction().begin();
		
		em.persist(receptacle);
		
		em.createNativeQuery("UPDATE product_item pi"
				+ " JOIN ereturn er ON pi.ereturn = er.id "
				+ "SET pi.receptacle = :receptacle "
				+ "WHERE pi.returnAction = :returnAction "
				+ "AND er.destination = :destination "
				+ "AND er.status = 'RECEIVED'")
				.setParameter("receptacle", receptacle.getId())
				.setParameter("returnAction", receptacle.getReturnAction())
				.setParameter("destination", receptacle.getDestination().getId())
				.executeUpdate();		
		
		em.getTransaction().commit();
		em.close();

```

And this is the log from hibernate

```auto
19:47:29,048 DEBUG [org.hibernate.SQL] - 
    UPDATE
        product_item pi 
    JOIN
        ereturn er 
            ON pi.ereturn = er.id 
    SET
        pi.receptacle = ? 
    WHERE
        pi.returnAction = ? 
        AND er.destination = ? 
        AND er.status = 'RECEIVED'
Hibernate: 
    UPDATE
        product_item pi 
    JOIN
        ereturn er 
            ON pi.ereturn = er.id 
    SET
        pi.receptacle = ? 
    WHERE
        pi.returnAction = ? 
        AND er.destination = ? 
        AND er.status = 'RECEIVED'
19:47:29,049 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [1] as [BIGINT] - [20]
19:47:29,049 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [3] as [BIGINT] - [148375]
19:47:29,049 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] - binding parameter [2] as [VARBINARY] - [RETURNTOCLIENT]

```

I take same query from the logs, replace the parameters and run it in my mysql console… the query works!!!

QUESTION: why mysql is not changing any data when the update query above runs?

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:** [May 22, 2018, 10:28am UTC](https://discourse.hibernate.org/t/native-query-update-not-doing-any-changes-to-the-database/813/2 "2018-05-22T10:28:13Z")

</div>

If you don’t get an’t exception it means it works.

How did you establish it does not work? Did you debug it and open a MySQL Workbench? In this case, no change is visible for other DB conbections until you commit.

---

<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:** [May 22, 2018, 11:00am UTC](https://discourse.hibernate.org/t/native-query-update-not-doing-any-changes-to-the-database/813/3 "2018-05-22T11:00:27Z")

</div>

The code runs without exception. In order to test I open mysql workbench and run this command:

select \* from product\_item where receptacle is not null;

which returns nothing…

then I run the command from hibernate logs and it works

---

<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:** [May 22, 2018, 11:05am UTC](https://discourse.hibernate.org/t/native-query-update-not-doing-any-changes-to-the-database/813/4 "2018-05-22T11:05:38Z")

</div>

ok… I found the issue…

The problem was that returnAction is an enum so I have to convert to string… should hibernate catch this and throw an exception?

this is how I fixed it

```auto
		em.createNativeQuery("UPDATE product_item pi"
				+ " JOIN ereturn er ON pi.ereturn = er.id "
				+ "SET pi.receptacle = :receptacle "
				+ "WHERE pi.returnAction = :returnAction "
				+ "AND er.destination = :destination "
				+ "AND er.status = 'RECEIVED'")
				.setParameter("receptacle", receptacle.getId())
				.setParameter("returnAction", receptacle.getReturnAction().toString())
				.setParameter("destination", receptacle.getDestination().getId())
				.executeUpdate();

```

---

<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:** [May 22, 2018, 11:21am UTC](https://discourse.hibernate.org/t/native-query-update-not-doing-any-changes-to-the-database/813/5 "2018-05-22T11:21:34Z")

</div>

`The problem was that returnAction is an enum so I have to convert to string… should hibernate catch this and throw an exception?`

Hibernate can do that automatically for JPQL queries, not for native SQL which cannot be parsed by Hibernate.

So, you have to provide the type info from the Hibernate-specific [`Query#setParameter`](https://docs.jboss.org/hibernate/orm/5.2/javadocs/org/hibernate/query/Query.html#setParameter-java.lang.String-java.lang.Object-org.hibernate.type.Type-):

```auto
em.createNativeQuery("UPDATE product_item pi"
+ " JOIN ereturn er ON pi.ereturn = er.id "
+ "SET pi.receptacle = :receptacle "
+ "WHERE pi.returnAction = :returnAction "
+ "AND er.destination = :destination "
+ "AND er.status = 'RECEIVED'")
unwrap(org.hibernate.query.Query.class)
.setParameter("receptacle", receptacle.getId())
.setParameter("returnAction", receptacle.getReturnAction(), new EnumType())
.setParameter("destination", receptacle.getDestination().getId())
.executeUpdate();

```