Go database/sql 中获取查询结果行数的策略与实践

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3" // 导入SQLite驱动
    "log"
)

// Order 结构体表示订单数据
type Order struct {
    ID        int
    ProductID int
    Quantity  int
    // ... 其他字段
}

// initDB 初始化数据库连接并创建表
func initDB(db *sql.DB) {
    sqlStmt := `
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        product_id INTEGER,
        quantity INTEGER
    );
    DELETE FROM orders; -- 清空旧数据
    INSERT INTO orders (product_id, quantity) VALUES (101, 2);
    INSERT INTO orders (product_id, quantity) VALUES (102, 1);
    INSERT INTO orders (product_id, quantity) VALUES (101, 3);
    `
    _, err := db.Exec(sqlStmt)
    if err != nil {
        log.Fatalf("Error initializing database: %v", err)
    }
    log.Println("Database initialized with sample data.")
}

// GetOrderCountByProductID 通过独立的COUNT(*)查询获取指定商品ID的订单数量
func GetOrderCountByProductID(db *sql.DB, productID int) (int, error) {
    var count int
    // 使用QueryRow获取单行单列结果
    row := db.QueryRow("SELECT COUNT(*) FROM orders WHERE product_id = ?", productID)
    err := row.Scan(&count) // 将结果扫描到count变量
    if err != nil {
        return 0, fmt.Errorf("failed to query order count for product ID %d: %w", productID, err)
    }
    return count, nil
}

func main() {
    db, err := sql.Open("sqlite3", "./orders.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    initDB(db)

    productID := 101
    count, err := GetOrderCountByProductID(db, productID)
    if err != nil {
        log.Fatalf("Error getting order count: %v", err)
    }
    fmt.Printf("Number of orders for product ID %d: %d\n", productID, count) // 预期输出 2

    productID = 103
    count, err = GetOrderCountByProductID(db, productID)
    if err != nil {
        log.Fatalf("Error getting order count: %v", err)
    }
    fmt.Printf("Number of orders for product ID %d: %d\n", productID, count) // 预期输出 0
}

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3" // 导入SQLite驱动
    "log"
)

// Order 结构体表示订单数据
type Order struct {
    ID        int
    ProductID int
    Quantity  int
}

// GetOrdersAndCountByProductID 查询指定商品ID的所有订单，并返回订单列表和数量
func GetOrdersAndCountByProductID(db *sql.DB, productID int) ([]Order, int, error) {
    // 执行查询
    rows, err := db.Query("SELECT id, product_id, quantity FROM orders WHERE product_id = ?", productID)
    if err != nil {
        return nil, 0, fmt.Errorf("failed to query orders for product ID %d: %w", productID, err)
    }
    defer rows.Close() // 确保在函数退出时关闭rows，释放数据库资源

    var orders []Order
    count := 0

    // 遍历结果集
    for rows.Next() {
        var order Order
        // 将当前行的数据扫描到Order结构体中
        if err := rows.Scan(&order.ID, &order.ProductID, &order.Quantity); err != nil {
            return nil, 0, fmt.Errorf("failed to scan order data: %w", err)
        }
        orders = append(orders, order) // 将订单添加到列表中
        count++                      // 递增计数器
    }

    // 检查遍历过程中是否发生错误
    if err := rows.Err(); err != nil {
        return nil, 0, fmt.Errorf("error during rows iteration: %w", err)
    }

    return orders, count, nil
}

func main() {
    db, err := sql.Open("sqlite3", "./orders.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 假设initDB函数已经初始化了数据库并填充了数据
    initDB(db) // 再次调用初始化，确保有数据

    productID := 101
    orders, count, err := GetOrdersAndCountByProductID(db, productID)
    if err != nil {
        log.Fatalf("Error getting orders and count: %v", err)
    }
    fmt.Printf("Found %d orders for product ID %d:\n", count, productID) // 预期输出 2
    for _, order := range orders {
        fmt.Printf("  Order ID: %d, Product ID: %d, Quantity: %d\n", order.ID, order.ProductID, order.Quantity)
    }

    productID = 103
    orders, count, err = GetOrdersAndCountByProductID(db, productID)
    if err != nil {
        log.Fatalf("Error getting orders and count: %v", err)
    }
    fmt.Printf("Found %d orders for product ID %d:\n", count, productID) // 预期输出 0
}