Hibernate custom delete query multiple rows

Question

Still new to hibernate custom queries.

My table is

ID   TID   R1   Position
1     1     1     2
2     1     1     3

I want a custom query to delete rows with TID 1 and R1 1

My current sql looks like

@Query(value = "delete from Table t where t.TID= :tid and t.R1 = :r1", nativeQuery = true)
void deleteByTIDAndR1(@Param("tid") Integer TID, @Param("r1") Integer r1);

It gives me the following error:

.springframework.orm.jpa.JpaSystemException: could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet] with root cause

My controller retrieves the right tid and r1 ID's. Is it even possible to delete multiple rows at once? And where could my error be?

Edit

After I add @Modyfying to the query i get the error

TransactionRequiredException: Executing an update/delete query

And after i add @Transactional in combination with @Modifying i get

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't where t.tid= 1 and t.R1 = 99' at line 1

Benoit · Accepted Answer · 2017-10-12 13:02:39Z

1

I think you lack the @Modifying annotation, indicating a query that modify the database:

@Modifying
@Query(value = "delete from Table where TID= :tid and R1 = :r1", nativeQuery = true)
void deleteByTIDAndR1(@Param("tid") Integer TID, @Param("r1") Integer r1);

And yes this method can delete zero, one or more rows.

edited Oct 12, 2017 at 13:02

answered Oct 12, 2017 at 12:49

Benoit

5,4242 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Urban Over a year ago

When i add @Modifying i get the error TransactionRequiredException: Executing an update/delete query

Benoit Over a year ago

I updated the answer: the actual delete must be delete from Table where TID= :tid and R1 = :r1 - get rid of t param.

user641887 Over a year ago

Add @ transactional annotation on the service layer if you have multiple db operations. -add @ transactional annotation on the DAO layer if you have a single db operation

Urban Over a year ago

@Benoit your comment to delete the t param actually works. But i dont understand why. t. is just an alias right?

Benoit Over a year ago

Alias are only valid for SELECT not for DELETE. At least in MySql. Just test your delete statement in a sql console as I did and you will figure out by yourself.

Collectives™ on Stack Overflow

Hibernate custom delete query multiple rows

Edit

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related