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!")
}
