如何在Kitura应用中调用MySQL存储过程并使用查询结果

Hey fellow developer! Let me break down exactly how to call a MySQL stored procedure from your Kitura app and fetch the results you need. I’ve done this a few times, so I’ll share the steps that work reliably.

Prerequisites First #

Before diving into code, make sure you have these covered:

• Your Kitura project uses SwiftKuery-MySQL (the official Kitura SQL framework; it’s way easier to work with than older drivers).
• You’ve already created your target stored procedure in MySQL. For this example, let’s assume we have a get_user_by_id procedure that takes an INT user_id parameter and returns the matching user’s details.

Step 1: Set Up the Database Connection Pool #

First, we’ll create a connection pool to manage MySQL connections efficiently (critical for a server-side app like Kitura):

import SwiftKuery
import SwiftKueryMySQL

// Configure your database credentials
let dbConfig = MySQLConnectionConfiguration(
    host: "localhost",
    port: 3306,
    user: "your_db_user",
    password: "your_db_password",
    database: "your_database_name"
)

// Create a connection pool (tweak capacity based on your app's needs)
let dbPool = MySQLConnection.createPool(
    with: dbConfig,
    poolOptions: ConnectionPoolOptions(initialCapacity: 5, maxCapacity: 15)
)

Step 2: Write the Stored Procedure Call Logic #

Next, we’ll create a function that calls the stored procedure, handles the connection, and parses the results. Note that stored procedures use the CALL syntax, and we’ll use parameterized queries to avoid SQL injection:

// Define a simple User model to hold our results
struct User: Codable {
    let id: Int
    let name: String
    let email: String
}

func fetchUserById(userId: Int, completion: @escaping (User?, Error?) -> Void) {
    // The CALL statement with a parameter placeholder (?)
    let callQuery = "CALL get_user_by_id(?)"
    
    // Grab a connection from the pool
    dbPool.getConnection { connection, connectionError in
        guard let conn = connection else {
            completion(nil, connectionError)
            return
        }
        
        // Execute the stored procedure with our parameter
        conn.execute(query: callQuery, parameters: [userId]) { executionResult in
            switch executionResult {
            case .success(let queryResult):
                // Iterate through the result set (stored procs can return multiple sets)
                queryResult.next { rowResult in
                    defer { conn.close() } // Ensure we close the connection when done
                    
                    switch rowResult {
                    case .row(let row):
                        // Parse the database row into our User model
                        guard let id = row["id"] as? Int,
                              let name = row["name"] as? String,
                              let email = row["email"] as? String else {
                            completion(nil, NSError(domain: "KituraMySQL", code: 0, userInfo: [NSLocalizedDescriptionKey: "Failed to parse user data"]))
                            return
                        }
                        completion(User(id: id, name: name, email: email), nil)
                    case .error(let fetchError):
                        completion(nil, fetchError)
                    case .end:
                        // No rows returned = user not found
                        completion(nil, nil)
                    }
                }
            case .error(let executionError):
                completion(nil, executionError)
                conn.close()
            }
        }
    }
}

Step 3: Integrate with Kitura Routes #

Now let’s hook this up to a Kitura route so we can test it via HTTP:

import Kitura

let router = Router()

// Add a GET route to fetch a user by ID
router.get("/users/:userId") { request, response, next in
    // Extract and validate the user ID from the URL
    guard let userIdStr = request.parameters["userId"],
          let userId = Int(userIdStr) else {
        try? response.status(.badRequest).send("Invalid user ID format").end()
        return next()
    }
    
    // Call our stored procedure function
    fetchUserById(userId: userId) { user, error in
        if let error = error {
            try? response.status(.internalServerError)
                .send("Failed to fetch user: \(error.localizedDescription)")
                .end()
        } else if let user = user {
            // Send the user data as JSON
            try? response.send(json: user).end()
        } else {
            try? response.status(.notFound).send("User not found").end()
        }
        next()
    }
}

// Start the Kitura server
Kitura.addHTTPServer(onPort: 8080, with: router)
Kitura.run()

Key Tips for Success #

• Always use parameterized queries: Never concatenate user input directly into your CALL statement—this opens you up to SQL injection attacks. The ? placeholder and parameters array handle this safely.
• Handle multiple result sets: If your stored procedure runs multiple SELECT statements, you’ll need to call queryResult.next() repeatedly until you hit .end to process all sets.
• Connection cleanup: The defer { conn.close() } ensures we return the connection to the pool even if an error occurs—don’t skip this, or you’ll leak connections over time.
• Error handling: Cover all failure paths (connection issues, execution errors, parsing failures) and return meaningful HTTP status codes to clients.

内容的提问来源于stack exchange，提问作者Mac3n