#一、sq简介:
sq 是对 sqlx 的简单封装。这意味着sq
能直接调用sqlx
的API
。
大部分API
与database/sql
包下的API
同名。
只有Take()
和Select()
这2个API
是自定义API
。
##二、使用方法:
package main
import (
_ "github.com/go-sql-driver/mysql" //mysql driver
"gitee.com/gopher2011/sq"
)
func main(){
configs := make(map[string]*sq.Config)
configs["default"] = &sq.Config{
Enable: true,
Driver: "mysql",
DSN: "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8&parseTime=True&loc=Asia%2FShanghai",
ShowSql: true,
}
//connection database
sq.Open(configs)
sq.QueryRowX("select * from users where id = 1")
}
###1、使用 default
数据库, 就能使用sq
内置的函数CRUD
数据库。
SQL语句
操作数据库//Exec
sq.Exec("insert into users(name,email,created_at,updated_at) value(?,?,?,?)","test","test@gmail.com",time.Now(),time.Now())
//QueryX
rows,err := sq.QueryX("select * from users")
for rows.Next() {
user := &Users{}
err = rows.StructScan(user)
}
rows.Close()
//QueryRowX
user := &Users{}
err := sq.QueryRowX("select * from users where id = ?",1).StructScan(user)
//Take
user := &Users{}
err := sq.Take(user,"select * from users where id = ?",1)
//Select
users := make([]*Users)
err := sq.Select(&users,"select * from users")
//Change database
db := sq.Use("test")
db.QueryX("select * from tests")
也可以设置默认的数据库连接名,例如: 以下操作设置默认数据库为log
sq.SetDefaultLink("log")
sq.Open(configs)
gosql.Get
etc., will use the configuration with the connection namelog
###2、使用结构体操作数据库。
ModelStruct
的API
操作数据库。type Users struct {
Id int `db:"id"`
Name string `db:"name"`
Email string `db:"email"`
Status int `db:"status"`
CreatedAt time.Time `db:"created_at"`
UpdatedAt time.Time `db:"updated_at"`
}
func (u *Users) TableName() string {
return "users"
}
func (u *Users) PK() string {
return "id"
}
//Take
user := &Users{}
sq.Model(user).Where("id=?",1).Take()
//Select
user := make([]*Users,0)
sq.Model(&user).Select()
//Insert and auto set CreatedAt
sq.Model(&User{Name:"test",Email:"test@gmail.com"}).Insert()
//Update
sq.Model(&User{Name:"test2",Email:"test@gmail.com"}).Where("id=?",1).Update()
//If you need to update the zero value, you can do so
sq.Model(&User{Status:0}).Where("id=?",1).Update("status")
//Delete
sq.Model(&User{}).Where("id=?",1).Delete()
使用结构体构造where
条件
//Take where id = 1 and name = "test1"
user := &Users{Id:1,Name:"test1"}
sq.Model(&user).Take()
//Update default use primary key as the condition
sq.Model(&User{Id:1,Name:"test2"}).Update()
//Use custom conditions
//Builder => UPDATE users SET `id`=?,`name`=?,`updated_at`=? WHERE (status = ?)
sq.Model(&User{Id:1,Name:"test2"}).Where("status = ?",1).Update()
//Delete
sq.Model(&User{Id:1}).Delete()
But the zero value is filtered by default, you can specify fields that are not filtered. For example
user := &Users{Id:1,Status:0}
sq.Model(&user).Take("status")
You can use the genstruct tool to quickly generate database structs
The Tx
function has a callback function, if an error is returned, the transaction rollback
sq.Tx(func(tx *sq.DB) error {
for id := 1; id < 10; id++ {
user := &Users{
Id: id,
Name: "test" + strconv.Itoa(id),
Email: "test" + strconv.Itoa(id) + "@test.com",
}
//v2 support, do some database operations in the transaction (use 'tx' from this point, not 'gosql')
tx.Model(user).Insert()
if id == 8 {
return errors.New("interrupt the transaction")
}
}
//query with transaction
var num int
err := tx.QueryRowX("select count(*) from user_id = 1").Scan(&num)
if err != nil {
return err
}
return nil
})
If you need to invoke context, you can use
gosql.Txx
Now support gosql.Begin() or gosql.Use("other").Begin() for example:
tx, err := sq.Begin()
if err != nil {
return err
}
for id := 1; id < 10; id++ {
_, err := tx.Exec("INSERT INTO users(id,name,status,created_at,updated_at) VALUES(?,?,?,?,?)", id, "test"+strconv.Itoa(id), 1, time.Now(), time.Now())
if err != nil {
return tx.Rollback()
}
}
return tx.Commit()
If your fields contain the following field names, they will be updated automatically
AUTO_CREATE_TIME_FIELDS = []string{
"create_time",
"create_at",
"created_at",
"update_time",
"update_at",
"updated_at",
}
AUTO_UPDATE_TIME_FIELDS = []string{
"update_time",
"update_at",
"updated_at",
}
Create
Update
Delete
Count
support map[string]interface
,For example:
//Insert
sq.Table("users").Insert(map[string]interface{}{
"id": 1,
"name": "test",
"email": "test@test.com",
"created_at": "2018-07-11 11:58:21",
"updated_at": "2018-07-11 11:58:21",
})
//Update
sq.Table("users").Where("id = ?", 1).Update(map[string]interface{}{
"name": "fifsky",
"email": "fifsky@test.com",
})
//Delete
sq.Table("users").Where("id = ?", 1).Delete()
//Count
sq.Table("users").Where("id = ?", 1).Count()
//Change database
sq.Use("db2").Table("users").Where("id = ?", 1).Count()
//Transaction `tx`
tx.Table("users").Where("id = ?", 1}).Count()
Now Model support sql.Null* field's, Note, however, that if sql.Null* is also filtered by zero values,For example
type Users struct {
Id int `db:"id"`
Name string `db:"name"`
Email string `db:"email"`
Status int `db:"status"`
SuccessTime sql.NullString `db:"success_time" json:"success_time"`
CreatedAt time.Time `db:"created_at" json:"created_at"`
UpdatedAt time.Time `db:"updated_at" json:"updated_at"`
}
user := &Users{
Id: 1,
SuccessTime: sql.NullString{
String: "2018-09-03 00:00:00",
Valid: false,
}
}
err := sq.Model(user).Take()
Builder SQL:
Query: SELECT * FROM users WHERE (id=?);
Args: []interface {}{1}
Time: 0.00082s
If sql.NullString
of Valid
attribute is false, SQL builder will ignore this zero value
Reference GORM Expr, Resolve update field self-update problem
sq.Table("users").Update(map[string]interface{}{
"id":2,
"count":gosql.Expr("count+?",1)
})
//Builder SQL
//UPDATE `users` SET `count`=count + ?,`id`=?; [1 2]
Because database/sql does not inspect your query and it passes your arguments directly to the driver, it makes dealing with queries with IN clauses difficult:
SELECT * FROM users WHERE level IN (?);
sqlx.In
is encapsulated In gosql
and can be queried using the following schema
var levels = []int{4, 6, 7}
rows, err := sq.QueryX("SELECT * FROM users WHERE level IN (?);", levels)
//or
user := make([]*Users, 0)
err := gosql.Select(&user, "select * from users where id in(?)",[]int{1,2,3})
gosql used the golang structure to express the relationships between tables,You only need to use the relation
Tag to specify the associated field, see example
⚠️ Since version v2, the relation query across library connections needs to be specified using connection
tag
type MomentList struct {
models.Moments
User *models.Users `json:"user" db:"-" relation:"user_id,id"` //one-to-one
Photos []*models.Photos `json:"photos" db:"-" relation:"id,moment_id" connection:"db2"` //one-to-many
}
Get single result
moment := &MomentList{}
err := sq.Model(moment).Where("status = 1 and id = ?",14).Take()
//output User and Photos and you get the result
SQL:
2018/12/06 13:27:54
Query: SELECT * FROM `moments` WHERE (status = 1 and id = ?);
Args: []interface {}{14}
Time: 0.00300s
2018/12/06 13:27:54
Query: SELECT * FROM `moment_users` WHERE (id=?);
Args: []interface {}{5}
Time: 0.00081s
2018/12/06 13:27:54
Query: SELECT * FROM `photos` WHERE (moment_id=?);
Args: []interface {}{14}
Time: 0.00093s
Get list result, many-to-many
var moments = make([]*MomentList, 0)
err := sq.Model(&moments).Where("status = 1").Limit(10).Select()
//You get the total result for *UserMoment slice
SQL:
2018/12/06 13:50:59
Query: SELECT * FROM `moments` WHERE (status = 1) LIMIT 10;
Time: 0.00319s
2018/12/06 13:50:59
Query: SELECT * FROM `moment_users` WHERE (id in(?));
Args: []interface {}{[]interface {}{5}}
Time: 0.00094s
2018/12/06 13:50:59
Query: SELECT * FROM `photos` WHERE (moment_id in(?, ?, ?, ?, ?, ?, ?, ?, ?, ?));
Args: []interface {}{[]interface {}{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}}
Time: 0.00087s
Relation Where:
moment := &MomentList{}
err := sq.Relation("User" , func(b *sq.ModelStruct) {
//this is builder instance,
b.Where("gender = 0")
}).Take(moment , "select * from moments")
Hooks are functions that are called before or after creation/querying/updating/deletion.
If you have defiend specified methods for a model, it will be called automatically when creating, updating, querying, deleting, and if any callback returns an error, gosql
will stop future operations and rollback current transaction.
// begin transaction
BeforeChange
BeforeCreate
// update timestamp `CreatedAt`, `UpdatedAt`
// save
AfterCreate
AfterChange
// commit or rollback transaction
Example:
func (u *Users) BeforeCreate() (err error) {
if u.IsValid() {
err = errors.New("can't save invalid data")
}
return
}
func (u *Users) AfterCreate(tx *gosql.DB) (err error) {
if u.Id == 1 {
u.Email = "after@test.com"
tx.Model(u).Update()
}
return
}
BeforeChange and AfterChange only used in create/update/delete
All Hooks:
BeforeChange
AfterChange
BeforeCreate
AfterCreate
BeforeUpdate
AfterUpdate
BeforeDelete
AfterDelete
BeforeFind
AfterFind
Hook func type supports multiple ways:
func (u *Users) BeforeCreate()
func (u *Users) BeforeCreate() (err error)
func (u *Users) BeforeCreate(tx *gosql.DB)
func (u *Users) BeforeCreate(tx *gosql.DB) (err error)
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。