Understanding Large Language Models

Large Language Models (LLMs) represent a significant advancement in artificial intelligence, characterized by their massive parameter counts and extensive training on diverse datasets. These models excel at understanding and generating human language, making them ideal for tasks requiring natural language comprehension.

Key Applications of LLMs:

1. Natural Language Processing: LLMs can interpret user queries and generate coherent, contextually relevant responses. Tools like ChatGPT demonstrate this capability effectively.

2. Multimodal Generation: Models such as DALL·E can create images based on textual descriptions, showcasing versatility beyond text.

3. Speech Synthesis: LLMs transform written text into human-like speech.

4. Intelligent Recommendations: By analyzing user behavior patterns, LLMs deliver personalized suggestions and predictions.

LLMs are pre-trained on enormous text corpora, learning linguistic structures, semantics, and contextual relationships. This training enables them to perform diverse language tasks including text generation, translation, summarization, question answering, and sentiment analysis.

Why Integrate LLMs into Database Applications?

Traditional database systems require users to understand SQL syntax and schema details. LLMs bridge this gap by enabling natural language interactions with data stores.

Advantages of LLM Integration:

• Cost-Effective Development: Major LLM providers offer generous free tier allocations, significantly reducing development costs.

• Minimal Learning Curve: Developers only need to understand API communication patterns—no specialized ML expertise required.

• Unified Data Processing: Whether dealing with Redis, MySQL, or other data sources, LLMs can process and transform textual information effectively.

System Architecture

This implementation follows a straightforward flow for converting natural language queries into executable SQL statements:

┌─────────┐     ┌──────────────┐     ┌───────┐     ┌────────────┐
│  User   │────▶│  Spring Boot │────▶│  LLM  │────▶│  SQL Result│
│ Request │     │    Backend   │     │  API  │     │            │
└─────────┘     └──────────────┘     └───────┘     └────────────┘
                      │
                      ▼
              ┌──────────────┐
              │   Database   │
              │  (DDL Info)  │
              └──────────────┘

Request Flow:

1. Client submits a request containing database credentials (host, user, password, table name) and a natural language question
2. Backend establishes a database connection and retrieves the table's DDL definition
3. Backend constructs a prompt combining the DDL, user quession, and system instructions
4. Backend sends the prompt to the LLM API
5. LLM analyzes the input and generates corresponding SQL
6. Backend returns the SQL result to the client

Implementation

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.12.3</version>
    </dependency>

    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-annotations</artifactId>
        <version>2.12.3</version>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>

    <dependency>
        <groupId>com.squareup.okhttp3</groupId>
        <artifactId>okhttp</artifactId>
    </dependency>

    <dependency>
        <groupId>com.alibaba.fastjson2</groupId>
        <artifactId>fastjson2</artifactId>
        <version>2.0.43</version>
    </dependency>

    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.0.27</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

LLM Configuraton

Create a configuration class to manage LLM connection parameters:

@ConfigurationProperties("llm.provider")
@Component
@Data
public class LlmProperties {
    private String appId;
    private String modelEndpoint;
    private String webSocketUrl;
    private String systemRole;
    private String serviceHost;
    private String apiPath;
    private String secretKey;
    private String accessKey;
    private Long timeoutMillis;
    private String instructionTemplate;

    public String getSecureWebSocketUrl() {
        try {
            String httpsUrl = webSocketUrl.replace("wss://", "https://");
            Date currentTime = new Date();
            SimpleDateFormat formatter = new SimpleDateFormat(
                "EEE, dd MMM yyyy HH:mm:ss z", Locale.US
            );
            formatter.setTimeZone(TimeZone.getTimeZone("GMT"));
            String formattedTime = formatter.format(currentTime);
            
            String signatureOrigin = 
                String.format("host: %s\ndate: %s\n", serviceHost, formattedTime);
            
            javax.crypto.Mac mac = 
                javax.crypto.Mac.getInstance("HmacSHA256");
            SecretKeySpec signingKey = 
                new SecretKeySpec(
                    secretKey.getBytes(StandardCharsets.UTF_8), 
                    "HmacSHA256"
                );
            mac.init(signingKey);
            byte[] hash = mac.doFinal(
                signatureOrigin.getBytes(StandardCharsets.UTF_8)
            );
            
            String signature = Base64.getEncoder().encodeToString(hash);
            
            String authorizationOrigin = String.format(
                "hmac username=\"%s\", algorithm=\"%s\", " +
                "headers=\"%s\", signature=\"%s\"",
                appId, "hmac-sha256", "host date", signature
            );
            
            String authorization = Base64.getEncoder().encodeToString(
                authorizationOrigin.getBytes(StandardCharsets.UTF_8)
            );
            
            return String.format(
                "%s?authorization=%s&date=%s&host=%s",
                httpsUrl, authorization, formattedTime, serviceHost
            );
        } catch (Exception e) {
            throw new RuntimeException("Failed to construct authenticated URL", e);
        }
    }
}

Database Connection Service

@Service
@Slf4j
public class DatabaseService {

    public String fetchTableSchema(String host, String username, 
                                    String password, String database, 
                                    String tableName) {
        StringBuilder schemaBuilder = new StringBuilder();
        
        String sql = "SHOW CREATE TABLE " + tableName;
        
        try (Connection conn = DriverManager.getConnection(
                String.format(
                    "jdbc:mysql://%s:3306/%s?useSSL=false&serverTimezone=UTC",
                    host, database
                ),
                username,
                password
            ));
            
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql)) {
            
            if (rs.next()) {
                schemaBuilder.append(rs.getString(2));
            }
        } catch (SQLException e) {
            log.error("Schema retrieval failed for table: {}", tableName, e);
            throw new RuntimeException("Database connection error", e);
        }
        
        return schemaBuilder.toString();
    }
}

LLM Comunication Handler

@Service
@Slf4j
public class LlmClientService {
    private final LlmProperties config;
    private final DatabaseService dbService;
    private final ObjectMapper jsonMapper;

    public LlmClientService(LlmProperties config, 
                            DatabaseService dbService) {
        this.config = config;
        this.dbService = dbService;
        this.jsonMapper = new ObjectMapper();
    }

    public String generateSql(String userQuestion, 
                              DbConnectionParams params) {
        String tableSchema = dbService.fetchTableSchema(
            params.getHost(),
            params.getUsername(),
            params.getPassword(),
            params.getDatabase(),
            params.getTableName()
        );
        
        String prompt = buildPrompt(tableSchema, userQuestion);
        
        return sendToLlm(prompt);
    }

    private String buildPrompt(String schema, String question) {
        return String.format(
            "%s\n\nTable Schema:\n%s\n\nUser Query:\n%s\n\nGenerated SQL:",
            config.getInstructionTemplate(),
            schema,
            question
        );
    }

    private String sendToLlm(String prompt) {
        // WebSocket communication implementation
        // Processes streaming responses from LLM endpoint
        return processStreamResponse(prompt);
    }
    
    private String processStreamResponse(String prompt) {
        // Handle WebSocket streaming and extract SQL result
        StringBuilder responseBuilder = new StringBuilder();
        CountDownLatch latch = new CountDownLatch(1);
        
        try {
            Request request = new Request.Builder()
                .url(config.getSecureWebSocketUrl())
                .build();
            
            WebSocket ws = new OkHttpClient().newWebSocket(request, 
                new WebSocketListener() {
                    @Override
                    public void onMessage(WebSocket ws, String text) {
                        LlmResponse response = jsonMapper.readValue(
                            text, 
                            LlmResponse.class
                        );
                        if ("content.part".equals(response.getType()) 
                            || "content.text".equals(response.getType())) {
                            responseBuilder.append(response.getContent());
                        }
                        if ("message.end".equals(response.getType())) {
                            latch.countDown();
                        }
                    }
                    
                    @Override
                    public void onFailure(WebSocket ws, Throwable t, 
                                         Response response) {
                        log.error("LLM communication error", t);
                        latch.countDown();
                    }
                });
            
            String payload = buildRequestPayload(prompt);
            ws.send(payload);
            latch.await(config.getTimeoutMillis(), TimeUnit.MILLISECONDS);
            ws.close(1000, null);
            
        } catch (Exception e) {
            log.error("LLM processing failed", e);
            throw new RuntimeException("LLM invocation failed", e);
        }
        
        return responseBuilder.toString();
    }
    
    private String buildRequestPayload(String prompt) throws JsonProcessingException {
        Map<String, Object> payload = new HashMap<>();
        payload.put("role", config.getSystemRole());
        payload.put("content", prompt);
        
        Map<String, Object> request = new HashMap<>();
        request.put("header", Map.of("appId", config.getAppId()));
        request.put("parameter", Map.of(
            config.getModelEndpoint(), 
            Map.of("status", 1)
        ));
        request.put("payload", Map.of("message", Map.of(
            "role", "user",
            "content", payload
        )));
        
        return jsonMapper.writeValueAsString(request);
    }
}

Request DTO

@Data
public class SqlGenerationRequest {
    private String host;
    private String username;
    private String password;
    private String database;
    private String tableName;
    private String userQuestion;
}

REST Controller

@RestController
@RequestMapping("/api/sql")
public class SqlGenerationController {
    private final LlmClientService llmService;

    public SqlGenerationController(LlmClientService llmService) {
        this.llmService = llmService;
    }

    @PostMapping("/generate")
    public ResponseEntity<Map<String, String>> generateSql(
            @RequestBody SqlGenerationRequest request) {
        try {
            String generatedSql = llmService.generateSql(
                request.getUserQuestion(),
                new DbConnectionParams() {{
                    setHost(request.getHost());
                    setUsername(request.getUsername());
                    setPassword(request.getPassword());
                    setDatabase(request.getDatabase());
                    setTableName(request.getTableName());
                }}
            );
            
            return ResponseEntity.ok(
                Map.of("sql", generatedSql, "status", "success")
            );
        } catch (Exception e) {
            return ResponseEntity.badRequest()
                .body(Map.of(
                    "error", e.getMessage(),
                    "status", "failed"
                ));
        }
    }
}

Application Configuration

spring:
  application:
    name: sql-generator-service

llm:
  provider:
    app-id: ${XUNFEI_APP_ID}
    model-endpoint: ${XUNFEI_DOMAIN}
    web-socket-url: wss://spark-api.xf-yun.com
    service-host: spark-api.xf-yun.com
    secret-key: ${XUNFEI_SECRET}
    access-key: ${XUNFEI_API_KEY}
    timeout-millis: 30000
    instruction-template: |
      You are a SQL expert. Based on the provided table schema and user query,
      generate a precise SQL statement. Only output the SQL query without
      additional explanation.

Usage Example

curl -X POST http://localhost:8080/api/sql/generate \
  -H "Content-Type: application/json" \
  -d '{
    "host": "localhost",
    "username": "root",
    "password": "password",
    "database": "ecommerce",
    "tableName": "orders",
    "userQuestion": "Show all orders placed in the last 30 days with total amount exceeding 1000"
  }'

Response:

{
  "sql": "SELECT * FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND total_amount > 1000",
  "status": "success"
}

Conclusion

This implementation demonstrates the fundamental integration pattern between traditional backend services and LLM APIs. The architecture cleanly separates concerns: database operations, prompt construction, and LLM communication are all encapsulated within dedicated service components. Developers can extend this foundation by adding support for additional LLM providers, implementing result caching, or enhancing error handling mechanisms.