4 Replies Latest reply: Apr 4, 2012 12:32 AM by 927451 RSS

    JPA Query

    927451
      select * from (select * from trap where agentip="47.152.228.240" and acknowledged=false order by receivedTimestamp desc) as traps group by trapname order by receivedtimestamp desc;

      Can some one help me convert this MYSQL query to JPA SQL??? I tried lots of option but not working..
        • 1. Re: JPA Query
          cdelahun
          I'm not sure I understand the point of the select in the from clause, since you are returning the exact same data with only a table name change. If you are using JPQL, you must have an Entity mapped to the table you are selecting from. Are you looking to get entities back or raw data?

          Something like "Select trap from Trap trap where trap.agentip="47.152.228.240" and trap.acknowledged=false order by trap.receivedTimestamp desc" would work if you mapped an Entity Trap to table "trap".

          Best Regards
          Chris
          • 2. Re: JPA Query
            927451
            Thanks Chris for the reply.

            I have basically a table with name
            TRAP
            -----------
            trapName
            receivedTimeStamp
            agentIp
            Acknowledge
            ------------

            I have to write a query where I have to group by trapname & get the count of each traps. The rows returned should be in the descending order of receivedTimeStamp.
            In the query I have mentioned:
            select * from (select * from trap where agentip="47.152.228.240" and acknowledged=false order by receivedTimestamp desc) as traps group by trapname order by receivedtimestamp desc;

            The inner query gets me the receivedTimeStamp Descending, later on I am grouping it by trapName. If I don't use order by received timestamp in the inner query, it is not fetching me the proper results.

            The query I have mentioned works fine in MYSQL, I want to write a similar Query in JPQL, but I am unable to do so. I tried the below, it says unexpected token found in column 15.

            select t from (select tp from trap where tp.agentip="47.152.228.240" and tp.acknowledged=false order by tp.receivedTimestamp desc) as traps t group by t.trapname order by t.receivedtimestamp desc;

            Edited by: 924448 on Apr 1, 2012 10:10 PM
            • 3. Re: JPA Query
              cdelahun
              I am not sure why ordering on the inner query would make the results any different from executing SQL:
              "select * from trap where agentip="47.152.228.240" and acknowledged=false group by trapname order by receivedtimestamp desc"
              Since the where clause should be processed before the group by, and the broup by operate over the results independent of the inner query order by statement.

              You can always just use native SQL if a JPQL query "select t.fiield1, t.field2, <etc> from trap t where t.agentip="47.152.228.240" and t.acknowledged=false group by t.trapname order by t.receivedtimestamp desc"
              doesn't work for you.

              EclipseLink is also seems to have support for subselects in the from clause through feature: https://bugs.eclipse.org/bugs/show_bug.cgi?id=350597
              described here: http://wiki.eclipse.org/EclipseLink/DesignDocs/312146
              You can try this support with the EclipseLink 2.4 nightly

              Best Regards,
              Chris
              • 4. Re: JPA Query
                927451
                Finally the below query worked for my requirements..


                SELECT *
                FROM trap t WHERE t.agentip IN ("47.152.228.237","47.152.228.240")
                AND t.acknowledged=false
                AND t.receivedTimestamp = (SELECT MAX(t2.receivedTimestamp)
                FROM trap t2
                WHERE t2.trapname = t.trapname
                and t2.agentip IN ("47.152.228.237","47.152.228.240")
                and t2.acknowledged=false) ORDER BY receivedTimestamp DESC;