CODEWITHSUNDEEP
sqleclipselinkjpql
Tharaka
Tharaka

Reputation: 199

EclipseLink- JPQL join tables through query

I have problem with my JPQl. this is a one to many relationship with TrainRoute and TrainRouteStation. I'm trying to create a inner join and fetch the data. native SQL query working when I used mysql workbeanch and I'm trying convert it to JPQL. also, I was trying to fix from 2 days.

Error : Object comparisons can only be used with OneToOneMappings. Other mapping comparisons must be done through query keys or direct attribute level comparisons.

Class: TrainRoute

    @Basic(optional = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id    
    @Column(name = "train_route_id", unique = true, nullable = false)
    public Long getTrainRouteId() {
        return this.trainRouteId;
    }

    public void setTrainRouteId(Long trainRouteId) {
        this.trainRouteId = trainRouteId;
    }

    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.EAGER, mappedBy = "trainRoute")
    public List<TrainRouteStationData> getTrainRouteStations() {
        return this.trainRouteStations;
    }

    public void setTrainRouteStations(List<TrainRouteStationData> trainRouteStations) {
        this.trainRouteStations = trainRouteStations;
    }

Class: TrainRouteStation

    @Basic(optional = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id    
    @Column(name = "train_route_station_id", unique = true, nullable = false)
    public Long getTrainRouteStationId() {
        return this.trainRouteStationId;
    }

    public void setTrainRouteStationId(Long trainRouteStationId) {
        this.trainRouteStationId = trainRouteStationId;
    }

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "train_route_id", nullable = false)
    public TrainRouteData getTrainRoute() {
        return this.trainRoute;
    }

JPQL :"SELECT s FROM TrainRouteData t inner join TrainRouteStationData s ON t.trainRouteId=s.trainRoute where s.stationSeqN >=1 AND s.stationSeqN <=3 AND t.trainRouteDescX='Test1-Test2' order by s.stationSeqN asc"

Native SQL : SELECT train_route_station.* FROM train_route inner join train_route_station ON train_route.train_route_id=train_route_station.train_route_id where train_route_station.station_seq_n >= 1 AND train_route_station.station_seq_n <= 3 AND train_route.train_route_desc_x='Test1-Test2' order by train_route_station.station_seq_n asc

And it throw an error: Exception Description: Object comparisons can only be used with OneToOneMappings. Other mapping comparisons must be done through query keys or direct attribute level comparisons. Mapping: [org.eclipse.persistence.mappings.DirectToFieldMapping[trainRouteId-->train_route.train_route_id]] Expression: [ Query Key trainRouteId

How can I change that query?

Upvotes: 0

Views: 107

Answers (1)

JB Nizet
JB Nizet

Reputation: 691635

That's not how joins work in JPQL.

The correct query is 

select s from TrainRouteData t inner join t.trainRouteStations s 
where s.stationSeqN >= 1 
and s.stationSeqN <= 3 
and t.trainRouteDescX = 'Test1-Test2' 
order by s.stationSeqN asc

Upvotes: 1

Related Questions