Spring Boot + GPT：我做了一个能自己写 SQL 的后端系统

随着大语言模型技术的快速发展，AI在软件开发领域的应用越来越广泛。本文将详细介绍如何构建一个基于Spring Boot和GPT的智能后端系统，该系统能够根据自然语言描述自动生成SQL查询，实现智能化的数据访问层。

开始

对于大模型的选择不一定非得选择ChatGPT我们还可以选择国内的大模型

• 通义千问
• DeepSeek

系统架构设计

整体架构

智能SQL生成系统采用分层架构，主要包括：

• 自然语言输入层：处理用户自然语言请求
• AI模型层：GPT模型进行语义理解和SQL生成
• SQL解析层：验证和优化生成的SQL
• 数据访问层：执行SQL并返回结果
• 业务逻辑层：处理业务逻辑和数据转换

核心组件关系

Spring Boot后端实现

项目依赖配置

Spring Boot项目依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>
    <dependency>
        <groupId>com.theokanning.openai-gpt3-java</groupId>
        <artifactId>service</artifactId>
        <version>0.12.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

核心实体类

// 数据库表实体
@Entity
@Table(name = "products")
public class Product {
   
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "price")
    private BigDecimal price;

    @Column(name = "category")
    private String category;

    @Column(name = "description")
    private String description;

    @Column(name = "created_at")
    private LocalDateTime createdAt;

    // 构造函数、getter和setter
    public Product() {
   }

    public Product(String name, BigDecimal price, String category, String description) {
   
        this.name = name;
        this.price = price;
        this.category = category;
        this.description = description;
        this.createdAt = LocalDateTime.now();
    }
}

// SQL生成请求DTO
public class SqlGenerationRequest {
   
    @NotBlank(message = "查询描述不能为空")
    private String queryDescription;

    @NotBlank(message = "数据库类型不能为空")
    private String databaseType;

    private List<String> tableSchemas;

    // 构造函数、getter和setter
}

// SQL生成响应DTO
public class SqlGenerationResponse {
   
    private String generatedSql;
    private String explanation;
    private boolean isValid;
    private List<String> suggestions;

    // 构造函数、getter和setter
}

AI服务层实现

// GPT服务类
@Service
public class GptSqlService {
   

    private final OpenAIService openAIService;
    private final SqlValidator sqlValidator;

    public GptSqlService(@Value("${openai.api.key}") String apiKey) {
   
        this.openAIService = new OpenAIService(apiKey);
        this.sqlValidator = new SqlValidator();
    }

    public SqlGenerationResponse generateSql(SqlGenerationRequest request) {
   
        // 构建GPT提示词
        String prompt = buildPrompt(request);

        // 调用GPT生成SQL
        CompletionRequest completionRequest = CompletionRequest.builder()
                .model("text-davinci-003")
                .prompt(prompt)
                .maxTokens(500)
                .temperature(0.3)
                .build();

        CompletionResult result = openAIService.createCompletion(completionRequest);

        String generatedSql = result.getChoices().get(0).getText().trim();

        // 验证生成的SQL
        SqlValidationResult validationResult = sqlValidator.validate(generatedSql);

        SqlGenerationResponse response = new SqlGenerationResponse();
        response.setGeneratedSql(generatedSql);
        response.setIsValid(validationResult.isValid());
        response.setSuggestions(validationResult.getSuggestions());
        response.setExplanation("根据您的描述生成的SQL查询");

        return response;
    }

    private String buildPrompt(SqlGenerationRequest request) {
   
        StringBuilder prompt = new StringBuilder();
        prompt.append("你是一个专业的SQL专家。请根据以下自然语言描述生成相应的SQL查询语句。\n\n");
        prompt.append("数据库类型: ").append(request.getDatabaseType()).append("\n");
        prompt.append("表结构信息:\n");

        for (String schema : request.getTableSchemas()) {
   
            prompt.append(schema).append("\n");
        }

        prompt.append("\n查询需求: ").append(request.getQueryDescription());
        prompt.append("\n\n请生成对应的SQL查询语句，只返回SQL代码，不要包含其他解释。");

        return prompt.toString();
    }
}

SQL验证器

// SQL安全验证器
@Component
public class SqlValidator {
   

    private static final Set<String> DANGEROUS_KEYWORDS = Set.of(
        "DROP", "DELETE", "UPDATE", "INSERT", "CREATE", "ALTER", 
        "TRUNCATE", "GRANT", "REVOKE", "EXEC", "EXECUTE"
    );

    public SqlValidationResult validate(String sql) {
   
        SqlValidationResult result = new SqlValidationResult();

        if (sql == null || sql.trim().isEmpty()) {
   
            result.setValid(false);
            result.addSuggestion("生成的SQL为空");
            return result;
        }

        // 检查危险关键字
        String upperSql = sql.toUpperCase();
        for (String keyword : DANGEROUS_KEYWORDS) {
   
            if (upperSql.contains(keyword)) {
   
                result.setValid(false);
                result.addSuggestion("检测到危险SQL关键字: " + keyword);
                return result;
            }
        }

        // 检查基本语法
        if (!isValidSelectStatement(sql)) {
   
            result.setValid(false);
            result.addSuggestion("SQL语句不符合SELECT语法规范");
            return result;
        }

        result.setValid(true);
        return result;
    }

    private boolean isValidSelectStatement(String sql) {
   
        String trimmedSql = sql.trim().toUpperCase();
        return trimmedSql.startsWith("SELECT") && 
               (trimmedSql.contains("FROM") || trimmedSql.contains("JOIN"));
    }
}

// SQL验证结果类
public class SqlValidationResult {
   
    private boolean valid;
    private List<String> suggestions;

    public SqlValidationResult() {
   
        this.suggestions = new ArrayList<>();
        this.valid = true;
    }

    public void addSuggestion(String suggestion) {
   
        this.suggestions.add(suggestion);
    }

}

控制器层

// 主要控制器
@RestController
@RequestMapping("/api/sql")
@CrossOrigin(origins = "*")
public class SqlGenerationController {
   

    private final GptSqlService gptSqlService;
    private final DatabaseService databaseService;

    public SqlGenerationController(GptSqlService gptSqlService, DatabaseService databaseService) {
   
        this.gptSqlService = gptSqlService;
        this.databaseService = databaseService;
    }

    @PostMapping("/generate")
    public ResponseEntity<SqlGenerationResponse> generateSql(@Valid @RequestBody SqlGenerationRequest request) {
   
        try {
   
            SqlGenerationResponse response = gptSqlService.generateSql(request);
            return ResponseEntity.ok(response);
        } catch (Exception e) {
   
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                    .body(createErrorResponse("SQL生成失败: " + e.getMessage()));
        }
    }

    @PostMapping("/execute")
    public ResponseEntity<Object> executeSql(@Valid @RequestBody SqlGenerationRequest request) {
   
        try {
   
            // 首先生成SQL
            SqlGenerationResponse sqlResponse = gptSqlService.generateSql(request);

            if (!sqlResponse.isValid()) {
   
                return ResponseEntity.badRequest().body(sqlResponse);
            }

            // 执行SQL查询
            List<Map<String, Object>> results = databaseService.executeQuery(sqlResponse.getGeneratedSql());

            Map<String, Object> response = new HashMap<>();
            response.put("sql", sqlResponse.getGeneratedSql());
            response.put("results", results);
            response.put("count", results.size());

            return ResponseEntity.ok(response);
        } catch (Exception e) {
   
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                    .body(Map.of("error", "SQL执行失败: " + e.getMessage()));
        }
    }

    private SqlGenerationResponse createErrorResponse(String message) {
   
        SqlGenerationResponse response = new SqlGenerationResponse();
        response.setGeneratedSql("");
        response.setExplanation(message);
        response.setIsValid(false);
        return response;
    }
}

数据库服务层

// 数据库操作服务
@Service
public class DatabaseService {
   

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Map<String, Object>> executeQuery(String sql) {
   
        // 验证SQL安全性（再次验证）
        if (!isSafeQuery(sql)) {
   
            throw new IllegalArgumentException("不安全的SQL查询");
        }

        return jdbcTemplate.queryForList(sql);
    }

    private boolean isSafeQuery(String sql) {
   
        String upperSql = sql.toUpperCase().trim();

        // 只允许SELECT查询
        if (!upperSql.startsWith("SELECT")) {
   
            return false;
        }

        // 检查是否包含危险操作
        return !upperSql.contains("DROP") && 
               !upperSql.contains("DELETE") && 
               !upperSql.contains("UPDATE") && 
               !upperSql.contains("INSERT");
    }

    // 获取数据库表结构
    public List<String> getTableSchemas() {
   
        List<String> schemas = new ArrayList<>();

        // 获取所有表名
        List<String> tableNames = jdbcTemplate.queryForList(
            "SELECT TABLE_NAME FROM information_schema.tables WHERE table_schema = DATABASE()", 
            String.class
        );

        for (String tableName : tableNames) {
   
            String schema = getTableSchema(tableName);
            schemas.add(schema);
        }

        return schemas;
    }

    private String getTableSchema(String tableName) {
   
        List<Map<String, Object>> columns = jdbcTemplate.queryForList(
            "SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM information_schema.columns WHERE table_name = ?",
            tableName
        );

        StringBuilder schema = new StringBuilder();
        schema.append("表: ").append(tableName).append("\n");
        schema.append("字段:\n");

        for (Map<String, Object> column : columns) {
   
            schema.append("  ")
                  .append(column.get("COLUMN_NAME"))
                  .append(" (")
                  .append(column.get("DATA_TYPE"))
                  .append(", ")
                  .append(column.get("IS_NULLABLE"))
                  .append(")\n");
        }

        return schema.toString();
    }
}

前端集成示例

简单的前端界面

<!DOCTYPE html>
<html>
<head>
    <title>智能SQL生成器</title>
    <style>
        body {
    font-family: Arial, sans-serif; margin: 20px; }
        .container {
    max-width: 800px; margin: 0 auto; }
        textarea {
    width: 100%; height: 100px; }
        .result {
    margin-top: 20px; padding: 10px; background: #f5f5f5; }
        button {
    padding: 10px 20px; margin: 5px; }
    </style>
</head>
<body>
    <div class="container">
        <h1>智能SQL生成器</h1>

        <div>
            <label>查询描述:</label>
            <textarea id="queryInput" placeholder="请输入查询需求，例如：查询价格大于100的产品"></textarea>
        </div>

        <div>
            <button onclick="generateSql()">生成SQL</button>
            <button onclick="executeSql()">执行查询</button>
        </div>

        <div class="result" id="result"></div>
    </div>

    <script>
        async function generateSql() {
   
            const query = document.getElementById('queryInput').value;
            if (!query) {
   
                alert('请输入查询描述');
                return;
            }

            try {
   
                const response = await fetch('/api/sql/generate', {
   
                    method: 'POST',
                    headers: {
   
                        'Content-Type': 'application/json'
                    },
                    body: JSON.stringify({
   
                        queryDescription: query,
                        databaseType: 'MySQL',
                        tableSchemas: [] // 可以从后端获取表结构
                    })
                });

                const result = await response.json();
                document.getElementById('result').innerHTML = 
                    `<h3>生成的SQL:</h3><pre>${
     result.generatedSql}</pre>
                     <h3>说明:</h3><p>${
     result.explanation}</p>`;
            } catch (error) {
   
                console.error('Error:', error);
                document.getElementById('result').innerHTML = `<p>错误: ${
     error.message}</p>`;
            }
        }

        async function executeSql() {
   
            const query = document.getElementById('queryInput').value;
            if (!query) {
   
                alert('请输入查询描述');
                return;
            }

            try {
   
                const response = await fetch('/api/sql/execute', {
   
                    method: 'POST',
                    headers: {
   
                        'Content-Type': 'application/json'
                    },
                    body: JSON.stringify({
   
                        queryDescription: query,
                        databaseType: 'MySQL',
                        tableSchemas: []
                    })
                });

                const result = await response.json();
                if (response.ok) {
   
                    document.getElementById('result').innerHTML = 
                        `<h3>执行结果:</h3>
                         <p>查询到 ${
     result.count} 条记录</p>
                         <pre>${
     JSON.stringify(result.results, null, 2)}</pre>`;
                } else {
   
                    document.getElementById('result').innerHTML = 
                        `<h3>错误:</h3><p>${
     result.error}</p>`;
                }
            } catch (error) {
   
                console.error('Error:', error);
                document.getElementById('result').innerHTML = `<p>错误: ${
     error.message}</p>`;
            }
        }
    </script>
</body>
</html>

安全性考虑

SQL注入防护

系统采用了多层防护机制：

1. 输入验证：验证用户输入的合法性
2. SQL验证：检查生成SQL的安全性
3. 执行限制：只允许SELECT查询
4. 参数化查询：使用参数化查询防止注入

访问控制

// 安全配置
@Configuration
@EnableWebSecurity
public class SecurityConfig {
   

    @Bean
    public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
   
        http
            .authorizeHttpRequests(authz -> authz
                .requestMatchers("/api/sql/**").authenticated()
                .anyRequest().permitAll()
            )
            .httpBasic(withDefaults())
            .csrf(csrf -> csrf.disable());

        return http.build();
    }
}

性能优化

缓存机制

// 查询结果缓存
@Service
public class CachedSqlService {
   

    private final Cache<String, List<Map<String, Object>>> queryCache;

    public CachedSqlService() {
   
        this.queryCache = Caffeine.newBuilder()
                .maximumSize(1000)
                .expireAfterWrite(Duration.ofMinutes(10))
                .build();
    }

    public List<Map<String, Object>> executeWithCache(String sql) {
   
        return queryCache.get(sql, key -> {
   
            // 执行实际查询
            return executeQuery(sql);
        });
    }
}

部署和监控

应用配置

application.yml

server:
  port: 8080

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/your_database
    username: your_username
    password: your_password
    driver-class-name: com.mysql.cj.jdbc.Driver

  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: false
    properties:
      hibernate:
        format_sql: true

openai:
  api:
    key: your_openai_api_key

logging:
  level:
    com.yourpackage: DEBUG

监控指标

// 自定义监控指标
@Component
public class SqlMetricsService {
   

    private final MeterRegistry meterRegistry;

    public SqlMetricsService(MeterRegistry meterRegistry) {
   
        this.meterRegistry = meterRegistry;
    }

    public void recordSqlGeneration(String queryType, long duration) {
   
        Counter.builder("sql.generation.count")
               .tag("type", queryType)
               .register(meterRegistry)
               .increment();

        Timer.builder("sql.generation.duration")
             .register(meterRegistry)
             .record(duration, TimeUnit.MILLISECONDS);
    }
}

总结

通过Spring Boot和GPT的结合，我们成功构建了一个能够自动生成SQL的智能后端系统。该系统不仅提高了开发效率，还为非技术用户提供了友好的数据查询界面。

主要优势：

• 智能化：自然语言转SQL，降低使用门槛
• 安全性：多层防护机制确保数据安全
• 可扩展：模块化设计便于功能扩展
• 易用性：简洁的API接口便于集成

未来发展方向：

• 支持更多数据库类型
• 增强SQL优化功能
• 集成机器学习进行查询优化
• 提供可视化查询构建器

这个系统展示了AI技术在软件开发中的巨大潜力，为构建更智能、更高效的应用系统提供了新的思路。