Native query in jpa - getting entries due next week

Question

I want to retrieve all the entries that are due next week from the database. The query I wrote is:

@Query(value = "SELECT new model.dto.EntryDTO(v.name, e.e_number, e.dueDate, e.amount, eScan.scanTimestamp) FROM Entry e JOIN EntryScan eScan ON e.e_number = eScan.e_number JOIN Ve v ON e.vId= v.id WHERE e.dueDate BETWEEN CURRENT_DATE AND (CURRENT_DATE + INTERVAL '1 week')",
        nativeQuery = true)
    List<EntryDTO> findAllDueNextWeek();

and i get JDBC exception executing SQL org.postgresql.util.PSQLException: ERROR: syntax error at or near "." Position: 21. dueDate is type date. I have similar query to this that works perfectly with the DTO class. I guess the problem is with the syntax and the CURRENT_DATE and interval part. Can someone help?

Answer 1

This constructer expression approach don't work when nativeQuery=true.

If you need to use native sql you can use Interface-based Projections feature:

public interface EntryView {
    Long getId();
    String getName();
}

@Query(value = "SELECT e.id, e.name FROM entry_table e", nativeQuery = true)
List<EntryView> findAllDueNextWeek();

-OR-

You can remove the nativeQuery=true statement and make your query conform to JPQL and use the constructer expression.