conversion of LocalDate to entityview is failing about blaze-persistence

[beikov]

Thanks for the report and the PR with a fix. Can you please share how you actually ran into this problem? Are you MULTISET fetching an entity view that contains a LocalDate?

[rajadilipkolli]

Thanks for the report and the PR with a fix. Can you please share how you actually ran into this problem? Are you MULTISET fetching an entity view that contains a LocalDate?

Hi @beikov , yes you are right I faced this issue while I'm using MULTISET fetching

Below is the entitiyviews that I am using. Noticed that I am facing the similar issue with LocalDateTime as well. We are saving the date with timezone in database. So this might happen with OffSetDateTime as well.

I saw workaround available label, may I know what is the workaround so that I am implement in my solution as it is blocking from using entityViews with MULTISET

[beikov]

You can register a custom BasicUserType, also see the documentation.

[beikov]

Can you also please share the database you are using and the SQL query that is produced?

[rajadilipkolli]

I am using Postgres 16,

below is the query and stack trace

2024-01-30T01:23:24.347+05:30 DEBUG 28764 --- [mfscreener] [nio-8080-exec-3] [65b8022921c87af3955eed064ce1284f-74cf3583b16c4896] datasource-query-logger                  : Name:appdb, Connection:13, Time:23, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["select uce1_0.id,uce1_0.cas_type,uce1_0.created_by,uce1_0.created_date,uce1_0.file_type,(select json_agg(json_build_object('f0','' || fe1_0.id,'f1','' || fe1_0.amc,'f2','' || fe1_0.created_by,'f3','' || fe1_0.created_date,'f4','' || fe1_0.folio,'f5','' || fe1_0.kyc,'f6','' || fe1_0.last_modified_by,'f7','' || fe1_0.last_modified_date,'f8','' || fe1_0.pan,'f9',(select json_agg(json_build_object('f0','' || se1_0.id,'f1','' || se1_0.advisor,'f2','' || se1_0.amfi,'f3','' || se1_0.close,'f4','' || se1_0.close_calculated,'f5','' || se1_0.created_by,'f6','' || se1_0.created_date,'f7','' || se1_0.isin,'f8','' || se1_0.last_modified_by,'f9','' || se1_0.last_modified_date,'f10','' || se1_0.open,'f11','' || se1_0.rta,'f12','' || se1_0.rta_code,'f13','' || se1_0.scheme,'f14',(select json_agg(json_build_object('f0','' || te1_0.id,'f1','' || te1_0.amount,'f2','' || te1_0.balance,'f3','' || te1_0.created_by,'f4','' || te1_0.created_date,'f5','' || te1_0.description,'f6','' || te1_0.dividend_rate,'f7','' || te1_0.last_modified_by,'f8','' || te1_0.last_modified_date,'f9','' || te1_0.nav,'f10','' || te1_0.transaction_date,'f11','' || te1_0.type,'f12','' || te1_0.units)) from user_transaction_details te1_0 where se1_0.id=te1_0.user_scheme_detail_id),'f15','' || se1_0.type)) from user_scheme_details se1_0 where fe1_0.id=se1_0.user_folio_id))) from user_folio_details fe1_0 where uce1_0.id=fe1_0.user_cas_details_id),iie1_0.user_cas_details_id,iie1_0.address,iie1_0.created_by,iie1_0.created_date,iie1_0.email,iie1_0.last_modified_by,iie1_0.last_modified_date,iie1_0.mobile,iie1_0.name,uce1_0.last_modified_by,uce1_0.last_modified_date from user_cas_details uce1_0 join investor_info iie1_0 on uce1_0.id=iie1_0.user_cas_details_id where iie1_0.email=? and iie1_0.name=?"], Params:[([email protected],K Raja Dilip Chowdary)]
2024-01-30T01:23:32.415+05:30 ERROR 28764 --- [mfscreener] [nio-8080-exec-3] [                                                 ] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessApiUsageException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]] with root cause

2024-01-30 01:22:54.725343+05:30

java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
	at java.sql/java.sql.Timestamp.valueOf(Timestamp.java:236)
	at com.blazebit.persistence.view.impl.type.LocalDateTimeBasicUserType.fromString(LocalDateTimeBasicUserType.java:36)
	at com.blazebit.persistence.view.impl.type.LocalDateTimeBasicUserType.fromString(LocalDateTimeBasicUserType.java:30)
	at com.blazebit.persistence.view.impl.objectbuilder.transformer.MultisetTupleTransformer.transform(MultisetTupleTransformer.java:78)
	at com.blazebit.persistence.view.impl.objectbuilder.transformator.TupleTransformator.transform(TupleTransformator.java:84)
	at com.blazebit.persistence.view.impl.objectbuilder.transformator.TupleTransformator.transform(TupleTransformator.java:77)
	at com.blazebit.persistence.view.impl.objectbuilder.ChainingObjectBuilder.build(ChainingObjectBuilder.java:51)
	at com.blazebit.persistence.impl.builder.object.PreProcessingObjectBuilder.build(PreProcessingObjectBuilder.java:46)
	at com.blazebit.persistence.impl.query.ObjectBuilderTypedQuery.getResultList(ObjectBuilderTypedQuery.java:71)
	at com.blazebit.persistence.impl.query.ObjectBuilderTypedQuery.getSingleResult(ObjectBuilderTypedQuery.java:49)
	at com.blazebit.persistence.impl.AbstractQueryBuilder.getSingleResult(AbstractQueryBuilder.java:63)
	at com.learning.mfscreener.repository.CustomUserCASDetailsEntityRepositoryImpl.findByInvestorEmailAndName(CustomUserCASDetailsEntityRepositoryImpl.java:37)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:277)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:516)
	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:285)
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:628)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:168)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at com.blazebit.persistence.spring.data.repository.EntityViewReplacingMethodInterceptor.invoke(EntityViewReplacingMethodInterceptor.java:52)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:385)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at com.blazebit.persistence.spring.data.base.repository.EntityViewAwareCrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(EntityViewAwareCrudMethodMetadataPostProcessor.java:143)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220)
	at jdk.proxy3/jdk.proxy3.$Proxy216.findByInvestorEmailAndName(Unknown Source)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220)
	at jdk.proxy3/jdk.proxy3.$Proxy216.findByInvestorEmailAndName(Unknown Source)
	at com.learning.mfscreener.service.PortfolioService.findDelta(PortfolioService.java:99)
	at com.learning.mfscreener.service.PortfolioService.upload(PortfolioService.java:61)
	at com.learning.mfscreener.web.controllers.PortfolioController.upload(PortfolioController.java:33)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
	at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:174)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:765)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:717)
	at com.learning.mfscreener.web.controllers.PortfolioController$$SpringCGLIB$$0.upload(<generated>)

[beikov]

Thanks a lot for sharing. As far as I can say, we'd need to use a DateTimeFormatter with an optional time part in the LocalDateBasicUserType and DateBasicUserType, since people could potentially map a LocalDate/Date also to a timestamp.

[rajadilipkolli]

It applies to both LocalDateTimeBasicUserType and OffsetDateTimeBasicUserType as well, should we use ISO formats for all with DateTimeFormatter as it ignores timezone?

[rajadilipkolli]

Thanks a lot for sharing. As far as I can say, we'd need to use a DateTimeFormatter with an optional time part in the LocalDateBasicUserType and DateBasicUserType, since people could potentially map a LocalDate/Date also to a timestamp.

Hi @beikov , Should I update the PR to handle like below

 CharSequence sequence = "2024-01-30";
        if (sequence.toString().length() > 10) {
            LocalDate.parse(sequence, DateTimeFormatter.ISO_LOCAL_DATE_TIME);
        } else {
            LocalDate parse = LocalDate.parse(sequence, formatter);
        }

This handles both time and non time aspect as well?