Cedomir Mijajlovic Cedomir Mijajlovic - 4 years ago 209
MySQL Question

JPQL CASE STATEMENT in WHERE CLAUSE PROBLEMS

i have some problems with case statement in where clause if someone know how to fix this please help me! Thank you.

@Query("select e from EventTeamPlayer etp "
+ "join etp.event e "
+ "left join e.leagueTournament lt "
+ "left join lt.sportCategory sc "
+ "left join e.sport s "
+ "left join etp.homeTeamPlayer htpl "
+ "left join etp.awayTeamPlayer atpl "
+ "left join lt.country c "
+ "left join e.eventStatus es "
+ "where (e.startDate >= :startDate and e.startDate <= :endDate) "
+ "and lt.id = :leagueTournamentId "
+ "and (lt.defaultName like :searchTerm or "
+ "s.name like :searchTerm or "
+ "htpl.name like :searchTerm or "
+ "atpl.name like :searchTerm or "
+ "e.id like :searchTerm) and "
+ "(case when (:minDate is not null and :maxDate is not null) "
+ " then (e.startDate >=:minDate and e.startDate<=:maxDate) else true end) = true")
Page<Event> getEventsForWebAdmin(Pageable pageable,
@Param("searchTerm") String searchTerm,
@Param("leagueTournamentId") int leagueTournamentId,
@Param("startDate") Date startDate,
@Param("endDate") Date endDate,
@Param("minDate") Date minDate,
@Param("maxDate") Date maxDate);


AND HERE IS THE ERROR IN LOG :


Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException:
unexpected AST node: and near line 1, column 589
[select e from
com.itengine.bettinggateway.dao.EventTeamPlayer etp join etp.event e
left join e.leagueTournament lt left join lt.sportCategory sc left
join e.sport s left join etp.homeTeamPlayer htpl left join
etp.awayTeamPlayer atpl left join lt.country c left join e.eventStatus
es where (e.startDate >= :startDate and e.startDate <= :endDate) and
(lt.defaultName like :searchTerm or s.name like :searchTerm or
htpl.name like :searchTerm or atpl.name like :searchTerm or e.id like
:searchTerm) and (case when (:minDate is not null and :maxDate is not
null) then (e.startDate >=:minDate and e.startDate<=:maxDate) else
true end) = true and lt.id = :leagueTournamentId]

Answer Source

I dont think you can pull that kind of statement in JPQL.

Try to replace it with something like this:

AND
(
   (:minDate is not null and :maxDate is not null
         and e.startDate >=:minDate and e.startDate<=:maxDate)
   OR
   (:minDate is null or :maxDate is  null)                   
)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download