Average date difference using querydsl-jpa / querydsl-sql

Question

I'm trying to compute an average date difference using QueryDSL.

I created a small project to demonstrate what I'm 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)
        [...]

I 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.

QueryDSL added support for date differences, but it seems most of the code resides in the querydsl-sql project, so I'm wondering if I can benefit from this with querydsl-jpa.

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. I'm also considering creating a database function that delegates to TIMESTAMPDIFF(SECOND, x, y), but it's not very portable...

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

Answer 1

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.