How to query specific columns in one to many relationship - Spring data JPA?

Question

I have 2 classes

@Entity
public class User implements Serializable {

        @Id
        @Column(name = "user_id")
        private String id;

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

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

        @OneToMany(fetch = FetchType.LAZY)
        @JoinColumn(name = "user_id")
        private List<Address> addresses= new ArrayList<>();

}

@Entity
public class Address implements Serializable {

        @Id
        @Column(name = "address_id")
        private String id;

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

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

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

}

I need to get a list of User that has a list of Address in each user. The problem is if I do the sql like this:

@Query("select u from User where u.id = :userId")
List<User> getUserAndAddress(@Param("userId") String userId);

Hibernate will get all of Address fields because of the onetomany relationship, any idea how to write sql query to get the user list that only contain the Addresses that have id and code? I am using JPA and postgres. Thank you the net!

Answer 1

To query specific columns in a one-to-many relationship using Spring Data JPA, you typically fetch the parent entity and then navigate through its collection of child entities.

Let's assume you have two entities: Parent and Child, where Parent has a one-to-many relationship with Child.

Using JPQL

@Repository
public interface ParentRepository extends JpaRepository<Parent, Long> {
    @Query("SELECT p.name, c.name FROM Parent p JOIN p.children c WHERE p.id = :parentId")
    List<Object[]> findSpecificColumnsByParentId(@Param("parentId") Long parentId);
}

Using Query Methods

@Repository
public interface ParentRepository extends JpaRepository<Parent, Long> {
    List<Parent> findById(Long parentId);
    @Query("SELECT p.name, c.name FROM Parent p JOIN p.children c WHERE p.id = :parentId")
    List<Object[]> findSpecificColumnsByParentId(@Param("parentId") Long parentId);
}

Answer 2

Address mapping seems problematic, but I'll skip it.

There are several ways. I will show you how to use one of them, DTO. First, create a DTO class with the fields you want.

private List<UserQueryDTO> findUsers(String userId) {
   return em.createQuery("select new your.package.path.UserQueryDTO(u.id, a.code) from 
   Address a join a.user u where u.id = :userId, OrderQueryDTO.class)
   .setParameter("userId", 
   userId)
   .getResultList();
}

This is a simple way to use the entity manager. if you want, u can use querydsl or etc.

This method returns only the needed field as a DTO. I hope it will be of help.