JPA, Spring Webservice Select with NOT IN and IN

Question

我正在尝试这样做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))    
  )

在我的系统与JPA/Spring我得到的错误:

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

是不是我在语法上做错了?我在我的数据库(HANA)上测试了它，它工作正常。

谢谢你的帮助!

编辑更多错误日志

我最近的一次尝试是:

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) ))

编辑

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.

最新尝试:

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();

还是不行。= (

错误:

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 1

通常我只使用本机查询，因为我可以更容易地测试它们，但试试这个:

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 )    
  )

这应该适用于query.setParameter("tag", theListOfTags)。请注意，5.0.7之前的Hibernate版本在圆括号中的参数有语法问题。

空列表也会产生语法错误。

JPA规范在其示例中将其显示为有效语法，因此任何JPA提供程序都应该支持它:

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

Answer 2

尝试使用这个JPA查询

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

另外，如果你启用hibernate日志，那么你将能够看到生成的查询，看看它们是否在外部SQL程序中工作。

logging.level.org.hibernate=DEBUG