How can I convert this 3 JOIN query into a Spring Data JPA named query method?

Question

I am not so into Spring Data JPA and I have the following problem trying to implement a named query (the query defined by the method name).

I have these 3 entity classes:

@Entity
@Table(name = "room_tipology")
public class RoomTipology implements Serializable{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "tipology_name")
    private String name;

    @Column(name = "tipology_description")
    private String description;

    @Column(name = "time_stamp")
    private Date timeStamp;


    @OneToMany(mappedBy = "roomTipology")
    private List<Room> rooms;

    @OneToOne(mappedBy = "roomTipology")
    private RoomRate roomRate;

    // GETTER AND SETTER METHODS
}

That represents a tipology of room and that contains this field

@OneToMany(mappedBy = "roomTipology")
private List<Room> rooms;

So it contains the list of room associated to a specific room tipology, so I have this Room entity class:

@Entity
@Table(name = "room")
public class Room implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @ManyToOne
    @JoinColumn(name = "id_accomodation_fk", nullable = false)
    private Accomodation accomodation;

    @ManyToOne
    @JoinColumn(name = "id_room_tipology_fk", nullable = false)
    private RoomTipology roomTipology;

    @Column(name = "room_number")
    private String number;

    @Column(name = "room_name")
    private String name;

    @Column(name = "room_description")
    @Type(type="text")
    private String description;

    @Column(name = "max_people")
    private Integer maxPeople;

    @Column(name = "is_enabled")
    private Boolean isEnabled;

    // GETTER AND SETTER METHODS
}

Representing a room of an accomodation, it contains this annoted field:

@ManyToOne
@JoinColumn(name = "id_accomodation_fk", nullable = false)
private Accomodation accomodation;

And finally the Accomodation entity class:

@Entity
@Table(name = "accomodation")
public class Accomodation implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @OneToMany(mappedBy = "accomodation")
    private List<Room> rooms;

    @Column(name = "accomodation_name")
    private String name;

    @Column(name = "description")
    @Type(type="text")
    private String description;

    // GETTER AND SETTER METHODS
}

Ok, so now I have this Spring Data JPA repository class for RoomTipology:

@Repository
@Transactional(propagation = Propagation.MANDATORY)
public interface RoomTipologyDAO extends JpaRepository<RoomTipology, Long> {


}

Here I want to define a named query method that return to me the list of all the RoomTipology object related to a specific accomodation, I have done it using SQL and it works fine:

SELECT * 
FROM room_tipology as rt
JOIN room r 
ON rt.id = r.id_room_tipology_fk
JOIN accomodation a
ON r.id_accomodation_fk = a.id
WHERE a.id = 7

But now I want to translate it in a named query method (or at least using HQL)

How can I do it?

Please check my answer @AndreaNobili – ooozguuur Nov 28 '16 at 10:11 — ooozguuur, Nov 28 '16 at 10:11

score 2 · Accepted Answer · answered Nov 26 '16 at 18:55

Please Try:

@Repository
@Transactional(propagation = Propagation.MANDATORY)
public interface RoomTipologyDAO extends JpaRepository<RoomTipology, Long> {

   List<RoomTipology> findByRooms_Accomodation(Accomodation accomodation);

}

The query builder mechanism built into Spring Data repository infrastructure is useful for building constraining queries over entities of the repository. The mechanism strips the prefixes find…By, read…By, query…By, count…By, and get…By from the method and starts parsing the rest of it

At query creation time you already make sure that the parsed property is a property of the managed domain class. However, you can also define constraints by traversing nested properties.

Doc:Here

How can I convert this 3 JOIN query into a Spring Data JPA named query method?

1 Answers1