jpa query between two dates

Question

This is my query on SQL Server:

select count(*) from noleggi 
where id_utente = 1 AND id_libro =25 and GETDATE() BETWEEN inizio_prestito AND fine_prestito

Which is the correct syntax using JPA Repository?

I tried this:

@Query(value = "SELECT COUNT(n) from Noleggio n " +
            "WHERE  n.libroId = ?1 AND n.utenteId=?2 AND CURRENT_DATE() BETWEEN n.inizioPrestito AND n.finePrestito")

    Long countByUtenteIdAndLibroId(Long idLibro, Long idUtente, LocalDate inizioPrestito, LocalDate finePrestito);

And received this error:

Validation failed for query for method public abstract java.lang.Long com.finance.biblioteca.repository.NoleggioRepository.countByUtenteIdAndLibroId(java.lang.Long,java.lang.Long,java.time.LocalDate,java.time.LocalDate

Did you receive some error? No results? Tell us what happened. — Dherik, Dec 27 '19 at 14:59
Validation failed for query for method public abstract java.lang.Long com.finance.biblioteca.repository.NoleggioRepository.countByUtenteIdAndLibroId(java.lang.Long,java.lang.Long,java.time.LocalDate,java.time.LocalDate)! — Max, Dec 27 '19 at 15:03

score 0 · Answer 1 · answered Dec 27 '19 at 14:57

You need to implement the AttributeConverter<LocalDate, Date> interface with its two methods convertToDatabaseColumn and convertToEntityAttribute.

import java.sql.Date;
import java.time.LocalDate;

@Converter(autoApply = true)
public class LocalDateAttributeConverter implements AttributeConverter<LocalDate, Date> {

    @Override
    public Date convertToDatabaseColumn(LocalDate locDate) {
        return locDate == null ? null : Date.valueOf(locDate);
    }

    @Override
    public LocalDate convertToEntityAttribute(Date sqlDate) {
        return sqlDate == null ? null : sqlDate.toLocalDate();
    }
}

please refer to this link for more details. https://thoughts-on-java.org/persist-localdate-localdatetime-jpa/

score 0 · Answer 2 · answered Dec 27 '19 at 18:33

0

My thought on your issue is that you don't have parameters for the BETWEEN. You just put field as name and not as indexed parameters. Your code should looks something like this :

@Query(value = "SELECT COUNT(n) from Noleggio n " +
            "WHERE  n.libroId = ?1 AND n.utenteId=?2 AND CURRENT_DATE() BETWEEN ?3 AND ?4")

    Long countByUtenteIdAndLibroId(Long idLibro, Long idUtente, LocalDate inizioPrestito, LocalDate finePrestito);

Depending on your version of JPA, you will need or not to write your own LocalDateConverter as mentionned in the response above.

answered Dec 27 '19 at 18:33

Harry Coder

2,429
2
28
32

select * from noleggi where id_utente = 1 AND id_libro =25 and GETDATE() BETWEEN inizio_prestito AND fine_prestito – Max Dec 28 '19 at 10:03
my query have to count if my current date is between my two dates into database refered to UserID with that specific bookID – Max Dec 28 '19 at 10:07
i tried that @Query(value = "SELECT COUNT(n) FROM Noleggio n WHERE idutente= :idUtente AND idlibro = :idLibro AND :data BETWEEN n.inizioPrestito AND n.finePrestito") Long countByNoleggioUtenteIdAndLibroId(@Param("idUtente") Long idUtente, @Param("idLibro") Long IdLibro, @Param("data")LocalDate data); – Max Dec 28 '19 at 10:10
THE error is: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'idutente'. – Max Dec 28 '19 at 10:16
Ok its is more clear for me now. This error means that there is no a column with the name `idutente` in your table. Check and correct the name if needed. – Harry Coder Dec 28 '19 at 15:07
yes, I solved! the column into database is with underscore, thanks! – Max Dec 29 '19 at 09:51

jpa query between two dates

2 Answers2