JPA Criteria Equivalent Query Subquery greatest or max

Question

This SQL query does exactly what is needed when executed. I am looking for the JPA Criteria equivalent? Basically gets the Device entity and the LATEST(greatest) GPS coord from a One-To-Many relationship

SELECT DISTINCT t0.DEVICE_I, t0.ACTIVE_S, t0.APP_CONFIG_I, t0.PREV_APP_CONFIG_I, 
t0.APP_CONFIG_CONFIRMED_S, t0.APP_CONFIG_RECEIPT_D, t0.APP_CONFIG_SENT_S, t0.CHANNEL_X, 
t0.CREATION_TS, t0.CREATION_USER_I, t0.DEST_QUEUE_C, t0.DIVISION_C, t0.LAN_IP_X, 
t0.LAST_UPDATE_TS, t0.LAST_UPDATE_USER_I, t0.MOBILE_IP_X, t0.ORIGIN_MP_I, 
t0.PREV_CHANNEL_X, t0.TELEPHONE_NUMBER_X, t0.VERSION_X, t1.DEVICE_I, t1.COORDINATE_I, 
t1.CREATION_TS, t1.CREATION_USER_I, t1.GENERATED_TS, t1.GPGGA_X, t1.GPGSA_X, 
t1.GPGSV_X, t1.GPRMC_X, t1.GPVTG_X, t1.LAST_UPDATE_TS, t1.LAST_UPDATE_USER_I, 
t1.WORK_ORDER_NUMBER_I 

FROM DEVICE t0, GPS_COORD t1 

WHERE  t0.DEVICE_I = t1.DEVICE_I AND  
t1.GENERATED_TS IN ( select max(GENERATED_TS)
       from GPS_COORD
      group by DEVICE_I )
ORDER BY t1.DEVICE_I ASC, t1.GENERATED_TS DESC

The java code below is almost there and generates the following query which is missing joined fields needed from GpsCoord. These fields are included if a fetch is done, but the Join is necessary later in the where clause of the Subquery:

    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();

    CriteriaQuery<Device> cq = criteriaBuilder.createQuery(Device.class);

    Root<Device> device = cq.from(Device.class);
    cq.distinct(true);

    Join<Device, GpsCoord> j = device.join(Device_.gpsCoords, JoinType.LEFT);
    //Fetch<Device,GpsCoord> f = device.fetch(Device_.gpsCoords);
    
    CriteriaQuery<Device> select = cq.select(device);


    Subquery<Timestamp> sq = cq.subquery(Timestamp.class);
    Root<GpsCoord> gpsCoord = sq.from(GpsCoord.class);

    sq.select(criteriaBuilder.greatest(gpsCoord.get(GpsCoord_.generatedTs)));
    sq.groupBy(gpsCoord.get(GpsCoord_.device).get(Device_.deviceI));
    select.where(j.get(GpsCoord_.generatedTs).in(sq));
    
    
    TypedQuery<Device> query = this.getEntityManager().createQuery(cq);

Query that is generated is missing the GpsCoord fileds from the Join oepration

SELECT DISTINCT t0.DEVICE_I, t0.ACTIVE_S, t0.APP_CONFIG_I, t0.PREV_APP_CONFIG_I,     
t0.APP_CONFIG_CONFIRMED_S, t0.APP_CONFIG_RECEIPT_D, t0.APP_CONFIG_SENT_S, 
t0.CHANNEL_X,     t0.CREATION_TS, t0.CREATION_USER_I, t0.DEST_QUEUE_C, t0.DIVISION_C, 
t0.LAN_IP_X, t0.LAST_UPDATE_TS, t0.LAST_UPDATE_USER_I, t0.MOBILE_IP_X, t0.ORIGIN_MP_I, 
t0.PREV_CHANNEL_X, t0.TELEPHONE_NUMBER_X, t0.VERSION_X 

FROM SPW_OWN.DEVICE t0, SPW_OWN.GPS_COORD t1 

WHERE (t1.GENERATED_TS IN (SELECT MAX(t2.GENERATED_TS) FROM SPW_OWN.GPS_COORD t2, 
SPW_OWN.DEVICE t3 WHERE t2.DEVICE_I = t3.DEVICE_I GROUP BY t3.DEVICE_I)) 

AND t0.DEVICE_I = t1.DEVICE_I(+)

Using a multiselect from the CriteriaQuery like this generates the proper SQL when executed, but the following exception

Caused by: java.lang.Exception: java.lang.RuntimeException: Can not find constructor for "class Device" with argument types "[class java.lang.String ... java.sql.Timestamp]" to fill data.   

gets thrown after the call:

CriteriaQuery<Device> select = cq.multiselect(
                    //"deviceI",
                    device.get(Device_.deviceI),
                    //"activeS",
                    device.get(Device_.activeS),
                    //"appConfigConfirmedS",
                    device.get(Device_.appConfigConfirmedS),
                    //"appConfigReceiptD",
                    device.get(Device_.appConfigReceiptD),
                    //"appConfigSentS",
                    device.get(Device_.appConfigSentS),
                    //"channelX",
                    device.get(Device_.channelX),
                    //"destQueueC",
                    device.get(Device_.destQueueC),
                    //"lanIpX",
                    device.get(Device_.lanIpX),
                    //"mobileIpX",
                    device.get(Device_.mobileIpX),
                    //"prevChannelX",
                    device.get(Device_.prevChannelX),
                    //"telephoneNumberX",
                    device.get(Device_.telephoneNumberX),
                    //"versionX"
                    device.get(Device_.versionX),
                    //"device",
                    j.get(GpsCoord_.device),
                    //"gpggaX",
                    j.get(GpsCoord_.gpggaX),
                    //"gprmcX",
                    j.get(GpsCoord_.gprmcX),
                    //"gpgsaX",
                    j.get(GpsCoord_.gpgsaX),
                    //"gpgsvX",
                    j.get(GpsCoord_.gpgsvX),
                    //"gpvtgX"
                    j.get(GpsCoord_.gpvtgX),
                    j.get(GpsCoord_.generatedTs)
                    );

Answer 1

A few issues,

• you call, sq.from(GpsCoord.class); twice
• using device.fetch(Device_.gpsCoords); will also fetch the object, and will join it twice
• you IN is incorrect it should be,

select.where(criteriaBuilder.get(path).in(sq));