@Query giving QuerySyntaxException

Question

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?

score 0 · Answer 1 · answered Oct 04 '21 at 06:16

0

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)

answered Oct 04 '21 at 06:16

avocato

211
3
11

Why, if the table name is `communication` ? Although I tried using `Communication`, still same exceptions. – jenny jenkins Oct 04 '21 at 06:34
@jennyjenkins its a JPA query not native one, you should address the entity class which is `Communication`. And `appointment_type_ids` should be `appointmentTypeIds` – pleft Oct 04 '21 at 06:44
Thanks for your answer! But even `@Query("SELECT c FROM Communication c WHERE ?1 = ANY(c.appointentTypeIds)")` gives the same results. What am I doing wrong? – jenny jenkins Oct 04 '21 at 06:59

score 0 · Answer 2 · answered Oct 04 '21 at 08:22

0

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")

answered Oct 04 '21 at 08:22

pleft

7,567
2
21
45

Thank you again! This compiles fine but I had to add `@ElementCollection` with the `appointmentTypeIds` field. And after that on trying to `PUT` values into table with `"appointment_type_ids":[3,8],` I get a response `{ "code": "UNSUPPORTED_OPERATION", "message": "The provided 3 is not a List!" }` – jenny jenkins Oct 04 '21 at 09:34
@jennyjenkins one step at a time. First of all does after modifying the `@Query`, `CommunicationRepository#findAppointments` work as per your intentions? If yes then your original question is answered, you should upvote/mark as correct the answer(s) that solved your problem. If you have another question like the `PUT` operation in your comment above then it is a completely different question and you should post a new question including all the necessary info for the community to help you out. thanks – pleft Oct 04 '21 at 11:50
1

I don't know if it did because I didn't have any idea how to deal with columns marked as `@ElementCollection` and so I couldn't actually test it. What however ended up working for me was `@Query(value = "SELECT * FROM communication c WHERE ?1 = ANY(c.appointment_type_ids)", nativeQuery = true) List findAppointments(Integer integer);`. Thank you for your help! – jenny jenkins Oct 05 '21 at 10:59

score 0 · Answer 3 · answered Oct 04 '21 at 08:29

0

can you try this :

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

answered Oct 04 '21 at 08:29

abdallahsaboukh

11
3

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. You can find more information on how to write good answers in the help center: https://stackoverflow.com/help/how-to-answer . Good luck – nima Oct 04 '21 at 09:14
Thank you for answering! Using this I get `ERROR: operator does not exist: integer = integer[]` when `findAppointments()` is called. – jenny jenkins Oct 04 '21 at 09:41
try to use native query if it work tell me @Query(value="SELECT c FROM Communication c WHERE ?1 in(c.appointment_type_ids)",nativeQuery = true) – abdallahsaboukh Oct 04 '21 at 10:16

@Query giving QuerySyntaxException

3 Answers3