Querying into projection

Question

I have 2 tables in a one-to-many mapping, layoutFeature and layoutPadData.

Parent table layoutFeature has 2 columns: layoutId (Pkey) and colorHex.

Child table layoutPadData has 5 columns:

layoutId (Fkey), padId (Pkey), longitude, latitude, padName

Now I want to write a query which from database will fetch data like this projection:

public interface LayoutFeatureInfo {

    String getColorHex();
    
    List<LayoutPadDataInfo> getPadLevelDataList();
    
    interface LayoutPadDataInfo {
    
        double getLatitude();
    
        double getLongitude();
    
        String getPadName();
    }
}

I wrote this query :

But this doesn't work since the query returns:

1. All rows of layoutPadData table but I want selective of them under padLabelDataList

2. As I had done join, I get multiple rows of colorHex as well but I what I want is single property of parent and list of child interface something like this will be response of api when I fetch from DB:

Can anyone help me with the query please.

Answer 1

My personal experience with projections and join fetching has been really bad in spring boot (vanilla JPQL).

One approach is the one explained here https://vladmihalcea.com/one-to-many-dto-projection-hibernate/

Another approach is to use a 3rd party lib like https://github.com/Blazebit/blaze-persistence

I've heard great things about it but I've never used it before.

For your second problem a simpler issue is query child entity based on parent key and return a list of the things you want (hibernate is not good to understand and "turn" the sql like result-set into a single entity).