Spring AI Alibaba迎来全新成员NL2SQL ---- 为其设计一个自动化报表系统吧

随着企业数字化转型的深入，数据驱动决策已成为企业核心竞争力。传统的报表系统往往需要技术人员编写复杂的SQL查询，业务人员难以直接参与。本文将介绍一个基于Spring AI Alibaba NL2SQL模块构建的智能自动化报表系统，该系统允许用户通过自然语言描述生成报表，并支持定时调度、多格式输出和智能通知等功能

仓库开源地址: github.com/kaori-seaso…

系统设计方案

1. 架构设计

自动化报表系统采用分层架构，建立在 spring-ai-alibaba nl2sql 模块之上：

graph TD  
    A["用户界面层"] --> B["控制器层"]  
    B --> C["服务层"]  
    C --> D["NL2SQL 核心层"]  
    D --> E["数据层"]  
      
    C --> F["调度器"]  
    C --> G["通知服务"]  
    C --> H["可视化服务"]  
      
    subgraph "NL2SQL 核心"  
        D --> I["StateGraph工作流"]  
        I --> J["向量存储"]  
        I --> K["数据库连接"]  
    end

2. 核心功能模块

报表管理模块：

• 报表模板的创建、编辑、删除
• 自然语言查询模板管理
• 报表分类和标签管理

调度执行模块：

• 基于 Cron 表达式的定时执行
• 事件触发执行
• 并发控制和任务队列管理

结果处理模块：

• 数据可视化（图表生成）
• 多格式输出（Excel、PDF、HTML）
• 历史数据对比分析

通知分发模块：

• 邮件通知
• 企业微信/钉钉推送
• WebHook 集成

3. 完整代码实现

3.1 报表实体类

// ReportTemplate.java - 报表模板实体  
@Entity  
@Table(name = "report_templates")  
public class ReportTemplate {  
    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private Long id;  
      
    @Column(nullable = false)  
    private String name;  
      
    @Column(columnDefinition = "TEXT")  
    private String description;  
      
    @Column(name = "natural_query", columnDefinition = "TEXT", nullable = false)  
    private String naturalQuery; // 自然语言查询  
      
    @Column(name = "cron_expression")  
    private String cronExpression; // 定时表达式  
      
    @Enumerated(EnumType.STRING)  
    private ReportStatus status;  
      
    @Column(name = "output_format")  
    private String outputFormat; // JSON, EXCEL, PDF, HTML  
      
    @Column(name = "chart_config", columnDefinition = "TEXT")  
    private String chartConfig; // 图表配置JSON  
      
    @Column(name = "notification_config", columnDefinition = "TEXT")  
    private String notificationConfig; // 通知配置JSON  
      
    @CreationTimestamp  
    private LocalDateTime createdAt;  
      
    @UpdateTimestamp  
    private LocalDateTime updatedAt;  
      
    // 省略 getters/setters  
}  
  
// ReportExecution.java - 报表执行记录  
@Entity  
@Table(name = "report_executions")  
public class ReportExecution {  
    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private Long id;  
      
    @Column(name = "template_id", nullable = false)  
    private Long templateId;  
      
    @Enumerated(EnumType.STRING)  
    private ExecutionStatus status;  
      
    @Column(name = "execution_time")  
    private LocalDateTime executionTime;  
      
    @Column(name = "completion_time")  
    private LocalDateTime completionTime;  
      
    @Column(name = "generated_sql", columnDefinition = "TEXT")  
    private String generatedSql;  
      
    @Column(name = "result_data", columnDefinition = "LONGTEXT")  
    private String resultData;  
      
    @Column(name = "error_message", columnDefinition = "TEXT")  
    private String errorMessage;  
      
    @Column(name = "output_file_path")  
    private String outputFilePath;  
      
    // 省略 getters/setters  
}

3.2 核心服务实现

// AutoReportService.java - 自动化报表核心服务  
@Service  
@Transactional  
public class AutoReportService {  
      
    private final BaseNl2SqlService nl2SqlService;  
    private final ReportTemplateRepository templateRepository;  
    private final ReportExecutionRepository executionRepository;  
    private final ReportVisualizationService visualizationService;  
    private final ReportNotificationService notificationService;  
    private final ReportFileService fileService;  
      
    public AutoReportService(BaseNl2SqlService nl2SqlService,  
                           ReportTemplateRepository templateRepository,  
                           ReportExecutionRepository executionRepository,  
                           ReportVisualizationService visualizationService,  
                           ReportNotificationService notificationService,  
                           ReportFileService fileService) {  
        this.nl2SqlService = nl2SqlService;  
        this.templateRepository = templateRepository;  
        this.executionRepository = executionRepository;  
        this.visualizationService = visualizationService;  
        this.notificationService = notificationService;  
        this.fileService = fileService;  
    }  
  
    /**  
     * 执行报表生成  
     */  
    public ReportExecution executeReport(Long templateId) {  
        ReportTemplate template = templateRepository.findById(templateId)  
            .orElseThrow(() -> new ReportException("报表模板不存在: " + templateId));  
          
        ReportExecution execution = new ReportExecution();  
        execution.setTemplateId(templateId);  
        execution.setStatus(ExecutionStatus.RUNNING);  
        execution.setExecutionTime(LocalDateTime.now());  
        execution = executionRepository.save(execution);  
          
        try {  
            // 1. 使用NL2SQL生成SQL并执行  
            String naturalQuery = template.getNaturalQuery();  
            String generatedSql = nl2SqlService.nl2sql(naturalQuery);  
            execution.setGeneratedSql(generatedSql);  
              
            String resultData = nl2SqlService.executeSql(generatedSql);  
            execution.setResultData(resultData);  
              
            // 2. 数据可视化处理  
            ReportData reportData = parseResultData(resultData);  
            ChartConfig chartConfig = parseChartConfig(template.getChartConfig());  
              
            // 3. 生成输出文件  
            String outputFilePath = generateOutputFile(template, reportData, chartConfig);  
            execution.setOutputFilePath(outputFilePath);  
              
            // 4. 发送通知  
            sendNotifications(template, execution, reportData);  
              
            execution.setStatus(ExecutionStatus.SUCCESS);  
            execution.setCompletionTime(LocalDateTime.now());  
              
        } catch (Exception e) {  
            execution.setStatus(ExecutionStatus.FAILED);  
            execution.setErrorMessage(e.getMessage());  
            execution.setCompletionTime(LocalDateTime.now());  
            log.error("报表执行失败, templateId: {}", templateId, e);  
        }  
          
        return executionRepository.save(execution);  
    }  
  
    /**  
     * 创建报表模板  
     */  
    public ReportTemplate createTemplate(CreateReportTemplateRequest request) {  
        // 验证自然语言查询是否有效  
        try {  
            String testSql = nl2SqlService.nl2sql(request.getNaturalQuery());  
            log.info("模板验证成功，生成SQL: {}", testSql);  
        } catch (Exception e) {  
            throw new ReportException("自然语言查询验证失败: " + e.getMessage());  
        }  
          
        ReportTemplate template = new ReportTemplate();  
        template.setName(request.getName());  
        template.setDescription(request.getDescription());  
        template.setNaturalQuery(request.getNaturalQuery());  
        template.setCronExpression(request.getCronExpression());  
        template.setOutputFormat(request.getOutputFormat());  
        template.setChartConfig(request.getChartConfig());  
        template.setNotificationConfig(request.getNotificationConfig());  
        template.setStatus(ReportStatus.ACTIVE);  
          
        return templateRepository.save(template);  
    }  
  
    /**  
     * 批量执行定时报表  
     */  
    @Async  
    public void executeBatchReports(List<Long> templateIds) {  
        for (Long templateId : templateIds) {  
            try {  
                executeReport(templateId);  
            } catch (Exception e) {  
                log.error("批量执行报表失败, templateId: {}", templateId, e);  
            }  
        }  
    }  
      
    // ......
}

3.3 调度器实现

解释
// ReportScheduler.java - 报表调度器  
@Component  
@EnableScheduling  
public class ReportScheduler {  
      
    private final AutoReportService autoReportService;  
    private final ReportTemplateRepository templateRepository;  
    private final TaskScheduler taskScheduler;  
    private final Map<Long, ScheduledFuture<?>> scheduledTasks = new ConcurrentHashMap<>();  
      
    public ReportScheduler(AutoReportService autoReportService,  
                          ReportTemplateRepository templateRepository,  
                          TaskScheduler taskScheduler) {  
        this.autoReportService = autoReportService;  
        this.templateRepository = templateRepository;  
        this.taskScheduler = taskScheduler;  
    }  
  
    /**  
     * 应用启动时初始化所有定时任务  
     */  
    @PostConstruct  
    public void initializeScheduledReports() {  
        List<ReportTemplate> activeTemplates = templateRepository  
            .findByStatusAndCronExpressionIsNotNull(ReportStatus.ACTIVE);  
          
        for (ReportTemplate template : activeTemplates) {  
            scheduleReport(template);  
        }  
          
        log.info("初始化了 {} 个定时报表任务", activeTemplates.size());  
    }  
  
    /**  
     * 调度单个报表  
     */  
    public void scheduleReport(ReportTemplate template) {  
        if (template.getCronExpression() == null) {  
            return;  
        }  
          
        // 移除已存在的任务  
        unscheduleReport(template.getId());  
          
        // 创建新的定时任务  
        CronTrigger cronTrigger = new CronTrigger(template.getCronExpression());  
        ScheduledFuture<?> scheduledTask = taskScheduler.schedule(  
            () -> {  
                log.info("执行定时报表: {} [{}]", template.getName(), template.getId());  
                autoReportService.executeReport(template.getId());  
            },  
            cronTrigger  
        );  
          
        scheduledTasks.put(template.getId(), scheduledTask);  
        log.info("报表 {} 已调度, Cron: {}", template.getName(), template.getCronExpression());  
    }  
  
    /**  
     * 取消调度  
     */  
    public void unscheduleReport(Long templateId) {  
        ScheduledFuture<?> task = scheduledTasks.remove(templateId);  
        if (task != null) {  
            task.cancel(false);  
            log.info("取消报表调度: {}", templateId);  
        }  
    }  
  
    /**  
     * 手动触发报表执行  
     */  
    public void triggerReport(Long templateId) {  
        autoReportService.executeReport(templateId);  
    }  
}

3.4 REST API 控制器

解释
// ReportController.java - REST API控制器  
@RestController  
@RequestMapping("/api/reports")  
@Validated  
public class ReportController {  
      
    private final AutoReportService autoReportService;  
    private final ReportScheduler reportScheduler;  
    private final ReportTemplateRepository templateRepository;  
    private final ReportExecutionRepository executionRepository;  
      
    public ReportController(AutoReportService autoReportService,  
                           ReportScheduler reportScheduler,  
                           ReportTemplateRepository templateRepository,  
                           ReportExecutionRepository executionRepository) {  
        this.autoReportService = autoReportService;  
        this.reportScheduler = reportScheduler;  
        this.templateRepository = templateRepository;  
        this.executionRepository = executionRepository;  
    }  
  
    /**  
     * 创建报表模板  
     */  
    @PostMapping("/templates")  
    public ResponseEntity<ApiResponse<ReportTemplate>> createTemplate(  
            @Valid @RequestBody CreateReportTemplateRequest request) {  
          
        ReportTemplate template = autoReportService.createTemplate(request);  
          
        // 如果有定时表达式，启动调度  
        if (template.getCronExpression() != null) {  
            reportScheduler.scheduleReport(template);  
        }  
          
        return ResponseEntity.ok(ApiResponse.success(template));  
    }  
  
    /**  
     * 获取报表模板列表  
     */  
    @GetMapping("/templates")  
    public ResponseEntity<ApiResponse<Page<ReportTemplate>>> getTemplates(  
            @RequestParam(defaultValue = "0") int page,  
            @RequestParam(defaultValue = "10") int size,  
            @RequestParam(required = false) String keyword) {  
          
        Pageable pageable = PageRequest.of(page, size);  
        Page<ReportTemplate> templates;  
          
        if (StringUtils.hasText(keyword)) {  
            templates = templateRepository.findByNameContainingOrDescriptionContaining(  
                keyword, keyword, pageable);  
        } else {  
            templates = templateRepository.findAll(pageable);  
        }  
          
        return ResponseEntity.ok(ApiResponse.success(templates));  
    }  
  
    /**  
     * 手动执行报表  
     */  
    @PostMapping("/templates/{id}/execute")  
    public ResponseEntity<ApiResponse<ReportExecution>> executeReport(@PathVariable Long id) {  
        ReportExecution execution = autoReportService.executeReport(id);  
        return ResponseEntity.ok(ApiResponse.success(execution));  
    }  
  
    /**  
     * 测试自然语言查询  
     */  
    @PostMapping("/test-query")  
    public ResponseEntity<ApiResponse<TestQueryResponse>> testNaturalQuery(  
            @Valid @RequestBody TestQueryRequest request) {  
          
        try {  
            String generatedSql = autoReportService.testNaturalQuery(request.getQuery());  
            TestQueryResponse response = new TestQueryResponse(true, generatedSql, null);  
            return ResponseEntity.ok(ApiResponse.success(response));  
        } catch (Exception e) {  
            TestQueryResponse response = new TestQueryResponse(false, null, e.getMessage());  
            return ResponseEntity.ok(ApiResponse.success(response));  
        }  
    }  
  
    /**  
     * 获取报表执行历史  
     */  
    @GetMapping("/templates/{id}/executions")  
    public ResponseEntity<ApiResponse<Page<ReportExecution>>> getExecutionHistory(  
            @PathVariable Long id,  
            @RequestParam(defaultValue = "0") int page,  
            @RequestParam(defaultValue = "10") int size) {  
          
        Pageable pageable = PageRequest.of(page, size, Sort.by("executionTime").descending());  
        Page<ReportExecution> executions = executionRepository.findByTemplateId(id, pageable);  
          
        return ResponseEntity.ok(ApiResponse.success(executions));  
    }  
  
    /**  
     * 下载报表文件  
     */  
    @GetMapping("/executions/{id}/download")  
    public ResponseEntity<Resource> downloadReport(@PathVariable Long id) throws IOException {  
        ReportExecution execution = executionRepository.findById(id)  
            .orElseThrow(() -> new ReportException("执行记录不存在"));  
          
        if (execution.getOutputFilePath() == null) {  
            throw new ReportException("报表文件不存在");  
        }  
          
        Resource resource = new FileSystemResource(execution.getOutputFilePath());  
        if (!resource.exists()) {  
            throw new ReportException("报表文件已被删除");  
        }  
          
        return ResponseEntity.ok()  
            .header(HttpHeaders.CONTENT_DISPOSITION,   
                "attachment; filename="" + resource.getFilename() + """)  
            .body(resource);  
    }  
}

3.5 配置类

// AutoReportConfiguration.java - 自动配置类  
@Configuration  
@EnableJpaRepositories(basePackages = "com.example.autoreport.repository")  
@EntityScan(basePackages = "com.example.autoreport.entity")  
@EnableConfigurationProperties({AutoReportProperties.class})  
public class AutoReportConfiguration {  
  
    @Bean  
    @ConditionalOnMissingBean  
    public TaskScheduler taskScheduler() {  
        ThreadPoolTaskScheduler scheduler = new ThreadPoolTaskScheduler();  
        scheduler.setPoolSize(10);  
        scheduler.setThreadNamePrefix("report-scheduler-");  
        scheduler.setWaitForTasksToCompleteOnShutdown(true);  
        scheduler.setAwaitTerminationSeconds(30);  
        return scheduler;  
    }  
  
    @Bean  
    public ReportVisualizationService reportVisualizationService() {  
        return new DefaultReportVisualizationService();  
    }  
  
    @Bean  
    public ReportNotificationService reportNotificationService(AutoReportProperties properties) {  
        return new DefaultReportNotificationService(properties.getNotification());  
    }  
  
    @Bean  
    public ReportFileService reportFileService(AutoReportProperties properties) {  
        return new DefaultReportFileService(properties.getFile());  
    }  
}

3.6 应用配置

# application.yml  
spring:  
  # NL2SQL 基础配置  
  ai:  
    openai:  
      base-url: https://dashscope.aliyuncs.com/compatible-mode  
      api-key: ${DASHSCOPE_API_KEY}  
      model: qwen-max  
    dashscope:  
      api-key: ${DASHSCOPE_API_KEY}  
    vectorstore:  
      analytic:  
        collectName: report_chatbi  
        regionId: cn-hangzhou  
        dbInstanceId: ${ANALYTICDB_INSTANCE_ID}  
        managerAccount: ${ANALYTICDB_ACCOUNT}  
        managerAccountPassword: ${ANALYTICDB_PASSWORD}  
        namespace: ${ANALYTICDB_NAMESPACE}  
        namespacePassword: ${ANALYTICDB_NAMESPACE_PASSWORD}  
        defaultTopK: 10  
        defaultSimilarityThreshold: 0.01  
        accessKeyId: ${ALIYUN_ACCESS_KEY_ID}  
        accessKeySecret: ${ALIYUN_ACCESS_KEY_SECRET}  
  
  # 数据库配置  
  datasource:  
    url: jdbc:mysql://localhost:3306/auto_report?useUnicode=true&characterEncoding=utf8  
    username: ${DB_USERNAME}  
    password: ${DB_PASSWORD}  
    driver-class-name: com.mysql.cj.jdbc.Driver  
    
  jpa:  
    hibernate:  
      ddl-auto: update  
    show-sql: false  
    properties:  
      hibernate:  
        format_sql: true  
  
# 业务数据库配置（NL2SQL查询的目标数据库）  
chatbi:  
  dbconfig:  
    url: jdbc:mysql://localhost:3306/business_db  
    username: ${BUSINESS_DB_USERNAME}  
    password: ${BUSINESS_DB_PASSWORD}  
    connectiontype: jdbc  
    dialecttype: mysql  
  
# 自动报表系统配置  
auto-report:  
  # 文件存储配置  
  file:  
    storage-path: /data/reports  
    max-file-size: 100MB  
    allowed-formats: [EXCEL, PDF, HTML, JSON]  
    
  # 通知配置  
  notification:  
    email:  
      enabled: true  
      smtp-host: smtp.example.com  
      smtp-port: 587  
      username: ${EMAIL_USERNAME}  
      password: ${EMAIL_PASSWORD}  
    dingtalk:  
      enabled: true  
      webhook-url: ${DINGTALK_WEBHOOK}  
    wechat:  
      enabled: false  
      corp-id: ${WECHAT_CORP_ID}  
      corp-secret: ${WECHAT_CORP_SECRET}  
    
  # 执行配置  
  execution:  
    max-concurrent-reports: 5  
    timeout-minutes: 30  
    retry-count: 2

4. 使用示例

// 使用示例  
@Component  
public class ReportUsageExample {  
      
    @Autowired  
    private AutoReportService autoReportService;  
      
    public void createSalesReport() {  
        CreateReportTemplateRequest request = new CreateReportTemplateRequest();  
        request.setName("每日销售报表");  
        request.setDescription("统计每日销售数据，包括销售额、订单量等");  
        request.setNaturalQuery("查询昨天的销售总额、订单数量和热销商品前10名");  
        request.setCronExpression("0 0 9 * * ?"); // 每天上午9点执行  
        request.setOutputFormat("EXCEL");  
        request.setChartConfig("""  
            {  
                "charts": [  
                    {  
                        "type": "line",  
                        "title": "销售趋势",  
                        "xField": "date",  
                        "yField": "sales_amount"  
                    },  
                    {  
                        "type": "bar",  
                        "title": "热销商品",  
                        "xField": "product_name",  
                        "yField": "quantity"  
                    }  
                ]  
            }  
            """);  
        request.setNotificationConfig("""  
            {  
                "email": {  
                    "recipients": ["manager@example.com", "sales@example.com"],  
                    "subject": "每日销售报表 - {{date}}"  
                },  
                "dingtalk": {  
                    "enabled": true,  
                    "message": " 每日销售报表已生成，请查收！"  
                }  
            }  
            """);  
          
        ReportTemplate template = autoReportService.createTemplate(request);  
        System.out.println("报表模板创建成功: " + template.getId());  
    }  
}