java - JPA、Spring Webservice 使用 NOT IN 和 IN 选择

Question

我正在尝试执行此选择:

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

通常我只使用 native 查询，因为我可以更轻松地测试它们，但试试这个:

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