How to use native query from Groovy based Report data set

Support
#1

How we can load data using SQL script and preprocess this data somehow before printing to report?

2 Likes
#2

As it is described here: [url=https://doc.cuba-platform.com/manual-6.5/nativeQuery.html]https://doc.cuba-platform.com/manual-6.5/nativeQuery.html[/url] we can run native SQL queries and work with a result list that will contain Object[] rows.
If we want to work with Persistence and Query from a Groovy data set of a report then we have to import Persistence and AppBeans classes. We obtain Persistence from AppBeans and write Groovy code that will simply perform native SQL query and collect necessary results to a list of dictionaries:


import com.haulmont.cuba.core.Persistence
import com.haulmont.cuba.core.global.AppBeans
def result = []
def persistence = AppBeans.get(Persistence.class)
persistence.runInTransaction({
    def em = persistence.entityManager
    def q = em.createNativeQuery('select c.TITLE, c.SUMMARY from DEMO_CLIENT c')
    def resultList = q.getResultList()
    for (Object[] row : resultList) {
        result.add(['title': row[0], 'summary': row[1]])
    }
})
return result

Using the same approach we can obtain any application bean from AppBeans and call application business logic from a report.

#3

Thanks!!

#4

Thanks