Spring Boot complex Query

Question

I am trying to create a method in the repository to be able to find out what is the most recent contract signed by an employee. The problem I have is that if I want to filter by the start date of the contract, I also have to pass it as a parameter and I don't have that, that's what I need. I tried this but the result is all contracts of this employee.

@Query(value = "SELECT * "
    + "FROM Contracts c "
    + "WHERE c.employeeName = :name "
    + "AND c.dateStartContract = ("
        + "SELECT MAX(dateStartContract) "
        + "FROM Contracts d "
        + "WHERE d.employeeName = :name)")
public Contract findByContractIDEmployeeName(@Param("name") String name);

Answer 1

The problem was that the import of @Query at the repository was import org.springframework.data.jdbc.repository.query.Query; and must be import org.springframework.data.jpa.repository.Query;. After this change, the consult to data base is exactly my Query request.

My query is this.

@Query(value = "SELECT c "
    + "FROM Contract c "
    + "WHERE c.contractID.employeeName = :name "
    + "AND c.contractID.contractStartDate = ("
        + "SELECT MAX(contractID.contractStartDate) "
        + "FROM Contract d "
        + "WHERE d.contractID.employeename = :name)")
public Contract findByContractIDNameEmployeeAndByContractIDDateStartContract(@Param("name") String name);