golang使用sqlx操作MySQL

标签：sqlx return err nil fmt Printf golang failed MySQL

 

package main

//sqlx示例

import (
    "errors"
    "fmt"

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

var DB *sqlx.DB

type User struct {
    ID   int    `db:"id"`
    Name string `db:"name"`
    Age  int    `db:"age"`
}

func initDB() (err error) {
    dsn := "user:password@tcp(xx.xx.xx.xx:3306)/go_db"
    DB, err = sqlx.Connect("mysql", dsn)
    if err != nil {
        return
    }
    return nil
}

//单条查询操作
func queryDemo() {
    sqlStr := "select id,name,age from info where id = ?"
    user := User{}
    err := DB.Get(&user, sqlStr, 1)
    if err != nil {
        fmt.Printf("query failed,err:%v\n", err)
        return
    }
    fmt.Printf("user:%#v\n", user)
    fmt.Printf("user:%v\n", user)
}

//查询多条
func queryMultiDemo() {
    sqlStr := "select id,name,age from info where id > ?"
    var users []User
    err := DB.Select(&users, sqlStr, 0)
    if err != nil {
        fmt.Println("query multi failed,err:%v\n", err)
        return
    }
    fmt.Printf("users: %v\n", users)
    for _, user := range users {
        fmt.Printf("user:%v\n", user)
    }
}

// 插入数据
func insertRowDemo() {
    sqlStr := "insert into info(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 info set age=? where id = ?"
    ret, err := DB.Exec(sqlStr, 39, 1)
    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 info where id = ?"
    ret, err := DB.Exec(sqlStr, 1)
    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)
}

//事务操作
func transDemo() {
    tx, err := DB.Beginx() //开启事务
    // tx.MustExec() //带Must的一般标识出错就直接panic
    if err != nil {
        if tx != nil {
            tx.Rollback()
        }
        fmt.Printf("begin trans failed,err:%v\n", err)
        return
    }
    sql1 := "update info set age=age-? where id=?"
    tx.MustExec(sql1, 2, 1)
    err = tx.Commit()
    if err != nil {
        tx.Rollback()
        fmt.Printf("commit failed,err:%v\n", err)
    }
    fmt.Println("数据更新成功")

}

//事务操作2
func transDemo2() (err error) {
    tx, err := DB.Beginx()
    if err != nil {
        fmt.Println("begin trans failed,err:", err)
        return
    }

    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        } else if err != nil {
            fmt.Println("rollback")
            tx.Rollback()
        } else {
            err = tx.Commit()
            fmt.Println("sql commit")
        }
    }()

    sqlStr1 := "update info set age=99 where id = ?"
    r, err := tx.Exec(sqlStr1, 1)
    if err != nil {
        fmt.Println("sqlstr1执行失败,err:", err)
        return
    }
    n, err := r.RowsAffected()
    if err != nil {
        return
    }
    if n != 1 {
        return errors.New("exec sqlStr1 failed")
    }
    return err
}

func main() {
    err := initDB()
    if err != nil {
        fmt.Printf("init DB failed,err:%v\n", err)
        return
    }
    //查询单条
    // queryDemo()
    // queryMultiDemo()
    // insertRowDemo()
    // updateRowDemo()
    // transDemo()
    transDemo2()
}

 

package main
//sqlx示例
import (     "errors"     "fmt"
    _ "github.com/go-sql-driver/mysql"     "github.com/jmoiron/sqlx" )
var DB *sqlx.DB
type User struct {     ID   int    `db:"id"`     Name string `db:"name"`     Age  int    `db:"age"` }
func initDB() (err error) {     dsn := "TestVenus:xxxxxxxxx@tcp(172.23.102.xx:3306)/go_db"     DB, err = sqlx.Connect("mysql", dsn)     if err != nil {         return     }     return nil }
//单条查询操作 func queryDemo() {     sqlStr := "select id,name,age from info where id = ?"     user := User{}     err := DB.Get(&user, sqlStr, 1)     if err != nil {         fmt.Printf("query failed,err:%v\n", err)         return     }     fmt.Printf("user:%#v\n", user)     fmt.Printf("user:%v\n", user) }
//查询多条 func queryMultiDemo() {     sqlStr := "select id,name,age from info where id > ?"     var users []User     err := DB.Select(&users, sqlStr, 0)     if err != nil {         fmt.Println("query multi failed,err:%v\n", err)         return     }     fmt.Printf("users: %v\n", users)     for _, user := range users {         fmt.Printf("user:%v\n", user)     } }
// 插入数据 func insertRowDemo() {     sqlStr := "insert into info(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 info set age=? where id = ?"     ret, err := DB.Exec(sqlStr, 39, 1)     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 info where id = ?"     ret, err := DB.Exec(sqlStr, 1)     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) }
//事务操作 func transDemo() {     tx, err := DB.Beginx() //开启事务     // tx.MustExec() //带Must的一般标识出错就直接panic     if err != nil {         if tx != nil {             tx.Rollback()         }         fmt.Printf("begin trans failed,err:%v\n", err)         return     }     sql1 := "update info set age=age-? where id=?"     tx.MustExec(sql1, 2, 1)     err = tx.Commit()     if err != nil {         tx.Rollback()         fmt.Printf("commit failed,err:%v\n", err)     }     fmt.Println("数据更新成功")
}
//事务操作2 func transDemo2() (err error) {     tx, err := DB.Beginx()     if err != nil {         fmt.Println("begin trans failed,err:", err)         return     }
    defer func() {         if p := recover(); p != nil {             tx.Rollback()             panic(p)         } else if err != nil {             fmt.Println("rollback")             tx.Rollback()         } else {             err = tx.Commit()             fmt.Println("sql commit")         }     }()
    sqlStr1 := "update info set age=99 where id = ?"     r, err := tx.Exec(sqlStr1, 1)     if err != nil {         fmt.Println("sqlstr1执行失败,err:", err)         return     }     n, err := r.RowsAffected()     if err != nil {         return     }     if n != 1 {         return errors.New("exec sqlStr1 failed")     }     return err }
func main() {     err := initDB()     if err != nil {         fmt.Printf("init DB failed,err:%v\n", err)         return     }     //查询单条     // queryDemo()     // queryMultiDemo()     // insertRowDemo()     // updateRowDemo()     // transDemo()     transDemo2() }

标签：sqlx,return,err,nil,fmt,Printf,golang,failed,MySQL
From： https://www.cnblogs.com/wushaoyu/p/16519204.html