Unable to import groovy.sql.Sql in reports

Support
1

I am using the jmix 2.7.4 and the reports add-on. In Cuba I could use:

import groovy.sql.Sql

to get the groovy Sql instance to perform native SQL. But when I tried this in Jmix, it failed.

Script1.groovy: 1: unable to resolve class groovy.sql.Sql

How can I import the Sql instance.

CK

2

I can fix the import problem by adding this dependency to the cradle:

implementation 'org.apache.groovy:groovy-sql:5.0.4'

Is this correct ?

But now I am missing the “persistence” object in the Groovy dataset which was available in the Cuba reports. What is the fix for this ?

CK

4

Hello,

As I understand, you have a Groovy data band in a report, and you want to execute an SQL query within that Groovy code.
How about making a service to execute that SQL via EntityManager, and then call it inside the code? It works, I call the BPM engine api inside my reports…

Also, try this one (not sure if it will work, will try tomorrow):

import org.springframework.jdbc.core.JdbcTemplate

def jdbcTemplate = applicationContext.getBean(JdbcTemplate)

// Access report parameters via 'params' map
def startDate = params['startDate'].value
def endDate   = params['endDate'].value
def statusParam = params['status'].value

def rows = jdbcTemplate.queryForList("""
    SELECT 
        id,
        name,
        created_date,
        amount
    FROM orders
    WHERE created_date BETWEEN ? AND ?
      AND status = ?
    ORDER BY created_date
""", startDate, endDate, statusParam)

return rows

If it works, add logic to test params for nulls if needed.

Kind regards,
Mladen

5

Hi Mladen,

Of course we could rewrite the Groovy code to avoid the dependence on Persistence. We have hundreds of reports in Cuba reports and we are maintaining both the Cuba version (for current customers), now we need a Jmix version of the same report for our new customers. Hence, it required a lot of one time migration effort, as well as on-going effort to maintain both versions.

So, I take it there is NO way to get the same Persistence instance back in the Groovy dataset in Jmix reports, right ?

Regards,
CK

6

Hi CK!

Thank you for your question.

The recommended way to perform native SQL queries is a jdbsTemplate. Adding the groovy-sql dependency is technically correct, but it is not recommended for Jmix applications.

Best regards,
Dmitriy

8

Hello,

Sorry to hear that. I have been in such position before. I used to think “Hey, at least I have customers” to cheer myself up.
I wrote some code to convert to the new versions, wrote Velocity templates, used copy-paste while being extremely careful … today I would use AI.

I recommend you try with AI, if possible with agentic AI - it should have no problem replacing CUBA code with jdbcTemplate code, while preserving the original SQL, and even test it. It would be fast.

Kind regards,
Mladen

1 Like