初始化项目

首先，使用go mod init初始化项目并添加GoooQo依赖：

go get -u github.com/doytowin/goooqo

然后，初始化数据库连接和事务管理器：

func main() {
	db, _ := sql.Open("sqlite3", "./test.db")
	tm := rdb.NewTransactionManager(db)
	//...
}

接着，我们就可以为每张表创建对应数据访问接口了：

userDataAccess := rdb.NewTxDataAccess[UserEntity](tm)

接口定义

方法NewTxDataAccess返回一个TxDataAccess实例，该实例由用于CRUD操作的DataAccess接口和用于事务操作的TransactionManager接口组成。

package core

type DataAccess[E Entity] interface {
	Get(ctx context.Context, id any) (*E, error)
	Delete(ctx context.Context, id any) (int64, error)
	Query(ctx context.Context, query Query) ([]E, error)
	Count(ctx context.Context, query Query) (int64, error)
	DeleteByQuery(ctx context.Context, query Query) (int64, error)
	Page(ctx context.Context, query Query) (PageList[E], error)
	Create(ctx context.Context, entity *E) (int64, error)
	CreateMulti(ctx context.Context, entities []E) (int64, error)
	Update(ctx context.Context, entity E) (int64, error)
	Patch(ctx context.Context, entity E) (int64, error)
	PatchByQuery(ctx context.Context, entity E, query Query) (int64, error)
}

type TxDataAccess[E Entity] interface {
	TransactionManager
	DataAccess[E]
}

type PageList[D any] struct {
    List  []D   `json:"list"`
    Total int64 `json:"total"`
}

可以看到，DataAccess接口中的所有方法一共只接收4类参数：

  • context.Context可以是普通的Context，也可以是开启了事务的TransactionContext
  • id 实体的主键
  • Entity 实体对象，用于映射表名和列名，需要组合IntId或者Int64Id
  • Query 查询对象，用于动态构造查询条件和分页语句，需要组合PageQuery

创建对象

假设我们在test.db中有以下用户表：

idnamescorememo
1Alley80Good
2Dave75Well
3Bob60
4Tim92Great
5Emy100Great

我们只需要为表定义一个实体对象UserEntity和一个查询对象UserQuery，然后创建数据访问接口即可：

type UserEntity struct {
    goooqo.Int64Id
    Name    *string `json:"name,omitempty"`
    Score   *int    `json:"score,omitempty"`
    Memo    *string `json:"memo,omitempty"`
    Deleted *bool   `json:"deleted,omitempty"`
}

func (u UserEntity) GetTableName() string {
    return "t_user"
}

type UserQuery struct {
    goooqo.PageQuery
    IdGt     *int64
    IdIn     *[]int64
    ScoreLt  *int
    MemoNull *bool
    MemoLike *string
    Deleted  *bool
    UserOr   *[]UserQuery

    Account    *string    `condition:"(username = ? OR email = ?)"`
    ScoreLtAvg *UserQuery `subquery:"select avg(score) from t_user"`
    ScoreLtAny *UserQuery `subquery:"SELECT score FROM t_user"`
    ScoreLtAll *UserQuery `subquery:"select score from UserEntity"`
    ScoreGtAvg *UserQuery `select:"avg(score)" from:"UserEntity"`

    ScoreInScoreOfUser    *UserQuery //score IN (SELECT score FROM t_user WHERE ...)
    ScoreGtAvgScoreOfUser *UserQuery //score > (SELECT AVG(score) FROM t_user WHERE ...)
}

调用示例

Get

根据id查询数据：

user, err := userDataAccess.Get(ctx, 3)
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE id = ?" args="[3]"

Query

根据查询条件查询数据：

userQuery := UserQuery{ScoreLt: P(80)}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE score < ?" args="[80]"

userQuery := UserQuery{PageQuery: PageQuery{PageSize: P(20), 
    Sort: P("id,desc;score")}, MemoLike: P("Great")}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE memo LIKE ? ORDER BY id DESC, score LIMIT 20 OFFSET 0" args="[Great]"

userQuery := UserQuery{IdIn: &[]int64{1, 4, 12}, Deleted: P(true)}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE id IN (?, ?, ?) AND deleted = ?" args="[1 4 12 true]"

userQuery := UserQuery{UserOr: &[]UserQuery{
    {IdGt: P(int64(10)), MemoNull: P(true)}, 
    {ScoreLt: P(80), MemoLike: P("Good")},
}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (id > ? AND memo IS NULL OR score < ? AND memo LIKE ?)" args="[10 80 Good]"

userQuery := UserQuery{ScoreGtAvg: &UserQuery{Deleted: P(true)},
     ScoreLtAny: &UserQuery{}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE score > (SELECT avg(score) FROM t_user WHERE deleted = ?) 
// AND score < ANY(SELECT score FROM t_user)" args="[true]"

userQuery := UserQuery{Account: P("John")}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (username = ? OR email = ?)" args="[John John]"

Count

根据查询条件查询数据的总数：

userQuery := UserQuery{ScoreLt: P(60)}
cnt, err := userDataAccess.Count(ctx, userQuery)
// SQL="SELECT count(0) FROM t_user WHERE score < ?" args="[60]"

Page

根据查询条件查询数据和总数：

userQuery := UserQuery{PageQuery: PageQuery{PageSize: P(20)}, ScoreLt: P(80)}
page, err := userDataAccess.Page(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE score < ? LIMIT 20 OFFSET 0" args="[80]"
// SQL="SELECT count(0) FROM t_user WHERE score < ?" args="[80]"

Delete

根据id删除数据：

tc, _ := tm.StartTransaction(tc)
cnt, err := userDataAccess.Delete(tc, 3)
// SQL="DELETE FROM t_user WHERE id = ?" args="[3]"

DeleteByQuery

根据查询条件删除数据：

userQuery := UserQuery{ScoreLt: P(80)}
cnt, err := userDataAccess.DeleteByQuery(tc, userQuery)
// SQL="DELETE FROM User WHERE score < ?" args="[80]"

Create

创建单条数据：

entity := UserEntity{Name: P("John"), Score: P(90), Deleted: P(false)}
id, err := userDataAccess.Create(tc, &entity)
// SQL="INSERT INTO t_user (name, score, memo, deleted) VALUES (?, ?, ?, ?)" args="[John 90 <nil> false]"

CreateMulti

创建多条数据：

entities := []UserEntity{
    {Name: P("John"), Score: P(90), Memo: P("Great"), Deleted: P(false)}, 
    {Name: P("Alex"), Score: P(55)},
}
cnt, err := userDataAccess.CreateMulti(tc, entities)
// SQL="INSERT INTO t_user (name, score, memo, deleted) VALUES 
// (?, ?, ?, ?), (?, ?, ?, ?)" args="[John 90 Great false Alex 55 <nil> <nil>]"

Update

根据id更新所有字段：

entity := UserEntity{Int64Id: NewInt64Id(2), Score: P(90), Memo: P("Great")}
cnt, err := userDataAccess.Update(tc, entity)
// SQL="UPDATE t_user SET score = ?, memo = ? WHERE id = ?" args="[90 Great 2]"

Patch

根据id更新所有非空字段：

entity := UserEntity{Int64Id: NewInt64Id(2), Score: P(90)}
cnt, err := userDataAccess.Patch(tc, entity)
// SQL="UPDATE t_user SET score = ? WHERE id = ?" args="[90 2]"

PatchByQuery

根据查询条件更新所有非空字段：

entity := UserEntity{Memo: P("Add Memo")}
query := UserQuery{MemoNull: P(true)}
cnt, err := userDataAccess.PatchByQuery(tc, entity, query)
// SQL="UPDATE t_user SET memo = ? WHERE memo IS NULL" args="[Add Memo]"

小结

GoooQo基于查询对象动态查询条件，从而为单表的增删查改操作构建了一组完全基于对象的调用接口，大大简化了增删查改操作的参数构造。

更多用法请参考GoooQo的在线文档：https://goooqo.docs.doyto.win/v/zh

© 2024 Yuan Zhen. All rights reserved.