Native query Update not doing any changes to the database

Hibernate ORM
1

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:

		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

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

2

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.

3

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

4

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

		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();
5

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:

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();
1 Like