Error while using JPQL to query by joining tables

Question

I am trying to fetch list of train entities using source and destination properties of route entity which has one to many relationship with each other. The train table having route id as foreign key. The table names are route and train respectively. Please help me as the query is throwing java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.infyrail.app.repository.RouteRepository.findBySourceDestination(java.lang.String,java.lang.String)! RouteRepository:

public interface RouteRepository extends JpaRepository<RouteEntity, Integer> {
    @Query("SELECT t FROM train t JOIN route r WHERE r.source=?1 AND r.destination=?2")
    public List<TrainEntity> findBySourceDestination(String source,String destination);
}

RouteEntity:

@Entity
@Table(name="route")
public class RouteEntity {
     @Id              
     @GenericGenerator(name="route_id",
    strategy="com.infyrail.app.generator.RouteIdGenerator")
                @GeneratedValue(generator = "route_id")
    @Min(value = 100)
    @Max(value = 999)
    Integer id;

    String source;
    String destination;
    @OneToMany(mappedBy = "route",
            cascade=CascadeType.ALL,orphanRemoval = true)

    private List<TrainEntity> trainList;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getSource() {
        return source;
    }
    public void setSource(String source) {
        this.source = source;
    }
    public String getDestination() {
        return destination;
    }
    public void setDestination(String destination) {
        this.destination = destination;
    }
    public List<TrainEntity> getTrainList() {
        return trainList;
    }
    public void setTrainList(List<TrainEntity> trainList) {
        this.trainList = trainList;
    }

}

TrainEntity:

@Entity
@Table(name="train")
public class TrainEntity {

    //@GeneratedValue(strategy = GenerationType.IDENTITY)
     @Id              
     @GenericGenerator(name="train_id",
    strategy="com.infyrail.app.generator.TrainIdGenerator")
                @GeneratedValue(generator = "train_id")
    @Min(value = 100)
    @Max(value = 999)
    Integer id;
    String trainName;
    String arrivalTime;
    String departureTime;
    Double fare;
    @ManyToOne(fetch = FetchType.LAZY)
    @Autowired
    RouteEntity route;
    public RouteEntity getRoute() {
        return route;
    }
    public void setRoute(RouteEntity route) {
        this.route = route;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getTrainName() {
        return trainName;
    }
    public void setTrainName(String trainName) {
        this.trainName = trainName;
    }
    public String getArrivalTime() {
        return arrivalTime;
    }
    public void setArrivalTime(String arrivalTime) {
        this.arrivalTime = arrivalTime;
    }
    public String getDepartureTime() {
        return departureTime;
    }
    public void setDepartureTime(String departureTime) {
        this.departureTime = departureTime;
    }
    public Double getFare() {
        return fare;
    }
    public void setFare(Double fare) {
        this.fare = fare;
    }


}

Answer 1

Here is the problem. Your query should define the table bean name instead of the actual table name.

In your case you should use TrainEntity instead of train and RouteEntity instead of route.

public interface RouteRepository extends JpaRepository<RouteEntity, Integer> {
    @Query("SELECT t FROM TrainEntity t JOIN RouteEntity r WHERE r.source=?1 AND r.destination=?2")
    public List<TrainEntity> findBySourceDestination(String source,String destination);
}