sql - 如何嵌套 groovy sql 结果集并为其生成 JSON？

Question

在我的数据库中，我有 Order 和相应的行。我想使用 groovy 为每个订单生成 JSON，并在下面写了一段代码，但它没有按预期工作。

def OrderResultSet=conn.rows('select ORDER_NUMBER,COMPANY_NAME from ORDERS where ORDER_NUMBER=74')
OrderResultSet.each{ 
    def LineResultset=conn.rows('select * from LINE where HEADER_ID=:ORDER_NUMBER',[ORDER_NUMBER:it.ORDER_NUMBER])
}

如何链接结果集并生成 JSON，如下所示：

{  
   "ORDER_NUMBER":7493767,
   "COMPANY_NAME":"WWT",
   "LINE":[  
      {  
         "HEADER_ID":7493767,
         "LINE_ID":12,
         "INSTALLSITE_ID":22,
         "TEXT":"world"
      },
      {  
         "HEADER_ID":7493767,
         "LINE_ID":11,
         "INSTALLSITE_ID":21,
         "TEXT":"hello"
      }
   ]
}

标签： sqlgroovy

Answer 1

import groovy.json.JsonBuilder

def OrderResultSet=conn.rows('select ORDER_NUMBER,COMPANY_NAME from ORDERS where ORDER_NUMBER=74')
OrderResultSet.each{ 
    it.LINE=conn.rows('select * from LINE where HEADER_ID=:ORDER_NUMBER',[ORDER_NUMBER:it.ORDER_NUMBER])
}

println new JsonBuilder(OrderResultSet).toPrettyString()