GORM官网地址:
GORM 指南 | GORM - The fantastic ORM library for Golang, aims to be developer friendly.
参考:
安装:
go get -u github.com/go-sql-driver/mysql
示例:
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
// go get -u github.com/go-sql-driver/mysql
// 定义一个全局对象db
var db *sql.DB
// 定义一个初始化数据库的函数
func initDB() (err error) {
// DSN:Data Source Name
dsn := "user:pwd@tcp(127.0.0.1:3306)/db_name?charset=utf8mb4&parseTime=True"
// 不会校验账号密码是否正确
// 注意!!!这里不要使用:=,我们是给全局变量赋值,然后在main函数中使用全局变量db
db, err = sql.Open("mysql", dsn)
if err != nil {
return err
}
// 尝试与数据库建立连接(校验dsn是否正确)
err = db.Ping()
if err != nil {
return err
}
return nil
}
type user struct {
id string
name_cn string
name_en string
}
// 查询单条数据示例
func queryRowDemo() {
sqlStr := "select id, name_cn, name_en from asset_type"
var u user
// 非常重要:确保QueryRow之后调用Scan方法,否则持有的数据库链接不会被释放
err := db.QueryRow(sqlStr).Scan(&u.id, &u.name_cn, &u.name_en)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
return
}
fmt.Printf("id:%s name_cn:%s name_en:%s\n", u.id, u.name_cn, u.name_en)
}
// 查询多条
func queryRows() {
sqlStr := "SELECT id, name_cn, name_en from asset_type"
rows, err := db.Query(sqlStr)
if err != nil {
fmt.Printf("query failed, err:%v\n", err)
return
}
// 非常重要:关闭rows释放持有的数据库链接
defer rows.Close()
for rows.Next() {
var u user
err := rows.Scan(&u.id, &u.name_cn, &u.name_en)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
return
}
fmt.Printf("id:%s name_cn:%s name_en:%s\n", u.id, u.name_cn, u.name_en)
}
}
// 插入数据
func insertRowDemo() {
sqlStr := "insert into user(name, age) values (?,?)"
ret, err := db.Exec(sqlStr, "王五", 38)
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 prepareInsertDemo() {
sqlStr := "insert into user(name, age) values (?,?)"
stmt, err := db.Prepare(sqlStr)
if err != nil {
fmt.Printf("prepare failed, err:%v\n", err)
return
}
defer stmt.Close()
_, err = stmt.Exec("小王子", 18)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
_, err = stmt.Exec("沙河娜扎", 18)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
fmt.Println("insert success.")
}
// 事务操作示例
func transactionDemo() {
tx, err := db.Begin() // 开启事务
if err != nil {
if tx != nil {
tx.Rollback() // 回滚
}
fmt.Printf("begin trans failed, err:%v\n", err)
return
}
sqlStr1 := "Update user set age=30 where id=?"
ret1, err := tx.Exec(sqlStr1, 2)
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("exec sql1 failed, err:%v\n", err)
return
}
affRow1, err := ret1.RowsAffected()
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
return
}
sqlStr2 := "Update user set age=40 where id=?"
ret2, err := tx.Exec(sqlStr2, 3)
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("exec sql2 failed, err:%v\n", err)
return
}
affRow2, err := ret2.RowsAffected()
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
return
}
fmt.Println(affRow1, affRow2)
if affRow1 == 1 && affRow2 == 1 {
fmt.Println("事务提交啦...")
tx.Commit() // 提交事务
} else {
tx.Rollback()
fmt.Println("事务回滚啦...")
}
fmt.Println("exec trans success!")
}
func main() {
err := initDB() // 调用输出化数据库的函数
if err != nil {
fmt.Printf("init db failed,err:%v\n", err)
return
}
// queryRowDemo()
queryRows()
}
sqlx库
安装:go get github.com/jmoiron/sqlx
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var db *sqlx.DB
type user struct {
ID int
Account string
Role string
}
func initDB() (err error) {
dsn := "root:Mjolnir@tcp(127.0.0.1:3306)/assess?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(20)
db.SetMaxIdleConns(10)
return
}
// 查询单条数据示例
func queryRowDemo() {
sqlStr := "select id, account, role 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 role:%s\n", u.ID, u.Account, u.Role)
}
// 查询多条数据示例
func queryMultiRowDemo() {
sqlStr := "select id, account, role 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)
}
// sqlx中的exec方法与原生sql中的exec使用基本一致:
// 插入数据
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, 6)
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 deleteDemo() {
sqlStr := "delete from user where id=?"
ret, err := db.Exec(sqlStr, 10)
if err != nil {
fmt.Printf("delete failed, err:%v\n", err)
return
}
n, err := ret.RowsAffected()
if err != nil {
fmt.Printf("get rows affected failed, err:%v\n", err)
return
}
fmt.Printf("delete success, affected rows:%d\n", n)
}
// 事务操作:对于事务操作,我们可以使用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=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() {
initDB()
queryRowDemo()
queryMultiRowDemo()
}