0

Is it possible to use a MySQL User Defined Variable inside a @Query Annotation from Spring Data?

Edit: Here is my current Code:

 @Query( value = "select case when (?2 = 0) then -1 when ((@cd:=(select count(*) + 1 from test.stats where " +
            "points = ?2)) <= 2) then (@cd) else (@cd + (select count(*) from test.stats where points = ?2 and " +
            "uuid > ?1)) end as rank from test.stats where uuid = ?1;", nativeQuery = true )
    Integer getRank( String uuid, Integer points );

@cd := ... seems to be the error, but how can I fix it? Here is the full stack trace:

org.hibernate.QueryException: Space is not allowed after parameter prefix ':' [select case when (?2 = 0) then -1 when ((@cd:=(select count(*) + 1 from test.stats where points = ?2)) <= 2) then (@cd) else (@cd + (select count(*) from test.stats where points = ?2 and uuid > ?1)) end as rank from test.stats where uuid = ?1;]
    at org.hibernate.engine.query.spi.ParameterParser.parse(ParameterParser.java:173) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.query.spi.ParamLocationRecognizer.parseLocations(ParamLocationRecognizer.java:59) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.query.internal.NativeQueryInterpreterStandardImpl.getParameterMetadata(NativeQueryInterpreterStandardImpl.java:34) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.query.spi.QueryPlanCache.getSQLParameterMetadata(QueryPlanCache.java:128) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.getNativeQueryImplementor(AbstractSharedSessionContract.java:951) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createNativeQuery(AbstractSharedSessionContract.java:874) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.AbstractSessionImpl.createNativeQuery(AbstractSessionImpl.java:23) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_151]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_151]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_151]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_151]
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350) ~[spring-orm-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at com.sun.proxy.$Proxy101.createNativeQuery(Unknown Source) ~[na:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_151]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_151]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_151]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_151]
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:304) ~[spring-orm-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at com.sun.proxy.$Proxy101.createNativeQuery(Unknown Source) ~[na:na]
    at org.springframework.data.jpa.repository.query.NativeJpaQuery.createJpaQuery(NativeJpaQuery.java:75) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateQuery(AbstractStringBasedJpaQuery.java:82) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:210) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:214) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) ~[spring-data-jpa-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:590) ~[spring-data-commons-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578) ~[spring-data-commons-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.6.RELEASE.jar:5.0.6.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[spring-data-commons-2.0.7.RELEASE.jar:2.0.7.RELEASE]
    at

The full stack trace is too big, so here is the link: https://pastebin.com/73nERHDB

p4skal
  • 17
  • 6
  • Why don't you just try it? – Jens Schauder Jun 06 '18 at 06:48
  • Well i've tried this but, it didn't worked for me. http://prntscr.com/jrm3mr – p4skal Jun 06 '18 at 13:29
  • So what have you tried? And what does "didn't worked for me" mean? Please update your question with the code you tried and the compile error, stacktrace or anything else to showcase that it doesn't work as anticipated. – Jens Schauder Jun 06 '18 at 14:08
  • It's pretty simple. When writing those @variables intllij says it doesn't know the @ character and when executing it, i get an sql error inside spring data. – p4skal Jun 06 '18 at 18:41
  • Please update your question with the code you tried and the full stack trace of the exception. – Jens Schauder Jun 07 '18 at 04:39
  • I have tried fixing the problem with native queries, but it doesn't work. Question is updated. – p4skal Jun 08 '18 at 12:09
  • The problem is probably the := I think I read something about an alternative assignment operator (= or ==) could you try that one? – Jens Schauder Jun 08 '18 at 13:11
  • ==: [Here](https://pastebin.com/5RFbk1PN) =: [Here](https://pastebin.com/diw6qg9L) – p4skal Jun 08 '18 at 13:23
  • Seems not to pass Hibernates parser. Maybe this does help? https://stackoverflow.com/questions/2712240/how-to-use-mysql-variables-with-hibernate – Jens Schauder Jun 08 '18 at 13:38
  • 1
    Well i've solved it with JdbcTemplate now. Thank you anyways! – p4skal Jun 08 '18 at 14:08

0 Answers0