#数据库操作

package main

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

    _ "github.com/jackc/pgx/v5/stdlib" // 匿名导入：注册驱动
)

type User struct {
    ID        int64
    Name      string
    CreatedAt time.Time
}

func main() {
    db, err := sql.Open("pgx", "postgres://user:pass@localhost:5432/mydb?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Open 不建立连接，需用 Ping 验证连通性
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        log.Fatal("数据库连接失败:", err)
    }

    // 单行查询
    var u User
    err = db.QueryRowContext(ctx,
        "SELECT id, name, created_at FROM users WHERE id = $1", 1,
    ).Scan(&u.ID, &u.Name, &u.CreatedAt)
    if err == sql.ErrNoRows {
        fmt.Println("用户不存在")
        return
    }
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%+v\n", u)
}

// 插入并获取自增 ID
func createUser(db *sql.DB, name string) (int64, error) {
    var id int64
    err := db.QueryRow(
        "INSERT INTO users (name) VALUES ($1) RETURNING id", name,
    ).Scan(&id)
    return id, err
}

// 多行查询
func listUsers(db *sql.DB) ([]User, error) {
    rows, err := db.Query("SELECT id, name, created_at FROM users ORDER BY id")
    if err != nil {
        return nil, err
    }
    defer rows.Close() // 必须关闭

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.CreatedAt); err != nil {
            return nil, err
        }
        users = append(users, u)
    }
    return users, rows.Err() // 检查迭代中的错误
}

// 事务
func transfer(db *sql.DB, from, to int64, amount float64) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback() // 出错或未提交时自动回滚

    if _, err := tx.Exec("UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from); err != nil {
        return err
    }
    if _, err := tx.Exec("UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to); err != nil {
        return err
    }
    return tx.Commit()
}

package main

import (
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type Product struct {
    gorm.Model        // 嵌入：内建 ID/CreatedAt/UpdatedAt/DeletedAt
    Code        string `gorm:"uniqueIndex;size:64"`
    Price       uint
    Description string `gorm:"default:''"`
}

func main() {
    dsn := "host=localhost user=user password=pass dbname=mydb port=5432 sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("连接失败")
    }

    // 自动迁移：按模型创建/更新表结构（生产环境慎用）
    _ = db.AutoMigrate(&Product{})
}

package main

import (
    "fmt"

    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

func crudDemo(db *gorm.DB) {
    // 增
    p := Product{Code: "G01", Price: 100, Description: "键盘"}
    result := db.Create(&p) // p.ID 会被回填
    fmt.Println(result.RowsAffected, result.Error)

    // 查
    var first Product
    db.First(&first, "code = ?", "G01") // 主键/条件查询单条

    var products []Product
    db.Where("price > ?", 50).
        Order("price desc").
        Limit(10).
        Find(&products) // 查询列表

    // 改
    db.Model(&first).Updates(Product{Price: 150}) // 只更新非零字段
    // 需要更新零值字段时使用 map：
    // db.Model(&first).Updates(map[string]any{"price": 0, "description": ""})

    // 删（gorm.Model 含 DeletedAt 时默认为软删除）
    db.Delete(&Product{}, "code = ?", "G01")
    // db.Unscoped().Delete(...) // 真删除

    // 事务
    _ = db.Transaction(func(tx *gorm.DB) error {
        if err := tx.Create(&Product{Code: "G02", Price: 200}).Error; err != nil {
            return err // 返回错误自动回滚
        }
        return nil // 返回 nil 自动提交
    })

    // 高级：upsert 与锁
    db.Clauses(clause.OnConflict{DoNothing: true}).Create(&p)
    db.Clauses(clause.Locking{Strength: "UPDATE"}).First(&first, "code = ?", "G01")
}