QueryDSL in Extension Scripts

The Extension Script Tester supports QueryDSL, a type-safe query framework that you can use to construct SQL queries using a fluent, object-oriented API. It provides a Java query framework that enables direct access to database data.

QueryDSL uses auto-generated table representations called Q classes. These classes are mapped to specific database tables and columns in WFM, which you can reference in a structured and error-resistant manner. For example, you can use QEmployee.employee to access the employee table, and its fields, such as employee.name or employee.active, to reference directly in the query. This approach improves readability and reduces errors from typos or incorrect column names.

To build a query, you can create a SQLQuery object and construct the logic using the provided Q classes. However, direct usage of certain methods, such as fetch(), is restricted to maintain security. Instead, the scripting environment includes QueryBuilderService, which you can inject and use to run queries safely.

To use QueryDSL in the Extension Script Tester, set the script type to dbquery-studio. You can also retrieve the SQL string generated by QueryDSL and view it, along with an explain plan that provides insight into query cost and execution paths. The Generated SQL column shows the raw SQL generated from your query, and the execution plan for your query is provided in the Explain Plan column to help you analyze its performance and structure.

By deault, the Generated SQL and Explain Plan are not automatically provided. To retrieve them, you must run the execute method with the includeExplainPlan parameter set to true. You have two options:
<T> List<T> execute(SQLQuery<T> query);
<T> List<T> execute(SQLQuery<T> query, boolean includeExplainPlan);

To include the Generated SQL and Explain Plan in the output, use the second method and pass true to include the additional query details.

See Debugging with the Extension Script Tester.

Note: The Groovy script engine uses allow list rules for QueryDSL. Scripts cannot use dynamic SQL techniques, such as building table names from input or variables. Use Q classes for table access, and avoid reflection or string-based queries.