Jpa does not recognize WHERE condition

Viewed 67

I have two tables

  class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    String name
    
    @OneToMany(mappedBy = "student", cascade = CascadeType.ALL)
    List<Book> books
    }
class Book {
  
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
        
    String name

    String time_stamp;
  
    @ManyToOne(fetch = FetchType.LAZY)
    @JsonIgnore
    private Student student;    
}

So in the database, I have for example:

Table Student

id name
1 John

Table Book

id name time_stamp studentId
1 Bla 12:00 1
-- ---- ---------- ----------
2 Test 12:45 1
@Query(value = "SELECT * FROM student LEFT JOIN Book ON student.id = book.student.id WHERE book.time_stamp >= '12:00' AND book.time_stamp <= '12:00'", nativeQuery = true)
List<Student> findFromTo();

If we run the query in MySql Workbench everything is ok we receive only Students and books in given timestamp.

The problem occurs when we run the query through spring JPA here we receive Student between this timestamp but with all the books linked to the Student.

I want to receive in jpa Student object with Books objects which are filtered with timestamp.

1 Answers

What do you want to receive? You can LAZY fetch the books and customize the serialization.

If using Jackson you can customize the field you need for different views, look up the JsonView annotation.

You might also be interested in Projections on the repository side.

Related