gin-sqlx-day4

package main

import (
   "errors"
   "fmt"
   "github.com/jmoiron/sqlx" //go mod tidy
   _"github.com/go-sql-driver/mysql" //驱动

)
//封装了很多函数，很友好
var db *sqlx.DB

//结构体+tag
type user struct { //大写
   ID int`db:"id"`  //与数据库上的数据列对应上
   Age int  `db:"age"`
   Name string    `db:"name"`
}
func initDB() (err error) {
   dsn := "root:hitlk@tcp(10.249.145.151:3306)/bjpowernode?charset=utf8mb4&parseTime=True"
   // 也可以使用MustConnect连接不成功就panic
   db, err = sqlx.Connect("mysql", dsn) //封装了open ping
   if err != nil {
      fmt.Printf("connect DB failed, err:%v\n", err)
      return
   }

   db.SetMaxOpenConns(20)
   db.SetMaxIdleConns(10)
   return
}

// 查询单条数据示例,查询出来的数据直接映射到结构体
func queryRowDemo() {
   sqlStr := "select id, name, age from user where id=?"
   var u user
   err := db.Get(&u, sqlStr, 1)
   if err != nil {
      fmt.Printf("get failed, err:%v\n", err)
      return
   }
   fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
}
// 查询多条数据示例
func queryMultiRowDemo() {
   sqlStr := "select id, name, age from user where id > ?"
   var users []user
   err := db.Select(&users, sqlStr, 0)
   if err != nil {
      fmt.Printf("query failed, err:%v\n", err)
      return
   }
   fmt.Printf("users:%#v\n", users)
}

// 插入数据
func insertRowDemo() {
   sqlStr := "insert into user(name, age) values (?,?)"
   ret, err := db.Exec(sqlStr, "沙河小王子", 19)
   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)
}

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

// 删除数据
func deleteRowDemo() {
   sqlStr := "delete from user where id = ?"
   ret, err := db.Exec(sqlStr, 3)
   if err != nil {
      fmt.Printf("delete failed, err:%v\n", err)
      return
   }
   n, err := ret.RowsAffected() // 操作影响的行数
   if err != nil {
      fmt.Printf("get RowsAffected failed, err:%v\n", err)
      return
   }
   fmt.Printf("delete success, affected rows:%d\n", n)
}

//DB.NamedExec方法用来绑定SQL语句与结构体或map中的同名字段。
func insertUserDemo()(err error){
   sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
   _, err = db.NamedExec(sqlStr,
      map[string]interface{}{
         "name": "七米",
         "age": 28,
      })
   return
}

func namedQuery()  {
   sqlStr := "SELECT * FROM user WHERE name=:name"
   //使用map做命名查询
   rows,err := db.NamedQuery(sqlStr,map[string]interface{}{"name":"xiaoli"})
   if err != nil{
      fmt.Printf("db.namequery failed,err:%v\n",err)
      return
   }
   defer rows.Close()
   for rows.Next(){
      var u user
      rows.StructScan(&u)
      fmt.Printf("user:%#v",u)
   }

   //使用结构体查询,根据结构字段的db tag进行映射
   u := user{
      Name: "七米",
   }
   rows,err = db.NamedQuery(sqlStr,u)
   if err != nil{
      fmt.Printf("err:%v\n",err)
      return
   }

   defer rows.Close()
   for rows.Next() {
      var u user
      rows.StructScan(&u)
      fmt.Printf("user:%#v\n",u)
   }
}

//事务操作
func transactionDemo2()(err error) {
   tx, err := db.Beginx() ///////////////// 开启事务
   if err != nil {
      fmt.Printf("begin trans failed, err:%v\n", err)
      return err
   }
   defer func() {
      if p := recover(); p != nil {
         tx.Rollback()
         panic(p) // re-throw panic after Rollback
      } else if err != nil {
         fmt.Println("rollback")
         tx.Rollback() // err is non-nil; don't change it
      } else {
         err = tx.Commit() // err is nil; if Commit returns error update err
         fmt.Println("commit")
      }
   }()

   sqlStr1 := "Update user set age=20 where id=?"

   rs, err := tx.Exec(sqlStr1, 1)
   if err!= nil{
      return err
   }
   n, err := rs.RowsAffected()
   if err != nil {
      return err
   }
   if n != 1 {
      return errors.New("exec sqlStr1 failed")
   }
   sqlStr2 := "Update user set age=50 where i=?"
   rs, err = tx.Exec(sqlStr2, 5)
   if err!=nil{
      return err
   }
   n, err = rs.RowsAffected()
   if err != nil {
      return err
   }
   if n != 1 {
      return errors.New("exec sqlStr1 failed")
   }
   return err
}

func main()  {
   if err := initDB();err!=nil{
      fmt.Printf("init DB failed,err:%v\n",err)
   }
   fmt.Println("init DB success...")

   //queryRowDemo()
   //queryMultiRowDemo()
   //insertRowDemo()
   //updateRowDemo()
   //deleteRowDemo()
   //insertUserDemo()
   //namedQuery()
   transactionDemo2()

}