Spring JPA Hibernate how to find array column contains certain elements Named Query

Question

Suppose I have an entity

@Entity
public class Course{
    @Id
    private Long id;

    private String courseName;

    //getter and setters
}

now I want to find a Course by its name, hence in its repository, I put a named query,

public interface CourseRepository extends JpaRepository<Course, Long> {

    @Query(value = "SELECT * FROM course c where c.name = :name", nativeQuery=true)
    List<Course> findByName(@Param("name") String name);

}

Now lets say Course entity gets a String[] column which will contain the name of days the course class supposed to be held. So the entity becomes

@Entity
public class Course{
    @Id
    private Long id;

    private String courseName;

    //will contain {"Tue", "Wed", "Thr"} etc ... the class days
    private String [] classDays; 

    //getter and setters
}

Now I want to find the Courses that are held in Thr. How do I write a named query for that ? I have something like this in mind

SELECT * FROM course c where :day in c.class_days

but will that work ?

Answer 1

No, because classDays is not mapped to work in that way(it will be passed to the database like a bytea(postgresql) or blob(oracle) not a list of string). You can use @ElementCollection to be able to execute this query SELECT * FROM course c where :day in c.class_days

@Entity
public class Course{
    @Id
    private Long id;

    private String courseName;

    //will contain {"Tue", "Wed", "Thr"} etc ... the class days
    @ElementCollection
    @CollectionTable(
        name="class_days_table",
        joinColumns=@JoinColumn(name="COURSE_ID")
  )
  @Column(name="class_days")
    private List<String> classDays; 

    //getter and setters
}