search

Blazebit / blaze-persistence

Rich Criteria API for JPA providers
https://persistence.blazebit.com
Apache License 2.0
697 stars 85 forks source link

conversion of LocalDate to entityview is failing #1864

Open rajadilipkolli opened 4 months ago

rajadilipkolli commented 4 months ago

Description

Date values when mapped to @Entityview Localdate is failing

Expected behavior

Expecting the value in database column to be converted to LocalDate when provided with correct value. Code is using Timestamp.valueOf(sequence.toString()) to convert a string but Timestamp.valueOf expects string to be in format yyyy-[m]m-[d]d hh:mm:ss[.f...] which is causing the problem

Actual behavior

Attaching debugging screen shot which shows the valid input. Capture_localdate

Steps to reproduce

Environment

Version: 1.6.11 JPA-Provider: Hibernate 6.4.2.Final DBMS: Postgres 16 Application Server: Spring boot 3.2.2

beikov commented 4 months ago

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 commented 4 months ago

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 commented 4 months ago

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

beikov commented 4 months ago

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

rajadilipkolli commented 4 months ago

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:[(rajadilipkolli@gmail.com,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 commented 4 months ago

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 commented 4 months ago

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

rajadilipkolli commented 4 months ago

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?

roma2341 commented 2 days ago

Default basic types are broken. Here is fix for some I had issues with:

public class CustomDateBasicUserType implements BasicUserType<Date>, VersionBasicUserType<Date> {
    public static final BasicUserType<?> INSTANCE = new CustomDateBasicUserType();

    public CustomDateBasicUserType() {
    }

    public boolean isMutable() {
        return true;
    }

    public boolean supportsDirtyChecking() {
        return false;
    }

    public boolean supportsDirtyTracking() {
        return false;
    }

    public boolean supportsDeepEqualChecking() {
        return true;
    }

    public boolean supportsDeepCloning() {
        return true;
    }

    public boolean isEqual(Date initial, Date current) {
        return initial.getTime() == current.getTime();
    }

    public boolean isDeepEqual(Date initial, Date current) {
        return initial.getTime() == current.getTime();
    }

    public int hashCode(Date object) {
        return object.hashCode();
    }

    public boolean shouldPersist(Date entity) {
        return false;
    }

    public String[] getDirtyProperties(Date entity) {
        return DIRTY_MARKER;
    }

    public Date deepClone(Date object) {
        return (Date)object.clone();
    }

    public Date nextValue(Date current) {
        return new Date();
    }

    public Date fromString(CharSequence sequence) {
        if (sequence.toString().length() > 10) {
             return Timestamp.valueOf(sequence.toString());
        } else {
            return Date.from(Instant.from(LocalDate.parse(sequence, DateTimeFormatter.ISO_LOCAL_DATE)
                    .atStartOfDay().atZone(ZoneId.systemDefault())));
        }
    }

    public String toStringExpression(String expression) {
        return "TO_CHAR(" + expression + ", 'YYYY-MM-DD HH24:MI:SS.US')";
    }
}

public class CustomBigIntegerBasicUserType extends ImmutableBasicUserType<BigInteger> {
    public static final BasicUserType<BigInteger> INSTANCE = new CustomBigIntegerBasicUserType();

    public CustomBigIntegerBasicUserType() {
    }

    public BigInteger fromString(CharSequence sequence) {
        return new BigDecimal(sequence.toString()).toBigInteger();
    }

    public String toStringExpression(String expression) {
        return expression;
    }
}

register them like this:

public EntityViewManager createEntityViewManager(CriteriaBuilderFactory cbf,
                                                     EntityViewConfiguration entityViewConfiguration) {
        entityViewConfiguration.registerBasicUserType(BigInteger.class,
                new CustomBigIntegerBasicUserType());
        entityViewConfiguration.registerBasicUserType(Date.class,
                new CustomDateBasicUserType());
        return entityViewConfiguration.createEntityViewManager(cbf);
    }