@Query giving QuerySyntaxException-CodePudding

I want to get the rows from communication table whose appointment_type_ids list contains integer.

Communication.java

@Getter
@Setter
@Entity
@Table(name = "communication")
public class Communication{


   
    @Column("id")
    private Long id;

    @Column("name")
    private String name;

    @Column("appointment_type_ids")
    private List<Integer> appointmentTypeIds;

}

CommunicationRepository.java


@Repository
public interface CommunicationRepository extends JpaRepository<Communication, Long>,
        QuerydslPredicateExecutor<Communication>, JpaSpecificationExecutor<Communication> {

    @Query("SELECT c FROM communication c WHERE ?1 = ANY(c.appointment_type_ids)")
    List<Communication> findAppointments(Integer integer);
}

I am getting the following error

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: c near line 1, column 56 [SELECT c FROM communication c WHERE ?1 = ANY(c.appointment_type_ids)]

How can I solve this?

CodePudding user response：

I think you should refer to the table via Communication name not communication. So it should be

SELECT c FROM Communication c WHERE ?1 = ANY(c.appointment_type_ids)

CodePudding user response：

ANY expression needs a subquery, I think you need MEMBER OF for your query.

@Query("SELECT c FROM Communication c  WHERE ?1 MEMBER OF c.appointentTypeIds")

CodePudding user response：

can you try this :

SELECT c FROM Communication c WHERE ?1  in(c.appointment_type_ids)