Go sqlx库

sqlx is a library which provides a set of extensions on go's standard database/sql library.

sqlx support some db: mysql, postgresql, oracle, sqlite ...

https://github.com/jmoiron/sqlx

示例代码：

该库目前只对查询进行了深度封装，对于更新和插入封装较少。

新建表

package main

import (

    "database/sql"

    _ "github.com/go-sql-driver/mysql"

    "github.com/jmoiron/sqlx"

)

var schema = `Create table person2(

first_name varchar(),

last_name varchar(),

email varchar());

`

var schema2 = `Create table place(

country varchar(),

city varchar() NULL,

telcode int());

`

type Person struct {

    FirstName string `db:"first_name"`

    LastName string `db:"last_name"`

    Email string

}

type Place struct {

    Country string

    city sql.NullString

    TelCode int

}

func main() {

    dsn := "root:123456@tcp(172.16.65.200:3306)/golang"

    db, err := sqlx.Connect("mysql", dsn)

    if err != nil {

        panic(err)

    }

    result, err := db.Exec(schema2)

    if err != nil {

        panic(err)

    }

    _, err = result.RowsAffected()

    if err != nil {

        panic(err)

    }

}

单行查询使用 sqlx.Get()，多行查询使用 sqlx.Select()

package main

import (

    "database/sql"

    _ "github.com/go-sql-driver/mysql"

    "fmt"

    "log"

    "github.com/jmoiron/sqlx"

)

type User struct {

    Id   int    `db:"id"`

    Name string `db:"name"`

    Age  int    `db:"age"`

}

// 单行查询，如果查询到多个结果，只返回第一行，查询不到结果就ErrNoRows错误。

func QueryRow(db *sqlx.DB) {

    var user User

    err := db.Get(&user, "select id, name, age from user where id=?", )

    if err == sql.ErrNoRows {

        log.Printf("not found data of the id:%d", )

    }

    if err != nil {

        panic(err)

    }

    fmt.Printf("user: %#v\n", user)

}

// 多行查询, 查询不到任何记录也不会报错。

func Query(db *sqlx.DB) {

    var users []*User

    err := db.Select(&users, "select id, name, age from user")

    if err != nil {

        panic(err)

    }

    if err == sql.ErrNoRows {

        log.Printf("not found data")

        return

    }

    for _, user := range users {

        fmt.Println(user.Id, user.Name)

    }

}

func main() {

    dsn := "root:123456@tcp(172.16.65.200:3306)/golang"

    db, err := sqlx.Connect("mysql", dsn)

    if err != nil {

        panic(err)

    }

    err = db.Ping()

    if err != nil {

        panic(err)

    }

    fmt.Printf("connect to db success\n")

    QueryRow(db)

    Query(db)

}

更新和插入使用sqlx.Exec()

package main

import (

    _ "github.com/go-sql-driver/mysql"

    "fmt"

    "github.com/jmoiron/sqlx"

)

func Update(db *sqlx.DB) {

    name := "Miles"

    age :=

    id := 

    result, err := db.Exec("update user set name=?, age=? where id=?", name, age, id)

    if err != nil {

        panic(err)

    }

    // RowsAffected returns the number of rows affected by an

    // update, insert, or delete.

    rowsAffected, err := result.RowsAffected()

    if err != nil {

        panic(err)

    }

    fmt.Printf("update id:%d, affect rows:%d\n", id, rowsAffected)

}

func Insert(db *sqlx.DB) {

    name := "Lucy"

    age := 

    result, err := db.Exec("insert into user(name, age) values (?,?)", name, age)

    if err != nil {

        panic(err)

    }

    id, err := result.LastInsertId()

    if err != nil {

        panic(err)

    }

    affected, err := result.RowsAffected()

    if err != nil {

        panic(err)

    }

    fmt.Printf("last insert id:%d affect rows:%d\n", id, affected)

}

func main() {

    dsn := "root:123456@tcp(172.16.65.200:3306)/golang"

    db, err := sqlx.Connect("mysql", dsn)

    if err != nil {

        panic(err)

    }

    err = db.Ping()

    if err != nil {

        panic(err)

    }

    fmt.Println("connect to db success!!!")

    Update(db)

    Insert(db)

}

预处理，直接使用原生的sql.db，没有进行过任何封装

package main

import (

    _ "github.com/go-sql-driver/mysql"

    "fmt"

    "github.com/jmoiron/sqlx"

)

type User2 struct {

    Id int `db:"id"`

    Name string `db:"name"`

    Age int `db:"age"`

}

// 预处理是为了提高查询性能；

// 实现的原理是先将查询语句发送给Mysql数据库做预解析；

// 然后再将需要查询的条件数据发送给Mysql数据库进行执行；

// 这种原理类似于GO语言和Python语言执行效率的对比；

// Go语言是需要先编译的，Python是一边执行一边编译。

func PrepareQuery(db *sqlx.DB, id int) {

    stmt, err := db.Prepare("select id, name, age from user where id>?")

    if err != nil {

        panic(err)

    }

    rows, err := stmt.Query(id)

    if err != nil {

        panic(err)

    }

    defer stmt.Close()

    defer rows.Close()

    for rows.Next(){

        var user User2

        err := rows.Scan(&user.Id, &user.Name, &user.Age)

        if err != nil {

            panic(err)

        }

        fmt.Printf("user: %#v\n", user)

    }

}

func main() {

    dsn := "root:123456@tcp(172.16.65.200:3306)/golang"

    db, err := sqlx.Connect("mysql", dsn)

    if err != nil {

        panic(err)

    }

    defer db.Close()

    PrepareQuery(db, )

}

原则性操作

package main

import (

    _ "github.com/go-sql-driver/mysql"

    "fmt"

    "github.com/jmoiron/sqlx"

)

func Transaction(db *sqlx.DB) {

    // 开启事务

    tx, err := db.Begin()

    if err != nil {

        panic(err)

    }

    result, err := tx.Exec("insert into user(name, age)values(?,?)", "Jack", )

    if err != nil {

        // 失败回滚

        tx.Rollback()

        panic(err)

    }

    fmt.Println("result", result)

    exec, err := tx.Exec("update user set name=?, age=? where id=?", "Jack", , )

    if err != nil {

        // 失败回滚

        tx.Rollback()

        panic(err)

    }

    fmt.Println("exec", exec)

    // 提交事务

    err = tx.Commit()

    if err != nil {

        // 失败回滚

        tx.Rollback()

        panic(err)

    }

}

func main() {

    dsn := "root:123456@tcp(172.16.65.200:3306)/golang"

    db, err := sqlx.Connect("mysql", dsn)

    if err != nil {

        panic(err)

    }

    err = db.Ping()

    if err != nil {

        panic(err)

    }

    Transaction(db)

}

Named

    // Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &person

    tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"})

    tx.Commit()

更多用法

package main

import (

    "database/sql"

    "fmt"

    "log"

    _ "github.com/lib/pq"

    "github.com/jmoiron/sqlx"

)

var schema = `

CREATE TABLE person (

    first_name text,

    last_name text,

    email text

);

CREATE TABLE place (

    country text,

    city text NULL,

    telcode integer

)`

type Person struct {

    FirstName string `db:"first_name"`

    LastName  string `db:"last_name"`

    Email     string

}

type Place struct {

    Country string

    City    sql.NullString

    TelCode int

}

func main() {

    // this Pings the database trying to connect, panics on error

    // use sqlx.Open() for sql.Open() semantics

    db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")

    if err != nil {

        log.Fatalln(err)

    }

    // exec the schema or fail; multi-statement Exec behavior varies between

    // database drivers;  pq will exec them all, sqlite3 won't, ymmv

    db.MustExec(schema)

    tx := db.MustBegin()

    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net")

    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@gmail.net")

    tx.MustExec("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New York", "")

    tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Hong Kong", "")

    tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Singapore", "")

    // Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &person

    tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"})

    tx.Commit()

    // Query the database, storing results in a []Person (wrapped in []interface{})

    people := []Person{}

    db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")

    jason, john := people[], people[]

    fmt.Printf("%#v\n%#v", jason, john)

    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}

    // Person{FirstName:"John", LastName:"Doe", Email:"johndoeDNE@gmail.net"}

    // You can also get a single result, a la QueryRow

    jason = Person{}

    err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")

    fmt.Printf("%#v\n", jason)

    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}

    // if you have null fields and use SELECT *, you must use sql.Null* in your struct

    places := []Place{}

    err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")

    if err != nil {

        fmt.Println(err)

        return

    }

    usa, singsing, honkers := places[], places[], places[]

    fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers)

    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}

    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}

    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}

    // Loop through rows using only one struct

    place := Place{}

    rows, err := db.Queryx("SELECT * FROM place")

    for rows.Next() {

        err := rows.StructScan(&place)

        if err != nil {

            log.Fatalln(err)

        }

        fmt.Printf("%#v\n", place)

    }

    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}

    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}

    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}

    // Named queries, using `:name` as the bindvar.  Automatic bindvar support

    // which takes into account the dbtype based on the driverName on sqlx.Open/Connect

    _, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`,

        map[string]interface{}{

            "first": "Bin",

            "last": "Smuth",

            "email": "bensmith@allblacks.nz",

    })

    // Selects Mr. Smith from the database

    rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:fn`, map[string]interface{}{"fn": "Bin"})

    // Named queries can also use structs.  Their bind names follow the same rules

    // as the name -> db mapping, so struct fields are lowercased and the `db` tag

    // is taken into consideration.

    rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:first_name`, jason)

}

巴特西

Go sqlx库

示例代码：

最新文章

热门文章