Query auto generated table in Spring Boot-CodePudding

I have 2 entities. One being Courses and other one Batch

    @Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
public class Course {
    @Id
    private String uuid;

    @Column
    private String tileImage;

    @Column
    private String description;

    @Column
    private Boolean isActive;

    @Column
    private String name;

    @Column
    private String durationWeek;

    @Column
    private String durationHour;

    @Column
    private int price;

    @Column
    private String apply;

    @Column
    private Integer linkClicked;

    @Column
    @OneToMany(cascade = CascadeType.ALL)
    private Set<Batch> batches;

}

And one is Batch

    @Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
public class Batch {
    @Id
    private String uuid;

    @Column
    private Date startDate;

    @Column
    private Date endDate;

    @Column
    private String taughtBy;
}

On running in Spring boot, it generates 3 table Course Batch Courses_batches (coueseUUid and BatchUUid)

Issue is I want to query the Courses_Batches table? How can I do that by Spring JPA?

CodePudding user response：

Spring Data provides many ways to define a query that we can execute. One of these is the @Query annotation.

You can use also native SQL to define our query. All we have to do is set the value of the nativeQuery attribute to true and define the native SQL query in the value attribute of the annotation:

@Query(value = "SELECT * FROM Courses_Batches cb WHERE cb.course_uuid = ?1", 
            nativeQuery = true)
Object[] findAllByCourseIdInJoinTable(String courseId);

You set the column names according to your structure.

CodePudding user response：

It really depends on the result you want: you probably don't want the tuple Course_Batches which represents the association between Course and Batch, you probably want all Course that matches Batches or the reverse.

This association does not have any specify attribute, and if were to have attributes, there should be an intermediate entity.

You could use a Spring Data @Query, the findBy variant or a Criteria: here I assumed that you can use Java multiline string for clarity, but you would have to use concatenation and space for older version of Java:

@Query("""
  select new com.example.foobar.PairCourseBatch(c, b)
  from Course c
  left join c.batches b
  where c.uuid = :courseUuid
    and b.uuid = :batchUuid
""")
List<PairCourseBatch> findAllByCourseIdInJoinTable(
  @Param("courseUuid") String courseUuid,
  @Param("batchUuid") String batchUuid
);

The PairCourseBatch should be a fully qualified type in the query because otherwise JPA would not be able to find it. It expect a constructor taking the course and batch as parameter.

I don't know if you can use generics (eg: Pair<Course, Batch>) but you could return specific attribute and construct a non entity type:

select new com.example.foobar.PairCourseBatch(c.tileImage, b.startDate, b.endDate)

The advantage of using it is cleared in the return type: you don't have to cast component of an Object[].