Jhispter Repository Custom @Query

Question

I have 2 tables in my database, ProfesorInfo and StudentInfo in a one-to-many relationship. I want to build a custom query to get all students that have a relationship to a certain professor, given the professor's id. Something like :

@SuppressWarnings("unused")
@Repository
public interface StudentInfoRepository extends JpaRepository<StudentInfo, Long> {

    @Query("SELECT * FROM StudentInfo WHERE ProfesorID = profesorId")
    List<StudentInfo> findAllByProfesorID(Long profesorId);

The @Query doesn't seem to be working. Why is this the case and how can I make it work ?

Answer 1

I don't think you are using the query parameter correctly, it should look something like this:

import org.springframework.data.repository.query.Param;
... 
@SuppressWarnings("unused")
@Repository
public interface StudentInfoRepository extends JpaRepository<StudentInfo, Long> {

    @Query("SELECT s FROM StudentInfo s WHERE s.ProfesorID = :profesorId")
    List<StudentInfo> findAllByProfesorID(@Param("profesorId") Long profesorId);

The colon tells JPA that the next word is a variable that should be replaced by the value of a @Param with the same name.

Imagine a @Query as having to navigate, load and filter entities directly in your Java objects. I say this because your field name ProfesorID starting with a capital 'P' looks weird. If this field is named profesorID in your java entity then it should be named the same when you want to access it inside a jpa query.

If you want more information about this, here is an excellent guide with examples.