Hibernate JPA : issue with Optional parameter in repository query

Question

When executing the code below I have this error :

org.hibernate.HibernateException: Could not determine a type for class: java.util.Optional

@Query("Select distinct  new com.myapp.web.rest.dto.AnalyseStockDTO(" +
            "a.lot.id,a.calibre, a.montantHt, l.valorisation, a.nbColis, 0, a.poidsNet, 0," +
            "(s.qteStock * s.lot.emballage.poidsNetStandard), s.qteStock-0, a.nbPieces, s.qteStock, a.prixUnitaire) " +
            "from Annonce a, Stock s, Lot l " +
            "where a.lot.dateArrivee >= :dateDebut and (:dateFin = null or a.lot.dateArrivee<=:dateFin) and a.lot.id not in (select distinct v1.lot.id from Vente v1) " +
            "and a.lot.id = s.lot.id and a.calibre = s.calibre and a.lot.id = l.id " +
            "and s.qteStock>0 ")
List<AnalyseStockDTO> getAnalyseStock(@Param("dateDebut") LocalDate dateDebut, @Param("dateFin") Optional<LocalDate> dateFin);

venteRepository.getAnalyseStock(dateDebut, Optional.ofNullable(dateFin));

Do you know why?

score 1 · Answer 1 · answered Dec 14 '18 at 09:08

Hibernate just does not know how to deal with Optional, only simple types can be used as query parameters.

The possibility of having dateFin being null is managed at the query level, you simply don't need to use Optional:

List<AnalyseStockDTO> getAnalyseStock(@Param("dateDebut") LocalDate dateDebut, @Param("dateFin") LocalDate dateFin);
venteRepository.getAnalyseStock(dateDebut, dateFin);

score 1 · Accepted Answer · answered Dec 14 '18 at 09:34

1) Hibernate doesn't support Optionals this way. Just pass null as a parameter and never use comparing by = null in your queries. Use :dateFin IS NULL clause because NULLs need special treating in database systems.

2) Don't use Optionals as method parameters - Optional wasn't designed for this purpose. Why should Java 8's Optional not be used in arguments

Hibernate JPA : issue with Optional parameter in repository query

2 Answers2