spring-bootjpaspring-data-jpacriteriaquery

JPA CriteriaQuery Unable to locate appropriate constructor for sum of date diffrerence


I know this question look similar, but I think for me the case is different

this is the exception message

Exception while fetching data (/periodicReport/userReport) : org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [com.analytics.entity.projections.UserParticipantCountAndDurationImpl]. Expected arguments are: java.util.UUID, long, java.util.Date [select new com.analytics.entity.projections.UserParticipantCountAndDurationImpl(generatedAlias0.userID, count(distinct generatedAlias0.userID), sum(function('TIMEDIFF', generatedAlias0.endTime, generatedAlias0.startTime))) from com.analytics.entity.ConferenceParticipant as generatedAlias0 group by generatedAlias0.userID order by :param0 desc]

I'm expecting the sum to be Long and I have specified it in the criteria query. I don't have any idea where Date is defined

the snipet for the buider

Expression<Long> sum = criteriaBuilder.sum(
            criteriaBuilder.function(
                    "TIMEDIFF",
                    Long.class,
                    participantRoot.<Date>get("endTime"),
                    participantRoot.<Date>get("startTime")
            )
    );
    Expression<Long> count = criteriaBuilder.countDistinct(participantRoot.get("userID"));
    reportQuery.select(criteriaBuilder.construct(
            UserParticipantCountAndDurationImpl.class,
            participantRoot.get("userID").as(UUID.class).alias(USER_ID),
            count.as(Long.class).alias(PARTICIPANTS),
            sum.as(Long.class).alias(PARTICIPANT_DURATION)
    ));

the class in question

@Data
@NoArgsConstructor
public class UserParticipantCountAndDurationImpl implements UserParticipantCountAndDuration, Serializable {

   private UUID userID;

   private long participants;

   public long participantDuration;

   public UserParticipantCountAndDurationImpl(
           UUID userID,
           long participants,
           long participantDuration
   ) {
       this.userID = userID;
       this.participants = participants;
       this.participantDuration = participantDuration;
   }
}

And yes I have tried to change signature of the constructor. in that case the query will run, but then thows CastException since it java.util.Date


Solution

  • TIMEDIFF return date time as the result, so it cant be casted to Long or double, the the Sql object in the raw result is DateTime, refer this. I can solve this by using a function to get minutes from the datetime. but I solved this by getting difference of UNIXTIMESTAMP for both the datetime for the summation. Since UNIXTTIMESTAMP is always long I can cast it to Long or BigInteger