Using Querydsl to execute/bind external SQL-strings

[Samppa Saarela]

Hi,

In some cases supporting different databases requires structurally very different queries. This is typically the case with reports using advanced (e.g. analytic) functions not supported by e.g. H2. Quite often these queries are just constant strings with either no parameters or just a few know parameters. There might exist a cross-db variants of these queries, but those are far from optimal. Of course one may skip Querydsl all together and use e.g. plain JDB, but maybe it would be beneficial to be able to take advantage of Querydsl's execution logic? So, what would it take to override Querydsl's SQL serialization? Or is this totally stupid idea?

Br, 

  Samppa

[timowest]

Hi.

To me this sounds like an extension to query flag idea. In addition to the already existing positions there could be a full override:

query.addFlag(Position.QUERY_OVERRIDE, "select ...");

[Samppa Saarela]

That actually sounds like a very cool feature! You could implement a cross-db query using fluent api and then database-specific optimizations by overriding serialization - or the other way around - by just retaining parameter positions and select-fields. Would it be possible to skip basic serialization all together in case QUERY_OVERRIDE is used?

Shall I make a feature request for this? 

Br

  Samppa

[Esko Luontola]

On Thursday, 18 August 2016 10:30:37 UTC+3, Samppa Saarela wrote:

by just retaining parameter positions and select-fields.

It can be too much to require the parameter positions to stay the same. Here is an example of PostgreSQL-specific query and its QueryDSL/H2 alternative. The former is just one query with one parameter, the latter is two queries with the second query having lots of parameters:

https://github.com/HSLdevcom/parkandrideAPI/blob/656763b8ea99d045cf71b52c91fded4324b0db55/application/src/main/java/fi/hsl/parkandride/back/UtilizationDao.java#L112-L161

[Samppa Saarela]

In findLatestUtilizationH2, you're essentially making the pricing/utilization-join in Java-code. There exists a way to do that in basic SQL (e.g. http://stackoverflow.com/a/3800572). That leaves us with an in-clause having unspecified number of elements. It should be possible to apply that in-clause in an outer select and use QUERY_OVERRIDE in a subquery, but I guess it depends on DB's query optimizer how efficient it is. And of course you could always construct a proper replacement SQL with correct number of ?'s programmatically. But, yeah, while it would allow much more room for DB-specific optimization, not all possible optimizations could be achieved with this trick.

[Samppa Saarela]

https://github.com/querydsl/querydsl/issues/2004

On Tuesday, August 16, 2016 at 9:09:18 PM UTC+3, timowest wrote: