Using manual SqlResultSetMapping with named native queries fails [DATAJPA-1280]

[spring-projects-issues]

Eugene Nikiforov opened DATAJPA-1280 and commented

javax.persistence.NamedNativeQuery result couldn't properly use javax.persistence.SqlResultSetMapping declaration according to DATAJPA-1273 fix.

I'll provide Kotlin code for my example, but I think it doesn't play any role in this case.

Problem statement:

We want to execute some native query and automatically map this result to some DTO object (non-entity), we could achieve it by creating some named native query and mapping it through SqlResultSetMapping (Hibernate successfully parse all this statements and do it as per JPA standard):

Code that worked in Spring Data JPA 2.0.3

@Entity
@SqlResultSetMappings(
        SqlResultSetMapping(
                name = "summary",
                classes = [
                    ConstructorResult(
                            targetClass = ChartData::class,
                            columns = [
                                ColumnResult(name = "name", type = String::class),
                                ColumnResult(name = "value", type = Double::class)
                            ]
                    )
                ]
        )
)
@NamedNativeQueries(
        NamedNativeQuery(name = "Playlist.totalSummary",
                query = """
                    ...
                """, resultSetMapping = "summary")
)
@Table(name = "playlist")
class Playlist {
....
}

@Query(name = "Playlist.totalSummary")
    fun totalSummary(@Param("user_id") userId: Long): List<ChartData>

Now (Spring data JPA 2.0.5 and above), after fixing problem with named queries, native one are also affected:

• org.springframework.data.jpa.repository.query.NamedQuery class instantiate query with result type = Tuple
• Then org.hibernate.internal.AbstractSharedSessionContract uses this type to define which resultTransformer to choose
• As far as it is Tuple, contract setup org.hibernate.jpa.spi.NativeQueryTupleTransformer, which uses some checks on creation:

if ( tuple.length != aliases.length ) {
				throw new HibernateException( "Got different size of tuples and aliases" );
			}

Tuple length in this case with SqlResultSetMappings is 1, because it knows what we want to return (look at org.hibernate.loader.hql.QueryLoader#getResultList method where results is coming as a list) and it is not a tuple, then it is checking with aliases from query's select clause and got a mismatch.

If I could help you somewhere or if something is still not clear I'm ready to help you with investigations

---

Affects: 2.1 M2 (Lovelace), 2.0.5 (Kay SR5), 1.11.12 (Ingalls SR12), 2.0.7 (Kay SR7)

Reference URL: https://github.com/EugeneNik/spring-data-datajpa-1280-example

Referenced from: commits 6098d01, c9605c5, c751659

Backported to: 2.0.8 (Kay SR8), 1.11.13 (Ingalls SR13)

3 votes, 7 watchers

[spring-projects-issues]

Oliver Drotbohm commented

Can you please add the actual query and the type definition of ChartData. If the aliases defined in the query match the constructor argument names of ChartData you shouldn't even need to define a result set mapping in the first place

[spring-projects-issues]

Eugene Nikiforov commented

Hi, Oliver, sure

data class ChartData(var name: String, var value: Double)

 NamedNativeQuery(name = "Playlist.totalSummary",
                query = """
                    select coalesce(playlist.year, 0) as name, count(mark.id) as value
                    from talk
                    inner join playlist on talk.playlist_id = playlist.id
                    inner join conference on playlist.conference_id = conference.id
                    left join mark on mark.talk_id = talk.id and mark.user_id = :user_id and mark.is_seen = true
                    where (conference.is_visible = true or 1 = (select 1 from user_roles where user_roles.user_id = :user_id and user_roles.role = 'ROLE_ADMIN'))
                           and (playlist.is_visible = true or 1 = (select 1 from user_roles where user_roles.user_id = :user_id and user_roles.role = 'ROLE_ADMIN'))

                    group by coalesce(playlist.year, 0)
                    order by coalesce(playlist.year, 0)
                """,
                resultSetMapping = "summary")

[spring-projects-issues]

Oliver Drotbohm commented

Can you confirm this works if you don't use the result set mapping in the first place?

[spring-projects-issues]

Eugene Nikiforov commented

Could you clarify a little bit what approach I should use? I think there are several possibilities to invoke this query and I'm not sure which method you are thinking will work.

I mean if I remove @SqlResultSetMapping then I have to remove resultSetMapping from the NamedNativeQuery declaration, so it won't been automatically matched, I could go on with createQuery and returning Tuple, like in your test example in commit, but it leads me to customization of my existing repository and add mapping to place that worked previously.

Also I've already tried to reimplement this code in another way

    @Query("""
        select coalesce(playlist.year, 0) as name, count(mark.id) as value
                    from talk
                    inner join playlist on talk.playlist_id = playlist.id
                    inner join conference on playlist.conference_id = conference.id
                    left join mark on mark.talk_id = talk.id and mark.user_id = :user_id and mark.is_seen = true
                    where (conference.is_visible = true or 1 = (select 1 from user_roles where user_roles.user_id = :user_id and user_roles.role = 'ROLE_ADMIN'))
                           and (playlist.is_visible = true or 1 = (select 1 from user_roles where user_roles.user_id = :user_id and user_roles.role = 'ROLE_ADMIN'))

                    group by coalesce(playlist.year, 0)
                    order by coalesce(playlist.year, 0)
    """, nativeQuery = true)
    fun totalByYearsPlaylistSummary(@Param("user_id") userId: Long): List<ChartData>

but got this exception

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [org.myconfs.base.dto.ChartData]

	at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:321)
	at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:194)
	at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:174)
	at org.springframework.data.repository.query.ResultProcessor$ProjectingConverter.convert(ResultProcessor.java:293)
	at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.lambda$and$0(ResultProcessor.java:213)
	at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.convert(ResultProcessor.java:224)
	at org.springframework.data.repository.query.ResultProcessor.processResult(ResultProcessor.java:152)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:141)
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:590)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.data.repository.core.support.MethodInvocationValidator.invoke(MethodInvocationValidator.java:99)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy228.totalByYearsPlaylistSummary(Unknown Source)
	at org.myconfs.integration.base.repositories.PlaylistStatisticsRepositoryTest.testTotalByYearSummary(PlaylistStatisticsRepositoryTest.kt:459)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:124)
	at org.testng.internal.MethodInvocationHelper$1.runTestMethod(MethodInvocationHelper.java:230)
	at org.springframework.test.context.testng.AbstractTestNGSpringContextTests.run(AbstractTestNGSpringContextTests.java:180)
	at org.testng.internal.MethodInvocationHelper.invokeHookable(MethodInvocationHelper.java:242)
	at org.testng.internal.Invoker.invokeMethod(Invoker.java:567)
	at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:707)
	at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:979)
	at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:125)
	at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:109)
	at org.testng.TestRunner.privateRun(TestRunner.java:648)
	at org.testng.TestRunner.run(TestRunner.java:505)
	at org.testng.SuiteRunner.runTest(SuiteRunner.java:455)
	at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:450)
	at org.testng.SuiteRunner.privateRun(SuiteRunner.java:415)
	at org.testng.SuiteRunner.run(SuiteRunner.java:364)
	at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
	at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:84)
	at org.testng.TestNG.runSuitesSequentially(TestNG.java:1187)
	at org.testng.TestNG.runSuitesLocally(TestNG.java:1116)
	at org.testng.TestNG.runSuites(TestNG.java:1028)
	at org.testng.TestNG.run(TestNG.java:996)
	at org.testng.IDEARemoteTestNG.run(IDEARemoteTestNG.java:72)
	at org.testng.RemoteTestNGStarter.main(RemoteTestNGStarter.java:123)

[spring-projects-issues]

Oliver Drotbohm commented

Oh, right. As you're using a DTO, you'd need a constructor expression which AFAIR is not allowed in native queries. I'll have to look into this. As a workaround, can you just switch to a projection interface exposing getters instead? That should allow the projecting conversion to work properly

[spring-projects-issues]

Eugene Nikiforov commented

Yes, projection way now works well (It hadn't worked before your fix), I'm sure I could go forward with this approach. Nevertheless, I'm not sure, that it is perfect that JPA standard annotation doesn't work, because of some spring data assumptions

[spring-projects-issues]

Eugene Nikiforov commented

Short update:

I found another approach that currently works fine without removing and reimplementing major parts of code:

We still use class projections, namedNativeQueries and SqlResultSetMapping, so the main structure would be the same as was described in issue description earlier, the only thing to change is repository function definition

@Query
    fun <T> totalSummary(@Param("user_id") userId: Long, beanProjection: Class<T>): List<T>

so two major things should be done:

• projection (either interface or class one) is provided
• named query mapping is defined (repository's function has same name as defined in @NamedNativeQuery definition, so it could be matched by name in spring data's internal)

After these steps projections will work fine with @SQLResultSetMapping, class based DTO's and constructor mapping

[spring-projects-issues]

Oliver Drotbohm commented

That's useful information Eugene. Is there a chance you collect this into an – ideally Java based – example project that shows the different approaches (working / not-working) side by side? Basically a repository with two or three query methods, an entity with the queries declared as you have and a DTO class and interface?

[spring-projects-issues]

Eugene Nikiforov commented

Sure, Oliver, when I'll finish it I'll come back

[spring-projects-issues]

Eugene Nikiforov commented

Oliver, I added sample project (based on Java 8) https://github.com/EugeneNik/spring-data-datajpa-1280-example and provided tests with different situations.

There are 2 entities with same fields and 2 projections (1 interface based and 1 class based). I used 2 entities just not to declare every kind of mapping in 1 class.

Actually, I think you could notice several issues with projection mappings.

If I could help you in some other ways or you'll need some more details, please, let me know

[spring-projects-issues]

Craig commented

I believe this is the same issue reported at https://stackoverflow.com/questions/49056084/got-different-size-of-tuples-and-aliases-exception-after-spring-boot-2-0-0-rel (hopefully that's helpful information)

[spring-projects-issues]

Hamid Virani commented

I am getting the same issue as mentioned in the stackoverflow link

[spring-projects-issues]

Miguel Martín-Forero commented

This is happening also with version 1.11.11.RELEASE

[spring-projects-issues]

Igor Zboichik commented

Seems to work fine, when in repo List without generic is returned (https://stackoverflow.com/a/49985099/2262442). 

[spring-projects-issues]

Oliver Drotbohm commented

This is now fixed but the original erroneous examples needs a few tweaks to work:

• upgrade to latest snapshots of Spring Data (temporary)
• in the @Query annotations nativeQuery needs to be set to true
• the @SqlResultSetMapping needs to use a constructor results to let Hibernate create instances of IdAndDescription

We've just pushed an example to our Spring Data Examples for reference. See the README for details

[spring-projects-issues]

Eugene Nikiforov commented

Oliver Drotbohm Thank you so much. It works, but I have one question here, Do we really need the second step in future or it will be improved? (I could work on this enhancement voluntarily for the future release)

Just my thoughts - when we are fetching declared Query by name it means that we will try to gather all defined NamedQueries on the entity and as far as they are defined by annotations we know is it native or not... (NamedNativeQuery vs NamedQuery), so do we really need to put this extra nativeQuery=true in Repository level to start mapping that could be start just with "annotation class". Maybe I don't cover some scenarios with this thoughts, so please, feel free to correct me

[spring-projects-issues]

Oliver Drotbohm commented

…and as far as they are defined by annotations we know is it native or not…

Unfortunately, we don't. We don't scan any JPA annotations (as named queries can also be defined in XML), we just call EntityManager.createNamedQuery(…) and the returned instance unfortunately doesn't indicate whether it's native or not

[spring-projects-issues]

Eugene Nikiforov commented

Ahh...That makes sense, sorry for inconvenience, but anyway I believe these comments will be useful for further users, who will investigate it. Thank you for clarification

[spring-projects-issues]

Oliver Drotbohm commented

Indeed. I'll see what we can do in whenever JPA.next is going to be worked on. Would be a useful addition to the EntityManager API to be able to look that information up