Average date difference in querydsl-jpa / querydsl-sql?

[Etienne Neveu]

Hi everyone :)

We introduced QueryDSL in our project a few months ago, and we love it. It's just so much better than the Criteria API or building JPQL queries using String concatenation. But we now need to write more complex queries, and we are puzzled.

I created a small project to demonstrate what we are trying to accomplish, in a simplified manner (the real query is a lot more complex, with tons of joins / where / sort clauses). We have a Customer class with a birthDate field, and we are trying to get the average age, in seconds, of our customers. We also want the maximum age, but let's focus on the average for this post.

I tried writing this query using querydsl-jpa, but it fails with an obscure error:

java.lang.NullPointerException

at org.hibernate.dialect.function.StandardAnsiSqlAggregationFunctions$AvgFunction.determineJdbcTypeCode(StandardAnsiSqlAggregationFunctions.java:106)

at org.hibernate.dialect.function.StandardAnsiSqlAggregationFunctions$AvgFunction.render(StandardAnsiSqlAggregationFunctions.java:100)

at org.hibernate.hql.internal.ast.SqlGenerator.endFunctionTemplate(SqlGenerator.java:233)

[...]

We also tried other approaches, like using NumberTemplate.create(Double.class, "{0} - {1}", DateExpression.currentDate(), customer.birthDate).avg(), but it doesn't return the correct value. If we want to get a date difference in seconds, it seems we need to find some way of calling the database-specific date/time difference functions, not just use the minus sign.

Sadly, computing a date difference doesn't seem to be possible in JPQL, so I guess querydsl-jpa has limitations there too. So we would have to write a native SQL query, or find some hack to have the QueryDsl-generated JPQL call a native database function.

JPA 2.1 added support for invoking database functions, but there is a problem: the MySQL function takes the form TIMESTAMPDIFF(SECOND, '2012-06-06 13:13:55', '2012-06-06 15:20:18'). It would probably be possible if the first parameter (SECOND) was a String, but it seems to be a reference to some kind of constant, and it seems complicated to generate JPQL with the first parameter unquoted.

I see QueryDSL added support for date differences, but it seems most of the code resides in the querydsl-sql project.

Here are my questions:

1) Is it possible to compute the average date difference using querydsl-jpa, having it maybe call the native database functions using JPA 2.1 support (maybe using Expressions.numberTemplate())? Or are we forced to use querydsl-sql?

2) If we have to use querydsl-sql, how do we generate both QCustomer and SCustomer? QCustomer is currently generated from the Customer entity using the plugin "com.mysema.maven:apt-maven-plugin". If I understood correctly, I have to use a different plugin (com.querydsl:querydsl-maven-plugin) to generate the SCustomer query type?

When looking at querydsl-sql-example, I don't see any entity class, so I guess the query types are generated by QueryDSL from the database schema? Is there a way to generate the SCustomer query type from the entity instead, like we do with querydsl-jpa?

3) If we use querydsl-sql, is there a way to "re-use" our querydsl-jpa predicates / sorts / joins clauses in the querydsl-sql query? Or do we have to duplicate that code using querydsl-sql-specific classes?

4) Am I missing something? Is there a simpler way of doing what I'm trying to do?

Thanks a lot for reading this, and for any advice you might provide.

- Etienne

[timowest]

Hi Etienne.

Using template expressions you should be able to inject any custom JPQL snippets into the Querydsl query. That should answer your first question.

Using both querydsl-jpa and querydsl-sql in the same project is possible, but adds some complexity.

Timo

[Venkatesh Dhandapani]

NumberTemplate

                        .create(Double.class, "FUNCTION(\'TIMESTAMPDIFF\',\'MINUTE\',{0},{1})", qFulfillment.pickedDate,

                                qFulfillmentPickupConfirmationEntity.pickedUpDate).min().divide(60).as(MIN_PICKUP_HR)

[Venkatesh Dhandapani]

compile("com.mysema.querydsl:querydsl-sql:3.7.4") in build.gradle

        QFulfillmentEntity qFulfillment = QFulfillmentEntity.fulfillmentEntity;

        QFulfillmentPickupConfirmationEntity qFulfillmentPickupConfirmationEntity = QFulfillmentPickupConfirmationEntity.fulfillmentPickupConfirmationEntity;

        QFulfillmentAuditEntity qFulfillmentAuditEntity = QFulfillmentAuditEntity.fulfillmentAuditEntity;

        LocalDateTime now = LocalDateTime.now();

        LocalDateTime midnightToday = LocalDateTime.of(now.toLocalDate(), LocalTime.MIDNIGHT);

        //JPQLQuery query = createQueryObject(context, context.getOrganization());

        JPASQLQuery query = null;

        if(dataSourceProperties.getPlatform().equals("h2"))

        {

            query = new JPASQLQuery(entityManager, H2Templates.DEFAULT);

        }

        else if(dataSourceProperties.getPlatform().equals("mysql"))

        {

            query = new JPASQLQuery(entityManager, MySQLTemplates.DEFAULT);

        }

        else if(dataSourceProperties.getPlatform().equals("oracle"))

        {

            query = new JPASQLQuery(entityManager, OracleTemplates.DEFAULT);

        }

        NumberExpression<Double> minPickupHr = new NumberPath<Double>(Double.class, MIN_PICKUP_HR);

        NumberExpression<Double> maxPickupHr = new NumberPath<Double>(Double.class, MAX_PICKUP_HR);

        NumberExpression<Double> avgPickupHr = new NumberPath<Double>(Double.class, AVG_PICKUP_HR);

        NumberExpression<Double> timeDiffExpr = NumberOperation.create(Double.class,Ops.DateTimeOps.DIFF_MINUTES, qFulfillmentAuditEntity.pickedDate, qFulfillmentAuditEntity.pickedDate);

       /* DateTimeExpression<Long> minPickupHr = new DateTimePath<Long>(Long.class, MIN_PICKUP_HR);

        DateTimeExpression<Long> maxPickupHr = new DateTimePath<Long>(Long.class, MAX_PICKUP_HR);

        DateTimeExpression<Long> avgPickupHr = new DateTimePath<Long>(Long.class, AVG_PICKUP_HR);

        DateTimeExpression<Long> dateTimeExpression = Expressions.dateTimeOperation(Long.class, Ops.DateTimeOps.DIFF_MINUTES,qFulfillmentAuditEntity.pickedDate, qFulfillmentPickupConfirmationEntity.pickedUpDate);*/

        long queryExecutionStart = System.currentTimeMillis();

        List<StoreOrderTimeMetricDTO> storeOrderTimeMetricDTOS = new ArrayList<>();

        List<Tuple> timePerStoreLocation = query.from(qFulfillment).groupBy(qFulfillment.shipFromLocationId,qFulfillmentPickupConfirmationEntity.pickedUpDate,qFulfillmentAuditEntity.pickedDate)

                .join(qFulfillmentPickupConfirmationEntity)

                //.on(qFulfillmentPickupConfirmationEntity.parentFulfillment.pk.as("qFulfillmentPickupConfirmationEntity.FULFILLMENT_PK").eq(qFulfillment.pk))

                .join(qFulfillmentAuditEntity).on(qFulfillmentAuditEntity.fulfillmentId.eq(qFulfillment.fulfillmentId))

                .where(qFulfillment.shipFromLocationId.in(storesDTO.getStoreIdList())

                        .and(qFulfillment.deliveryMethodId.eq(OrderDeliveryMethod.PickUpAtStore.name())

                                .or(qFulfillment.deliveryMethodId.eq(OrderDeliveryMethod.ShipToStore.name())

                                        .and(qFulfillment.destinationActionId

                                                .eq(OrderDestinationAction.Pickup.name()))))

                        .and(qFulfillment.maxStatusId.eq(FulfillmentStatus.PICKED_UP.getValue()))

                        .and(qFulfillment.minStatusId.eq(FulfillmentStatus.PICKED_UP.getValue()))

                        .and(qFulfillmentPickupConfirmationEntity.pickedUpDate.between(midnightToday, now)))

                .list(qFulfillment.shipFromLocationId, timeDiffExpr.min().divide(60).as(MIN_PICKUP_HR), timeDiffExpr.max().divide(60).as(MAX_PICKUP_HR),  timeDiffExpr.avg().divide(60).as(AVG_PICKUP_HR));

        long query1ExecutionTime = System.currentTimeMillis() - queryExecutionStart;

        logger.info(() -> MessageFormat

                .format("-- getCustomerPickUpTimeSincePicking() elapseTime(): {0} millsecs ", query1ExecutionTime));

        /*constructTimeMetricDTOs(qFulfillment, minPickupHr, maxPickupHr, avgPickupHr, storeOrderTimeMetricDTOS,

                timePerStoreLocation);*/

        logger.methodExit(context, "getCustomerPickUpTimeSincePicking", storeOrderTimeMetricDTOS, null);

        return storeOrderTimeMetricDTOS;