package main

import (
    "fmt"
    "time"

    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

type user struct {
    Id         int
    Name       string // 首字母大写，不然后面的Get拿不到值会报错，这个字段需要跟数据库字段对应
    Age        int
    Gender     []uint8 // go语言目前没有跟bit类型对应的数据类型
    Birthday   time.Time
    Salary     float32
    Department string
}

func main() {
    // 连接数据库
    var db *sqlx.DB
    dsn := "root:123456.com@(127.0.0.1:3306)/golang?charset=utf8mb4&parseTime=true"
    db, err := sqlx.Connect("mysql", dsn)
    if err != nil {
        fmt.Printf("connect DB failed, err:%v\n", err)
        return
    }
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(10)
    // 单个查询
    sqlStr := "select name,age,gender,birthday,salary,department from emp where name=?"
    var u user
    err1 := db.Get(&u, sqlStr, "zhangsan")
    if err1 != nil {
        fmt.Printf("get failed, err:%v\n", err1)
        return
    }
    fmt.Printf("name:%s,age:%d,gender:%#v,department:%s,salary:%f\n", u.Name, u.Age, u.Gender, u.Department, u.Salary)
    // name:zhangsan,age:20,gender:[]byte{0x1},department:教学部,salary:8000.000000

    // 多行查询
    sqlStr1 := "select * from emp where id > ?"
    var users []user
    err2 := db.Select(&users, sqlStr1, 2)
    if err2 != nil {
        fmt.Printf("query failed, err:%v\n", err2)
        return
    }
    // fmt.Printf("users:%#v\n", users)
    for _, v := range users {
        fmt.Println(v)
    }
    // {3 rain 22 [1] 1990-06-06 00:00:00 +0000 UTC 20000 销售部}
    // {4 zhangsan 20 [1] 1985-12-12 00:00:00 +0000 UTC 8000 教学部}
    // {5 hanmeimei 21 [1] 1987-08-08 00:00:00 +0000 UTC 5000 保安部}

    // 插入数据
    sqlStr2 := "insert into emp(name,age,gender,birthday,salary,department) values (?,?,?,?,?,?)"
    ret, err := db.Exec(sqlStr2, "杨淑芳", 19, 0, "2000-01-02", 8123.32, "销售部")
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }
    theID, err := ret.LastInsertId() // 新插入数据的id
    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert success, the id is %d.\n", theID)
    // insert success, the id is 6.

    // 更新数据
    sqlStr3 := "update emp set age=? where id = ?"
    ret1, err := db.Exec(sqlStr3, 39, 6)
    if err != nil {
        fmt.Printf("update failed, err:%v\n", err)
        return
    }
    n, err := ret1.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows:%d\n", n)
    // update success, affected rows:1

    // 删除数据
    sqlStr4 := "delete from emp where id = ?"
    ret3, err := db.Exec(sqlStr4, 6)
    if err != nil {
        fmt.Printf("delete failed, err:%v\n", err)
        return
    }
    n, err4 := ret3.RowsAffected() // 操作影响的行数
    if err4 != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err4)
        return
    }
    fmt.Printf("delete success, affected rows:%d\n", n)
    // delete success, affected rows:1
}