JPA, Spring Webservice Select with NOT IN and IN

Question

I'm trying to do this select:

SELECT c FROM Incident c 
WHERE c.incidentID IN 
  ( 
   SELECT DISTINCT d.incidentID FROM TagIncident d WHERE tagName IN ( d.tagName=?1 ) 
   AND d.incidentID NOT IN 
   (SELECT a.incidentID FROM TagIncident a WHERE tagName IN (a.tagName=?2))    
  )

In my system with JPA/Spring I'm getting the error:

"HTTP Status 500 - Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: An exception occurred while creating a query in EntityManager:"

Is something that I'm doing wrong in the syntax? I tested it on my Database(HANA) and it worked alright.

Thanks for any help!

Edit More Errors Log

My latest try was:

SELECT c FROM Incident c WHERE c.incidentID IN 
( SELECT DISTINCT d.incidentID FROM TagIncident d WHERE d.tagName IN 
( d.tagName=?1 ) AND d.incidentID NOT IN 
( SELECT a.incidentID FROM TagIncident a WHERE a.tagName IN (a.tagName=?2) ))

Edit

Exception Description: Syntax error parsing [SELECT c FROM Incident c WHERE c.incidentID IN ( SELECT DISTINCT d.incidentID FROM TagIncident d WHERE d.tagName IN ( d.tagName=?1 ) AND d.incidentID NOT IN ( SELECT a.incidentID FROM TagIncident a WHERE a.tagName IN (a.tagName=?2) ))].  [117, 131] 
The expression at index {0} is not a valid expression. [215, 229] 
The expression at index {0} is not a valid expression.; nested exception is java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:  Exception Description: Syntax error parsing [SELECT c FROM Incident c WHERE c.incidentID IN ( SELECT DISTINCT d.incidentID FROM TagIncident d WHERE d.tagName IN ( d.tagName=?1 ) AND d.incidentID NOT IN ( SELECT a.incidentID FROM TagIncident a WHERE a.tagName IN (a.tagName=?2) ))].  [117, 131] 
The expression at index {0} is not a valid expression. [215, 229] 
The expression at index {0} is not a valid expression.] with root cause Local Exception Stack:  Exception [EclipseLink-0] (Eclipse Persistence Services - 2.6.0.v20150309-bf26070): org.eclipse.persistence.exceptions.JPQLException Exception Description: Syntax error parsing [SELECT c FROM Incident c WHERE c.incidentID IN ( SELECT DISTINCT d.incidentID FROM TagIncident d WHERE d.tagName IN ( d.tagName=?1 ) AND d.incidentID NOT IN ( SELECT a.incidentID FROM TagIncident a WHERE a.tagName IN (a.tagName=?2) ))].  [117, 131] 
The expression at index {0} is not a valid expression. [215, 229] 
The expression at index {0} is not a valid expression.

Latest Try:

List<String> list_add_tags = new ArrayList<String>();
List<String> list_remove_tags = new ArrayList<String>();

// creating custom sql_query
String sql_query = "SELECT c FROM Incident c WHERE c.incidentID IN ( SELECT DISTINCT(d.incidentID) FROM TagIncident d WHERE d.tagName IN ( :add_tags ) AND d.incidentID NOT IN ( SELECT a.incidentID FROM TagIncident a WHERE a.tagName IN (:remove_tags)))";

TypedQuery<Incident> query = em.createQuery(sql_query, Incident.class);

query.setParameter("add_tags", list_add_tags);
query.setParameter("remove_tags", list_remove_tags);

return query.getResultList();

Still doesn't work. =(

ERROR:

You have attempted to set a value of type class java.util.ArrayList for parameter add_tags with expected type of class java.lang.String

Which JPA provider are you using? Hibernate, EclipseLink, OpenJPA or one of the lesser-known DataNucleaus, Toplink, ObjectDB? In Hibernate you can assign lists, as is shown in the examples in section 4.6.17.5 the JPA specifications, but in their own words they thought they supported it as a bonus feature. Maybe you should try removing the parentheses around the parameter? The official example does imply you shouldn't need them. — coladict, Oct 05 '16 at 14:39
EclipseLink 2.6.0, removed the parenthesis from the IN with the lists and it worked, what could I understand by doing that? How do I check my JPA version? — Matheus Bica, Oct 05 '16 at 15:44

coladict · Accepted Answer · 2016-10-05T14:43:34.053

1

Normally I only use native queries, because I can test them easier, but try this:

SELECT c FROM Incident c 
WHERE c.incidentID IN 
  ( 
   SELECT DISTINCT d.incidentID FROM TagIncident d WHERE tagName IN :at 
   AND d.incidentID NOT IN 
   (SELECT a.incidentID FROM TagIncident a WHERE tagName IN :rt )    
  )

This should work with query.setParameter("tag", theListOfTags). Just be aware that versions of Hibernate before 5.0.7 have a syntax problem with parameters in parentheses.

An empty list will also generate a syntax error.

JPA specifications show this as valid syntax in their examples, so any JPA provider should support it:

SELECT e
FROM Employee e
WHERE TYPE(e) IN :empTypes

edited Oct 05 '16 at 14:43

answered Oct 03 '16 at 20:37

coladict

4,799
1
16
27

The tags are different, the tag is something I don't want in the last Select and it is some tag that I want in the second Select. I will try without parenthesis. Thanks! – Matheus Bica Oct 03 '16 at 20:41
It doesn't have to be called `tag`. That's just using named parameters, so you don't have to use positional and call `setParameter` twice, or keep track of positions in complex queries with lots of parameters. The biggest correction I made was removing `[ad].tagName=?1` from there, because you are making the comparison with the `IN (list of values)` part. – coladict Oct 03 '16 at 20:48
You are right! It's a list! I will try that, thanks! – Matheus Bica Oct 03 '16 at 20:55
If i just add it as: `SELECT c FROM Incident c WHERE c.incidentID IN ( SELECT DISTINCT d.incidentID FROM TagIncident d WHERE tagName IN ( tag1,tag2,tag3 ) AND d.incidentID NOT IN (SELECT a.incidentID FROM TagIncident a WHERE tagName IN ( tag4,tag5,tag6 )) )` It should work didn't? – Matheus Bica Oct 05 '16 at 13:39
Updated my latest try, pls check if you can! – Matheus Bica Oct 05 '16 at 13:55
I removed the parenthesis and inserted as you said and it worked like a charm. Thanks a lot mate! I guess my version EclipseLink 2.6.0 does not support parenthesis in NOT IN or IN besides when there is a select inside. You saved my day! =D – Matheus Bica Oct 05 '16 at 15:45

score 0 · Answer 2 · answered Oct 03 '16 at 20:19

0

Try using this JPA query

SELECT c FROM Incident c, TagIncident t
WHERE c.incidentID = t.incidentID
AND t.tagName = ?1
AND t.tagName != ?2

Also if you enable hibernate logging then you'll be able to see the generated queries and see if they work in external SQL program.

logging.level.org.hibernate=DEBUG

answered Oct 03 '16 at 20:19

11thdimension

10,333
4
33
71

This SQL will result in different result. =( Its not equivalent, where do I set this Logging? in the Bean? – Matheus Bica Oct 03 '16 at 20:26
If you're using spring boot you can put this property into `application.properties` file. – 11thdimension Oct 03 '16 at 20:28

JPA, Spring Webservice Select with NOT IN and IN

2 Answers2