CODEWITHSUNDEEP
javaspring-bootspring-data-jpa
Aaron
Aaron

Reputation: 2755

Spring Repository interface : Find record using foreign key

I want to fetch existing records using two foreign keys.

Basically what I have here is a Route that has an origin and a destination (both are Terminal entities).

My aim is to create a function to fetch a Route record using both my origin and destination as condition in the query. I currently have no idea on how to do it with Spring Data JPA.

Below are my entities and relationship mapping.

@Entity
@Table(name = "ROUTE")
public class Route implements Serializable {

    public Route() {}

    public Route(Terminal origin, Terminal destination, int totalKM) {
        this.origin = origin;
        this.destination = destination;
        this.totalKM = totalKM;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "ROUTE_ID")
    private long id;

    @ManyToOne(cascade = {CascadeType.MERGE, CascadeType.REMOVE})
    @JoinColumn(name = "TERMINAL_ORIGIN_ID")
    @JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property="id")
    private Terminal origin;

    @ManyToOne(cascade = {CascadeType.MERGE, CascadeType.REMOVE})
    @JoinColumn(name = "TERMINAL_DESTINATION_ID")
    private Terminal destination;

    //...Getters and Setters
}

@Entity
@Table(name = "TERMINAL")
public class Terminal implements Serializable {

    public Terminal() {}

    public Terminal(String name, Town town, List<Route> routes) {
        this.name = name;
        this.town = town;
        this.routes = routes;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "TERMINAL_ID")
    private long id;

    private String name;

    @ManyToOne(cascade = {CascadeType.MERGE, CascadeType.REMOVE})
    @JoinColumn(name = "TOWN_ID")
    @JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property="id")
    private Town town;

    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Route> routes;

    //...Getters and Setters
}

Now i want something like findbyOriginAndDestination in my Route Repository. As much as possible I want to avoid fetching in the DB twice so I want to create a function in my repository layer to use both my origin and destination to fetch an existing record (if there is)

@Repository
public interface RouteRepository extends JpaRepository<Route, Long> {

    Route findByOriginAndDestination(long originId, long destinationId);

}

Upvotes: 10

Views: 29082

Answers (1)

Javvano
Javvano

Reputation: 1059

you can specify related entity's column as query condition by Query Property expressions

so it might be work 

@Repository
public interface RouteRepository extends JpaRepository<Route, Long> {

    Route findByOrigin_IdAndDestination_Id(long originId, long destinationId);

}

Upvotes: 25

Related Questions