Join three entities using spring data

Question

i'm working with spring data in my project herers the entities:

rr:

@Id
@GeneratedValue( strategy = GenerationType.IDENTITY )
private Long   id;
private String model;

@ManyToOne
@JoinColumn( name = "owner" )
private ffperson;
private String constructor;
//getters and setters

zaa:

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

@Column( name = "name" )
private String Name;
//getters and setters

gff @Id @GeneratedValue( strategy = GenerationType.IDENTITY ) private Long id;

@Column( name = "lastname", unique = true )
private String lastName;

@Column( name = "firstname", unique = true )
private String firstName;

@Column( name = "birth_date", unique = true )
private Date   dateOfBirth;

gfff:

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

@ManyToOne
@JoinColumn( name = "dog_id" )
private ffdog;

@ManyToOne
@JoinColumn( name = "person_id" )
private ffperson;

i want to write a query with spring data which can fetch all the persons with thier own dogs and cars..Can any body help please

thanks for your answer ser...yes its a uni directional mapping — , Jun 04 '17 at 10:51
If the mappings from `Person` to other entity would have been bidirectional your query would be easy to make. — Abdullah Khan, Jun 04 '17 at 10:57
if you want you can tell me the changes that i have to do and i will try then i will get you back — , Jun 04 '17 at 10:58
You will have to change your entities if you want to make it bidirectional. — Abdullah Khan, Jun 04 '17 at 11:09

score 0 · Accepted Answer · edited Aug 04 '18 at 08:06

0

The easiest way to fetch them - change the current data model.

Person:

@Entity
public class Person {

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

    @Column(name = "lastname")
    private String lastName;

    @Column(name = "firstname")
    private String firstName;

    @Column(name = "birth_date")
    private Date  dateOfBirth;

    @MayToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "PERSON_DOG",
               joinColumns = {@JoinColumn(name = "PERSON_ID")},
               inverseJoinColumns = {@JoinColumn(name = "DOG_ID")})
    private List<Dog> dogs = new ArrayList<>();

    @OneToMany(mappedBy = "person")
    private List<Car> cars = new ArrayList<>();
}

Dog:

@Entity
public class Dog {

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

    @Column(name = "name" )
    private String Name;
    //getters and setters
}

Car:

@Entity
public class Car {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY )
    private Long  id;
    private String model;

    @ManyToOne
    @JoinColumn(name = "owner")
    @JsonIgnore // this will prevent the loop on response
    private Person person;

    private String constructor;
    //getters and setters
}

Then just querying Person you will get all relations.

public interface PersonRepository extends CrudRepository<Person, Long> {
    List<Person> findAll();
}

Also notice, that I've how created JoinTable. You don't need to do it manually, if you will not manage it on your own (sometimes it required if you will populate it with certain conditions or if you have additional column).

There is also some strange things in you model, why should first_name, last_name and date_of_birth are unique? Many persons may have similar data for these columns.

edited Aug 04 '18 at 08:06

marc_s

732,580
175
1,330
1,459

answered Jun 04 '17 at 11:04

J-Alex

6,881
10
46
64

thank you for you answer ser but i have to keep the same entities which mentioned above – Jun 04 '17 at 11:07
its impossible to get the data with the entites that i wrote?? – Jun 04 '17 at 11:08
I kept the same entities, just changed to bidirectional relations – J-Alex Jun 04 '17 at 11:09
Well, you can, but it's not optimal solution. How you're going to represent all the fetched data? You definitely will need to build DTO object then and create a custom mapper – J-Alex Jun 04 '17 at 11:11
Table "PERSON_DOG" not found; SQL statement: select dogs0_.person_id as person_i1_2_0_, dogs0_.dogs_id as dogs_id2_3_0_, dog1_.id as id1_1_1_, dog1_.name as name2_1_1_ from person_dog dogs0_ inner join dog dog1_ on dogs0_.dogs_id=dog1_.id where dogs0_.person_id=? [42102-192] – Jun 04 '17 at 11:26
You should use the name of your previous `PersonDog` join table, I just wrote an example. – J-Alex Jun 04 '17 at 11:29
org.h2.jdbc.JdbcSQLException: Table "PERSON_DOG" not found; SQL statement: select dogs0_.person_id as person_i1_2_0_, dogs0_.dog_id as dog_id2_3_0_, dog1_.id as id1_1_1_, dog1_.name as name2_1_1_ from person_dog dogs0_ inner join dog dog1_ on dogs0_.dog_id=dog1_.id where dogs0_.person_id=? [42102-192] – Jun 04 '17 at 11:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/145801/discussion-between-j-alex-and-imsi-imsi). – J-Alex Jun 04 '17 at 11:37

Abdullah Khan · Answer 2 · 2017-06-04T12:02:32.327

0

Update your entities like so :

Person

@Entity
@Table(name = "person")
public class Person {

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

    @OneToMany(mappedBy = "person", cascade = CascadeType.ALL)
    Set<Dog> dogs = new HashSet<>();

    @OneToMany(mappedBy = "person", cascade = CascadeType.ALL)
    Set<Car> cars = new HashSet<>();

    ...

}

Dog

@Entity
@Table(name="dog")
public class Dog {

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

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

    @ManyToOne
    private Person person;

    ...

}

Car

@Entity
@Table(name = "car")
public class Car {

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

    @ManyToOne
    private Person person;

    ...

}

Now create a PersonRepository and fetch all the person with there dogs and cars

public interface PersonRepository extends CrudRepository<Person, Long> {

    @Query("select p from Person p join fetch p.dogs join fetch p.cars")
    List<Person> findAll();

}

Note: Use Join Fetch to fetch dogs and cars eagerly. Otherwise you may get into n + 1 issue or Lazy fetch issue.

edited Jun 04 '17 at 12:02

answered Jun 04 '17 at 11:24

Abdullah Khan

12,010
6
65
78

` Set dogs;` guarantee NullPointer if you are not initialize it and will try to add something to that set. Also, why to you cascade? I don't think that dog and car are really should be deleted if Person does. – J-Alex Jun 04 '17 at 11:40
Cascade because i think `Person` owns cars and dogs and hence there persistence. Besides that can be used as per ones need. Secondly how will `Set dogs` give NPE? Give some references. – Abdullah Khan Jun 04 '17 at 11:45
`new Pesron().getDogs().add(new Dog());` – J-Alex Jun 04 '17 at 11:50
Why would you fetch dogs owned by a newly borned baby? – Abdullah Khan Jun 04 '17 at 11:51
It's just and example, it doesn't matter what you're adding to collection. https://stackoverflow.com/questions/7791325/is-it-good-practice-to-initialize-fields-inside-a-jpa-entity-getter – J-Alex Jun 04 '17 at 11:52

Join three entities using spring data

2 Answers2