go sqlx 学习笔记

想为人民种水稻
2,435 阅读3分钟
1. golang 原生 sql 操作 MySQL
1.1 获取 MySQL 驱动

go get -u github.com/go-sql-driver/mysql

1.2 拼接 Data Source Name

​ 1.2.1 完整的 DNS 格式：username:password@protocol(address)/dbname?param=value ​ 1.2.2 例子：dsn := "root:123456@tcp(127.0.0.1:3306)/golang"

1.3 使用 sql.open() 创建数据库连接

db, err := sql.Open("mysql", dsn)

1.4 可以设置连接数

​ 1.4.1 设置最大的连接数：db.SetMaxOpenConns(100)

​ 1.4.2 设置空闲连接数：db.SetMaxIdleConns(16)

1.5 代码demo
package main

import (
	"database/sql"
	"fmt"

	//导入驱动
	_ "github.com/go-sql-driver/mysql"
)

//定义 book 结构体，与数据库字段一致
type book struct {
	id    int
	name  string
	price float64
}

//查询一条数据
func selectOne(db *sql.DB) {
	book := new(book)
	r := db.QueryRow("select id,name,price from book where id =?", 1)
	err := r.Scan(&book.id, &book.name, &book.price)
	if err != nil {
		fmt.Printf("查询失败%v\n", err)
	} else {
		fmt.Printf("id:%d,name:%s,price:%f", book.id, book.name, book.price)
	}
}

//插入一条数据
//update delete 也是同样使用Exec(),
func insertData(db *sql.DB) {
	result, err := db.Exec("insert into book(name,price) values(?,?)", "python", 22.22)
	if err != nil {
		fmt.Printf("insert err：%v\n", err)
	} else {
		//获取到新增数据的ID
		id, _ := result.LastInsertId()
		fmt.Printf("insert id is :%d\n", id)
	}
}

//查询多条数据
func selectMore(db *sql.DB) {
	book := new(book)
	rows, err := db.Query("select id,name,price from book where id>?", 0)
	//记住要关闭连接
	defer func() {
		if rows != nil {
			rows.Close()
		}
	}()
	if err != nil {
		fmt.Printf("查询失败%v\n", err)
	}
	for rows.Next() {
		rows.Scan(&book.id, &book.name, &book.price)
		fmt.Printf("id:%d,name:%s,price:%f\n", book.id, book.name, book.price)
	}
}
func main() {
	//username:password@protocol(address)/dbname?param=value
	dsn := "root:123456@tcp(127.0.0.1:3306)/golang"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		fmt.Printf("数据库连接异常%v\n", err)
		return
	}
	db.SetMaxOpenConns(100)
	db.SetMaxIdleConns(16)

	//新增
	insertData(db)
	//查询
	selectOne(db)
	//查询多条数据
	selectMore(db)
}
2. golang 第三方库 sqlx 操作MySQL
2.1 获取 sqlx 库

go get github.com/jmoiron/sqlx

2.2 使用 sqlx.open() 创建连接

sqlx.Open("mysql", dsn)

2.3 代码Demo
package main

import (
	"fmt"

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

//book 结构体
type book struct {
	ID    int64   `db:"id"`
	Name  string  `db:"name"`
	Price float64 `db:"price"`
}

//插入数据
func insertData(db *sqlx.DB) {
	result, err := db.Exec("insert into book(name,price) values(?,?)", "java", 23.33)
	if err != nil {
		fmt.Printf("insert err：%v\n", err)
	}
	id, _ := result.LastInsertId()
	fmt.Println("新增的ID:\n", id)

}

//查询一条数据
func queryOne(db *sqlx.DB) {
	row := db.QueryRow("select id,name,price from book where id =?", 1)
	book := new(book)
	err := row.Scan(&book.ID, &book.Name, &book.Price)
	if err != nil {
		fmt.Printf("Scan err:%v\n", err)
	}
	fmt.Printf("id:%d,name:%s,price:%f\n", book.ID, book.Name, book.Price)
}

//使用 sqlx get() 查询数据
func getData(db *sqlx.DB) {
	book := new(book)
	//Get()可以将查询的数据赋值给机构体，不需要自己调用Scan()
	err := db.Get(book, "select * from book where id=?", 2)
	if err != nil {
		fmt.Printf("get err:%v\n", err)
	}
	fmt.Printf("id:%d,name:%s,price:%f\n", book.ID, book.Name, book.Price)

}

//使用 sqlx select() 查询数据
func selectData(db *sqlx.DB) {
	books := make([]book, 0)
	// Select() 可以将查询出来的多条数据赋值给结构体切片,不用自己调用Scan()和close()
	err := db.Select(&books, "select * from book")
	if err != nil {
		fmt.Printf("Select err:%v\n", err)
	}
	fmt.Printf("books : %v\n", books)

}

func main() {
	dsn := "root:123456@tcp(127.0.0.1:3306)/golang"
	db, err := sqlx.Open("mysql", dsn)
	if err != nil {
		fmt.Printf("open err:%v\n", err)
	}
	db.SetMaxOpenConns(100)
	db.SetMaxIdleConns(16)
	insertData(db)
	queryOne(db)
	getData(db)
	selectData(db)

}
3. 使用 Preparex

​ Preparex() 方法返回 *sqlx.Stmt, error 可以通过 Stmt 的方法将参数拼接到 sql 语句

3.1 代码demo
package main

import (
	"fmt"

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

//book 结构体
type book struct {
	ID    int64   `db:"id"`
	Name  string  `db:"name"`
	Price float64 `db:"price"`
}

func main() {
	dsn := "root:123456@tcp(127.0.0.1:3306)/golang"
	dbConn, err := sqlx.Open("mysql", dsn)
	if err != nil {
		fmt.Printf("open err %v\n", err)
	}
	//使用 Preparex
	stmtx, err := dbConn.Preparex("select * from book where id >?")
	if err != nil {
		fmt.Printf("err:%v\n", err)
	}
	b := make([]book, 0)
	stmtx.Select(&b, 2)
	fmt.Printf("books:%v\n", b)
	//
	stmt, err := dbConn.Preparex("select * from book where id =?")
	myBook := new(book)
	stmt.Get(myBook, 3)
	fmt.Printf("myBook:%v\n", myBook)

}
4. 使用事务

​ 开启事务使用 sqlx.DB 中的 Begin()

​ 提交事务使用 sqlx.DB 中的 Commit()

4.1代码 demo
package main

import (
	"fmt"

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

/**
// this will not work if connection pool > 1
db.MustExec("BEGIN;")
db.MustExec(...)
db.MustExec("COMMIT;")
*/

func test01(db *sqlx.DB) {
	tx, err := db.Begin()
	if err != nil {
		fmt.Printf("err:%v\n", err)
		return
	}
	tx.Exec("update book set price=? where id =?", 55.55, 3)
	tx.Exec("update book set price=? where id =?", 55.55, 4)
	tx.Commit()

}

func main() {
	dsn := "root:123456@tcp(127.0.0.1:3306)/golang"
	dbConn, err := sqlx.Open("mysql", dsn)
	defer dbConn.Close()
	if err != nil {
		fmt.Printf("open err %v\n", err)
		return
	}
	test01(dbConn)
}
5. 将数据转成 map

​ 使用 MapScan()

5.1 代码 demo
package main

import (
	"fmt"

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

//book 结构体
type book struct {
	ID    int64
	Name  string
	Price float64
}

func main() {
	dsn := "root:123456@tcp(127.0.0.1:3306)/golang"
	dbConn, err := sqlx.Open("mysql", dsn)
	if err != nil {
		fmt.Printf("open err %v\n", err)
		return
	}
	defer dbConn.Close()
	rows, err := dbConn.Queryx("select * from book")
	for rows.Next() {
		results := make(map[string]interface{})
		rows.MapScan(results)
		fmt.Printf("results:%s\n", results)
	}

}
备注：建表语句
CREATE TABLE IF NOT EXISTS `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `price` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
avatar
文章
阅读
粉丝