Mixing JPA + Native SQL

[Derek Meyer]

Hello all,

I'm new to Querydsl, I've been using Spring Data on my client's project so far, and found Querydsl through the Spring Data docs. So before I get into my questions I'd like to say thank you to any contributors - Querydsl is pretty awesome!

Project Environment:

SQL Server 2008R2

JPA 2.1

Hibernate 4

Querydsl 4 JPA & SQL

Background:

I have a query that requires a subquery in the SELECT/projection. This works great using Querydsl JPA except in my specific case - I need to ORDER BY and LIMIT/TOP in the subquery. I have found ample documentation that explains that LIMIT/TOP is not possible in JPA Criteria subqueries. Frustrating! And confusing because Querydsl's JPASubQuery API has limit() available. Anyhow, as a workaround I've attempted to use JPA in conjunction with native SQL.

For reference here's the query outline in pure Querydsl JPA (which doesn't work due to limit not working in subqueries):

QContract contract = QContract.contract;
QContract rateScheduleContract = new QContract("rsContract")
QRateSchedule rateSchedule = QRateSchedule.rateSchedule
List<ContractQueryResult> queryResults = jpaQueryFactory.select(
         //@QueryProjection constructor
         new QContractQueryResult(
             contract,
             (JPAExpressions.selectFrom(rateSchedule)
                    .innerJoin(rateSchedule.contracts, rsContract)
                    .where(
                          rateScheduleContract.contractNumber.eq(contract.contractNumber)
                          //subquery static criteria here
                    )
                    .orderBy(rateSchedule.startDate.desc())
                    .limit(1)
             )
         )
    ).from(contract)
    .where(
        //main query dynamic criteria here
    ).fetch();

"Using Native SQL in JPA queries" documentation contains some examples of how JPA and native SQL can be mixed. But I note that the mix of JPA metamodel + SQL metamodel is limited to one example, where the SQL metamodels are used for all query parameters and the JPA metamodel is used only for the SELECT/projection.

Question 1:

Let's say I rewrite the above query into a JPASQLQuery. I can't find such a thing as a JPASQLSubQuery. Is it possible to write a subquery to the JPASQLQuery where the SELECT/projection is a JPA metamodel? Or more directly, do JPAExpressions or SQLExpressions support that?

Question 2:

Is there a way to write my main query in pure Querydsl JPA rather than a JPASQLQuery, while still executing native SQL for the subquery (so that I can use TOP/LIMIT)? If the Querydsl API doesn't support that natively with the SQL metamodel, is it possible to write the subquery in pure JPA and pass it into the Querydsl JPA projection?

Thank you very much for your help in advance!

[timowest]

Answers inline

You can use SQLSubQuery for subqueries in JPASQLQuery. Using entity path properties in native queries will work if the property names are equal to the column names. So this kind of usage is not in general support.

 

Question 2:

Is there a way to write my main query in pure Querydsl JPA rather than a JPASQLQuery, while still executing native SQL for the subquery (so that I can use TOP/LIMIT)? If the Querydsl API doesn't support that natively with the SQL metamodel, is it possible to write the subquery in pure JPA and pass it into the Querydsl JPA projection?

subqueries in JPQL are only supported in the WHERE part and can be only JPQL, not SQL. 

Thank you very much for your help in advance!

I wouldn't mix JPQL and SQL in the same query, you can still populate anything you want from the SQL query results.