Spring data jpa SELECT ... FOR UPDATE Query not working with @query
Solution 1
Take a look at the following answer for a question similar to yours: Select for update not working
Here is what it says:
For Update basically puts a lock on rows, to achieve the same using JPA you need to use Lock Modes. To set the lock, you can use EntityManager or TypeQuery and use LockModeType.PESSIMISTIC_WRITE.
Refer this article
Solution 2
My guess is that it is not the FOR
token that is causing the trouble here.
I think that "SELECT UPDATE t FROM X t" is invalid SQL syntax.
user1575601
Updated on June 12, 2022Comments
-
user1575601 almost 2 years
I am trying to use FOR UPDATE with SELECT in Spring Data Jpa Repository.
Below query doesn't give error while running it on Database console. But in Spring Data JPA throws an exception, it is not able to recognize token for in below query.
Intentionally using Braces instead of greater and smaller than symbol in first line of code as it is hiding data inside those tags while trying to post questionpublic interface XRepository extends JpaRepository(X, String) { @Query(value = "SELECT UPDATE t FROM X t WHERE t.a = ?1 AND t.b = ?2 FOR UPDATE") X findByAAndB(String a, String b); }
Stack trace:
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: FOR near line 1, column 141 IT is giving error for FOR in Query at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1679) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1608) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:294) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_181] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_181] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_181] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_181] at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:347) ~[spring-orm-4.3.13.RELEASE.jar:4.3.13.RELEASE] at com.sun.proxy.$Proxy114.createQuery(Unknown Source) ~[na:na] at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86) ~[spring-data-jpa-1.11.9.RELEASE.jar:na] ... 67 common frames omitted Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:288) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1894) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final] at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final] ... 74 common frames omitted