如何使 LIMIT 在 Grails HQLexecuteUpdate() 中工作?

发布于 2024-10-15 12:26:40 字数 406 浏览 7 评论 0原文

我正在运行 grails 1.3.6 并且我有以下代码:

String hql = '''
    UPDATE
        ApiResponse a
    SET
        a.lockId = :lockId
    WHERE
        a.lockId = 0
    ORDER BY
        a.dateAdded asc
    LIMIT 5
    '''
ApiResponse.executeUpdate(hql, [lockId : workerId])

看来此代码更新了 DB 中的所有行而不是 5 个最旧的条目。这是否意味着 LIMIT 在 HQL 中不起作用?请帮助我如何在 GORM 或 HQL 中实现相同的 SQL 逻辑。基本上,我需要使用 LIMIT 进行批量更新。

I'm running grails 1.3.6 and I have this code:

String hql = '''
    UPDATE
        ApiResponse a
    SET
        a.lockId = :lockId
    WHERE
        a.lockId = 0
    ORDER BY
        a.dateAdded asc
    LIMIT 5
    '''
ApiResponse.executeUpdate(hql, [lockId : workerId])

It seems that this code updates all rows in DB instead of the 5 oldest entries. Does this mean LIMIT is not working in HQL? Please help me how to achieve the same SQL logic in GORM or HQL. Basically, I need to do a bulk update using LIMIT.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

给不了的爱 2024-10-22 12:26:40

我做什么(grails 1.3.7):

ExAus.executeQuery( "select distinct <field> from <controller>", [max: 20, offset: 0] )

what i do (grails 1.3.7):

ExAus.executeQuery( "select distinct <field> from <controller>", [max: 20, offset: 0] )
为人所爱 2024-10-22 12:26:40

在等待有人回复的同时,我想我找到了解决方法。就是这样:

def c = ApiResponse.createCriteria()
def targetList = c.list {
    eq('lockId', 0)
    maxResults(5)
    order("dateAdded", 'asc')
}

String hql = '''
    UPDATE
        ApiResponse
    SET
        lockId = :lockId
    WHERE
        id in (:ids)
'''
ApiResponse.executeUpdate(hql, [lockId : workerId, ids: targetList.collect {it.id}])

我相信这种方法仍然可以被认为与原来的逻辑相同。然而,这必须进行 2 次查询。

请随意建议其他方法。谢谢!

While waiting for someone to reply, I think I found a workaround. Here it is:

def c = ApiResponse.createCriteria()
def targetList = c.list {
    eq('lockId', 0)
    maxResults(5)
    order("dateAdded", 'asc')
}

String hql = '''
    UPDATE
        ApiResponse
    SET
        lockId = :lockId
    WHERE
        id in (:ids)
'''
ApiResponse.executeUpdate(hql, [lockId : workerId, ids: targetList.collect {it.id}])

I believe this approach can still be considered same logic with the original. However, this has to make 2 queries.

Feel free to suggest other approaches. Thanks!

两人的回忆 2024-10-22 12:26:40

我知道这篇文章很旧,但问题仍然相关,因为我遇到了同样的问题。

我转而使用 Groovy sql (jdbc)。

您需要首先使用以下方法在服务/控制器中注入 dataSource 对象:

def dataSource

然后您可以在您的方法中执行此操作:

String sqlQuery = '''
UPDATE
    API_RESPONSE a
SET
    a.LOCK_ID = :lockId
WHERE
    a.LOCK_ID = 0
ORDER BY
    a.DATE_ADDED asc
LIMIT 5
'''
def sql = new Sql(dataSource)
sql.executeUpdate(sqlQuery, [lockId : workerId])

请注意,您将需要在 sql 查询中使用数据库本机表和列名称。

I know the post is quite old but the question is still relevant since I had the same problem.

I fell back to using Groovy sql (jdbc) instead.

You will need to inject the dataSource object within your service/controller first by using:

def dataSource

Then you may do this in your method:

String sqlQuery = '''
UPDATE
    API_RESPONSE a
SET
    a.LOCK_ID = :lockId
WHERE
    a.LOCK_ID = 0
ORDER BY
    a.DATE_ADDED asc
LIMIT 5
'''
def sql = new Sql(dataSource)
sql.executeUpdate(sqlQuery, [lockId : workerId])

Note that you will need to use the database native table and column names in the sql query.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文