gpt4 book ai didi

mysql - 如何在 Grails 2.4.0 中编写原生 SQL 查询?

转载 作者:行者123 更新时间:2023-11-29 18:49:23 28 4
gpt4 key购买 nike

我正在开发 Grails 2.4.0。我想在 Groovy Controller 中执行 native 查询。查询如下:

SELECT AVG(REPLACE(n.ep_text, 'PPM', '')), MONTH(n.date_creat) 
from notification n
where n.type = 42
GROUP BY MONTH(n.date_creat)

首先,我执行上面的查询,但它没有找到REPLACE函数,例如:

String query1 = "SELECT n.id, avg(REPLACE(n.epText, 'PPM', '')) FROM Notification as n";
def result = Notification.executeQuery(query1.toString())

我怎样才能执行其中的REPLACE函数?

其次,我对此进行了一些研发,但执行 native 查询需要 sessionFactory。无法理解如何在 Grails 2.4.0 中获取 Hibernate 的当前 session 来执行 native 查询?

如有任何帮助,我们将不胜感激。

最佳答案

为了使用 native 查询,我们可以使用 SessionFactory,它是一个 bean,我们可以简单地向 Grails Controller 或服务声明它,依赖注入(inject)将处理它。以下是使用此 bean 执行 native 查询的示例代码。

class PublicService {
def sessionFactory
def getMatchedValue(){
def currentSession = sessionFactory.currentSession
def q = "select bank.id as id, bank.credit_amount as creditAmount, bank.debit_amount as debitAmount, bank.transaction_date as transactionDate, bank.transaction_name as transactionName, receipt.cr_date as crDate, receipt.picture_reference as pictureReference, receipt.receipt_date as receiptDate, receipt.reimbursment as reimbursment, receipt.total_amount as totalAmount, receipt.vendor as vendor " +
"from bank inner join receipt on bank.debit_amount=receipt.total_amount where ((bank.transaction_date >= receipt.receipt_date) and (bank.transaction_date <= DATE_ADD(receipt.receipt_date, INTERVAL 5 DAY) ))"//sample native query
def data = currentSession.createSQLQuery(q)
data.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);//if you are using alias for query e.g bank.credit_amount as creditAmount
final result = data.list()
return result
}
}

关于mysql - 如何在 Grails 2.4.0 中编写原生 SQL 查询?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/44437428/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com