CRUD
Definition
After creating the transaction manager, we can create a TxDataAccess for each entity for CRUD operations:
userDataAccess := rdb.NewTxDataAccess[UserEntity](tm)The method NewTxDataAccess returns a TxDataAccess instance, which consists of the DataAccess interface for CRUD operations and the TransactionManager interface for transaction operations.
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"`
}The parameter ctx can be a normal context.Context or a TransactionContext for transactions.
For the definition of Entity, check:
For the definition of Query, check:
Usages
The following examples are based on UserEntity and UserQuery:
type UserEntity struct {
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 {
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
ScoreGtAvgScoreOfUser *UserQuery
}Get
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
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
Update all fields by 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
Update non-nil fields by 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
Update non-nil fields by query conditions.
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]"Last updated