CODEWITHSUNDEEP
spring-data-jpaspring-data
Pavel
Pavel

Reputation: 2101

Hibernate @Query throw syntax error at or near "."

I try to get field tags from the entity Article:

@Getter
@Setter
@Entity
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "articles")
public class Article {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Embedded
    private Author author;

    @Column(unique = true)
    private String title;

    private String content;

    @ElementCollection(targetClass = String.class,
            fetch = FetchType.EAGER
    )
    @CollectionTable(name = "tags",
            joinColumns = @JoinColumn(name = "article_id")
    )
    @Column(name = "tag")
    private List<String> tags = new ArrayList<>();
}

With ArticleRepository extends JpaRepository<Article, Long>, JpaSpecificationExecutor<Article> and @Query:

@Query("SELECT " +
        " new java.util.ArrayList(a.tags) " +
        " FROM Article a " +
        " WHERE a.title = :title")
List<String> findAllTagsOfArticle(String title);

And get a result:

Hibernate: 
    select
        . as col_0_0_ 
    from
        articles article0_ 
    inner join
        tags tags1_ 
            on article0_.id=tags1_.article_id 
    where
        article0_.title=?
2022-07-21 15:17:24.666  WARN 2196 --- [   scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2022-07-21 15:17:24.666 ERROR 2196 --- [   scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "."

Generated SQL query looks correct but after SELECT hibernate generate .

Why does it happens and how to fix it?

Upvotes: 0

Views: 960

Answers (1)

Davide D&#39;Alto
Davide D&#39;Alto

Reputation: 8206

You cannot pass a collection (a.tags) like that. The idea is that the constructor will receive a single row from the query result. But also, there's no need to use new ArrayList.

This should work:

@Query("select t from Article a join a.tags t where a.title = :title")
List<String> findAllTagsOfArticle(String title);

You can also simplify a bit the mapping:

    @ElementCollection
    @CollectionTable(name = "tags",
            joinColumns = @JoinColumn(name = "article_id")
    )
    @Column(name = "tag")
    private List<String> tags = new ArrayList<>();

There is no need to specify EAGER and the target class in this case.

Upvotes: 1

Related Questions