package main

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

    _ "github.com/go-sql-driver/mysql" // 注册 MySQL 驱动
    // _ "github.com/lib/pq"           // PostgreSQL
    // _ "github.com/mattn/go-sqlite3"  // SQLite
)

func main() {
    // 连接数据库
    dsn := "user:password@tcp(localhost:3306)/mydb?parseTime=true"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 验证连接
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }
    fmt.Println("数据库连接成功")
}

// 设置连接池参数
db.SetMaxOpenConns(25)                 // 最大打开连接数
db.SetMaxIdleConns(10)                 // 最大空闲连接数
db.SetConnMaxLifetime(5 * time.Minute) // 连接最大生命周期
db.SetConnMaxIdleTime(3 * time.Minute) // 空闲连接最大生命周期

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

func getUser(db *sql.DB, id int) (*User, error) {
    var u User
    err := db.QueryRow("SELECT id, name, email, created_at FROM users WHERE id = ?", id).
        Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)
    if err != nil {
        if err == sql.ErrNoRows {
            return nil, fmt.Errorf("user %d not found", id)
        }
        return nil, err
    }
    return &u, nil
}

func listUsers(db *sql.DB) ([]User, error) {
    rows, err := db.Query("SELECT id, name, email, 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.Email, &u.CreatedAt); err != nil {
            return nil, err
        }
        users = append(users, u)
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return users, nil
}

func createUser(db *sql.DB, name, email string) (int64, error) {
    result, err := db.Exec(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        name, email,
    )
    if err != nil {
        return 0, err
    }
    return result.LastInsertId()
}

func updateUser(db *sql.DB, id int, name string) error {
    _, err := db.Exec("UPDATE users SET name = ? WHERE id = ?", name, id)
    return err
}

func deleteUser(db *sql.DB, id int) error {
    _, err := db.Exec("DELETE FROM users WHERE id = ?", id)
    return err
}

func transferMoney(db *sql.DB, fromID, toID int, amount float64) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback() // 如果 Commit 了，Rollback 是空操作

    // 扣款
    _, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromID)
    if err != nil {
        return err
    }

    // 入账
    _, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toID)
    if err != nil {
        return err
    }

    return tx.Commit()
}

import "github.com/jmoiron/sqlx"

type User struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Email     string    `db:"email"`
    CreatedAt time.Time `db:"created_at"`
}

func main() {
    db, _ := sqlx.Connect("mysql", dsn)
    defer db.Close()

    // 查询单行
    var user User
    db.Get(&user, "SELECT * FROM users WHERE id = ?", 1)

    // 查询多行
    var users []User
    db.Select(&users, "SELECT * FROM users ORDER BY id")

    // 命名参数
    db.NamedExec("INSERT INTO users (name, email) VALUES (:name, :email)",
        User{Name: "Alice", Email: "[email protected]"})

    // In 查询
    query, args, _ := sqlx.In("SELECT * FROM users WHERE id IN (?)", []int{1, 2, 3})
    query = db.Rebind(query)
    db.Select(&users, query, args...)
}

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

type User struct {
    ID        uint           `gorm:"primaryKey"`
    Name      string         `gorm:"size:100;not null"`
    Email     string         `gorm:"size:200;uniqueIndex"`
    Age       int            `gorm:"default:0"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"` // 软删除
}

func main() {
    dsn := "user:password@tcp(localhost:3306)/mydb?parseTime=true"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    
    // 自动迁移
    db.AutoMigrate(&User{})

    // 创建
    user := User{Name: "Alice", Email: "[email protected]", Age: 30}
    db.Create(&user)

    // 查询
    var found User
    db.First(&found, 1)                          // 主键查询
    db.Where("name = ?", "Alice").First(&found)  // 条件查询
    db.Where("age > ?", 20).Find(&users)         // 查询多条

    // 更新
    db.Model(&user).Update("name", "Bob")
    db.Model(&user).Updates(User{Name: "Bob", Age: 31})

    // 删除
    db.Delete(&user) // 软删除

    // 预加载
    type Order struct {
        ID     uint
        UserID uint
        Amount float64
    }
    var userWithOrders User
    db.Preload("Orders").First(&userWithOrders, 1)
}

// 分页
var users []User
db.Offset(10).Limit(10).Find(&users)

// 排序
db.Order("age desc, name asc").Find(&users)

// 计数
var count int64
db.Model(&User{}).Where("age > ?", 18).Count(&count)

// 选择特定字段
db.Select("name", "age").Find(&users)

// 聚合
db.Model(&User{}).Select("avg(age)").Scan(&avgAge)

// 事务
db.Transaction(func(tx *gorm.DB) error {
    if err := tx.Create(&user).Error; err != nil {
        return err
    }
    if err := tx.Create(&order).Error; err != nil {
        return err
    }
    return nil
})

// 批量插入
db.CreateInBatches(users, 100)