I have entity
@Data
@Entity
@Table(name = "event")
@DynamicInsert
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode
public class Event {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "accountable_unit_id")
private Long accountableUnitId;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "type_id")
private EventType type;
@Column(name = "name")
private String name;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "status_id")
@Enumerated(EnumType.STRING)
private EventStatus status;
@Column(name = "user_info")
private String userInfo;
@Column(name = "deadline_dttm")
private Instant deadlineOn;
@Column(name = "completed_dttm")
private Instant completedOn;
@Column(name = "status_modify_dttm")
private Instant statusUpdatedOn;
@Column(name = "create_dttm")
private Instant createdOn;
@Column(name = "modify_dttm")
private Instant updatedOn;
@Version
@Column(name = "version")
private int version;
@Column(name = "status_name")
private String statusName;
And I have repository with one method
@Repository
public interface EventRepository extends JpaRepository<Event, Long>, JpaSpecificationExecutor<Event> {
@Query(value = "select e.*,\n" +
" CASE \n" +
" WHEN es.code = 'in_progress' and e.deadline_dttm < now()\n" +
" THEN 'Просрочено' \n" +
" ELSE es.\"name\"\n" +
" END status_name \n" +
"from public.\"event\" e join public.event_status es on e.status_id = es.id",
countQuery = "select count(*) from public.\"event\"",
nativeQuery = true)
Page<Event> findAllWithDynamicStatusName(Specification<Event> spec, Pageable pageable);
}
But unfortunately Specification doesn't work with native query. Does anyone know how to rewrite this SQL to JPQL or HQL? The main problem is I can't use SQL CASE structure like I am using it in the native query.
Or maybe you can give me an advise on how to make it works with Specification and native queries?
Thank you very much for your answers!