# 增删查改接口

## 接口定义

创建事务管理器后，我们需要为每个实体创建一个数据访问接口：

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

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

```go
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`

对于`Entity`的定义，请参考：

{% content-ref url="../entity-mapping/entity-object" %}
[entity-object](https://goooqo.docs.doyto.win/zh/entity-mapping/entity-object)
{% endcontent-ref %}

对于`Query`的定义，请参考：

{% content-ref url="../query-mapping/query-object" %}
[query-object](https://goooqo.docs.doyto.win/zh/query-mapping/query-object)
{% endcontent-ref %}

## 示例

以下接口调用示例基于实体对象`UserEntity`和查询对象`UserQuery`：

```go
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 //score IN (SELECT score FROM t_user WHERE ...)
    ScoreGtAvgScoreOfUser *UserQuery //score > (SELECT AVG(score) FROM t_user WHERE ...)
}
```

## 调用示例

### Get

根据id查询数据：

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

### Query

根据查询条件查询数据：

```go
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

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

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

### Page

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

```go
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删除数据：

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

### DeleteByQuery

根据查询条件删除数据：

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

### Create

创建单条数据：

```go
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

创建多条数据：

```go
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更新所有字段：

```go
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更新所有非空字段：

```go
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

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

```go
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]"
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://goooqo.docs.doyto.win/zh/api/crud.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.