09-golang连接mysql数据库-03-sqlx

1. 连接数据库.Connect()

package my_db

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)
var DbName *sqlx.DB

func InitDB() (err error) {
	DbName,_ =sqlx.Connect("mysql","root:40010355@(127.0.0.1:3306)/liubeidb")

	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	DbName.SetMaxOpenConns(20)
	DbName.SetMaxIdleConns(10)
	fmt.Printf("connect DB success\n")
	return
}

db.SetMaxOpenConns(20)  最大打开连接数
db.SetMaxIdleConns(10)  闲时最大连接数

2. 查询

2.1 单行查询.Get()

1）在my_db目录下创建user.go文件内容如下：

package my_db

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

type userTab struct {
	Id int `json:"id"`
	Name string `json:"name"`
	Age int `json:"age"`
}

var UserInfo userTab

func QueryRowDemo(id int) userTab{
	sqlStr := "select id, name, age from user where id=?"
	err := DbName.Get(&UserInfo, sqlStr, id)
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		//return
	}
	fmt.Printf("id:%d name:%s age:%d\n", UserInfo.Id, UserInfo.Name, UserInfo.Age)
	return UserInfo
}

以下，用gin写一个接口，用于查看结果（非必要）

2）创建路由

package http

import (
	"fmt"
	"github.com/gin-gonic/gin"
)

func ConfigRoutes() {
	r := gin.Default()

	v1 := r.Group("/v1")
	{
		v1.GET("/login", login)
		v1.GET("submit", submit)
		v1.GET("/mysql/select", mysqlSelect)
	}

	r.Run(":1840")
}

3）context

package http

import (
	"github.com/gin-gonic/gin"
	"testmysql/my_db"
)

func mysqlSelect(c *gin.Context) {
	msg := my_db.QueryRowDemo(1)
	c.JSON(200, msg)
}

4）查看结果

2.2 多行查询.Select()

1）user.go文件添加内容如下：

func QueryMultiRowDemo() []userTab{
	sqlStr := "select id, name, age from user where id > ?"
	var usersInfo []userTab
	err := DbName.Select(&usersInfo, sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
	}
	fmt.Printf("users:%#v\n", usersInfo)
	return usersInfo
}

以下利用gin 写一个路由，仅为查看，非连接数据库必须：

2）contect

func mysqlSelects(c *gin.Context) {
	msg := my_db.QueryMultiRowDemo()
	c.JSON(200, msg)
}

3）v1中添加一条路由如下：

		v1.GET("/mysql/selects", mysqlSelects)

4）查看

3. 插入.Exec()

func InsertRowDemo(name string,age int) {
	sqlStr := "insert into user(name, age) values (?,?)"
	_, err := DbName.Exec(sqlStr, name,age)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the name is %d.\n", name)
}

4. 删除.Exec()

• 代码如下：

func DeleteRowDemo(id int) string{
	sqlStr := "delete from user where id = ?"
	ret, err := DbName.Exec(sqlStr, id)
	if err != nil {
		//fmt.Printf("delete failed, err:%v\n", err)
		return err.Error()
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		//fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return err.Error()
	}
	return  "delete success, affected rows:"+string(n)
}

• gin 上加一个接口，添加路由如下：

func mysqlDelete(c *gin.Context) {
	msg := my_db.DeleteRowDemo(6)
	c.JSON(200, msg)
}


func ConfigRoutes() {
	// 1.创建路由
	// 默认使用了2个中间件Logger(), Recovery()
	r := gin.Default()


	// 路由组1 ，处理GET请求
	v1 := r.Group("/v1")
	{
        ......
		v1.GET("/mysql/delete", mysqlDelete)
	}
	......
}

• 成功删除，输出结果如下
  

5. 更新.Exec()

• 代码

func UpdateRowDemo(age int,id int) {
	sqlStr := "update user set age=? where id = ?"
	ret, err := DbName.Exec(sqlStr, age, id)
	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)
}

• maim中调用

我们不能再从外部传入变量，小心sql注入。

func main() {
	my_db.InitDB()
	my_db.UpdateRowDemo(20,5)
}

6. 事务

6.1 开始事务.Begin()

6.2 提交事务.Commit()

6.3 回滚.Rollback()

• 示例：

func TransactionDemo() {
	tx, err := DBName.Begin() // 开启事务
	if err != nil {
		if tx != nil {
			tx.Rollback() // 回滚
		}
		fmt.Printf("begin trans failed, err:%v\n", err)
		return
	}
	sqlStr1 := "Update user set age=18 where id=?"
	_, err = tx.Exec(sqlStr1, 4)
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec sql1 failed, err:%v\n", err)
		return
	}
	sqlStr2 := "Update user set age=19 where id=?"
	_, err = tx.Exec(sqlStr2, 5)
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("exec sql2 failed, err:%v\n", err)
		return
	}
	err = tx.Commit() // 提交事务
	if err != nil {
		tx.Rollback() // 回滚
		fmt.Printf("commit failed, err:%v\n", err)
		return
	}
	fmt.Println("exec trans success!")
}