JPA 2.0 Query for Collection Map to compare to List String

Question

I'm trying to get a Map value from a joint collection to be compared to a List String. I keep getting exceptions.

Here is my Query,

 public long countByTypeAndPropertyKeyWithValue(List<String> type, String key, List<String> value, Date fromTime, Date toTime) {
    EntityManager em = entityManagerFactory.createEntityManager();
    Query query = em.createQuery("select count(o) from EbizEvent o JOIN o.properties p where o.type in :type and KEY(p)=:name and VALUE(p)= :val and o.timestamp between :fromTime and :toTime");
    query.setParameter("type", type);
    query.setParameter("name", key);
    query.setParameter("val", value);
    query.setParameter("fromTime", fromTime);
    query.setParameter("toTime", toTime);
    long count = (Long) query.getSingleResult();
    return count;
}

The EbizEvent table has a Property table which gets joint,

@ElementCollection( fetch = FetchType.EAGER)
@MapKeyColumn(name="KEY")
@Column(name="VALUE")
@CollectionTable(name="EBIZ_EVENT_PROPERTY", joinColumns=@JoinColumn(name="EVENT_ID"))
Map<String, String> properties = new HashMap<String, String>();

Problem is, the query keeps getting an exception at the Value area,

This works if my val is just a String. It errors out if I use the List.

VALUE(p)= :val

Parameter "Parameter('val')" declared in "select count(o) from EbizEvent o JOIN o.properties p where o.type in :type and KEY(p)=:name and VALUE(p)= :val and o.timestamp between :fromTime and :toTime" is set to value of "[SGP]" of type "java.util.ArrayList", but this parameter is bound to a field of type "java.lang.String".

I also have tried using IN which works on the non Property tables, but still throws me an exception.

VALUE(p) IN :val

"Encountered "VALUE ( p ) IN" at character 97, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CASE", "COALESCE", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "ELSE", "EMPTY", "END", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INDEX", "INNER", "IS", "JOIN", "KEY", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "NULLIF", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "THEN", "TRAILING", "TRIM", "TYPE", "UPDATE", "UPPER", "VALUE", "WHEN", "WHERE", , , , , , , , , ]." while parsing JPQL "select count(o) from EbizEvent o JOIN o.properties p where o.type in :type and KEY(p)=:name and VALUE(p) in :val and o.timestamp between :fromTime and :toTime". See nested stack trace for original parse error.

I have also tried :val member of VALUE(p), This threw the same error above.

Looking for anything JPA over the net is proving to be a daunting task. I can't find anything which is in the same boat.

score -1 · Answer 1 · answered Aug 25 '16 at 12:19

-1

check your query, I think it is wrong, you are using JOIN without ON.

answered Aug 25 '16 at 12:19

duardito

107
2
8

Like I said, it works if the val is not a List String but rather just a String. So how does that make the query wrong with JOIN? http://stackoverflow.com/questions/3730625/jpa-join-in-jpql – Jared Teng Aug 25 '16 at 12:22
That was not me who flagged down your Answer. Though I don't blame them. Answers are supposed to be tested to a degree or have research backing it up. As for the negative rating, maybe ask a mod to help you out. – Jared Teng Aug 26 '16 at 08:49
ok, sorry for disappointment. If your problem is when you send a list, maybe some data inside list is scaping query. – duardito Aug 26 '16 at 09:03

JPA 2.0 Query for Collection Map to compare to List String

1 Answers1