package dbclient
import (
"database/sql/driver"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"reflect"
)
var db *sqlx.DB
func InitDB() (err error) {
dsn := "root:123456@tcp(127.0.0.1:3306)/nacos?charset=utf8mb4&parseTime=True"
db, err = sqlx.Connect("mysql", dsn)
if err != nil {
fmt.Println("dsn 格式有误")
return err
}
//设置最大连接数
db.SetMaxOpenConns(10)
//设置最大空闲链接数
db.SetMaxIdleConns(5)
fmt.Println("db connect success")
return
}
//db.Get获取单条记录
func SelectOne() {
sqlStr := "select username,role from roles where username = ?"
var r role
err := db.Get(&r, sqlStr, "nacos")
if err != nil {
fmt.Println("db get failed,", err)
return
}
fmt.Printf("%v\n", r)
}
//db.Select 获取多条记录
func SelectMore() {
sqlStr := "select username,role from roles"
var rs []role
err := db.Select(&rs, sqlStr)
if err != nil {
fmt.Println("db get failed,", err)
return
}
fmt.Printf("%v\n", rs)
}
func Update() {
sqlStr := "update roles set role = ? where username =?"
exec, err := db.Exec(sqlStr, "wwwww", "heheheh")
if err != nil {
fmt.Printf("update is failed,err:%v\n", err)
return
}
fmt.Println(exec.RowsAffected())
}
func Insert() {
sqlStr := "insert into roles values(?,?)"
exec, err := db.Exec(sqlStr, "wwwww", "heheheh")
if err != nil {
fmt.Printf("update is failed,err:%v\n", err)
return
}
fmt.Println(exec.RowsAffected())
}
//使用NamedExec
//用来绑定struct中同名的字段
func NamedExec() {
sqlStr := "insert into roles values(:username,:role)"
r := role{
"zhangsan",
"admin",
}
exec, err := db.NamedExec(sqlStr, r)
if err != nil {
fmt.Printf("update is failed,err:%v\n", err)
return
}
fmt.Println(exec.RowsAffected())
}
func NamedQuery() {
sqlStr := "select * from roles"
rows, err := db.NamedQuery(sqlStr, role{})
if err != nil {
fmt.Println("NamedQuery filed,err:", err)
return
}
for rows.Next() {
var r role
rows.StructScan(&r)
fmt.Println(r)
}
}
func TxExec() {
tx, err := db.Begin()
if err != nil {
fmt.Println("tx begin failed,err:", err)
return
}
sqlStr := "update roles set role = ? where username =?"
_, err = tx.Exec(sqlStr, "wwww11w", "heheheh")
//err = errors.New("ss")
if err != nil {
fmt.Printf("update is failed,err:%v\n", err)
return
tx.Rollback()
}
tx.Commit()
}
//sqlx.in使用
type role struct {
Username string `db:"username"`
Role string `db:"role"`
}
//要使用sqlx.in进行数据插入 需要结构体实现driver.Valuer接口
func (r *role) Value() (driver.Value, error) {
return []interface{}{r.Username, r.Role}, nil
}
//使用sqlx.in进行插入
func BatchInsert(rs []interface{}) error {
//这里不太方便需要手写固定个数的(?)
query, args, err := sqlx.In("insert into roles values(?),(?)", rs...)
if err != nil {
fmt.Println("sqlx.in failed", err)
return err
}
fmt.Println(query) //生成的sql语句
fmt.Println(args) //生成的参数
db.Exec(query, args)
return err
}
// todo 这个方法还是要再看一下
func NamedExecInsert() error {
var rs = make([]*role, 2)
rs = append(rs, &role{
"lisi",
"admin",
}, &role{
"lisi1",
"admin1",
})
exec, err := db.NamedExec("insert into roles values (:username,:role)", rs)
if err != nil {
fmt.Println(err)
}
fmt.Println(exec.RowsAffected())
return err
}
func QueryIn(usernames []string) (rs []role, err error) {
query, args, err := sqlx.In("select * from roles where username in (?)", usernames)
if err != nil {
fmt.Println("sqlx.in failed,err", err)
return nil, err
}
fmt.Println("query:", query)
// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
query = db.Rebind(query)
fmt.Println(reflect.TypeOf(args))
// todo 对切片...生成的啥 对数组...生成的切片
err = db.Select(&rs, query, args...)
if err != nil {
fmt.Println("select failed", err)
return rs, err
}
fmt.Println(rs)
return rs, err
}
sqlx简单应用
最新推荐文章于 2025-05-10 12:47:27 发布
1943

被折叠的 条评论
为什么被折叠?



