CODEWITHSUNDEEP
javapostgresqlspring-data-jpadto
lutsik
lutsik

Reputation: 153

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 have to get a DTO which contains all Mentor fields and an "activeStudents" MentorStudents field without a "mentorId" field. How do it?

Upvotes: 2

Views: 1161

Answers (1)

RJ.Hwang
RJ.Hwang

Reputation: 1913

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);
}

Upvotes: 1

Related Questions