SQL到HQL查询Grails [英] SQL to HQL query Grails

问题描述

我有一个SQ：查询，我想在Grails中使用，但需要将其转换为HQL，以便我可以将它用于findAll方法。代码就像

  artifacts = Artifact.findAll（
FROM Artifact WHERE id NOT IN（+ 
SELECT artifact_id FROM Classification+ 
WHERE active =：active）AND document_id =：doc_id，
 [active：'1'，doc_id：document.id，max：limit，offset： startIndex]）
  

这给了我stringIndexOutofBounds错误。我认为这可能是因为语法，因为SQL在数据库上工作正常。

解决方案

您可以使用类和字段名称HQL以及SQL中的表和列名称。所以你不应该在HQL查询中看到下划线（至少不是很多）。此外，这不太可能是一个因素，但是从 findAll 运行的HQL查询以及从 executeQuery 运行的HQL查询可以稍微不同。我不记得有什么区别，但无论涉及哪个域类， executeQuery 都是正确的。

很难知道没有看到域类，但看起来像 artifact_id 应该是 artifact.id ，并且 document_id 应该是 document.id 。而且由于你有Document实例，所以比较对象而不是它们的ID更适合于O-O。最后我假定 active 是一个布尔属性，所以它需要一个布尔值而不是1或0.因此，把这些放在一起，我最好的猜测是这是你想要的：



  def artifacts = Artifact.executeQuery（
FROM Artifact WHERE id NOT IN（ + 
SELECT artifact.id FROM分类+ 
WHERE active =：activeAND document =：doc，
 [active：true，doc：document]，
 [max：limit，offset：startIndex]）
  

请注意，您需要从pagination controls中分离param值分成两张地图。


I have an SQ: query which I want to use in Grails but need to convert it to HQL so I could use it for findAll method. The code goes like

artifacts = Artifact.findAll(
   "FROM Artifact WHERE id NOT IN ( " +
   "SELECT artifact_id FROM Classification " +
   "WHERE active = :active) AND document_id =:doc_id",
   [active: '1', doc_id:document.id, max:limit, offset:startIndex])

This is giving me stringIndexOutofBounds error. I think it s probably because of the syntax because the SQL works fine on the database.

解决方案

You use class and field names in HQL, and table and column names in SQL. So you shouldn't see underscores (at least not many) in HQL queries. Also, it's unlikely to be a factor here, but HQL queries run from findAll and those run from executeQuery can be slightly different. I can't remember what the difference is, but executeQuery is the correct one no matter which domain class is involved.

It's hard to know without seeing the domain classes, but it seems like artifact_id should be artifact.id, and document_id should be document.id. And since you have the Document instance, it's more O-O-proper to compare the objects and not their ids. Finally I'm assuming that active is a boolean property, so it needs a boolean value and not a 1 or 0. So, putting this all together, my best guess is that this is what you want:

def artifacts = Artifact.executeQuery(
   "FROM Artifact WHERE id NOT IN ( " +
   "SELECT artifact.id FROM Classification " +
   "WHERE active = :active) AND document =:doc",
   [active: true, doc:document],
   [max:limit, offset:startIndex])

Note that you need to separate param values from pagination controls into two maps.

这篇关于SQL到HQL查询Grails的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！