Having this entity:
data class Avatar(
// id is generated by postgres default value
@Id
val id: UUID? = null,
val userId: Long,
var avatar: ByteArray,
)
and this schema:
CREATE TABLE avatar (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id bigint NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
avatar bytea NOT NULL
);
And this repository:
@Repository
interface AvatarRepository : CoroutineCrudRepository<Avatar, UUID> {
@Query("SELECT user_id FROM avatar WHERE id = :avatarId")
suspend fun findUserIdById(avatarId: UUID): Long?
}
I can save and fetch entities, but when removing the @Query I cannot use the findUserIdById to only load the userId attribute:
internal class AvatarRepositoryTest : RepositoryTest() {
@Autowired
private lateinit var avatarRepository: AvatarRepository
@Test
fun findUserIdById() {
setupDevData()
val expectedUserId = 1L
val bytes = byteArrayOf(0x2E)
runBlocking {
val stored = avatarRepository.save(Avatar(
id = null,
userId = expectedUserId,
avatar = bytes,
))
assertThat(stored.id).isNotNull
// ----------> the next line fails WITHOUT THE @Query("...") annotation <----------
val actual = avatarRepository.findUserIdById(stored.id!!)
assertThat(actual).isEqualTo(expectedUserId)
}
}
}
Results in this error:
Failed to convert from type [io.r2dbc.postgresql.PostgresqlRow] to type [java.lang.Long] for value 'PostgresqlRow{context=ConnectionContext{client=io.r2dbc.postgresql.client.ReactorNettyClient@8e0755, codecs=io.r2dbc.postgresql.codec.DefaultCodecs@7fecd70a, connection=PostgresqlConnection{client=io.r2dbc.postgresql.client.ReactorNettyClient@8e0755, codecs=io.r2dbc.postgresql.codec.DefaultCodecs@7fecd70a}, configuration=PostgresqlConnectionConfiguration{applicationName='r2dbc-postgresql', autodetectExtensions='true', compatibilityMode=false, connectTimeout=null, database='db', extensions=[], fetchSize=io.r2dbc.postgresql.PostgresqlConnectionConfiguration$Builder$$Lambda$862/0x000000080107d440@6974166c, forceBinary='false', host='localhost', loopResources='null', options='{}', password='****', port=49416, preferAttachedBuffers=false, socket=null, tcpKeepAlive=false, tcpNoDelay=true, username='test'}, portalNameSupplier=io.r2dbc.postgresql.DefaultPortalNameSupplier@6bfe38c7, statementCache=IndefiniteStatementCache{cache={SELECT avatar.id, avatar.user_id, avatar.avatar FROM avatar WHERE avatar.id = $1={[I@1b3a3e42=S_0}}, counter=1}}, columns=[Field{column=1, dataType=2950, dataTypeModifier=-1, dataTypeSize=16, format=FORMAT_TEXT, name='id', table=16419}, Field{column=2, dataType=20, dataTypeModifier=-1, dataTypeSize=8, format=FORMAT_TEXT, name='user_id', table=16419}, Field{column=3, dataType=17, dataTypeModifier=-1, dataTypeSize=-1, format=FORMAT_TEXT, name='avatar', table=16419}], isReleased=false}'; nested exception is java.lang.ClassCastException: class java.util.UUID cannot be cast to class java.lang.Number (java.util.UUID and java.lang.Number are in module java.base of loader 'bootstrap')
org.springframework.core.convert.ConversionFailedException: Failed to convert from type [io.r2dbc.postgresql.PostgresqlRow] to type [java.lang.Long] for value 'PostgresqlRow{context=ConnectionContext{client=io.r2dbc.postgresql.client.ReactorNettyClient@8e0755, codecs=io.r2dbc.postgresql.codec.DefaultCodecs@7fecd70a, connection=PostgresqlConnection{client=io.r2dbc.postgresql.client.ReactorNettyClient@8e0755, codecs=io.r2dbc.postgresql.codec.DefaultCodecs@7fecd70a}, configuration=PostgresqlConnectionConfiguration{applicationName='r2dbc-postgresql', autodetectExtensions='true', compatibilityMode=false, connectTimeout=null, database='db', extensions=[], fetchSize=io.r2dbc.postgresql.PostgresqlConnectionConfiguration$Builder$$Lambda$862/0x000000080107d440@6974166c, forceBinary='false', host='localhost', loopResources='null', options='{}', password='****', port=49416, preferAttachedBuffers=false, socket=null, tcpKeepAlive=false, tcpNoDelay=true, username='test'}, portalNameSupplier=io.r2dbc.postgresql.DefaultPortalNameSupplier@6bfe38c7, statementCache=IndefiniteStatementCache{cache={SELECT avatar.id, avatar.user_id, avatar.avatar FROM avatar WHERE avatar.id = $1={[I@1b3a3e42=S_0}}, counter=1}}, columns=[Field{column=1, dataType=2950, dataTypeModifier=-1, dataTypeSize=16, format=FORMAT_TEXT, name='id', table=16419}, Field{column=2, dataType=20, dataTypeModifier=-1, dataTypeSize=8, format=FORMAT_TEXT, name='user_id', table=16419}, Field{column=3, dataType=17, dataTypeModifier=-1, dataTypeSize=-1, format=FORMAT_TEXT, name='avatar', table=16419}], isReleased=false}'; nested exception is java.lang.ClassCastException: class java.util.UUID cannot be cast to class java.lang.Number (java.util.UUID and java.lang.Number are in module java.base of loader 'bootstrap')
at org.springframework.core.convert.support.ConversionUtils.invokeConverter(ConversionUtils.java:47)
at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:192)
at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:175)
at org.springframework.data.r2dbc.convert.MappingR2dbcConverter.read(MappingR2dbcConverter.java:116)
at org.springframework.data.r2dbc.convert.EntityRowMapper.apply(EntityRowMapper.java:46)
at org.springframework.data.r2dbc.repository.query.AbstractR2dbcQuery.lambda$executeQuery$2(AbstractR2dbcQuery.java:111)
at io.r2dbc.postgresql.PostgresqlResult.lambda$map$1(PostgresqlResult.java:111)
at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:102)
at reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onNext(MonoFlatMapMany.java:250)
at org.springframework.security.test.context.support.ReactorContextTestExecutionListener$DelegateTestExecutionListener$SecuritySubContext.onNext(ReactorContextTestExecutionListener.java:120)
at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:184)
at reactor.core.publisher.FluxFilterFuseable$FilterFuseableConditionalSubscriber.onNext(FluxFilterFuseable.java:337)
at reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onNext(FluxContextWrite.java:107)
at reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onNext(FluxPeekFuseable.java:854)
at io.r2dbc.postgresql.util.FluxDiscardOnCancel$FluxDiscardOnCancelSubscriber.onNext(FluxDiscardOnCancel.java:86)
at reactor.core.publisher.FluxCreate$BufferAsyncSink.drain(FluxCreate.java:793)
at reactor.core.publisher.FluxCreate$BufferAsyncSink.next(FluxCreate.java:718)
at reactor.core.publisher.FluxCreate$SerializedFluxSink.next(FluxCreate.java:154)
at io.r2dbc.postgresql.client.ReactorNettyClient$Conversation.emit(ReactorNettyClient.java:735)
at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.emit(ReactorNettyClient.java:986)
at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:860)
at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:767)
at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:118)
at reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onNext(FluxPeekFuseable.java:854)
at reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:220)
at reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:220)
at reactor.netty.channel.FluxReceive.drainReceiver(FluxReceive.java:280)
at reactor.netty.channel.FluxReceive.onInboundNext(FluxReceive.java:389)
at reactor.netty.channel.ChannelOperations.onInboundNext(ChannelOperations.java:401)
at reactor.netty.channel.ChannelOperationsHandler.channelRead(ChannelOperationsHandler.java:94)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)
at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:311)
at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:432)
at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:276)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
at io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:795)
at io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:480)
at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:378)
at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)
at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.base/java.lang.Thread.run(Thread.java:832)
Caused by: java.lang.ClassCastException: class java.util.UUID cannot be cast to class java.lang.Number (java.util.UUID and java.lang.Number are in module java.base of loader 'bootstrap')
at org.springframework.data.r2dbc.convert.R2dbcConverters$RowToNumberConverterFactory$RowToNumber.convert(R2dbcConverters.java:191)
at org.springframework.data.r2dbc.convert.R2dbcConverters$RowToNumberConverterFactory$RowToNumber.convert(R2dbcConverters.java:178)
at org.springframework.core.convert.support.GenericConversionService$ConverterFactoryAdapter.convert(GenericConversionService.java:437)
at org.springframework.core.convert.support.ConversionUtils.invokeConverter(ConversionUtils.java:41)
... 50 more
I thought this is supported?
It is not possible to select specific columns by specifying them in the query as in your example find[UserId]By.... That syntax is not supported.
You need to use a @Query as you already showed or a projection:
data class AvatarUserId(
val userId: Long
)
@Query("SELECT user_id FROM avatar WHERE id = :avatarId") // <-- still required in kotlin
suspend fun findById(id: UUID): AvatarUserId?
However, this would work in Kotlin only if all non-fetched fields of the domain class are nullable, because the result mapping for dto projections first instantiates the actual domain class with the limited data selected as defined by the DTO. This effectively sets non-fetched fields null which is not allowed in Kotlin if they are non-null. This would still be possible in java because it is not null-sage - but I would consider that a hacky trick.
However, by setting the @Query the query type changes and the result mapping strategy will not first instantiate the domain entity but immediately the DTO. In the specific example at hand, that looks overengineered to use the DTO projection then, but for reference it is good to know.
see also
Result mapping for DTO projections depends on the actual query type. Derived queries use the domain type to map results, and Spring Data creates DTO instances solely from properties available on the domain type. Declaring properties in your DTO that are not available on the domain type is not supported.
[...]
DTO projections used with query methods annotated with @Query map query results directly into the DTO type. Field mappings on the domain type are not considered. Using the DTO type directly, your query method can benefit from a more dynamic projection that isn’t restricted to the domain model.