How to include the mapped values of a @OneToMany relation on a query-CodePudding

I am interested in performing the two following queries:

A. Getting a persons basic info e.g.

{
    "id": 186506,
    "pidm": 324950,
    "first_name": "Jane"
}

B. Getting a person's basic information as well as their phone numbers e.g

{
    "id": 186506,
    "pidm": 324950,
    "first_name": "Jane",
    "phones": ["1234567890", "0987654321"]
}

How should I go about it? Is it possible? With this mapping, so far I'm able to get either the basic information, or the phones, but not the combination of the two as I would like.

These are the domain I'm using:

@Entity
@Table(name="PERSON")
class Person implements Serializable {

    @Id
    @Column(name="person_id")
    Long id

    @Column(name="person_pidm")
    Long pidm

    @Column(name="person_first_name")
    String first_name

    @OneToMany(mappedBy = "person")
    @JsonBackReference
    private List<Telephone> phones

    List<Telephone> getPhones() {
        return phones
    }
}

@Entity
@Table(name="TELEPHONE")
class Telephone implements Serializable {

    @Id
    @Column(name="telephone_id")
    Long id

    @Column(name="person_pidm")
    String pidm

    @Column(name="telephone_phone_number")
    String phone_number

    @ManyToOne
    @JoinColumn(name="sprtele_pidm", referencedColumnName = "spriden_pidm")
    private SpridenDetail person;
}

CodePudding user response：

you can use HQL, many samples could be found in the document

CodePudding user response：

Try this i hope this will work for you

@Repository
public interface PersonRepository extends JpaRepository<Person,Long>{

//A. Getting a persons basic info e.g.

@Query("SELECT id,pidm,first_name FROM Person")
public List<Person> findAllPerson();


 //B. Getting a person's basic information as well as their phone numbers e.g

@Query("SELECT p,t FROM Person p JOIN p.phones t")
public List<Person> findAll(); 
}

CodePudding user response：

you can Limit the data by writing the separate queries like this:

 // if you just want person Basic info
        
    @Query("SELECT id,pidm,first_name FROM Person")
    public List<Person> findAllPerson();
    
    // if you want person's basic information as well as their phone numbers

    @Query("SELECT p,t.phone_number FROM Person p JOIN p.phones t")
    public List<Person> findAllPersonWithPhoneNumbers(); 
    }