java - 在 Spring 选择的 Where 中使用 'in' 而不是 '=' 的异常

Question

我正在使用 Spring 来选择一个值。这是存储库中的查询:

@Query(value = "select * from incidents where descriptioninfo like %:descriptioninfo% and fixed = :fixed and starttimestamp > :startTime and deviceid in (select id from devices) and deviceid in (select deviceid from devicesmaps) and (incidentseverityid in (:incidentseverityid))  order by starttimestamp desc limit :start, :length", nativeQuery = true)
public List<IncidentEntity> getByStarttimestampAndDescriptioninfoAndSeverityAndFixed(@Param("startTime") String startTime, @Param("descriptioninfo") String descriptioninfo, @Param("incidentseverityid") List<Integer> incidentseverityid, @Param("start") int start, @Param("length") int length, @Param("fixed") int fixed);

如果我运行它，我会得到异常:

Parameter with that name [descriptioninfo] did not exist

奇怪的是，将选择更改为:

@Query(value = "select * from incidents where descriptioninfo like %:descriptioninfo% and fixed = :fixed and starttimestamp > :startTime and deviceid in (select id from devices) and deviceid in (select deviceid from devicesmaps) and (incidentseverityid = (:incidentseverityid))   order by starttimestamp desc limit :start, :length", nativeQuery = true)
public List<IncidentEntity> getByStarttimestampAndDescriptioninfoAndSeverityAndFixed(@Param("startTime") String startTime, @Param("descriptioninfo") String descriptioninfo, @Param("incidentseverityid") List<Integer> incidentseverityid, @Param("start") int start, @Param("length") int length, @Param("fixed") int fixed);

(仅从

(incidentseverityid in (:incidentseverityid)) 

到

(incidentseverityid = (:incidentseverityid)) 

修复异常并检索所需的值!

在不同的参数(“incidentseverityid”而不是异常“descriptioninfo”中的参数)上从“in”到“=”的最小变化可以解决问题!

为什么？

• 使用“in”或“=”进行选择在 MySQL Workbench 中使用相同的参数。
• 请不要告诉我将 :param 更改为 ?1 because I had a problem with ? in java 8 .
• 注意表在MySQL中被转储和恢复甚至被修复，并且它的引擎在InnoDB和MyISAM之间发生了变化。

• 另请注意 - 即使创建一个名为“IncidentsServeritiesEntity”的类作为子类并使用适当的 int ID 也无济于事 - 只能从“in”切换到“=”。

  @Param("incidentseverityid") List<IncidentsServeritiesEntity> incidentseverityid
  

• 如果我使用其中之一，则选择不起作用:

  identseverityid in :incidentseverityid
  

或:

    (identseverityid in (:incidentseverityid))

(带括号)。

Answer 1

解决方案有点难看但有效:用 = 代替 in 定义缺少的内容，创建一个长的 or 命令并传递

String allParams = "";

for (String currParam : param)
{
    allParams += ("param = " + currParam + " or "); 
}

allParams = allParams .substring(5, allParams .length() - 3);