Mapping a database view entity with a simple entity and pass to DTO using Spring Data

I'm just learning Spring Data. I want to map a database view Entity with a simple Entity and pass to DTO which will contain columns both entities. I understand that I can use a special database view but I need to map precisely entities of Spring Data. I have a database view Entity "MentorStudents":

@Entity
@Table(name = "mentor_students")
@Immutable
public class MentorStudents implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "mentor_id", updatable = false, nullable = false)
    private Long mentorId;

    //This entity I need to map
    private Mentor mentor;

    @Column(name = "active_students")
    private Integer activeStudents;

    public MentorStudents() {
    }

    //getters, setters, equals, hashCode
}

A database view sql of an above entity is:

SELECT id AS mentor_id, active_students
   FROM mentor
LEFT JOIN ( SELECT mentor_id, count(mentor_id) AS active_students
   FROM contract
   WHERE close_type IS NULL
   GROUP BY mentor_id) active ON mentor.id = active.mentor_id
ORDER BY mentor.id;

And I have a simple Entity "Mentor":

@Entity
@Table(name = "mentor")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Mentor implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

    @NotNull
    @Column(name = "first_name", nullable = false)
    private String firstName;

    @NotNull
    @Column(name = "last_name", nullable = false)
    private String lastName;

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

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

    @NotNull
    @Column(name = "email", nullable = false)
    private String email;

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

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

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

    @Column(name = "max_students")
    private Long maxStudents;

    //getters, setters, equals, hashCode

I've to get a DTO wich contains all Mentor fields and an "activeStudents" MentorStudents field without a "mentorId" field. How do it?

1 answer

  • answered 2018-10-12 01:48 RJ.Hwang

    Use spring data projection:

    public interface YourDto {
      // all Mentor get fields
      String getFirstName();
      ...
      // activeStudents get field
      Integer getActiveStudents();
    }
    
    public interface YourRepository extends JpaRepository<YourEntity, Integer> {
      @Query(value = "select ...(all fields match YourDto) from Mentor m, MentorStudents s where m.id = s.mentorId and m.id = ?1")
      Optional<YourDto> findMyDto(Integer mentorId);
    }