做网站要不要学ps调兵山 网站建设

做网站要不要学ps,调兵山 网站建设,网站排名软件,网络系统管理与维护机考Spring Boot GPT#xff1a;我做了一个能自己写 SQL 的后端系统 随着大语言模型技术的快速发展#xff0c;AI在软件开发领域的应用越来越广泛。本文将详细介绍如何构建一个基于Spring Boot和GPT的智能后端系统#xff0c;该系统能够根据自然语言描述自动生成SQL查询#…Spring Boot GPT我做了一个能自己写 SQL 的后端系统随着大语言模型技术的快速发展AI在软件开发领域的应用越来越广泛。本文将详细介绍如何构建一个基于Spring Boot和GPT的智能后端系统该系统能够根据自然语言描述自动生成SQL查询实现智能化的数据访问层。开始对于大模型的选择不一定非得选择ChatGPT我们还可以选择国内的大模型通义千问DeepSeek系统架构设计整体架构智能SQL生成系统采用分层架构主要包括自然语言输入层处理用户自然语言请求AI模型层GPT模型进行语义理解和SQL生成SQL解析层验证和优化生成的SQL数据访问层执行SQL并返回结果业务逻辑层处理业务逻辑和数据转换核心组件关系组件职责技术栈自然语言处理器解析用户请求Spring BootSQL生成器GPT模型调用OpenAI APISQL验证器验证SQL安全性自定义规则引擎数据访问器执行SQL查询Spring Data JPASpring Boot后端实现项目依赖配置Spring Boot项目依赖dependencies dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-web/artifactId /dependency dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-data-jpa/artifactId /dependency dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter-validation/artifactId /dependency dependency groupIdcom.theokanning.openai-gpt3-java/groupId artifactIdservice/artifactId version0.12.0/version /dependency dependency groupIdmysql/groupId artifactIdmysql-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 ListString tableSchemas; // 构造函数、getter和setter } // SQL生成响应DTO public class SqlGenerationResponse { private String generatedSql; private String explanation; private boolean isValid; private ListString 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 SetString 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 ListString 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 ResponseEntitySqlGenerationResponse 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 ResponseEntityObject executeSql(Valid RequestBody SqlGenerationRequest request) { try { // 首先生成SQL SqlGenerationResponse sqlResponse gptSqlService.generateSql(request); if (!sqlResponse.isValid()) { return ResponseEntity.badRequest().body(sqlResponse); } // 执行SQL查询 ListMapString, Object results databaseService.executeQuery(sqlResponse.getGeneratedSql()); MapString, 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 ListMapString, 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 ListString getTableSchemas() { ListString schemas new ArrayList(); // 获取所有表名 ListString 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) { ListMapString, 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 (MapString, 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 classcontainer h1智能SQL生成器/h1 div label查询描述:/label textarea idqueryInput placeholder请输入查询需求例如查询价格大于100的产品/textarea /div div button onclickgenerateSql()生成SQL/button button onclickexecuteSql()执行查询/button /div div classresult idresult/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:/h3pre${ result.generatedSql}/pre h3说明:/h3p${ 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错误:/h3p${ result.error}/p; } } catch (error) { console.error(Error:, error); document.getElementById(result).innerHTML p错误: ${ error.message}/p; } } /script /body /html安全性考虑SQL注入防护系统采用了多层防护机制输入验证验证用户输入的合法性SQL验证检查生成SQL的安全性执行限制只允许SELECT查询参数化查询使用参数化查询防止注入访问控制// 安全配置 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 CacheString, ListMapString, Object queryCache; public CachedSqlService() { this.queryCache Caffeine.newBuilder() .maximumSize(1000) .expireAfterWrite(Duration.ofMinutes(10)) .build(); } public ListMapString, Object executeWithCache(String sql) { return queryCache.get(sql, key - { // 执行实际查询 return executeQuery(sql); }); } }部署和监控应用配置application.ymlserver: 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技术在软件开发中的巨大潜力为构建更智能、更高效的应用系统提供了新的思路。