go使用sqlx

基本使用

连接数据库

package main

import (
        "fmt"
        "github.com/jmoiron/sqlx"
)
import _ "github.com/go-sql-driver/mysql" // 匿名导入，只会执行mysql包中的init()方法，可以不用在main中使用mysql包

var db *sqlx.DB

func initDB() (err error) {
        // DSN:Data Source Name
        dsn := "tianbao:Tianbao0913@tcp(130.179.105.87:3306)/blog_service?charset=utf8mb4&parseTime=True"
        // 也可以使用MustConnect连接不成功就panic
        db, err = sqlx.Connect("mysql", dsn)
        if err != nil {
                fmt.Printf("connect DB failed, err:%v\n", err)
                return
        }
        db.SetMaxOpenConns(200)
        db.SetMaxIdleConns(100)
        return
}

func main() {
        err := initDB() // 调用输出化数据库的函数
        if err != nil {
                fmt.Printf("init db failed,err:%v\n", err)
                return
        }
        fmt.Printf("init db success,\n")
}

查询

查询单条

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

// 查询单条数据示例
func queryRowDemo() {
        sqlStr := "select id, name, age from user where 
        var u user
        err := db.Get(&u, sqlStr, 2)
        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)
}

查询多条

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

// 查询多条数据示例
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)
}

插入

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

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

更新

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

// 更新数据
func updateRowDemo() {
        sqlStr := "update user set age=? where id = ?"
        ret, err := db.Exec(sqlStr, 20, 2)
        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)
}

删除

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

// 删除数据
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)
}

sqlx的NamedExec

传参可使用key-value的形式，不用原来一个问号?对应一个参数

用来绑定SQL语句与结构体或map中的同名字段

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

func insertUserDemo()(err error){
        sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
        _, err = db.NamedExec(sqlStr,
                map[string]interface{}{
                        "name": "guo",
                        "age": 26,
                })
        return
}

sqlx的NamedQuery

支持查询

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

        u := user{
                Name: "guo",
        }
        // 使用结构体命名查询，根据结构体字段的 db tag进行映射
        rows, err = db.NamedQuery(sqlStr, u)
        if err != nil {
                fmt.Printf("db.NamedQuery failed, err:%v\n", err)
                return
        }
        defer rows.Close()
        for rows.Next(){
                var u user
                err := rows.StructScan(&u)
                if err != nil {
                        fmt.Printf("scan failed, err:%v\n", err)
                        continue
                }
                fmt.Printf("user:%#v\n", u)
        }
}

sqlx的事务回滚

sqlx提供了db.Beginx()和tx.Exec()方法进行事务操作

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=22 where 

        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=30 where 
        rs, err = tx.Exec(sqlStr2, 6)
        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
}