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:
All rows of layoutPadData table but I want selective of them under padLabelDataList
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.
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).