Matheus Bica Matheus Bica - 1 month ago 24
SQL Question

JPA, Spring Webservice Select with NOT IN and IN

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

Answer

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
Comments