CODEWITHSUNDEEP
jpaquerydsl
sunghun
sunghun

Reputation: 1424

How to organise this query using QueryDsl

I am trying to use QueryDsl to call SQL statement, which cannot be covered by Spring Data JPA. Here is the sentence I want to run.

SELECT timestamp, deviceId 
  FROM EventData a
INNER JOIN
(SELECT max(timestamp) timestamp, deviceId 
   FROM EventData 
  GROUP BY deviceId) b
USING (timestamp,deviceId)
WHERE deviceId in ('1', '2')

And I did like below:

@PersistenceContext
EntityManager em;

JPAQuery query = new JPAQuery(em);
QEventData eventData = new QEventData();

return
query.from(eventData)
     .innerJoin(
               query.from(eventData)
                    .groupBy(eventData.deviceId)
                    .list(eventData.deviceId, eventData.timestamp.max())
      ).on(eventData.deviceId.eq(?))
.where(eventData.deviceId.in("1", "2"))
.list(eventData);

I do not know what I put for innerJoin. I tried to QueryDsl examples but could not find the right one. Where can I get good examples for QueryDsl?

Thanks, your answer would be appreciated.

Upvotes: 1

Views: 3532

Answers (1)

David Fleeman
David Fleeman

Reputation: 2658

This is only a partial answer but it worked for me from a JUnit test. I used SQL queries, not JPA. It's worth trying:

    Connection connection = getConnection();
    SQLTemplates dialect = new PostgresTemplates(); // TODO: use appropriate db template
    QEventData c = new QEventData("c");
    QEventData c2 = new QEventData("c2");

    // Use this to hold alias for inner select
    PathBuilder<Object[]> c2alias = new PathBuilder<Object[]>(Object[].class, "c2alias");

    SQLQuery query =  new SQLQuery(connection , dialect);
    return query.from(c).innerJoin(
           new SQLSubQuery().from(c2)
                .groupBy(c2.deviceid)
                .list(c2.device, c2.timestamp.max().as("timestamp")), c2alias
    ).on(c.deviceid.eq(c2alias.get("deviceid")),
        c.timestamp.eq(c2alias.get("timestamp")))
    .where(c.deviceid.in(1, 2))
            .list(c.deviceid, c.timestamp);

Note 1: In the list() function, you have to explicitly list the fields to select or you get an error I believe that is due to the use of the PathBuilder inner select alias.

Note 2: Other posts indicate this may not work with JPA, just SQL: https://groups.google.com/forum/#!msg/querydsl/WDX6oB7Ivsg/0fzoLJA35lgJ

Note 3: In as much SQL as I have written, I have not seen the USING clause ever used in practice, for those who do not know it: http://docs.oracle.com/javadb/10.8.1.2/ref/rrefsqljusing.html

Note 4: If the above does not work with querydsl/JPA, then consider refactoring the query to use an EXISTS clause.

Upvotes: 1

Related Questions