Spring Data JPA - Unable to reuse parameter

Question

I'm attempting to use spring-data-jpa to select an entity containing all of the provided parameter values.

My Entity looks as follows:

@Entity
@Table(name = "books", schema = Constants.BENCHMARKS)
@NamedEntityGraph(name = "BookEntity.all", attributeNodes = @NamedAttributeNode("aliases"))
public class BookEntity
{
    @Id
    private String isbn = UUID.randomUUID().toString();

    @Column(name = "title")
    private String title;

    @Column(name = "description")
    private String description;

    @CollectionTable(schema = Constants.BENCHMARKS, name = "book_tags", joinColumns = @JoinColumn(name = "book_isbn"))
    @ElementCollection(fetch = FetchType.EAGER)
    private List<String> aliases;
}

My method looks as follows

@EntityGraph(value = "BookEntity.all", type = EntityGraphType.LOAD)
@Query("SELECT x FROM BookEntity x " + 
        "WHERE x IN (" + 
        "    SELECT y FROM BookEntity y" + 
        "    INNER JOIN y.aliases yt" + 
        "    WHERE yt IN (" + 
        "        :aliases" + 
        "    )" + 
        "    GROUP BY y" + 
        "    HAVING COUNT( DISTINCT yt) = COUNT(" + 
        "        :aliases)" + 
        "    )")
Iterable<BookEntity> findAllByAliasesContainsAll(@Param("aliases") Collection<String> aliases);

When I run the method I get the following exception

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: : near line 1, column 139 [select b from org.cubrc.example.hibernate.books.BookEntity b where :aliases member of b.aliases group by b.isbn having count(b) >= count( :aliases )]

I've already looked at How do I reuse a parameter witha Spring-Data-JPA Repository? which is where I got my original syntax. I've also looked at a number of other posts, but none of them are actually attempting to reuse a parameter in their query. Certainly none of them are working on as basic of an example (I'm not even mixing entities here).

I guess to sum things up with an actual question, what is wrong with my query syntax that is causing it to parse in correctly?

Response: Try including the parameter a second time

Passing in the parameter a second time did not change the result, it still complains about the colon, leading me to believe that it's not actually a reuse issue so much as an issue with count

Response: Try using SIZE instead of COUNT

Changing count to SIZE resulted in a new error

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Unexpected expression ? found for collection function size [SELECT x FROM org.cubrc.example.hibernate.books.BookEntity x WHERE x IN (    SELECT y FROM org.cubrc.example.hibernate.books.BookEntity y    INNER JOIN y.aliases yt    WHERE yt IN (        :aliases    )    GROUP BY y    HAVING COUNT( DISTINCT yt) = SIZE(        :aliases)    )]

Working Workaround:

I tried using the size manually, as in the following

@EntityGraph(value = "BookEntity.all", type = EntityGraphType.LOAD)
@Query("SELECT x FROM BookEntity x " + 
        "WHERE x IN (" + 
        "    SELECT y FROM BookEntity y" + 
        "    INNER JOIN y.aliases yt" + 
        "    WHERE yt IN (" + 
        "        :aliases" + 
        "    )" + 
        "    GROUP BY y" + 
        "    HAVING COUNT( DISTINCT yt) = (" + 
        "        :aliasesSize)" + 
        "    )")
Iterable<BookEntity> findAllByAliasesContainsAll(@Param("aliases") Collection<String> aliases, @Param("aliasesSize") long aliasesSize);

Doing so worked, so I'll use it for now. However, it'd be nice to avoid making the user pass in two parameters when I should really only need one.

does it work if you pass List of aliases twice and use different `@Param` names? — pvpkiran, Jul 19 '18 at 14:40

score 0 · Answer 1 · answered Jul 19 '18 at 14:48

It seems like actual problem is you are trying to use parameter as argument for count() function, which is not expected. As I understand idea is to check result with size of parameter collection. For that you can try to use 'SIZE()' JPA function or pass length of aliases as another parameter.

Spring Data JPA - Unable to reuse parameter

1 Answers1