Go语言ORM设计：理解内存缓存与真正的对象关系映射

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/mattn/go-sqlite3" // 引入SQLite驱动
)

// Person 结构体代表数据库中的一个人
// `db` 标签用于指定结构体字段对应的数据库列名
type Person struct {
    ID        int    `db:"pID"`
    FirstName string `db:"fName"`
    LastName  string `db:"lName"`
    Job       string `db:"job"`
    Location  string `db:"location"`
}

// DBManager 模拟一个简单的ORM数据库管理器
type DBManager struct {
    db *sql.DB
}

// NewDBManager 创建并初始化DBManager
func NewDBManager(dataSourceName string) (*DBManager, error) {
    db, err := sql.Open("sqlite3", dataSourceName)
    if err != nil {
        return nil, fmt.Errorf("failed to open database: %w", err)
    }
    // 确保数据库连接正常
    if err = db.Ping(); err != nil {
        return nil, fmt.Errorf("failed to connect to database: %w", err)
    }
    // 创建表（如果不存在）
    createTableSQL := `
    CREATE TABLE IF NOT EXISTS people (
        pID INTEGER PRIMARY KEY AUTOINCREMENT,
        fName TEXT NOT NULL,
        lName TEXT NOT NULL,
        job TEXT,
        location TEXT
    );`
    _, err = db.Exec(createTableSQL)
    if err != nil {
        return nil, fmt.Errorf("failed to create table: %w", err)
    }
    return &DBManager{db: db}, nil
}

// GetPersonByID 从数据库中根据ID加载一个人
func (m *DBManager) GetPersonByID(id int) (*Person, error) {
    row := m.db.QueryRow("SELECT pID, fName, lName, job, location FROM people WHERE pID = ?", id)
    p := &Person{}
    err := row.Scan(&p.ID, &p.FirstName, &p.LastName, &p.Job, &p.Location)
    if err == sql.ErrNoRows {
        return nil, nil // 未找到对应ID的人
    }
    if err != nil {
        return nil, fmt.Errorf("failed to scan person: %w", err)
    }
    return p, nil
}

// SavePerson 将一个人保存到数据库（插入或更新）
func (m *DBManager) SavePerson(p *Person) error {
    if p.ID == 0 { // ID为0表示新对象，执行插入操作
        res, err := m.db.Exec("INSERT INTO people (fName, lName, job, location) VALUES (?, ?, ?, ?)",
            p.FirstName, p.LastName, p.Job, p.Location)
        if err != nil {
            return fmt.Errorf("failed to insert person: %w", err)
        }
        id, err := res.LastInsertId()
        if err != nil {
            return fmt.Errorf("failed to get last insert ID: %w", err)
        }
        p.ID = int(id) // 更新对象的ID
        fmt.Printf("Inserted new person with ID: %d\n", p.ID)
    } else { // ID不为0表示现有对象，执行更新操作
        _, err := m.db.Exec("UPDATE people SET fName = ?, lName = ?, job = ?, location = ? WHERE pID = ?",
            p.FirstName, p.LastName, p.Job, p.Location, p.ID)
        if err != nil {
            return fmt.Errorf("failed to update person: %w", err)
        }
        fmt.Printf("Updated person with ID: %d\n", p.ID)
    }
    return nil
}

// DeletePersonByID 从数据库中删除一个人
func (m *DBManager) DeletePersonByID(id int) error {
    res, err := m.db.Exec("DELETE FROM people WHERE pID = ?", id)
    if err != nil {
        return fmt.Errorf("failed to delete person: %w", err)
    }
    rowsAffected, err := res.RowsAffected()
    if err != nil {
        return fmt.Errorf("failed to get rows affected: %w", err)
    }
    if rowsAffected == 0 {
        fmt.Printf("No person found with ID: %d to delete.\n", id)
    } else {
        fmt.Printf("Deleted person with ID: %d.\n", id)
    }
    return nil
}

func main() {
    // 使用内存数据库进行演示，实际应用中会连接到文件或网络数据库
    dbManager, err := NewDBManager(":memory:")
    if err != nil {
        log.Fatalf("Failed to initialize DBManager: %v", err)
    }
    defer dbManager.db.Close() // 确保数据库连接在程序结束时关闭

    // 1. 插入新用户
    p1 := &Person{FirstName: "Alice", LastName: "Smith", Job: "Engineer", Location: "NYC"}
    if err := dbManager.SavePerson(p1); err != nil {
        log.Fatal(err)
    }

    // 2. 读取用户
    fetchedP1, err := dbManager.GetPersonByID(p1.ID)
    if err != nil {
        log.Fatal(err)
    }
    if fetchedP1 != nil {
        fmt.Printf("Fetched: %+v\n", fetchedP1)
    }

    // 3. 更新用户
    fetchedP1.Job = "Senior Engineer"
    if err := dbManager.SavePerson(fetchedP1); err != nil {
        log.Fatal(err)
    }
    fetchedP1, err = dbManager.GetPersonByID(p1.ID) // 再次读取以确认更新
    if err != nil {
        log.Fatal(err)
    }
    if fetchedP1 != nil {
        fmt.Printf("Updated and Fetched: %+v\n", fetchedP1)
    }

    // 4. 删除用户
    if err := dbManager.DeletePersonByID(p1.ID); err != nil {
        log.Fatal(err)
    }

    // 5. 尝试再次获取已删除用户，验证删除