Package io.jmix.aitools.dataload.tool

Class JpqlExecutorTool

java.lang.Object
io.jmix.aitools.dataload.tool.JpqlExecutorTool
All Implemented Interfaces:
DataLoadAiTool, JmixAiTool
@Component("aitls_JpqlExecutorTool") public class JpqlExecutorTool extends Object implements DataLoadAiTool
Spring AI tool that validates, repairs if needed and executes a read-only JPQL query through JpqlExecutionService, returning the fetched rows.

  • Field Details

  • Constructor Details

    • JpqlExecutorTool

      public JpqlExecutorTool()

  • Method Details

    • executeQuery

      @Tool(name="aitls_executeQuery", description="Validates, repairs if needed and executes a read-only JPQL query through Jmix DataManager.\n\nCRITICAL REQUIREMENTS:\n1. MUST call domain model tools first to get complete entity schema information\n2. MUST use AS aliases for ALL SELECT fields (security requirement)\n3. The \"resultProperties\" must list returned columns in exactly the same order as the select expressions\n4. Use exact entity names and attribute names from the schema\n5. Prefer tested and reliable functions over advanced/experimental features\n\nTHE REQUEST MUST CONTAIN:\n - The original user text in \"userText\"\n - A JPQL select query in \"jpql\"\n - Structured parameters with name, type and value\n - The \"resultProperties\" listing returned columns in select-clause order\n - The \"maxResults\"\n - The \"firstResult\"\n\nGENERAL QUERY RULES:\n- Only select queries are allowed\n- Never use update, delete, insert, or merge\n- Use JPQL syntax only, never SQL syntax or vendor-specific SQL functions\n- Produce JPQL suitable for Jmix DataManager\n- Always select explicit scalar or property expressions that can be returned as tabular values\n- Do not return the root entity alias itself as the selected value\n- You may select properties of the root entity, properties reached through joins, and aggregate expressions\n\nPAGINATION RULES:\n- Results are limited to 20 rows by default.\n- If the result contains \'hasMore: true\', more data is available in the database.\n- To fetch the next set of results, call this tool again with an increased \"firstResult\".\n- if the user asks for a row limit or offset, put them into \"maxResults\" and \"firstResult\" instead.\n- Use server-side aggregation (COUNT, SUM, AVG) whenever possible instead of fetching all rows.\n\nALIAS REQUIREMENT:\n\u2713 CORRECT: SELECT c.name AS clientName, COUNT(o) AS orderCount FROM Client c LEFT JOIN c.orders o GROUP BY c\nThen provide: resultProperties = [\"clientName\", \"orderCount\"]\n\n\u2717 INCORRECT: SELECT c.name, COUNT(o) FROM Client c LEFT JOIN c.orders o GROUP BY c\n(Missing AS aliases and resultProperties parameter)\n\nJPQL SYNTAX RULES (Jmix/EclipseLink):\n- Use entity names not table names\n- Use attribute names not column names\n- Use entity relationships for joins not foreign keys\n- No SELECT * allowed - specify exact attributes\n- Use COUNT(entity) not COUNT(*)\n- AS aliases are MANDATORY for all SELECT fields (security and parsing requirement)\n- No subqueries in SELECT clause\n- Use GROUP BY entity not GROUP BY entity.id\n\nIMPORTANT - AVOID JPQL RESERVED WORDS AS ALIASES:\nNever use these words as AS aliases: id, position, user, order, table, group, where, select, from, join,\nleft, right, inner, outer, on, and, or, not, in, exists, between, like, is, null, true, false,\ncount, sum, avg, max, min, distinct, all, any, some, union, except, intersect, case, when, then,\nelse, end, new, constructor, size, index, key, value, entry, type, treat, current_date, current_time,\ncurrent_timestamp, local, date, time, timestamp, year, month, day, hour, minute, second.\n\n\u2713 CORRECT: SELECT co.position AS jobPosition (not AS position)\n\u2717 INCORRECT: SELECT co.position AS position\n\nDATE AND TIME RULES:\n- Never use SQL date functions such as DATE_SUB, DATE_ADD, NOW, CURDATE, or INTERVAL expressions\n- Use CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP without parentheses when they are needed in JPQL\n- You may use supported Jmix date macros when they fit the request: @between, @today, @dateEquals, @dateBefore, @dateAfter\n- You may use supported Jmix relative date time constants when they fit the request:\n FIRST_DAY_OF_CURRENT_YEAR, LAST_DAY_OF_CURRENT_YEAR, FIRST_DAY_OF_CURRENT_MONTH, LAST_DAY_OF_CURRENT_MONTH,\n FIRST_DAY_OF_CURRENT_WEEK, LAST_DAY_OF_CURRENT_WEEK, START_OF_CURRENT_DAY, END_OF_CURRENT_DAY,\n START_OF_YESTERDAY, START_OF_TOMORROW, START_OF_CURRENT_HOUR, END_OF_CURRENT_HOUR, START_OF_CURRENT_MINUTE,\n END_OF_CURRENT_MINUTE\n- For requests such as today, yesterday, this month, last month, before today, or after today, prefer\n supported Jmix date macros or relative date time constants over SQL-specific date arithmetic.\n- If a date range requires application-side calculation, prefer named parameters such as :fromDate\n and :toDate instead of SQL-specific date arithmetic\n- Use ISO date literals in single quotes for fixed dates:\n - WHERE o.date >= \'2024-01-01\'\n - WHERE o.date BETWEEN \'2024-01-01\' AND \'2024-01-31\'\n\nMACROS AND TIME CONSTANTS EXAMPLES:\n- Last 30 days: @between(o.date, now-30, now, day)\n- Last month: @between(o.date, now-1, now, month)\n- Today only: @today(o.date)\n- Yesterday: @dateEquals(o.date, now-1)\n- Future dates: @dateAfter(o.date, now)\n- Past dates: @dateBefore(o.date, now-7)\n- Current month with constants: o.date >= FIRST_DAY_OF_CURRENT_MONTH and o.date <= LAST_DAY_OF_CURRENT_MONTH\n- Today with constants: o.createdAt >= START_OF_CURRENT_DAY and o.createdAt <= END_OF_CURRENT_DAY\n\n\u2713 CORRECT for last 30 days:\nSELECT o.number AS orderNumber, o.total AS orderTotal FROM Order_ o WHERE @between(o.date, now-30, now, day)\n\n\u2717 INCORRECT: Don\'t use CURRENT_DATE arithmetic like \"CURRENT_DATE - 30\"\n\nPARAMETER HANDLING:\nParameters are automatically converted to appropriate types when possible:\n- Date strings \u2192 LocalDate/LocalDateTime/OffsetDateTime (e.g., \"2024-01-15\")\n- Numeric strings \u2192 BigDecimal/Integer/Long (e.g., \"1500.50\", \"42\")\n- UUID strings \u2192 UUID for entity IDs\n- Boolean strings \u2192 Boolean (\"true\", \"false\")\n- Other strings remain as strings (LIKE patterns, etc.)\n\nENUM PARAMETERS (CRITICAL):\n- For enum properties, use id from domain model enums mapping (enums.<ENUM_NAME>.id).\n- Do NOT pass enum constant names when enums provides numeric/string IDs.\n- Example for Invoice.status: enums {NEW: {id: 10}, PENDING: {id: 20}, OVERDUE: {id: 30}, PAID: {id: 40}}\n \u2713 CORRECT: WHERE i.status = :status with parameters {\"status\": 40}\n \u2717 INCORRECT: WHERE i.status = :status with parameters {\"status\": \"PAID\"}\n- For IN filters, also pass mapped values list (e.g., {\"statuses\": [20, 30]}).\n\nJMIX JPQL EXTENSIONS AND FUNCTIONS:\n\nDATE/TIME FUNCTIONS:\n- EXTRACT(field FROM date) - Extract date/time parts: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND\n Examples: EXTRACT(YEAR FROM o.date), EXTRACT(MONTH FROM o.date)\n- CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP - Current date/time values\n- DATE(datetime) - Extract date part from datetime\n- TIME(datetime) - Extract time part from datetime\n\nMATHEMATICAL FUNCTIONS:\n- Basic arithmetic: +, -, *, / (e.g., o.total * 2, o.total + 1000)\n- Parentheses for operation precedence\n- ABS(number) - Absolute value (limited support)\n- ROUND(number, digits) - Round to specified decimal places (limited support)\n\nSTRING FUNCTIONS:\n- CONCAT(str1, str2, ...) - Concatenate strings\n- SUBSTRING(string, start, length) - Extract substring\n- LENGTH(string) - String length\n- LOCATE(substring, string, start) - Find substring position\n- UPPER(string) - Convert to uppercase\n- LOWER(string) - Convert to lowercase\n- TRIM(string) - Remove leading/trailing whitespace\n- LIKE with wildcards (%, _) - Pattern matching (recommended)\n\nCONDITIONAL FUNCTIONS:\n- CASE WHEN condition THEN result ELSE alternative END - Conditional expressions\n- COALESCE(value1, value2, ...) - Return first non-null value\n- NULLIF(value1, value2) - Return null if values are equal\n\nAGGREGATE FUNCTIONS:\n- COUNT(entity) - Count entities (use entity, not *)\n- SUM(expression) - Sum of values\n- AVG(expression) - Average value\n- MAX(expression) - Maximum value\n- MIN(expression) - Minimum value\n- DISTINCT - Use with aggregates for unique values\n\nCOLLECTION FUNCTIONS:\n- SIZE(collection) - Collection size\n- IS EMPTY / IS NOT EMPTY - Check if collection is empty\n\nDATE MACROS (Jmix-specific):\n- @between(field, start, end, unit) - Date range queries\n Units: year, month, day, hour, minute, second\n Examples: @between(o.date, now-30, now, day), @between(o.date, now-1, now, month)\n- @today(field) - Today\'s date\n- @dateEquals(field, value) - Date equality\n- @dateBefore(field, value) - Date before\n- @dateAfter(field, value) - Date after\n- Special values: now, now-N (N units ago)\n\nBEST PRACTICES (TESTED AND RELIABLE):\n- Use EXTRACT for date parts instead of proprietary functions\n- Use LIKE with wildcards instead of REGEXP for pattern matching\n- Use basic arithmetic (+, -, *, /) instead of advanced mathematical functions\n- Use Jmix date macros (@between, @today) for date filtering\n- Test complex functions in development before using in production queries\n\nCRITICAL JPQL PARSER LIMITATIONS:\n\u2717 INCORRECT: COUNT(CASE WHEN o.date >= \'2025-01-01\' THEN 1 END)\n\u2717 INCORRECT: SUM(CASE WHEN @between(o.date, now-90, now, day) THEN o.total ELSE 0 END)\n\n\u2713 CORRECT: Use separate queries with simple WHERE clauses for period comparisons.\n\nWithout domain model tools first, queries may fail due to incorrect entity/attribute names.\nFor date ranges, prefer Jmix macros over parameters for better handling.\n") public JpqlExecutionResult executeQuery(@ToolParam(description="Structured request containing the original user text and the JPQL draft to execute.") JpqlExecutionRequest request, org.springframework.ai.chat.model.ToolContext toolContext)
      Validates, repairs if needed and executes the JPQL query described by the request.
      Parameters:
      request - execution request with the JPQL draft and its parameters
      toolContext - Spring AI tool context used to publish status updates
      Returns:
      execution result with the fetched rows, or failure details