<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>data-lake-analysis</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.5</version>
        <relativePath/><!-- lookup parent from repository -->
    </parent>

    <properties>
        <java.version>11</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.drill.exec</groupId>
            <artifactId>drill-jdbc-all</artifactId>
            <version>1.21.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

# 數(shù)據(jù)庫(kù)連接配置
spring.datasource.url=jdbc:drill:zk=local
spring.datasource.driver-class-name=org.apache.drill.jdbc.Driver
spring.jpa.show-sql=true

# 服務(wù)器端口配置
server.port=8080

package com.example.datalakeanalysis.controller;

import com.example.datalakeanalysis.exception.ApiRequestException;
import com.example.datalakeanalysis.service.DataLakeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.validation.FieldError;
import org.springframework.web.bind.MethodArgumentNotValidException;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

// 控制器類(lèi)，處理HTTP請(qǐng)求
@RestController
@RequestMapping("/api/v1")
publicclass DataLakeController {

    @Autowired
    private DataLakeService dataLakeService;  // 自動(dòng)注入數(shù)據(jù)湖服務(wù)

    // 處理GET請(qǐng)求，執(zhí)行銷(xiāo)售數(shù)據(jù)查詢(xún)
    @GetMapping("/sales/query")
    public List<Map<String, Object>> executeSalesQuery(@RequestParam@Valid String sql) throws SQLException {
        return dataLakeService.executeQuery(sql);  // 調(diào)用服務(wù)層方法執(zhí)行查詢(xún)并返回結(jié)果
    }

    // 處理驗(yàn)證異常，返回400 Bad Request狀態(tài)碼
    @ExceptionHandler(MethodArgumentNotValidException.class)
    @ResponseStatus(HttpStatus.BAD_REQUEST)
    public Map<String, String> handleValidationExceptions(
            MethodArgumentNotValidException ex) {
        Map<String, String> errors = new HashMap<>();
        ex.getBindingResult().getAllErrors().forEach((error) -> {
            String fieldName = ((FieldError) error).getField();  // 獲取字段名
            String errorMessage = error.getDefaultMessage();     // 獲取錯(cuò)誤信息
            errors.put(fieldName, errorMessage);                 // 將字段名和錯(cuò)誤信息放入Map
        });
        return errors;                                         // 返回錯(cuò)誤信息Map
    }

    // 處理SQL異常，返回500 Internal Server Error狀態(tài)碼
    @ExceptionHandler(SQLException.class)
    @ResponseStatus(HttpStatus.INTERNAL_SERVER_ERROR)
    public Map<String, String> handleSQLExceptions(SQLException ex) {
        Map<String, String> error = new HashMap<>();
        error.put("message", "Database query failed: " + ex.getMessage());  // 設(shè)置錯(cuò)誤消息
        return error;                                                         // 返回錯(cuò)誤信息Map
    }

    // 處理所有其他異常，返回500 Internal Server Error狀態(tài)碼
    @ExceptionHandler(Exception.class)
    @ResponseStatus(HttpStatus.INTERNAL_SERVER_ERROR)
    public Map<String, String> handleGenericExceptions(Exception ex) {
        Map<String, String> error = new HashMap<>();
        error.put("message", "An unexpected error occurred: " + ex.getMessage());  // 設(shè)置錯(cuò)誤消息
        return error;                                                             // 返回錯(cuò)誤信息Map
    }
}

package com.example.datalakeanalysis.exception;

// 自定義API請(qǐng)求異常類(lèi)
publicclass ApiRequestException extends RuntimeException {

    // 構(gòu)造函數(shù)，接受錯(cuò)誤消息
    public ApiRequestException(String message) {
        super(message);
    }

    // 構(gòu)造函數(shù)，接受錯(cuò)誤消息和原因
    public ApiRequestException(String message, Throwable cause) {
        super(message, cause);
    }
}

package com.example.datalakeanalysis.exception;

import lombok.AllArgsConstructor;
import lombok.Data;

// 異常響應(yīng)類(lèi)，包含錯(cuò)誤消息和詳細(xì)信息
@Data
@AllArgsConstructor
public class ApiRequestExceptionResponse {
    private String message;  // 錯(cuò)誤消息
    private String details;  // 詳細(xì)信息
}

package com.example.datalakeanalysis.exception;

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.context.request.WebRequest;

// 全局異常處理器，處理所有未捕獲的異常
@ControllerAdvice
publicclass GlobalExceptionHandler {

    // 處理ApiRequestException異常，返回400 Bad Request狀態(tài)碼
    @ExceptionHandler(ApiRequestException.class)
    public ResponseEntity<Object> handleApiRequestException(ApiRequestException e, WebRequest request) {
        ApiRequestExceptionResponse exceptionResponse = new ApiRequestExceptionResponse(e.getMessage(), request.getDescription(false));
        returnnew ResponseEntity<>(exceptionResponse, HttpStatus.BAD_REQUEST);
    }

    // 處理所有其他異常，返回500 Internal Server Error狀態(tài)碼
    @ExceptionHandler(Exception.class)
    public final ResponseEntity<Object> handleAllExceptions(Exception ex, WebRequest request) {
        ApiRequestExceptionResponse exceptionResponse = new ApiRequestExceptionResponse(ex.getMessage(),
                request.getDescription(false));
        returnnew ResponseEntity<>(exceptionResponse, HttpStatus.INTERNAL_SERVER_ERROR);
    }
}

package com.example.datalakeanalysis.model;

import lombok.Data;

// 銷(xiāo)售數(shù)據(jù)模型類(lèi)，使用Lombok簡(jiǎn)化getter和setter方法的編寫(xiě)
@Data
public class Sale {
    private String id;          // 銷(xiāo)售記錄ID
    private String product;     // 產(chǎn)品名稱(chēng)
    private double amount;      // 銷(xiāo)售金額
    private String date;        // 銷(xiāo)售日期
}

package com.example.datalakeanalysis.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

// 數(shù)據(jù)湖服務(wù)類(lèi)，負(fù)責(zé)執(zhí)行SQL查詢(xún)并返回結(jié)果
@Service
publicclass DataLakeService {

    @Autowired
    private DataSource dataSource;  // 自動(dòng)注入數(shù)據(jù)源

    // 執(zhí)行SQL查詢(xún)的方法
    public List<Map<String, Object>> executeQuery(String sql) throws SQLException {
        try (Connection connection = dataSource.getConnection();  // 獲取數(shù)據(jù)庫(kù)連接
             Statement statement = connection.createStatement();       // 創(chuàng)建Statement對(duì)象
             ResultSet resultSet = statement.executeQuery(sql)) {      // 執(zhí)行SQL查詢(xún)并獲取結(jié)果集

            List<Map<String, Object>> result = new ArrayList<>();   // 存儲(chǔ)查詢(xún)結(jié)果的列表
            while (resultSet.next()) {                              // 遍歷結(jié)果集中的每一行
                Map<String, Object> row = new HashMap<>();            // 每一行的數(shù)據(jù)存儲(chǔ)在一個(gè)Map中
                for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {  // 遍歷每一列
                    row.put(resultSet.getMetaData().getColumnName(i), resultSet.getObject(i));  // 將列名和值放入Map
                }
                result.add(row);                                      // 將Map添加到結(jié)果列表中
            }

            return result;                                          // 返回查詢(xún)結(jié)果
        }
    }
}

package com.example.datalakeanalysis;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

// 主啟動(dòng)類(lèi)，用于啟動(dòng)Spring Boot應(yīng)用程序
@SpringBootApplication
public class DataLakeAnalysisApplication {

    // 程序入口點(diǎn)
    public static void main(String[] args) {
        SpringApplication.run(DataLakeAnalysisApplication.class, args);
    }
}