Malformed query using IN

Question

I have this structure:

public enum SaleItemType {
    CRUISE,
    DAILY_HOSTING
}

public class Estimate {
    ...
    private List<SaleItemType> interestedSaleItemTypes;

    @Column(name = "sale_item_type")
    @CollectionTable(name = "estimate_sale_item_type", joinColumns = @JoinColumn(name = "estimate_id"))
    @ElementCollection(targetClass = SaleItemType.class)
    @Enumerated(EnumType.STRING)
    public List<SaleItemType> getInterestedSaleItemTypes() {
        return interestedSaleItemTypes;
    }
}

And i'm trying to do a simple query:

String q = "FROM " + Estimate.class.getSimpleName() + " e" + " WHERE e.interestedSaleItemTypes IN :a";
TypedQuery<Estimate> query1 = getEm().createQuery(q, Estimate.class);
query1.setParameter("a", EnumSet.of(SaleItemType.CRUISE));
query1.getResultList();

I'm getting this query(and error) on the log:

DEBUG SQL:92 - select estimate0_.id as id1_25_, estimate0_.average_ticket as average_2_25_, estimate0_.description as descript3_25_, estimate0_.end_date as end_date4_25_, estimate0_.pax_quantity as pax_quan5_25_, estimate0_.start_date as start_da6_25_ from estimate estimate0_ cross join estimate_sale_item_type interested1_ where estimate0_.id=interested1_.estimate_id and (. in (?))

DEBUG SqlExceptionHelper:124 - could not extract ResultSet [n/a] org.postgresql.util.PSQLException: No value specified for parameter 1.

Why hibernate is doing this query?

Im using Hibernate 5.1 Final

Checkout http://stackoverflow.com/questions/4828049/in-clause-in-hql-or-java-persistence-query-language — uncaught_exception, Apr 12 '16 at 14:26

score 1 · Accepted Answer · answered Apr 12 '16 at 14:28

1

The IN expression can be used to test if a value is in a collection but interestedSaleItemTypes is not a simple value but itself a collection. Therefore use MEMBER OF:

String q = "FROM Estimate e WHERE :a MEMBER OF e.interestedSaleItemTypes";
TypedQuery<Estimate> query1 = getEm().createQuery(q, Estimate.class);
query1.setParameter("a", SaleItemType.CRUISE);

answered Apr 12 '16 at 14:28

wero

30,527
3
46
72

Yep, it worked. Funny fact, i knew how to use `member of`but for some reason this time i didn't even considered it. – Solci Apr 13 '16 at 10:34

score 0 · Answer 2 · answered Apr 12 '16 at 14:24

0

Did you try to put parenthesis in your IN clause?

I don't know if it's required, but in all tutorials that I found, always had the parenthesis. http://www.postgresqltutorial.com/postgresql-in/

Also, as the IN clause is expecting a list of values you can use the setParameterList instead of setParameter. Try this:

String q = "FROM " + Estimate.class.getSimpleName() + " e" + " WHERE e.interestedSaleItemTypes IN (:a)";
TypedQuery<Estimate> query1 = getEm().createQuery(q, Estimate.class);
query1.setParameterList("a", EnumSet.of(SaleItemType.CRUISE));
query1.getResultList();

answered Apr 12 '16 at 14:24

Geny Herrera

66
4

1

the IN expression of SQL needs parentheses but the IN expresssion of JPA does not – wero Apr 12 '16 at 14:30
@wero True, but Hibernate for example needs parenthesis in some versions, check out [this post](http://stackoverflow.com/questions/2772305/jpql-in-clause-java-arrays-or-lists-sets/2793690#2793690) – Predrag Maric Apr 12 '16 at 14:34
There is no such method Query.setParameterList() in JPA – Neil Stockton Apr 12 '16 at 15:08

Malformed query using IN

2 Answers2