# 项目介绍

[![License](https://img.shields.io/:license-apache-brightgreen.svg)](https://www.apache.org/licenses/LICENSE-2.0.html) [![Maven Central](https://maven-badges.herokuapp.com/maven-central/win.doyto/doyto-query/badge.svg)](https://maven-badges.herokuapp.com/maven-central/win.doyto/doyto-query/) [![Sonar Stats](https://sonarcloud.io/api/project_badges/measure?project=win.doyto%3Adoyto-query\&metric=alert_status)](https://sonarcloud.io/dashboard?id=win.doyto%3Adoyto-query) [![Code Lines](https://sonarcloud.io/api/project_badges/measure?project=win.doyto%3Adoyto-query\&metric=ncloc)](https://sonarcloud.io/component_measures?id=win.doyto%3Adoyto-query\&metric=ncloc) [![Coverage Status](https://sonarcloud.io/api/project_badges/measure?project=win.doyto%3Adoyto-query\&metric=coverage)](https://sonarcloud.io/component_measures?id=win.doyto%3Adoyto-query\&metric=coverage)

DoytoQuery是一个基于对象查询映射（Object Query Mapping，OQM）技术实现的一款Java版数据库访问框架。其核心思想是完全通过对象构建查询语句，从而不再需要编写构建查询语句的代码。

所述对象包含以下几类：

* **查询对象（Query Object）** 用于动态构建WHERE子句，通过字段构建查询条件，并根据字段的赋值动态组合查询条件。
* **实体对象（Entity Object）** 用于映射单表查询语句中的表名和列名。实体对象的实例用于映射目标表的行数据。
* **分页查询对象（PageQuery）** 定义了分页和排序字段，用于接收前端传递的参数并生成相应的分页和排序子句。作为所有查询对象的基类，它为所有查询接口提供了分页和排序功能。
* **视图对象（View Object）** 用于映射包含查询对象的复杂查询语句。由于复杂查询语句通常包含聚合列和多表关联，因此我们需要一种新的对象类型来替代实体对象。
* **聚合查询对象（Having Object）** 是对聚合后的记录进行过滤的查询对象，用于映射HAVING子句，继承自基础的查询对象。查询对象中定义的字段用于构建`WHERE`子句的条件，而聚合查询对象中定义的字段则用于构建`HAVING`子句的条件。


# 快速上手

本教程将通过为如下角色表构建RESTful服务的完整过程，帮助您快速上手DoytoQuery框架。

| id | role\_name | role\_code | valid |
| -- | ---------- | ---------- | ----- |
| 1  | admin      | ADMIN      | true  |
| 2  | vip        | VIP        | true  |
| 3  | vip2       | VIP2       | true  |
| 4  | vip3       | VIP3       | true  |
| 5  | guest      | GUEST      | true  |

示例代码请访问[Github](https://github.com/f0rb/doyto-query-demo)。

### 初始化工程

#### 1. 在 [Spring Initializer](https://start.spring.io) 上初始化工程，添加以下4个依赖：

* Lombok
* Spring Web
* Validation
* HyperSQL Database

#### 2. 引入DoytoQuery

在`pom.xml`中添加如下依赖：

```xml
<dependencies>
    <dependency>
        <groupId>win.doyto</groupId>
        <artifactId>doyto-query-jdbc</artifactId>
        <version>${doyto-query.version}</version>
    </dependency>
    <dependency>
        <groupId>win.doyto</groupId>
        <artifactId>doyto-query-web</artifactId>
        <version>${doyto-query.version}</version>
    </dependency>
    <dependency>
        <groupId>win.doyto</groupId>
        <artifactId>doyto-query-dialect</artifactId>
        <version>${doyto-query.version}</version>
    </dependency>
    ...
</dependencies>
```

#### 3. 添加默认Web配置

DemoApplication需要继承`win.doyto.query.web.WebMvcConfigurerAdapter`

```java
package win.doyto.query.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import win.doyto.query.web.WebMvcConfigurerAdapter;

@SpringBootApplication
public class DemoApplication extends WebMvcConfigurerAdapter {

    public static void main(String[] args) {
        SpringApplication.run(DoytoQueryDemoApplication.class, args);
    }

}
```

#### 4. 配置分页插件

DoytoQuery默认提供的是MySQL的分页插件，而本demo使用的数据库是[HSQLDB](http://hsqldb.org)，所以我们这里需要引入`doyto-query-dialect`，然后在spring的`application.yaml`文件里配置使用。另外这里列名是小写加下划线的格式，这里同时配置一下`map-camel-case-to-underscore`为true，表示将驼峰形式的字段名映射为下划线形式的列名：

```yaml
doyto:
  query:
    config:
      dialect: win.doyto.query.dialect.HSQLDBDialect
      map-camel-case-to-underscore: true
```

### 初始化数据

在`src/main/resources`下创建`schema.sql`:

```sql
SET DATABASE SQL SYNTAX MYS TRUE;

drop table t_role if exists;
create table t_role
(
    id        bigint generated by default as identity (start with 1) primary key,
    role_name VARCHAR(100) not null,
    role_code VARCHAR(100) not null,
    valid     boolean DEFAULT TRUE
);

INSERT INTO t_role (role_name, role_code) VALUES ('admin', 'ADMIN');
INSERT INTO t_role (role_name, role_code) VALUES ('vip', 'VIP');
INSERT INTO t_role (role_name, role_code) VALUES ('vip2', 'VIP2');
INSERT INTO t_role (role_name, role_code) VALUES ('vip3', 'VIP3');
INSERT INTO t_role (role_name, role_code) VALUES ('guest', 'GUEST');

```

### 创建业务类

在package`win.doyto.query.demo.module.role`下，创建以下三个类：

* `RoleEntity`，用于映射表字段

```java
package win.doyto.query.demo.module.role;

import lombok.Getter;
import lombok.Setter;
import win.doyto.query.entity.AbstractPersistable;
import win.doyto.query.validation.CreateGroup;

import javax.persistence.Table;
import javax.validation.constraints.NotNull;

@Getter
@Setter
@Table(name = "t_role")
public class RoleEntity extends AbstractPersistable<Integer> {

    @NotNull(groups = CreateGroup.class)
    private String roleName;

    @NotNull(groups = CreateGroup.class)
    private String roleCode;

    private Boolean valid;
}

```

* `RoleQuery`，用于生成查询语句

```java
package win.doyto.query.demo.module.role;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.SuperBuilder;
import win.doyto.query.core.PageQuery;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
public class RoleQuery extends PageQuery {
    private String roleNameLike;
}

```

* `RoleController`，用于提供CRUD功能和RESTful接口

```java
package win.doyto.query.demo.module.role;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import win.doyto.query.web.controller.AbstractEIQController;

@RestController
@RequestMapping("role")
public class RoleController extends AbstractEIQController<RoleEntity, Integer, RoleQuery> {
}

```

### 测试验证

只需以上三个类就完成了整个RESTful服务的开发，接下来我们一起验证一下效果。

将`org.springframework.web.servlet.mvc.method.annotation`包的日志等级设置为trace，启动DemoApplication，可以看到`/role/`路径下已经有GET, PUT, PATCH等方法了：

```
s.w.s.m.m.a.RequestMappingHandlerMapping : 
	w.d.q.d.m.r.RoleController:
	{POST [/role]}: add(List)
	{PUT [/role/{id}]}: update(Serializable,Object)
	{GET [/role]}: paging(PageQuery)
	{GET [/role/{id}]}: getById(Serializable)
	{DELETE [/role/{id}]}: deleteById(Serializable)
	{PATCH [/role/{id}]}: patch(Serializable,Object)
```

然后通过curl访问一下分页查询接口：

> curl '<http://localhost:8080/role/?roleNameLike=vip\\&pageNumber=2\\&pageSize=2\\&sort=id,desc>'

```json
{
  "code": 0,
  "message": "ok",
  "data": {
    "list": [
      {
        "id": 2,
        "roleName": "vip",
        "roleCode": "VIP",
        "valid": true
      }
    ],
    "total": 3
  },
  "success": true
}
```

可以看到我们使用`roleNameLike=vip`过滤出3条数据`id=[2,3,4]``，再根据id倒序排列并通过`pageNumber=2`和`pageSize=2\`查询出了第二页id为2的记录。

> 更多查询字段的用法请参考[查询对象字段后缀汇总](https://query.doyto.win/manual/suffix-summary)。

再通过单元测试来验证一下：

```java
@Test
@Rollback
void patchRole() throws Exception {
    RequestBuilder requestBuilder = patch("/role/2")
        .content("{\"roleName\":\"new role\"}")
        .contentType(MediaType.APPLICATION_JSON);
    performAndExpectSuccess(requestBuilder);
    performAndExpectSuccess(get("/role/2"))
            .andExpect(jsonPath("$.data.roleName").value("new role"))
    ;
}
```

> 完整的测试用例请查看[这里](https://github.com/f0rb/doyto-query-demo/blob/main/src/test/java/win/doyto/query/demo/module/role/RoleControllerTest.java)。


# 查询对象


# 分页对象

查询对象需要继承`PageQuery`类以构造分页子句和排序子句。`PageQuery`类定义了三个字段，\
其中，`PageNumber`和`PageSize`用于构建分页子句，`Sort` 用于构建排序子句。

## 示例

### 定义

```java
@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class UserQuery extends PageQuery {
    private Long idGt;
    //...
}
```

### 分页

```java
UserQuery userQuery = UserQuery.builder().build();
List<UserEntity> users = userDataAccess.query(userQuery);
//SELECT id, score, memo FROM User

UserQuery userQuery = UserQuery.builder().pageSize(20).build();
List<UserEntity> users = userDataAccess.query(userQuery);
//SELECT id, score, memo FROM User LIMIT 20 OFFSET 0

// When only PageNumber is set, PageSize will be set to 10
UserQuery userQuery = UserQuery.builder().pageNumber(5).build();
List<UserEntity> users = userDataAccess.query(userQuery);
//SELECT id, score, memo FROM User LIMIT 10 OFFSET 40

UserQuery userQuery = UserQuery.builder().pageNumber(3).pageSize(50).build();
List<UserEntity> users = userDataAccess.query(userQuery);
//SELECT id, score, memo FROM User LIMIT 10 OFFSET 100	
```

### 排序

```java
UserQuery userQuery = UserQuery.builder().sort("id,desc;score,asc;memo").build();
List<UserEntity> users = userDataAccess.query(userQuery);
//SELECT id, score, memo FROM User ORDER BY id DESC, score ASC, memo
```

{% hint style="info" %}
赋值给`Sort`字段的字符串需要符合正则表达式：`PageQuery.SORT_PTN。`
{% endhint %}


# 谓词后缀字段

**谓词后缀字段**用于构建简单查询条件，谓词后缀字段的名称通过属性谓词表达式（Attribute-Predicate Expression，APE）进行定义，即:

$$
{x \in \mathcal{R} : xP(v)}
$$

简记为**xP**，其中*x*表示关系*R*中的属性，*P*来自于预定义的谓词集合，*v*表示查询参数。

### 谓词后缀映射

DoytoQuery采用谓词后缀映射方法，将查询对象中的字段中以预定义谓词结尾的字段映射为基本查询条件。每个基本查询条件由列名、比较运算符和比较值组成。

在查询对象中，用于映射基本查询条件的字段，命名格式为列名加谓词的别名，用于映射查询条件的列名和比较运算符，查询条件的比较值为字段的赋值。 一个查询对象实例中已赋值的字段会被映射为对应的查询条件，并由逻辑运算符AND拼接为查询子句。

以下为后缀映射的两个示例：

```java
UserQuery userQuery = UserQuery.builder().deleted(true).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE deleted = ?" args="[true]"

UserQuery userQuery = UserQuery.builder().idIn(Arrays.asList(1, 4, 12)).deleted(true).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE id IN (?, ?, ?) AND deleted = ?" args="[1 4 12 true]"
```

### 谓词后缀表

谓词后缀表预定义了DoytoQuery中支持的谓词后缀以及映射的查询条件。

<table><thead><tr><th>谓词后缀</th><th>字段名称</th><th>赋值</th><th>SQL查询条件</th><th data-hidden>MongoDB Condition</th></tr></thead><tbody><tr><td>(EMPTY)</td><td>id</td><td>5</td><td>id = 5</td><td>{"id":5}</td></tr><tr><td>Eq</td><td>idEq</td><td>5</td><td>id = 5</td><td>{"idEq":5}</td></tr><tr><td>Not</td><td>idNot</td><td>5</td><td>id != 5</td><td>{"idNot":{"$ne":5}}</td></tr><tr><td>Ne</td><td>idNe</td><td>5</td><td>id &#x3C;> 5</td><td>{"idNe":{"$ne":5}}</td></tr><tr><td>Gt</td><td>idGt</td><td>5</td><td>id > 5</td><td>{"idGt":{"$gt":5}}</td></tr><tr><td>Ge</td><td>idGe</td><td>5</td><td>id >= 5</td><td>{"idGe":{"$gte":5}}</td></tr><tr><td>Lt</td><td>idLt</td><td>5</td><td>id &#x3C; 5</td><td>{"idLt":{"$lt":5}}</td></tr><tr><td>Le</td><td>idLe</td><td>5</td><td>id &#x3C;= 5</td><td>{"idLe":{"$lte":5}}</td></tr><tr><td>NotIn</td><td>idNotIn</td><td>[1,2,3]</td><td>id NOT IN (1,2,3)</td><td>{"id":{"$nin":[1, 2, 3]}}</td></tr><tr><td>In</td><td>idIn</td><td>[1,2,3]</td><td>id IN (1,2,3)</td><td>{"id":{"$in":[1, 2, 3]}}</td></tr><tr><td>Null</td><td>memoNull</td><td>true</td><td>memo IS NULL</td><td>{"memo":{"$type", 10}}</td></tr><tr><td>Null</td><td>memoNull</td><td>false</td><td>memo IS NOT NULL</td><td>{"memo":{"$not":{"$type", 10}}}</td></tr><tr><td>NotLike</td><td>nameNotLike</td><td>"arg"</td><td>name NOT LIKE '%arg%'</td><td>{"name":{"$not":{"$regex":"arg"}}}</td></tr><tr><td>Like</td><td>nameLike</td><td>"arg"</td><td>name LIKE '%arg%'</td><td>{"name":{"$regex":"arg"}}</td></tr><tr><td>NotStart</td><td>nameNotStart</td><td>"arg"</td><td>name NOT LIKE 'arg%'</td><td>{"name":{"$not":{"$regex":"^arg"}}}</td></tr><tr><td>Start</td><td>nameStart</td><td>"arg"</td><td>name LIKE 'arg%'</td><td>{"name":{"$regex":"^arg"}}</td></tr><tr><td>NotEnd</td><td>nameNotEnd</td><td>"arg"</td><td>name NOT LIKE '%arg'</td><td>{"name":{"$not":{"$regex":"arg$"}}}</td></tr><tr><td>End</td><td>nameEnd</td><td>"arg"</td><td>name LIKE '%arg'</td><td>{"name":{"$regex":"arg$"}}</td></tr><tr><td>NotContain</td><td>nameNotContain</td><td>"arg"</td><td>name NOT LIKE '%arg%’</td><td>{"name":{"$not":{"$regex":"arg"}}}</td></tr><tr><td>Contain</td><td>nameContain</td><td>"arg"</td><td>name LIKE '%arg%’</td><td>{"name":{"$regex":"arg"}}</td></tr><tr><td>Rx</td><td>nameRx</td><td>"arg\d"</td><td>name REGEXP 'arg\d’</td><td>{"name":{"$regex":"arg\d"}}</td></tr></tbody></table>


# 逻辑后缀字段

默认情况下，查询对象各个字段对应的查询条件之间通过AND连接。如果想要定义使用逻辑运算符OR连接的查询条件，则需要在字段名称中定义后缀Or，并且支持以下三种字段类型：

```java
public class UserQuery extends PageQuery {
    // ...
    private List<String> nameStartOr;
    private UserQuery userOr;
    private List<UserQuery> usersOr;
}
```

### List\<String> nameStartOr;

```java
UserQuery userQuery = UserQuery.builder().nameStartOr(List.of("Bob","John","Tim")).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (name LIKE ? OR name LIKE ? OR name LIKE ?)" args="[Bob% John% Tim%]"
```

### UserQuery userOr;

```java
UserQuery userQuery = UserQuery.builder().nameStartOr(Arrays.asList(1, 4, 12)).deleted(trur).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (id IN (?, ?, ?) OR deleted = ?)" args="[1 4 12 true]"
```

### List\<UserQuery> usersOr;

```java
UserQuery userQuery = UserQuery.builder()
    .usersOr(List.of(
        UserQuery.builder().idIn(List.of(1L, 4L, 12L)).deleted(true).build(),
        UserQuery.builder().idGt(10L).deleted(false).build()
    )).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user
// WHERE (id IN (?, ?, ?) AND deleted = ? OR id > ? AND deleted = ?)"
// args="[1 4 12 true 10 false]"
```

## And后缀

当字段的名称以`And`结尾时，连接多个查询条件的逻辑运算符为AND。

```java
public class UserQuery extends PageQuery {
    // ...
    private UserQuery userOr;
    private UserQuery userAnd;
}
```

### UserAnd \*UserQuery

```java
UserQuery userAnd = UserQuery.builder().idIn(List.of(1L, 4L, 12L)).deleted(false).build();
UserQuery userOr = UserQuery.builder().deleted(true).userAnd(userAnd).build();
UserQuery userQuery = UserQuery.builder().scoreLt(80).userOr(userOr).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user
// WHERE score < ? AND (deleted = ? OR id IN (?, ?, ?) AND deleted = ?)"
// args="[80 true 1 4 12 false]"
```

### 相关文章

[在GoooQo中怎么表达select \* from user where id = ? or (name = ? and age = ?)](https://blog.doyto.win/post/goooqo-or-clause/)


# 子查询字段

对于一般的子查询条件，例如`score > (SELECT avg(score) FROM t_user WHERE removed = ?)`，在OQM中被分为三个部分分别进行映射：

* score >
* SELECT avg(score) FROM t\_user
* WHERE clause

第一部分`score >`， 可以使用字段名`scoreGtXxx`来映射得到。\
谓词后缀之后定义的字符串仅用于区分重复的字段名，在映射时会被忽略。

第二部分包含一个列名和一个表名，这些属于不变的静态，DoytoQuery提供了两种注解来保存这些信息：

* `@Subquery`：定义子查询语句的列名和表名；
* `@SubqueryV2`：定义一个视图对象，用于配合字段赋值生成子查询语句。

第三部分是另一个WHERE子句，可以通过查询对象来映射。因此，我们将字段类型定义为对应的查询对象，并通过查询对象映射方法将字段的值映射为子查询中WHERE子句。

## 示例

**注解`@Subquery`：**

```java
@SuperBuilder
@NoArgsConstructor
public class UserQuery extends PageQuery {
    // ...
    
    @Subquery(select = "avg(score)", from = UserEntity.class)
    private UserQuery scoreLtAvg;

    @Subquery(select = "score", from = UserEntity.class)
    private UserQuery scoreLtAny;

    @Subquery(select = "score", from = UserEntity.class)
    private UserQuery scoreLtAll;

    @Subquery(select = "avg(score)", from = UserEntity.class)
    private UserQuery scoreGtAvg;
}
```

**注解`@SubqueryV2`：**

```java
@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class MinimumCostSupplierQuery extends PageQuery {
    private Integer p_size;
    private String p_typeEnd;
    private String r_name;
    @SubqueryV2(MinSupplyCostView.class)
    private SupplyCostQuery psSupplycost;

    @View(value = PartEntity.class, context = true)
    @View(PartsuppEntity.class)
    @View(SupplierEntity.class)
    @View(NationEntity.class)
    @View(RegionEntity.class)
    private static class MinSupplyCostView {
        @NoLabel
        private Integer minPs_supplycost;
    }
}
```


# ER关系字段

### 抽象实体路径

在实体关系图中，多对多关系用于表示两个实体之间的关系。多对多关系是具有传递性的。例如，如果实体A与实体B具有多对多关系，而实体B与实体C具有多对多关系，则实体A和实体C也具有多对多关系，这是一种间接的多对多关系。

基于多对多关系的传递性，**抽象实体路径**的概念被提出用于描述实体之间这种直接或间接的多对多关系。抽象实体路径将从一个实体到另一个实体的所有实体作为节点来描述任意两个实体之间所具有的多对多关系。例如，实体A和实体B的抽象实体路径为\[A,B]，实体B和实体A的抽象实体路径为\[B,A]，实体C和实体A的抽象实体路径为\[C,B,A]。抽象实体路径包含了任意两个实体之间关系的全部信息，从而用于动态生成复杂的嵌套查询语句。

DoytoQuery引入**抽象实体路径**概念，定义注解`@DomainPath`来定义实体之间的关系。该标签用于查询对象中的用于查询实体关系的字段。例如，实体路径`` `entitypath:"user,role"` ``，基于预定的表名格式，可以得到两个实体表名t\_user和t\_role，中间表表名a\_user\_and\_role，以及两个外键名称user\_id和role\_id，进而生成查询语句：

```sql
SELECT * FROM t_user WHERE
id IN (
    SELECT user_id FROM a_user_and_role WHERE role_id IN (
       SELECT id FROM t_role WHERE ...
    )
)
```

### 示例

表 `t_menu` 有一个列 `parent_id`，它将 `id` 列本身引用为外键。`parent_id` 列用于定义菜单项之间的层次父子关系。菜单通过通用 RBAC 模型作为系统资源分配给用户。那么菜单到用户的实体路径即为：`menu,perm,role,user`，用于生成嵌套查询语句。

## 嵌套查询

DoytoQuery通过解析字段上配置的@DomainPath注解来为字段生成嵌套查询语句。

注解定义如下：

{% code title="DomainPath.java" %}

```java
@Target(FIELD)
@Retention(RUNTIME)
public @interface DomainPath {
    /**
     * To describe how to route from the host domain to the target domain.
     *
     * @return paths array
     */
    String[] value();

    String localAlias() default  "t";

    /**
     * The field in this domain to maintain the relationship with the target domain.
     *
     * @return name of the local field
     */
    String localField() default "id";

    /**
     * The field in another domain to maintain the relationship with this domain.
     *
     * @return name of the foreign field
     */
    String foreignField() default "id";

    String foreignAlias() default "t1";
}

```

{% endcode %}

#### 简单嵌套查询

假设一个具有层级关系的菜单表，子菜单的`parent_id`指向父菜单的`id`，这样可以通过如下语句查询出所有的父菜单：

```sql
SELECT * FROM menu WHERE id IN (SELECT parent_id FROM menu)
```

通过DoytoQuery执行该查询，需要创建对应的MenuQuery类，并添加一个用于查询的字段，配置@NestedQueries注解：

```java

@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class MenuQuery extends PageQuery {
    // many-to-one
    @DomainPath(value = "menu", localField = "parentId")
    private MenuQuery parent;

    // one-to-many
    @DomainPath(value = "menu", foreignField = "parentId")
    private MenuQuery children;

    @DomainPath({"menu", "perm", "role", "user"})
    private UserQuery user;

    private String nameLike;
    private Boolean valid;
}

```


# 自定义查询字段

## 使用场景

当遇到现有的字段映射方法无法支持的SQL语句时，可以使用注解`@QueryField`自定义SQL语句，作为临时方案。

## 注解定义

{% code title="QueryField.java" %}

```java
@Target(FIELD)
@Retention(RUNTIME)
public @interface QueryField {
    String and();
}
```

{% endcode %}

{% hint style="info" %}
当被注解字段的值满足过滤条件时，and变量里定义的条件语句将会被拼接到SQL中。
{% endhint %}

## 代码示例

**业务代码：**

```java
public class TestQuery extends PageQuery {
    @QueryField(and = "(username = ? OR email = ? OR mobile = ?)")
    private String account;
}
```

**单元测试：**

```java
@Test
void testQueryField() {
    TestQuery testQuery = TestQuery.builder().account("test").build();
    ArrayList<Object> argList = new ArrayList<>();

    String sql = BuildHelper.buildWhere(testQuery, argList);

    assertThat(sql).isEqualTo(" WHERE (username = ? OR email = ? OR mobile = ?)");
    assertThat(argList).containsExactly("test", "test", "test");
}
```

`TestQuery`的account字段通过`@QueryField`注解定义的查询条件被原样拼接到WHERE语句，并且因为查询条件里有3个占位符，account的值"test"也被三次添加到argList。


# 实体对象

## 示例

```java
@Getter
@Setter
public class UserEntity extends AbstractCommonEntity<Long, Long> {
    private String name;
    private Integer score;
    private String memo;
}
```

实体对象用于为CRUD语句的构建提供表名和列名。需要实现`Persistable`接口，或者继承基类`AbstractPersistable`, `AbstractEntity`, `AbstractCommonEntity`.

示例中`UserEntity`对应的增删查改语句为：

```sql
SELECT id, name, score, memo FROM t_user；
INSERT INTO t_user (name, score, memo) VALUES (?, ?, ?)
UPDATE t_user SET name = ?, score = ?, memo = ? WHERE id = ?;
DELETE FROM t_user WHERE id = ?;
```


# 枚举字段


# 分表

## 涉及组件

* IdWrapper
* AbstractDynamicService


# 视图对象

复杂查询通常涉及多个表的连接和列的聚合操作。虽然查询对象可以复用来构造`WHERE`子句，但实体对象无法完整表达查询的其他部分。因此，我们引入专门的**视图对象**来定义复杂查询中的静态部分，同时引入单独的**Having对象**用于生成`HAVING`子句。此外，视图对象也作为结果映射的目标。

为了实现复杂查询的自动映射，我们将映射过程划分为以下三部分：

1. **列映射**：视图对象中定义的字段用于映射查询结果中的所需列，包括普通列和聚合列。
2. **连接映射**：视图对象中的注解用于定义表之间的关系，支持自动生成必要的连接语句。
3. **HAVING子句映射**：Having对象用于生成HAVING子句，支持基于聚合结果的过滤条件。

该设计不仅提高了开发者构造和维护复杂查询语句的效率，也减少了手动拼装SQL带来的风险，显著提升了代码的可读性、可重用性和可维护性。

## 聚合查询接口

除了为单表数据访问设计接口外，还需为聚合查询设计单独接口。

设计并实现了接口 `AggregateClient`，定义如下： 该接口提供通用方法 `query`，允许开发者通过指定目标视图类和查询对象执行聚合查询，结果映射为对应视图对象列表。

```java
public interface AggregateClient {
    <V> AggregateChain<V> aggregate(Class<V> viewClass);

    default <V> List<V> query(Class<V> viewClass, DoytoQuery query) {
        return aggregate(viewClass).filter(query).query();
    }

    default <V> long count(Class<V> viewClass, DoytoQuery query) {
        return aggregate(viewClass).filter(query).count();
    }

    default <V> PageList<V> page(Class<V> viewClass, DoytoQuery query) {
        return aggregate(viewClass).filter(query).page();
    }
}
```


# 列映射

视图对象的字段不仅用于映射不同表的列，还用于表示聚合表达式和分组子句。

## 聚合列映射

聚合函数的应用是聚合查询的核心功能。所有通过聚合列查询的数据都需由视图类中定义的字段保存，因此我们需要将字段名映射为聚合列，使用前缀映射是一种很好的选择。

| 前缀         | 聚合函数名        | 字段名             | 聚合列表达式                                 |
| ---------- | ------------ | --------------- | -------------------------------------- |
| sum        | sum          | sumScore        | sum(score) AS sumScore                 |
| max        | max          | maxScore        | max(score) AS maxScore                 |
| min        | min          | minScore        | min(score) AS minScore                 |
| avg        | avg          | avgScore        | avg(score) AS avgScore                 |
| first      | first        | firstScore      | first(score) AS firstScore             |
| last       | last         | lastScore       | last(score) AS lastScore               |
| stdDevPop  | stddev\_pop  | stdDevPopScore  | stddev\_pop(score) AS stdDevPopScore   |
| stdDevSamp | stddev\_samp | stdDevSampScore | stddev\_samp(score) AS stdDevSampScore |
| stdDev     | stddev       | stdDev          | stddev(score) AS stdDev                |
| addToSet   | addToSet     | addToSetScore   | addToSet(score) AS addToSetScore       |
| push       | push         | pushScore       | push(score) AS pushScore               |
| count      | count        | countScore      | count(score) AS countScore             |
| count      | count        | count           | count(\*) AS count                     |

**示例** 如果要计算名为 `score` 的列的平均值，使用 `avg` 函数，定义字段名为 `avgScore`，遵循聚合前缀+列名的命名规则，最终映射为 `avg(score) AS avgScore`。

## 聚合表达式映射

对于无法直接用字段名表示的聚合表达式，可以通过注解指定具体的表达式，将表达式映射为列名，字段名作为标签。

**示例** 定义字段时添加表达式注解：

```java
@Column(name = "sum(l_extendedprice*(1-l_discount))")
private BigDecimal sum_disc_price;
```

该字段映射为：

```sql
sum(l_extendedprice*(1-l_discount)) AS sum_disc_price
```

**完整示例** 请参见表格：

| LN | 对象代码                                                         | SQL 语句                                                 |
| -- | ------------------------------------------------------------ | ------------------------------------------------------ |
| 1  | @View(CustomerEntity.class)                                  | SELECT                                                 |
| 2  | @View(OrdersEntity.class)                                    |                                                        |
| 3  | @View(LineitemEntity.class)                                  |                                                        |
| 4  | public class ShippingPriorityView {                          |                                                        |
| 5  | @GroupBy                                                     |                                                        |
| 6  | private String l\_orderkey;                                  | l\_orderkey,                                           |
| 7  | @Column(name = "SUM(l\_extendedprice \* (1 - l\_discount))") | SUM(l\_extendedprice \* (1 - l\_discount)) AS revenue, |
| 8  | private Double revenue;                                      |                                                        |
| 9  | @GroupBy                                                     |                                                        |
| 10 | private Date o\_orderdate;                                   | o\_orderdate,                                          |
| 11 | @GroupBy                                                     |                                                        |
| 12 | private String o\_shippriority;                              | o\_shippriority                                        |
| 13 | }                                                            | FROM customer, orders, lineitem                        |
|    | // @View(CustomerEntity.class)                               | WHERE o\_custkey = c\_custkey                          |
|    | // @View(OrdersEntity.class)                                 | AND l\_orderkey = o\_orderkey                          |
|    | // @View(LineitemEntity.class)                               |                                                        |
| 1  | public class ShippingPriorityQuery extends PageQuery {       |                                                        |
| 2  | private String c\_mktsegment;                                | AND c\_mktsegment = ?                                  |
| 3  | private Date o\_orderdateLt;                                 | AND o\_orderdate < ?                                   |
| 4  | private Date l\_shipdateGt;                                  | AND l\_shipdate > ?                                    |
| 5  | }                                                            | GROUP BY l\_orderkey, o\_orderdate, o\_shippriority    |
|    | // PageQuery.sort = "revenue,DESC;o\_orderdate"              | ORDER BY revenue DESC, o\_orderdate                    |


# 分组映射

对于`GROUP BY`子句，通常分组字段也需要出现在返回列中，可通过在视图类字段上添加注解声明其为分组列，并在映射时添加到`GROUP BY`后。

**示例**

定义视图对象时，在用于分组的字段上添加`@GroupBy`注解：

```java
@GroupBy
private int returnFlag;

@GroupBy
private int lineStatus;
```

带有`@GroupBy`注解的字段被映射为：

```sql
SELECT return_flag, line_status, ... FROM ...
GROUP BY return_flag, line_status ...
```


# 内连接

以TPC-H基准测试的第3条查询“Shipping Priority Query”为例，演示如何映射表连接。该查询语句如下：

```sql
SELECT l_orderkey, SUM(l_extendedprice * (1 - l_discount)) AS revenue, o_orderdate, o_shippriority
FROM customer, orders, lineitem
WHERE o_custkey = c_custkey
  AND l_orderkey = o_orderkey
  AND c_mktsegment = ?
  AND o_orderdate < ?
  AND l_shipdate > ?
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY revenue DESC, o_orderdate
```

这是典型的多表交叉连接，需处理以下两部分：

1. 多个表名：`FROM customer, orders, lineitem`
2. 连接条件：`WHERE o_custkey = c_custkey AND l_orderkey = o_orderkey`

### 多表名映射

**定义** 通过视图类上的注解 `@ComplexView` 和 `@View` 来配置连接的实体：

```java
@Target(TYPE)
@Retention(RUNTIME)
public @interface ComplexView {
    View[] value();
}

@Target(TYPE)
@Retention(RUNTIME)
@Repeatable(ComplexView.class)
public @interface View {
    Class<?> value();
    String alias() default "";
    ViewType type() default ViewType.TABLE_NAME;
}
```

**配置** 针对运输优先级查询，定义如下视图类：

```java
@View(CustomerEntity.class)
@View(OrdersEntity.class)
@View(LineitemEntity.class)
public class ShippingPriorityView { /*...*/ }
```

**解析** 通过注解配置，自动获取表名 `customer, orders, lineitem`。

### 连接条件映射

**定义** 通过注解 `@ForeignKey` 配置实体外键字段，确定实体间关系：

```java
@Target(FIELD)
@Retention(RUNTIME)
public @interface ForeignKey {
    Class<?> entity();
    String field();
}
```

**示例** 在 `OrdersEntity` 中配置外键字段：

```java
public class OrdersEntity extends AbstractPersistable<Long> {
  private String o_orderkey;
  @ForeignKey(entity = CustomerEntity.class, field = "c_custkey")
  private String o_custkey;
  //...
}
```

**解析** 当视图类用 `@View` 注解配置后，会扫描其他连接实体，查找外键对应实体，若存在则加入连接条件。

在 `ShippingPriorityView` 中找到 `OrdersEntity` 的 `o_custkey` 对应 `CustomerEntity` 的 `c_custkey`，添加连接条件 `o_custkey = c_custkey`，同理添加 `l_orderkey = o_orderkey`。

处理的 SQL 部分为：

```sql
FROM  customer, orders, lineitem
WHERE o_custkey = c_custkey
AND   l_orderkey = o_orderkey
```


# 外连接

通过JOIN/ON关键字实现的连接统一使用`@Join`进行映射。

这类连接在 `ON` 关键字后添加动态查询条件。为此，引入新的查询对象映射 `ON` 子句的条件，并定义为主查询对象的字段。

为此，设计了新注解 `@Join`，用于映射连接表和指定连接类型，同时复用已有的 `@ForeignKey` 注解映射连接条件。

| LN | 对象代码                                                           | SQL 语句                         |
| -- | -------------------------------------------------------------- | ------------------------------ |
| 1  | public class CustomerOrdersQuery extends PageQuery {           |                                |
| 2  | @Join(from = @View(value = CustomerEntity.class, alias = "c"), | FROM customer c                |
| 3  | type = Join.JoinType.LEFT\_JOIN,                               | LEFT JOIN orders o             |
| 4  | join = @View(value = OrdersEntity.class, alias = "o"))         | ON o.o\_custkey = c.c\_custkey |
| 5  | private JoinOrders joinOrders;                                 | AND o.o\_comment NOT LIKE ?    |
| 6  | }                                                              |                                |


# 聚合查询对象

SQL中`HAVING`子句用于过滤聚合后的分组记录。`HAVING`子句是可选的，语法类似`WHERE`子句。

`HAVING`子句的映射通过**Having 对象**实现。

映射过程复用查询对象映射算法，字段映射时同时支持前缀映射和后缀映射，例如，Having 对象中定义字段 `avgScoreGe` 会映射为：

```sql
HAVING avg(score) >= ?
```

在 Java 中，Having 对象实现一个空接口 `Having` 以标记其用于构建 HAVING 子句。同时，可以让 Having 对象继承查询对象，查询对象继承 `PageQuery`，形成三级结构。

* `PageQuery` 中的字段用于构建排序和分页子句；
* 查询对象中的字段用于构建 `WHERE` 子句；
* Having 对象中的字段用于构建 `HAVING` 子句。


# 增删查改接口

## 接口定义

`DataAccess`接口提供访问数据库的增删查改方法。

```java
public interface DataAccess<E extends Persistable<I>, I extends Serializable, Q extends DoytoQuery> {
    List<E> query(Q query);
    long count(Q query);
    PageList<E> page(Q query);
    <V> List<V> queryColumns(Q q, Class<V> clazz, String... columns);
    List<I> queryIds(Q query);

    default E get(I id) {
        return get(IdWrapper.build(id));
    }
    E get(IdWrapper<I> w);

    default int delete(I id) {
        return delete(IdWrapper.build(id));
    }
    int delete(IdWrapper<I> w);
    int delete(Q query);

    void create(E e);
    default int batchInsert(Iterable<E> entities, String... columns) {
        int count = 0;
        for (E entity : entities) {
            create(entity);
            count++;
        }
        return count;
    }

    int update(E e);
    int patch(E e);
    int patch(E e, Q q);
}
```

`DataAccess`接口中的所有方法一共只接收4类参数：

* `id` 实体的主键；
* `IdWrapper` 分表主键对象，用于分表查询；
* `Entity` 实体对象，用于映射表名和列名；
* `Query` 查询对象，用于动态构造查询条件和分页语句，需要继承`PageQuery`

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

{% content-ref url="/pages/qlp373ax6m3xSZB44dXP" %}
[实体对象](/zh/object-concepts/entity-object)
{% endcontent-ref %}

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

{% content-ref url="/spaces/eRFevZWNbuxdA1N2KdM3/pages/3mtv0dXnMCfluPcIxNf5" %}
[查询对象](/zh/object-concepts/query-object)
{% endcontent-ref %}

## 示例

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

```java
@Getter
@Setter
public class UserEntity extends AbstractCommonEntity<Long, Long> {
    @NotNull(groups = CreateGroup.class)
    private String name;
    private Integer score;
    private String memo;
    private Boolean deleted;
}

@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class UserQuery extends PageQuery {
    private Long idGt;
    private List<Long> idIn;
    private Integer scoreLt;
    private Boolean memoNull;
    private String memoLike;
    private Boolean deleted;
    private List<UserQuery> userOr;

    @QueryField(and = "(username = ? OR email = ?)")
    private String account;

    @Subquery(select = "avg(score)", from = UserEntity.class)
    private UserQuery scoreLtAvg;

    @Subquery(select = "score", from = UserEntity.class)
    private UserQuery scoreLtAny;

    @Subquery(select = "score", from = UserEntity.class)
    private UserQuery scoreLtAll;

    @Subquery(select = "avg(score)", from = UserEntity.class)
    private UserQuery scoreGtAvg;
}

@Bean
public JdbcDataAccess<UserEntity, Long, UserQuery>
userDataAccess(@Autowired DatabaseOperations databaseOperations) {
    return new JdbcDataAccess<>(databaseOperations, UserEntity.class);
}
```

### Get

根据id查询数据：

```java
UserEntity userEntity = userDataAccess.get(3L);
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE id = ?" args="[3]"
```

### Query

根据查询条件查询数据：

```java
// 示例 1
UserQuery userQuery = UserQuery.builder().scoreLt(80).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE score < ?" args="[80]"

// 示例 2
UserQuery userQuery = UserQuery.builder().memoLike("Great").pageSize(20).sort("id,desc;score").build();
List<UserEntity> users = userDataAccess.query(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]"

// 示例 3
UserQuery userQuery = UserQuery.builder().idIn(List.of(1L, 4L, 12L)).deleted(true).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE id IN (?, ?, ?) AND deleted = ?" args="[1 4 12 true]"

// 示例 4
UserQuery userQuery = UserQuery.builder()
    .userOr(List.of(
        UserQuery.builder().idGt(10L).memoNull(true).build(),
        UserQuery.builder().scoreLt(80).memoLike("Good").build()
    ))
    .build();
List<UserEntity> users = userDataAccess.query(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]"

// 示例 5
UserQuery userQuery = UserQuery.builder()
    .scoreGtAvg(UserQuery.builder().deleted(true).build())
    .scoreLtAny(UserQuery.builder().build())
    .build();
List<UserEntity> users = userDataAccess.query(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]"

// 示例 6
UserQuery userQuery = UserQuery.builder().account("John").build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (username = ? OR email = ?)" args="[John John]"
```

### Count

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

```java
UserQuery userQuery = UserQuery.builder().scoreLt(60).build();
long count = userDataAccess.count(userQuery);
// SQL="SELECT count(0) FROM t_user WHERE score < ?" args="[60]"
```

### Page

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

```java
UserQuery userQuery = UserQuery.builder().scoreLt(80).pageSize(20).build();
PageList<UserEntity> page = userDataAccess.page(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删除数据：

```java
int deletedCount = userDataAccess.delete(3L);
// SQL="DELETE FROM t_user WHERE id = ?" args="[3]"
```

### DeleteByQuery

根据查询条件删除数据：

```java
UserQuery userQuery = UserQuery.builder().scoreLt(80).build();
int deletedCount = userDataAccess.delete(userQuery);
// SQL="DELETE FROM t_user WHERE score < ?" args="[80]"
```

### Create

创建单条数据：

```java
UserEntity user = new UserEntity();
user.setName("John");
user.setScore(90);
user.setDeleted(false);
userDataAccess.create(user);
// SQL="INSERT INTO t_user (name, score, memo, deleted) VALUES (?, ?, ?, ?)" args="[John, 90, null, false]"
```

### CreateMulti

创建多条数据：

```java
UserEntity user1 = new UserEntity();
user1.setName("John");
user1.setScore(90);
user1.setMemo("Great");
user1.setDeleted(false);
UserEntity user2 = new UserEntity();
user2.setName("Alex");
user2.setScore(55);
List<UserEntity> entities = List.of(user1, user2);
int createdCount = userDataAccess.batchInsert(entities);
// SQL="INSERT INTO t_user (name, score, memo, deleted) VALUES (?, ?, ?, ?), (?, ?, ?, ?)" args="[John, 90, Great, false, Alex, 55, null, null]"
```

### Update

根据id更新所有字段：

```java
UserEntity user = new UserEntity();
user.setId(2L);
user.setScore(90);
user.setMemo("Great");
int updatedCount = userDataAccess.update(user);
// SQL="UPDATE t_user SET score = ?, memo = ? WHERE id = ?" args="[90 Great 2]"
```

### Patch

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

```java
UserEntity user = new UserEntity();
user.setId(2L);
user.setScore(90);
int patchedCount = userDataAccess.patch(user);
// SQL="UPDATE t_user SET score = ? WHERE id = ?" args="[90 2]"
```

### PatchByQuery

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

```java
UserEntity user = new UserEntity();
user.setMemo("Add Memo");
UserQuery query = UserQuery.builder().memoNull(true).build();
int patchedCount = userDataAccess.patch(user, query);
// SQL="UPDATE t_user SET memo = ? WHERE memo IS NULL" args="[Add Memo]"
```


# 中间表访问接口

### 表结构

```sql
create table t_user_and_role (user_id bigint, role_id int);
```

### 定义

```java
@Bean
public AssociativeService<Long, Integer> userAndRoleAssociativeService() {
    return new TemplateAssociativeService<>("t_user_and_role", "userId", "roleId");
}
```

### 使用

```java
@RestController
class AuthController {
    @Resource
    AssociativeService<Long, Integer> userAndRoleAssociativeService;

    @GetPostMapping("reallocateRolesForUser")
    public void reallocateRoles(Long userId, @RequestParam List<Integer> roleIds) {
        userAndRoleAssociativeService.reallocateForLeft(userId, roleIds);
    }
}

```

### 访问

访问该接口将执行以下 SQL语句

```sql
DELETE FROM t_user_and_role WHERE userId = ?;
INSERT INTO t_user_and_role (userId, roleId) values (?, ?)[, (?, ?)];
```


# Controller

### AbstractEIQController

请求和响应直接使用实体类作为参数：

```java
@RestController
@RequestMapping("role")
public class RoleController extends AbstractEIQController<RoleEntity, Integer, RoleQuery> {
}
```

### AbstractRestController

请求和响应使用DTO作为参数：

```java
@RestController
@RequestMapping("user")
public class UserController extends AbstractRestController<UserEntity, Long, UserQuery, UserRequest, UserResponse> {
}
```

### AbstractDynamicController

分表Controller：

```java
@JsonBody
@RestController
@RequestMapping("{platform}/menu")
public class MenuController extends AbstractDynamicController<MenuEntity, Integer, MenuQuery, MenuRequest, MenuResponse, MenuIdWrapper> {
    public MenuController(MenuService menuService) {
        super(menuService, new TypeReference<>() {});
    }
}

@Service
public class MenuService extends AbstractDynamicService<MenuEntity, Integer, MenuQuery> {
}

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class MenuIdWrapper implements IdWrapper<Integer> {
    private Integer id;
    private String platform;

    @Override
    public String toCacheKey() {
        return id + "-" + platform;
    }
}
```


# Service


# 异常断言

### 定义

```java
public interface ErrorCode {

    Integer getCode();

    String getMessage();
    
    ...

    static void assertNotNull(Object target, ErrorCode errorCode, Object... messages) {
        assertFalse(target == null, errorCode, messages);
    }

    static void assertTrue(boolean condition, ErrorCode errorCode, Object... messages) {
        assertFalse(!condition, errorCode, messages);
    }

    static void assertFalse(boolean condition, ErrorCode errorCode, Object... messages) {
        if (condition) {
            fail(errorCode, messages);
        }
    }

    static void fail(ErrorCode errorCode, Object... messages) {
        Logger logger = LoggerFactory.getLogger(ErrorCode.class);
        if (logger.isWarnEnabled()) {
            logger.warn("[{}]{} {}", errorCode.getCode(), errorCode.getMessage(), StringUtils.join(messages, ", "));
        }
        throw new ErrorCodeException(errorCode);
    }
}
```

### 用法

```java
public void patch(R request) {
    E e = buildEntity(request);
    int count = service.patch(e);
    ErrorCode.assertTrue(count == 1, PresetErrorCode.ENTITY_NOT_FOUND);
}
```

### 返回值

```json
{
  "code": "9",
  "message": "查询记录不存在"
}
```


# 客户端响应

## 注解使用

注解在Controller的类上，所有mapping方法的返回值都会被包装在`JsonResponse`的`data`字段。

注解在Controller的mapping方法上，仅该方法的返回值会被包装。

### 示例

* 使用

```java
@RestController
@RequestMapping("user")
@JsonBody
public class UserController extends AbstractRestController<UserEntity, Long, UserQuery, UserRequest, UserResponse> {
    //...
}
```

* 返回值

```json
{
  "code": "0",
  "message": "ok",
  "data": {}
}
```


# 校验


# 用户ID注入


# 缓存

为实体配置缓存：

```yml
doyto:
  query:
    caches: UserEntity, MenuEntity
```


# 排序参数


# 数据库方言

### 添加依赖

```xml
<dependency>
    <groupId>win.doyto</groupId>
    <artifactId>doyto-query-dialect</artifactId>
    <version>${doyto-query.version}</version>
</dependency>
```

### 配置方式

#### 方式一: 文件配置

```yml
doyto:
  query:
    config:
      dialect: win.doyto.query.dialect.PostgreSQLDialect
```

#### 方式二：静态方法设置

```java
GlobalConfiguration.instance().setDialect(new HSQLDBDialect());
```

### 数据库支持

| Database   | Dialect                                   |
| ---------- | ----------------------------------------- |
| HSQLDB     | win.doyto.query.dialect.HSQLDBDialect     |
| MySQL 5    | win.doyto.query.dialect.MySQLDialect      |
| MySQL 8    | win.doyto.query.dialect.MySQL8Dialect     |
| Oracle     | win.doyto.query.dialect.OracleDialect     |
| PostgreSQL | win.doyto.query.dialect.PostgreSQLDialect |
| SQL Server | win.doyto.query.dialect.SQLServerDialect  |
| SQLite     | win.doyto.query.dialect.SQLiteDialect     |

### 接口说明

{% code title="Dialect.java" %}

```java
package win.doyto.query.core;

public interface Dialect {
    String buildPageSql(String sql, int limit, long offset);
    default String wrapLabel(String fieldName) {
        return fieldName;
    }
    // Other methods..
}
```

{% endcode %}


# SQL日志

如果想要查看执行的SQL语句，只需要将**win.doyto.query.core.SqlAndArgs**的日志等级配置为debug即可。

在spring的yaml文件里配置如下：

{% code title="application.yml" %}

```yaml
logging:
  level:
    win.doyto.query.core.SqlAndArgs: debug
```

{% endcode %}

日志打印如下：

```
...
2021-02-25 22:17:18.442 DEBUG 80237 --- [           main] win.doyto.query.core.SqlAndArgs          : SQL  : SELECT platform, parentId, menuName, memo, valid, id, createUserId, createTime, updateUserId, updateTime FROM menu WHERE id = ?
2021-02-25 22:17:18.442 DEBUG 80237 --- [           main] win.doyto.query.core.SqlAndArgs          : Param: 3(java.lang.Integer)
2021-02-25 22:17:18.447 DEBUG 80237 --- [           main] win.doyto.query.core.SqlAndArgs          : SQL  : DELETE FROM menu WHERE id = ?
2021-02-25 22:17:18.447 DEBUG 80237 --- [           main] win.doyto.query.core.SqlAndArgs          : Param: 3(java.lang.Integer)
...
```


# 文章


# Introduction

DoytoQuery is a Java-based database access framework built on Object Query Mapping (OQM) technology. Its core idea is to generate query statements from a set of objects:

* **Query Object**: Used to dynamically construct the `WHERE` clause by building query conditions based on fields and combining them dynamically according to their assigned values.
* **Entity Object**: Used to map table names and column names in single-table queries. Instances of entity objects represent rows in the target table.
* **PageQuery**: Defines pagination and sorting fields, serving to receive parameters from the frontend and generate the corresponding pagination and sorting clauses. As the base class for all query objects, it provides pagination and sorting capabilities for all query interfaces.
* **View Object**: Used to map complex query statements that include query objects. Since complex queries often involve aggregate columns and multi-table joins, a new type of object is needed to replace the entity object.
* **Having Object**: A specialized query object used to filter aggregated records by mapping to the `HAVING` clause. It inherits from the base query object. Fields defined in the query object are used to construct conditions for the `WHERE` clause, while fields in the having object are used for the `HAVING` clause.


# Quickstart

1. Initialize the project on Spring Initializer with the following 4 dependencies:

* Lombok
* Spring Web
* Validation
* \[A database driver]

2. Add DoytoQuery dependencies in pom.xml:

```xml
<dependency>
    <groupId>win.doyto</groupId>
    <artifactId>doyto-query-jdbc</artifactId>
    <version>2.1.0</version>
</dependency>
<dependency>
    <groupId>win.doyto</groupId>
    <artifactId>doyto-query-web</artifactId>
    <version>2.1.0</version>
</dependency>
<dependency>
    <groupId>win.doyto</groupId>
    <artifactId>doyto-query-dialect</artifactId>
    <version>2.1.0</version>
</dependency>
```

3. Define entity and query objects for a table:

```java
@Getter
@Setter
@Entity(name = "user")
public class UserEntity extends AbstractPersistable<Long> {
    private String username;
    private Integer age;
    private Boolean valid;
}

@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class UserQuery extends PageQuery {
    private String username;
    private Integer ageGe;
    private Integer ageLt;
    private Boolean valid;
}
```

Invoking the method [`DataAccess#query(Q)`](https://github.com/doytowin/doyto-query/blob/main/doyto-query-api/src/main/java/win/doyto/query/core/DataAccess.java) in `UserService`:

```java
@Service
public class UserService extends AbstractCrudService<UserEntity, Long, UserQuery> {
    public List<UserEntity> findValidAdultUsers() {
        UserQuery userQuery = UserQuery.builder().ageGe(20).valid(true).pageSize(10).build();
        // Executed SQL: SELECT username, email, valid, id FROM t_user WHERE age >= ? AND valid = ? LIMIT 10 OFFSET 0
        // Parameters  : 20(java.lang.Integer), true(java.lang.Boolean)
        return dataAccess.query(userQuery);
    }
}
```

Define a controller to support RESTful API:

```java
@RestController
@RequestMapping("user")
public class UserController extends AbstractEIQController<UserEntity, Long, UserQuery> {
}
```

Set the log level of `logging.level.org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping` to `trace`, and after starting the Spring Boot application, you can see in the console that the RESTful interface is ready for `/user/`:

```
s.w.s.m.m.a.RequestMappingHandlerMapping : 
	w.d.q.d.m.u.UserController:
	{PUT [/user/{id}]}: update(Persistable)
	{DELETE [/user/{id}]}: remove(Serializable)
	{GET [/user/{id}]}: get(Serializable)
	{DELETE [/user/]}: delete(DoytoQuery)
	{PATCH [/user/]}: patch(Object,DoytoQuery)
	{GET [/user/]}: page(DoytoQuery)
	{POST [/user/]}: create(List)
	{PATCH [/user/{id}]}: patch(Object)
```

Refer to the [demo](https://github.com/doytowin/doyto-query-demo) for more details.


# Query Object

{% content-ref url="/pages/kadWba74T8M6wgxji4oT" %}
[Predicate-Suffix Field](/object-concepts/query-object/predicate-suffix-field)
{% endcontent-ref %}

{% content-ref url="/pages/6Ct6SEV3GNJe5tfwC1LM" %}
[Logic-Suffix Field](/object-concepts/query-object/logic-suffix-field)
{% endcontent-ref %}

{% content-ref url="/pages/wEiqj8mTKSBocuyrAHe6" %}
[Subquery Field](/object-concepts/query-object/subquery-field)
{% endcontent-ref %}

{% content-ref url="<https://github.com/doytowin/doyto-query-docs/blob/en/en/object-concepts/query-object/e-r-query-field.md>" %}
<https://github.com/doytowin/doyto-query-docs/blob/en/en/object-concepts/query-object/e-r-query-field.md>
{% endcontent-ref %}

{% content-ref url="/pages/icxmLWECgvvuWblzLt2k" %}
[Custom Condition Field](/object-concepts/query-object/custom-condition-field)
{% endcontent-ref %}


# PageQuery

A query object needs to inherit the `PageQuery` class to construct pagination and sorting clauses. The `PageQuery` class defines three fields, where `pageNumber` and `pageSize` are used to build the pagination clause, and `sort` is used to build the sorting clause.

## Example

### Definition

```java
@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class UserQuery extends PageQuery {
    private Long idGt;
    //...
}
```

### Pagination

```java
UserQuery userQuery = UserQuery.builder().build();
List<UserEntity> users = userDataAccess.query(userQuery);
//SELECT id, score, memo FROM User

UserQuery userQuery = UserQuery.builder().pageSize(20).build();
List<UserEntity> users = userDataAccess.query(userQuery);
//SELECT id, score, memo FROM User LIMIT 20 OFFSET 0

// When only PageNumber is set, PageSize will be set to 10
UserQuery userQuery = UserQuery.builder().pageNumber(5).build();
List<UserEntity> users = userDataAccess.query(userQuery);
//SELECT id, score, memo FROM User LIMIT 10 OFFSET 40

UserQuery userQuery = UserQuery.builder().pageNumber(3).pageSize(50).build();
List<UserEntity> users = userDataAccess.query(userQuery);
//SELECT id, score, memo FROM User LIMIT 10 OFFSET 100	
```

### Sorting

```java
UserQuery userQuery = UserQuery.builder().sort("id,desc;score,asc;memo").build();
List<UserEntity> users = userDataAccess.query(userQuery);
//SELECT id, score, memo FROM User ORDER BY id DESC, score ASC, memo
```

{% hint style="info" %}
The string assigned to the `sort` field must conform to the regular expression: `PageQuery.SORT_PTN`.
{% endhint %}


# Predicate-Suffix Field

**Predicate suffix fields** are used to construct simple query conditions. The names of predicate suffix fields are defined through Attribute-Predicate Expressions (APE), i.e.:

$$
{x \in \mathcal{R} : xP(v)}
$$

This is abbreviated as **xP**, where *x* represents an attribute in relation *R*, *P* is from a predefined set of predicates, and *v* denotes the query parameter.

### Predicate Suffix Mapping

DoytoQuery adopts the predicate suffix mapping method, which maps fields in the query object ending with predefined predicates to basic query conditions. Each basic query condition consists of a column name, a comparison operator, and a comparison value.

In a query object, the fields used to map basic query conditions are named in the format: column name + predicate alias. They are used to map the column name and comparison operator of the query condition, while the assigned value of the field serves as the comparison value for the query condition. In an instance of a query object, fields that have been assigned values are mapped to corresponding query conditions, which are then concatenated by the logical operator AND to form the query clause.

The following are two examples of suffix mapping:

```java
UserQuery userQuery = UserQuery.builder().deleted(true).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE deleted = ?" args="[true]"

UserQuery userQuery = UserQuery.builder().idIn(Arrays.asList(1, 4, 12)).deleted(true).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE id IN (?, ?, ?) AND deleted = ?" args="[1 4 12 true]"
```

### Predicate Suffix Table

The Predicate Suffix Table predefines the predicate suffixes supported in DoytoQuery and the query conditions to which they are mapped.

<table><thead><tr><th>谓词后缀</th><th>字段名称</th><th>赋值</th><th>SQL查询条件</th><th data-hidden>MongoDB Condition</th></tr></thead><tbody><tr><td>(EMPTY)</td><td>id</td><td>5</td><td>id = 5</td><td>{"id":5}</td></tr><tr><td>Eq</td><td>idEq</td><td>5</td><td>id = 5</td><td>{"idEq":5}</td></tr><tr><td>Not</td><td>idNot</td><td>5</td><td>id != 5</td><td>{"idNot":{"$ne":5}}</td></tr><tr><td>Ne</td><td>idNe</td><td>5</td><td>id &#x3C;> 5</td><td>{"idNe":{"$ne":5}}</td></tr><tr><td>Gt</td><td>idGt</td><td>5</td><td>id > 5</td><td>{"idGt":{"$gt":5}}</td></tr><tr><td>Ge</td><td>idGe</td><td>5</td><td>id >= 5</td><td>{"idGe":{"$gte":5}}</td></tr><tr><td>Lt</td><td>idLt</td><td>5</td><td>id &#x3C; 5</td><td>{"idLt":{"$lt":5}}</td></tr><tr><td>Le</td><td>idLe</td><td>5</td><td>id &#x3C;= 5</td><td>{"idLe":{"$lte":5}}</td></tr><tr><td>NotIn</td><td>idNotIn</td><td>[1,2,3]</td><td>id NOT IN (1,2,3)</td><td>{"id":{"$nin":[1, 2, 3]}}</td></tr><tr><td>In</td><td>idIn</td><td>[1,2,3]</td><td>id IN (1,2,3)</td><td>{"id":{"$in":[1, 2, 3]}}</td></tr><tr><td>Null</td><td>memoNull</td><td>true</td><td>memo IS NULL</td><td>{"memo":{"$type", 10}}</td></tr><tr><td>Null</td><td>memoNull</td><td>false</td><td>memo IS NOT NULL</td><td>{"memo":{"$not":{"$type", 10}}}</td></tr><tr><td>NotLike</td><td>nameNotLike</td><td>"arg"</td><td>name NOT LIKE '%arg%'</td><td>{"name":{"$not":{"$regex":"arg"}}}</td></tr><tr><td>Like</td><td>nameLike</td><td>"arg"</td><td>name LIKE '%arg%'</td><td>{"name":{"$regex":"arg"}}</td></tr><tr><td>NotStart</td><td>nameNotStart</td><td>"arg"</td><td>name NOT LIKE 'arg%'</td><td>{"name":{"$not":{"$regex":"^arg"}}}</td></tr><tr><td>Start</td><td>nameStart</td><td>"arg"</td><td>name LIKE 'arg%'</td><td>{"name":{"$regex":"^arg"}}</td></tr><tr><td>NotEnd</td><td>nameNotEnd</td><td>"arg"</td><td>name NOT LIKE '%arg'</td><td>{"name":{"$not":{"$regex":"arg$"}}}</td></tr><tr><td>End</td><td>nameEnd</td><td>"arg"</td><td>name LIKE '%arg'</td><td>{"name":{"$regex":"arg$"}}</td></tr><tr><td>NotContain</td><td>nameNotContain</td><td>"arg"</td><td>name NOT LIKE '%arg%’</td><td>{"name":{"$not":{"$regex":"arg"}}}</td></tr><tr><td>Contain</td><td>nameContain</td><td>"arg"</td><td>name LIKE '%arg%’</td><td>{"name":{"$regex":"arg"}}</td></tr><tr><td>Rx</td><td>nameRx</td><td>"arg\d"</td><td>name REGEXP 'arg\d’</td><td>{"name":{"$regex":"arg\d"}}</td></tr></tbody></table>


# Logic-Suffix Field

By default, the query conditions corresponding to each field in a query object are connected with the **AND** operator.\
If you want to define query conditions connected with the **OR** operator, you need to define the suffix **`Or`** in the field name, and the following three field types are supported:

```java
public class UserQuery extends PageQuery {
    // ...
    private List<String> nameStartOr;
    private UserQuery userOr;
    private List<UserQuery> usersOr;
}
```

### List\<String> nameStartOr;

```java
UserQuery userQuery = UserQuery.builder().nameStartOr(List.of("Bob","John","Tim")).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (name LIKE ? OR name LIKE ? OR name LIKE ?)" args="[Bob% John% Tim%]"
```

### UserQuery userOr;

```java
UserQuery userQuery = UserQuery.builder().nameStartOr(Arrays.asList(1, 4, 12)).deleted(trur).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (id IN (?, ?, ?) OR deleted = ?)" args="[1 4 12 true]"
```

### List\<UserQuery> usersOr;

```java
UserQuery userQuery = UserQuery.builder()
    .usersOr(List.of(
        UserQuery.builder().idIn(List.of(1L, 4L, 12L)).deleted(true).build(),
        UserQuery.builder().idGt(10L).deleted(false).build()
    )).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user
// WHERE (id IN (?, ?, ?) AND deleted = ? OR id > ? AND deleted = ?)"
// args="[1 4 12 true 10 false]"
```

## And Suffix

When a field name ends with **`And`**, the logical operator connecting multiple query conditions is **AND**.

```java
public class UserQuery extends PageQuery {
    // ...
    private UserQuery userOr;
    private UserQuery userAnd;
}
```

### UserAnd \*UserQuery

```java
UserQuery userAnd = UserQuery.builder().idIn(List.of(1L, 4L, 12L)).deleted(false).build();
UserQuery userOr = UserQuery.builder().deleted(true).userAnd(userAnd).build();
UserQuery userQuery = UserQuery.builder().scoreLt(80).userOr(userOr).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user
// WHERE score < ? AND (deleted = ? OR id IN (?, ?, ?) AND deleted = ?)"
// args="[80 true 1 4 12 false]"
```

### Related Article

[How to express `select * from user where id = ? or name = ? and age = ?` in GoooQo](https://blog.doyto.win/post/goooqo-or-clause-en/)


# Subquery Field

For typical subquery conditions, e.g., `score > (SELECT avg(score) FROM t_user WHERE removed = ?)`, is divided into three parts for separate mapping:

* score >
* SELECT avg(score) FROM t\_user
* WHERE clause

The first part, score >, can be mapped using a field name like scoreGtXxx. The string defined after the predicate suffix is only used to distinguish duplicate field names and is ignored during mapping.

The second part contains a column name and a table name, which are static and unchanging. DoytoQuery provides two annotations to store this information:

* `@Subquery` defines the column and table for the subquery statement;
* `@SubqueryV2` defines a view object, which is used in combination with field values to generate the subquery statement.

The third part is another WHERE clause, which can be mapped through a Query Object. Therefore, we define the field type as the corresponding Query Object and use the Query Object’s mapping method to map field values into the subquery’s WHERE clause.

## Example

**Annotation `@Subquery`：**

```java
@SuperBuilder
@NoArgsConstructor
public class UserQuery extends PageQuery {
    // ...
    
    @Subquery(select = "avg(score)", from = UserEntity.class)
    private UserQuery scoreLtAvg;

    @Subquery(select = "score", from = UserEntity.class)
    private UserQuery scoreLtAny;

    @Subquery(select = "score", from = UserEntity.class)
    private UserQuery scoreLtAll;

    @Subquery(select = "avg(score)", from = UserEntity.class)
    private UserQuery scoreGtAvg;
}
```

**Annotation `@SubqueryV2`：**

```java
@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class MinimumCostSupplierQuery extends PageQuery {
    private Integer p_size;
    private String p_typeEnd;
    private String r_name;
    
    // Subquery comdition: ps_supplycost = SELECT min(ps_supplycost) FROM partsupp, supplier, nation, region WHERE ...
    @SubqueryV2(MinSupplyCostView.class)
    private SupplyCostQuery psSupplycost;

    @View(value = PartEntity.class, context = true)
    @View(PartsuppEntity.class)
    @View(SupplierEntity.class)
    @View(NationEntity.class)
    @View(RegionEntity.class)
    private static class MinSupplyCostView {
        @NoLabel
        private Integer minPs_supplycost;
    }
}
```


# E-R Query Field

### Abstract Entity Path

In an entity-relationship diagram, a many-to-many relationship is used to represent the association between two entities. Many-to-many relationships are transitive. For example, if entity A has a many-to-many relationship with entity B, and entity B has a many-to-many relationship with entity C, then entities A and C also have a many-to-many relationship, which is an indirect many-to-many relationship.

Based on the transitivity of many-to-many relationships, the concept of an **Abstract Entity Path** is proposed to describe this direct or indirect many-to-many relationship between entities. An abstract entity path describes the many-to-many relationship between any two entities by considering all entities from one entity to another as nodes. For example, the abstract entity path between entity A and entity B is \[A, B]; the path between entity B and entity A is \[B, A]; and the path between entity C and entity A is \[C, B, A]. The abstract entity path contains all the information about the relationship between any two entities, thereby enabling the dynamic generation of complex nested query statements.

DoytoQuery introduces the concept of an **Abstract Entity Path** and defines the annotation `@DomainPath` to describe relationships between entities. This tag is used for fields in query objects that are intended to query entity relationships. For example, the entity path `` `entitypath:"user,role"` `` can, based on a predefined table name format, yield two entity table names `t_user` and `t_role`, an intermediate table name `a_user_and_role`, and two foreign key names `user_id` and `role_id`. This information is then used to generate a query statement:

```sql
SELECT * FROM t_user WHERE
id IN (
    SELECT user_id FROM a_user_and_role WHERE role_id IN (
       SELECT id FROM t_role WHERE ...
    )
)
```

### Example

The table `t_menu` has a column `parent_id` that references the `id` column itself as a foreign key. The `parent_id` column is used to define hierarchical parent-child relationships between menu items. Menus are assigned to users as system resources through a general RBAC model. Therefore, the entity path from a menu to a user is: `menu, perm, role, user`, which is used to generate nested query statements.

## Nested Queries

DoytoQuery generates nested query statements for fields by parsing the `@DomainPath` annotation configured on the fields.

The annotation is defined as follows:

{% code title="DomainPath.java" %}

```java
@Target(FIELD)
@Retention(RUNTIME)
public @interface DomainPath {
    /**
     * To describe how to route from the host domain to the target domain.
     *
     * @return paths array
     */
    String[] value();

    String localAlias() default  "t";

    /**
     * The field in this domain to maintain the relationship with the target domain.
     *
     * @return name of the local field
     */
    String localField() default "id";

    /**
     * The field in another domain to maintain the relationship with this domain.
     *
     * @return name of the foreign field
     */
    String foreignField() default "id";

    String foreignAlias() default "t1";
}
```

{% endcode %}

#### Simple Nested Queries

Assume a hierarchical menu table where a child menu's `parent_id` points to the parent menu's `id`. Using this, all parent menus can be queried with the following statement:

```sql
SELECT * FROM menu WHERE id IN (SELECT parent_id FROM menu)
```

To execute this query via DoytoQuery, you need to create the corresponding `MenuQuery` class and add a field for the query, configured with the `@NestedQueries` annotation:

```java

@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class MenuQuery extends PageQuery {
    // many-to-one
    @DomainPath(value = "menu", localField = "parentId")
    private MenuQuery parent;

    // one-to-many
    @DomainPath(value = "menu", foreignField = "parentId")
    private MenuQuery children;

    @DomainPath({"menu", "perm", "role", "user"})
    private UserQuery user;

    private String nameLike;
    private Boolean valid;
}

```


# Custom Condition Field

## Usage Scenario

When encountering SQL statements that cannot be supported by existing field mapping methods, the annotation `@QueryField` can be used to define custom SQL statements as a temporary solution.

## Annotation Definition

{% code title="QueryField.java" %}

```java
@Target(FIELD)
@Retention(RUNTIME)
public @interface QueryField {
    String and();
}
```

{% endcode %}

{% hint style="info" %}
When the value of the annotated field satisfies the filter condition, the conditional statement defined in the `and` variable will be appended to the SQL statement.
{% endhint %}

## Code Example

**Business Code:**

```java
public class TestQuery extends PageQuery {
    @QueryField(and = "(username = ? OR email = ? OR mobile = ?)")
    private String account;
}
```

**Unit Test:**

```java
@Test
void testQueryField() {
    TestQuery testQuery = TestQuery.builder().account("test").build();
    ArrayList<Object> argList = new ArrayList<>();

    String sql = BuildHelper.buildWhere(testQuery, argList);

    assertThat(sql).isEqualTo(" WHERE (username = ? OR email = ? OR mobile = ?)");
    assertThat(argList).containsExactly("test", "test", "test");
}
```

The query condition defined by the `@QueryField` annotation on the `account` field of `TestQuery` is appended verbatim to the WHERE clause. Because the query condition contains three placeholders, the value "test" of `account` is also added to the `argList` three times.


# Entity Object

Entity objects are used to provide table names and column names for constructing CRUD statements. They need to implement the `Persistable` interface or inherit base classes such as `AbstractPersistable`, `AbstractEntity`, or `AbstractCommonEntity`.

## Example

```java
@Getter
@Setter
public class UserEntity extends AbstractCommonEntity<Long, Long> {
    private String name;
    private Integer score;
    private String memo;
}
```

In the example, the CRUD statements corresponding to `UserEntity` are:

```sql
SELECT id, name, score, memo FROM t_user；
INSERT INTO t_user (name, score, memo) VALUES (?, ?, ?)
UPDATE t_user SET name = ?, score = ?, memo = ? WHERE id = ?;
DELETE FROM t_user WHERE id = ?;
```


# Enum Column


# Foreign Key


# Sharding

## Components

* IdWrapper
* AbstractDynamicService


# Patch Object

DoytoQuery supports **incremental updates** on numeric fields using special suffixes in the patch object.;

## Suffix Mapping

For example, to increase a user's score by a certain amount without first reading the original value, you can use a field like `scoreAe` (short for *Add/Extend*).

```java
public class UserPatch extends UserEntity {
    private Integer scoreAe;
}
```

```java
UserEntity userPatch = UserPatch.builder().id(1).valid(true).scoreAe(20).build();
userDataAccess.patch(userPatch);
// SQL: UPDATE t_user SET valid = ?, score = score + ? WHERE id = ?
```

## Annotation Mapping

Use `@Clause` to define a custom condition:

```java
public class UserPatch extends UserEntity {
    @Clause("score = score + ?")
    private Integer scoreAe;
}
```


# View Object

Complex queries often involve joins across multiple tables and aggregation operations on columns. While query objects can be reused to construct the `WHERE` clause, entity objects cannot fully express the other parts of a query. Therefore, we introduce a dedicated **view object** to define the static parts of a complex query, along with a separate **Having object** to generate the `HAVING` clause. Additionally, the view object serves as the target for result mapping.

To achieve automatic mapping for complex queries, we divide the mapping process into the following three parts:

1. **Column Mapping**: The fields defined in the view object are used to map the required columns in the query results, including both regular columns and aggregated columns.
2. **Join Mapping**: Annotations within the view object define the relationships between tables, supporting the automatic generation of necessary join statements.
3. **HAVING Clause Mapping**: The Having object is used to generate the `HAVING` clause, supporting filtering conditions based on aggregated results.

This design not only improves developers' efficiency in constructing and maintaining complex query statements but also reduces the risks associated with manually assembling SQL. It significantly enhances the readability, reusability, and maintainability of the code.

## Aggregate Query Interface

In addition to designing interfaces for single-table data access, a separate interface is required for aggregate queries.

The interface `AggregateClient` has been designed and implemented, defined as follows: This interface provides a general-purpose method `query`, allowing developers to perform aggregate queries by specifying the target view class and a query object, with the results mapped to a list of the corresponding view objects.

```java
public interface AggregateClient {
    <V> AggregateChain<V> aggregate(Class<V> viewClass);

    default <V> List<V> query(Class<V> viewClass, DoytoQuery query) {
        return aggregate(viewClass).filter(query).query();
    }

    default <V> long count(Class<V> viewClass, DoytoQuery query) {
        return aggregate(viewClass).filter(query).count();
    }

    default <V> PageList<V> page(Class<V> viewClass, DoytoQuery query) {
        return aggregate(viewClass).filter(query).page();
    }
}
```


# Column Mapping

Fields in a view object are not only used to map columns from different tables but also to represent aggregate expressions and grouping clauses.

## Aggregate Column Mapping

Applying aggregate functions is a core feature of aggregate queries. All data queried through aggregate columns must be stored in the fields defined in the view class. Therefore, we need to map field names to aggregate columns, and using prefix mapping is an excellent approach.

| Prefix      | Aggregate Function Name | Field Name      | Aggregate Column Expression            |
| ----------- | ----------------------- | --------------- | -------------------------------------- |
| sum         | sum                     | sumScore        | sum(score) AS sumScore                 |
| max         | max                     | maxScore        | max(score) AS maxScore                 |
| min         | min                     | minScore        | min(score) AS minScore                 |
| avg         | avg                     | avgScore        | avg(score) AS avgScore                 |
| first       | first                   | firstScore      | first(score) AS firstScore             |
| last        | last                    | lastScore       | last(score) AS lastScore               |
| stdDevPop   | stddev\_pop             | stdDevPopScore  | stddev\_pop(score) AS stdDevPopScore   |
| stdDevSamp  | stddev\_samp            | stdDevSampScore | stddev\_samp(score) AS stdDevSampScore |
| stdDev      | stddev                  | stdDev          | stddev(score) AS stdDev                |
| addToSet    | addToSet                | addToSetScore   | addToSet(score) AS addToSetScore       |
| push        | push                    | pushScore       | push(score) AS pushScore               |
| count       | count                   | countScore      | count(score) AS countScore             |
| count       | count                   | count           | count(\*) AS count                     |
| **Example** |                         |                 |                                        |

If you want to calculate the average value of a column named `score`, you can use the `avg` function. Define the field name as `avgScore`, following the naming convention of "aggregate prefix + column name". It will finally be mapped to `avg(score) AS avgScore`.

## Aggregate Expression Mapping

For aggregate expressions that cannot be directly represented by a field name, you can specify the specific expression through annotations, mapping the expression to a column name, with the field name used as a label.

**Example**

Add an expression annotation when defining a field:

```java
@Column(name = "sum(l_extendedprice*(1-l_discount))")
private BigDecimal sum_disc_price;
```

This field is mapped to:

```sql
sum(l_extendedprice*(1-l_discount)) AS sum_disc_price
```

**Complete Example**

Refer to the following table for a **complete example** :

| LN | Class Definition                                             | SQL Clauses                                            |
| -- | ------------------------------------------------------------ | ------------------------------------------------------ |
| 1  | @View(CustomerEntity.class)                                  | SELECT                                                 |
| 2  | @View(OrdersEntity.class)                                    |                                                        |
| 3  | @View(LineitemEntity.class)                                  |                                                        |
| 4  | public class ShippingPriorityView {                          |                                                        |
| 5  | @GroupBy                                                     |                                                        |
| 6  | private String l\_orderkey;                                  | l\_orderkey,                                           |
| 7  | @Column(name = "SUM(l\_extendedprice \* (1 - l\_discount))") | SUM(l\_extendedprice \* (1 - l\_discount)) AS revenue, |
| 8  | private Double revenue;                                      |                                                        |
| 9  | @GroupBy                                                     |                                                        |
| 10 | private Date o\_orderdate;                                   | o\_orderdate,                                          |
| 11 | @GroupBy                                                     |                                                        |
| 12 | private String o\_shippriority;                              | o\_shippriority                                        |
| 13 | }                                                            | FROM customer, orders, lineitem                        |
|    | // @View(CustomerEntity.class)                               | WHERE o\_custkey = c\_custkey                          |
|    | // @View(OrdersEntity.class)                                 | AND l\_orderkey = o\_orderkey                          |
|    | // @View(LineitemEntity.class)                               |                                                        |
| 1  | public class ShippingPriorityQuery extends PageQuery {       |                                                        |
| 2  | private String c\_mktsegment;                                | AND c\_mktsegment = ?                                  |
| 3  | private Date o\_orderdateLt;                                 | AND o\_orderdate < ?                                   |
| 4  | private Date l\_shipdateGt;                                  | AND l\_shipdate > ?                                    |
| 5  | }                                                            | GROUP BY l\_orderkey, o\_orderdate, o\_shippriority    |
|    | // PageQuery.sort = "revenue,DESC;o\_orderdate"              | ORDER BY revenue DESC, o\_orderdate                    |


# Group By Mapping

For the `GROUP BY` clause, grouping fields typically also need to appear in the returned columns. This can be achieved by adding an annotation to the field in the view class to declare it as a grouping column, which will then be appended to the `GROUP BY` clause during mapping.

**Example**

When defining a view object, add the `@GroupBy` annotation to the fields used for grouping:

```java
@GroupBy
private int returnFlag;

@GroupBy
private int lineStatus;
```

Fields marked with the `@GroupBy` annotation are mapped as:

```sql
SELECT return_flag, line_status, ... FROM ...
GROUP BY return_flag, line_status ...
```


# Inner Join

Taking the third query "Shipping Priority Query" from the TPC-H benchmark as an example, this section demonstrates how to map table joins. The query statement is as follows:

```sql
SELECT l_orderkey, SUM(l_extendedprice * (1 - l_discount)) AS revenue, o_orderdate, o_shippriority
FROM customer, orders, lineitem
WHERE o_custkey = c_custkey
  AND l_orderkey = o_orderkey
  AND c_mktsegment = ?
  AND o_orderdate < ?
  AND l_shipdate > ?
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY revenue DESC, o_orderdate
```

This is a typical multi-table cross-join, requiring handling of the following two parts:

1. **Multiple table names**: `FROM customer, orders, lineitem`
2. **Join conditions**: `WHERE o_custkey = c_custkey AND l_orderkey = o_orderkey`

### Multiple Table Name Mapping

**Definition**\
Configure the joined entities through the annotations `@ComplexView` and `@View` on the view class:

```java
@Target(TYPE)
@Retention(RUNTIME)
public @interface ComplexView {
    View[] value();
}

@Target(TYPE)
@Retention(RUNTIME)
@Repeatable(ComplexView.class)
public @interface View {
    Class<?> value();
    String alias() default "";
    ViewType type() default ViewType.TABLE_NAME;
}
```

**Configuration**\
For the shipping priority query, define the following view class:

```java
@View(CustomerEntity.class)
@View(OrdersEntity.class)
@View(LineitemEntity.class)
public class ShippingPriorityView { /*...*/ }
```

**Parsing**\
Through annotation configuration, the table names `customer, orders, lineitem` are automatically obtained.

### Join Condition Mapping

**Definition**\
Configure the foreign key fields of an entity through the annotation `@ForeignKey` to determine the relationships between entities:

```java
@Target(FIELD)
@Retention(RUNTIME)
public @interface ForeignKey {
    Class<?> entity();
    String field();
}
```

**Example**\
Configure the foreign key field in `OrdersEntity`:

```java
public class OrdersEntity extends AbstractPersistable<Long> {
  private String o_orderkey;
  @ForeignKey(entity = CustomerEntity.class, field = "c_custkey")
  private String o_custkey;
  //...
}
```

**Parsing**\
When a view class is configured with the `@View` annotation, other joined entities are scanned. If a foreign key corresponding to another entity is found, the join condition is added.

In `ShippingPriorityView`, it is found that `o_custkey` in `OrdersEntity` corresponds to `c_custkey` in `CustomerEntity`, so the join condition `o_custkey = c_custkey` is added. Similarly, `l_orderkey = o_orderkey` is added.

The processed SQL portion is:

```sql
FROM  customer, orders, lineitem
WHERE o_custkey = c_custkey
AND   l_orderkey = o_orderkey
```


# Outer Join

Connections implemented through the `JOIN/ON` keywords uniformly use `@Join` for mapping.

This type of connection adds dynamic query conditions after the `ON` keyword. To achieve this, a new query object is introduced to map the conditions of the `ON` clause, and it is defined as a field of the main query object.

For this purpose, a new annotation `@Join` is designed to map the joined tables and specify the join type, while reusing the existing `@ForeignKey` annotation to map the join conditions.

| LN | Class Definition                                               | SQL Clauses                    |
| -- | -------------------------------------------------------------- | ------------------------------ |
| 1  | public class CustomerOrdersQuery extends PageQuery {           |                                |
| 2  | @Join(from = @View(value = CustomerEntity.class, alias = "c"), | FROM customer c                |
| 3  | type = Join.JoinType.LEFT\_JOIN,                               | LEFT JOIN orders o             |
| 4  | join = @View(value = OrdersEntity.class, alias = "o"))         | ON o.o\_custkey = c.c\_custkey |
| 5  | private JoinOrders joinOrders;                                 | AND o.o\_comment NOT LIKE ?    |
| 6  | }                                                              |                                |


# Having Object

The `HAVING` clause in SQL is used to filter grouped records after aggregation. The `HAVING` clause is optional, and its syntax is similar to that of the `WHERE` clause.

The mapping of the `HAVING` clause is implemented through a **Having object**.

The mapping process reuses the query object mapping algorithm, supporting both prefix mapping and suffix mapping for field mapping. For example, defining a field `avgScoreGe` in a Having object will be mapped to:

```sql
HAVING avg(score) >= ?
```

In Java, a Having object implements an empty interface `Having` to mark that it is used for constructing the HAVING clause. Meanwhile, the Having object can extend a query object, and the query object can extend `PageQuery`, forming a three-level structure.

* Fields in `PageQuery` are used to construct the `ORDER BY` and pagination clauses;
* Fields in the query object are used to construct the `WHERE` clause;
* Fields in the Having object are used to construct the `HAVING` clause.


# CRUD

## The Definition of DataAccess Interface

`DataAccess` interface provides methods for accessing the database.

```java
public interface DataAccess<E extends Persistable<I>, I extends Serializable, Q extends DoytoQuery> {
    List<E> query(Q query);
    long count(Q query);
    PageList<E> page(Q query);
    <V> List<V> queryColumns(Q q, Class<V> clazz, String... columns);
    List<I> queryIds(Q query);

    default E get(I id) {
        return get(IdWrapper.build(id));
    }
    E get(IdWrapper<I> w);

    default int delete(I id) {
        return delete(IdWrapper.build(id));
    }
    int delete(IdWrapper<I> w);
    int delete(Q query);

    void create(E e);
    default int batchInsert(Iterable<E> entities, String... columns) {
        int count = 0;
        for (E entity : entities) {
            create(entity);
            count++;
        }
        return count;
    }

    int update(E e);
    int patch(E e);
    int patch(E e, Q q);
}
```

The `DataAccess` interface contains methods that accept only four categories of parameters in total:

* `id` - the primary key of the entity;
* `Entity` - an entity object, used to map to a table name and column names;
* `Query` - a query object, used to dynamically construct query conditions and pagination statements. It needs to extend `PageQuery`.
* `IdWrapper` - a sharding primary key object, used for sharded table queries;

For the definition of `Entity`, refer to:

{% content-ref url="/pages/GaDLHXM19HrkGl2ZoOrk" %}
[Entity Object](/object-concepts/entity-object)
{% endcontent-ref %}

For the definition of `Query`, refer to:

{% content-ref url="/pages/IuVKOkT7zJkJz8UIasWH" %}
[Query Object](/object-concepts/query-object)
{% endcontent-ref %}

## Example

The following interface calls are demonstrated based on the entity object `UserEntity` and the query object `UserQuery`:

```java
@Getter
@Setter
public class UserEntity extends AbstractCommonEntity<Long, Long> {
    @NotNull(groups = CreateGroup.class)
    private String name;
    private Integer score;
    private String memo;
    private Boolean deleted;
}

@Getter
@Setter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class UserQuery extends PageQuery {
    private Long idGt;
    private List<Long> idIn;
    private Integer scoreLt;
    private Boolean memoNull;
    private String memoLike;
    private Boolean deleted;
    private List<UserQuery> userOr;

    @QueryField(and = "(username = ? OR email = ?)")
    private String account;

    @Subquery(select = "avg(score)", from = UserEntity.class)
    private UserQuery scoreLtAvg;

    @Subquery(select = "score", from = UserEntity.class)
    private UserQuery scoreLtAny;

    @Subquery(select = "score", from = UserEntity.class)
    private UserQuery scoreLtAll;

    @Subquery(select = "avg(score)", from = UserEntity.class)
    private UserQuery scoreGtAvg;
}

@Bean
public JdbcDataAccess<UserEntity, Long, UserQuery>
userDataAccess(@Autowired DatabaseOperations databaseOperations) {
    return new JdbcDataAccess<>(databaseOperations, UserEntity.class);
}
```

### Get

Query data by id:

```java
UserEntity userEntity = userDataAccess.get(3L);
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE id = ?" args="[3]"
```

### Query

Query data by query conditions:

```java
// 示例 1
UserQuery userQuery = UserQuery.builder().scoreLt(80).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE score < ?" args="[80]"

// 示例 2
UserQuery userQuery = UserQuery.builder().memoLike("Great").pageSize(20).sort("id,desc;score").build();
List<UserEntity> users = userDataAccess.query(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]"

// 示例 3
UserQuery userQuery = UserQuery.builder().idIn(List.of(1L, 4L, 12L)).deleted(true).build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE id IN (?, ?, ?) AND deleted = ?" args="[1 4 12 true]"

// 示例 4
UserQuery userQuery = UserQuery.builder()
    .userOr(List.of(
        UserQuery.builder().idGt(10L).memoNull(true).build(),
        UserQuery.builder().scoreLt(80).memoLike("Good").build()
    ))
    .build();
List<UserEntity> users = userDataAccess.query(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]"

// 示例 5
UserQuery userQuery = UserQuery.builder()
    .scoreGtAvg(UserQuery.builder().deleted(true).build())
    .scoreLtAny(UserQuery.builder().build())
    .build();
List<UserEntity> users = userDataAccess.query(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]"

// 示例 6
UserQuery userQuery = UserQuery.builder().account("John").build();
List<UserEntity> users = userDataAccess.query(userQuery);
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (username = ? OR email = ?)" args="[John John]"
```

### Count

Query the total number of data based on the query conditions:

```java
UserQuery userQuery = UserQuery.builder().scoreLt(60).build();
long count = userDataAccess.count(userQuery);
// SQL="SELECT count(0) FROM t_user WHERE score < ?" args="[60]"
```

### Page

Paging based on the query conditions:

```java
UserQuery userQuery = UserQuery.builder().scoreLt(80).pageSize(20).build();
PageList<UserEntity> page = userDataAccess.page(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

Delete data by id:

```java
int deletedCount = userDataAccess.delete(3L);
// SQL="DELETE FROM t_user WHERE id = ?" args="[3]"
```

### DeleteByQuery

Delete data by query conditions:

```java
UserQuery userQuery = UserQuery.builder().scoreLt(80).build();
int deletedCount = userDataAccess.delete(userQuery);
// SQL="DELETE FROM t_user WHERE score < ?" args="[80]"
```

### Create

Create one record:

```java
UserEntity user = new UserEntity();
user.setName("John");
user.setScore(90);
user.setDeleted(false);
userDataAccess.create(user);
// SQL="INSERT INTO t_user (name, score, memo, deleted) VALUES (?, ?, ?, ?)" args="[John, 90, null, false]"
```

### CreateMulti

Create multiple records:

```java
UserEntity user1 = new UserEntity();
user1.setName("John");
user1.setScore(90);
user1.setMemo("Great");
user1.setDeleted(false);
UserEntity user2 = new UserEntity();
user2.setName("Alex");
user2.setScore(55);
List<UserEntity> entities = List.of(user1, user2);
int createdCount = userDataAccess.batchInsert(entities);
// SQL="INSERT INTO t_user (name, score, memo, deleted) VALUES (?, ?, ?, ?), (?, ?, ?, ?)" args="[John, 90, Great, false, Alex, 55, null, null]"
```

### Update

Update all columns by id:

```java
UserEntity user = new UserEntity();
user.setId(2L);
user.setScore(90);
user.setMemo("Great");
int updatedCount = userDataAccess.update(user);
// SQL="UPDATE t_user SET score = ?, memo = ? WHERE id = ?" args="[90 Great 2]"
```

### Patch

Update non-null columns by id:

```java
UserEntity user = new UserEntity();
user.setId(2L);
user.setScore(90);
int patchedCount = userDataAccess.patch(user);
// SQL="UPDATE t_user SET score = ? WHERE id = ?" args="[90 2]"
```

### PatchByQuery

Update non-null columns by query conditions:

```java
UserEntity user = new UserEntity();
user.setMemo("Add Memo");
UserQuery query = UserQuery.builder().memoNull(true).build();
int patchedCount = userDataAccess.patch(user, query);
// SQL="UPDATE t_user SET memo = ? WHERE memo IS NULL" args="[Add Memo]"
```


# Intermediate Table

### Table Structure

```sql
create table t_user_and_role (user_id bigint, role_id int);
```

### Bead Declaration

```java
@Bean
public AssociativeService<Long, Integer> userAndRoleAssociativeService() {
    return new TemplateAssociativeService<>("t_user_and_role", "userId", "roleId");
}
```

### Usage

Create a web api to invoke the `userAndRoleAssociativeService`:

```java
@RestController
class AuthController {
    @Resource
    AssociativeService<Long, Integer> userAndRoleAssociativeService;

    @GetPostMapping("reallocateRolesForUser")
    public void reallocateRoles(Long userId, @RequestParam List<Integer> roleIds) {
        userAndRoleAssociativeService.reallocateForLeft(userId, roleIds);
    }
}
```

### Access

Invoking this api will execute the following SQL statement

```sql
DELETE FROM t_user_and_role WHERE userId = ?;
INSERT INTO t_user_and_role (userId, roleId) values (?, ?)[, (?, ?)];
```


# Complex Query

`AggregateClient` is used to execute complex queries.

## Example

```java
@AllArgsConstructor
@JsonBody
@RestController
public class UserAggregateController {

    private AggregateClient aggregateClient;

    @GetMapping("user/queryCountOfEachLevel")
    public List<UserLevelCountView> queryCountOfEachLevel(UserLevelHaving query) {
        return aggregateClient.query(UserLevelCountView.class, query);
    }

}
```

Refer [View Object](/object-concepts/view-object) to learn how to define a view object.


# Pessimistic Lock


# Optimistic Lock

Make the entity object implement the `OptimisticLock` interface to support optimistic locking.

```java
public interface OptimisticLock {
    Integer currentVersion();
}
```

## Usage

```java

public class TestEntity extends AbstractPersistable<Integer> implements OptimisticLock {
    // ...

    private Integer version;

    @Column(name = "version")
    @Override
    public Integer currentVersion() {
        return version;
    }

}
```


# Configuration

### WebMvcConfigurerAdapter

Let the main application extend `WebMvcConfigurerAdapter` to obtain the default configuration, or use it as a reference:

```java
import org.springframework.boot.autoconfigure.SpringBootApplication;
import win.doyto.query.web.WebMvcConfigurerAdapter;

@SpringBootApplication
public class DemoApplication extends WebMvcConfigurerAdapter {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class);
    }
}
```


# Controller

### AbstractEIQController\<E, I, Q>

#### Example

```java
package win.doyto.query.web.demo.module.role;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import win.doyto.query.test.role.RoleEntity;
import win.doyto.query.test.role.RoleQuery;
import win.doyto.query.web.controller.AbstractEIQController;

@RestController
@RequestMapping("role")
public class RoleController extends AbstractEIQController<RoleEntity, Integer, RoleQuery> {
}

```

### AbstractRestController\<E, I, Q, R, S>

### AbstractDynamicController\<E, I, Q, R, S, W>

`AbstractDynamicController` is for table sharding.

#### Example

```java
package win.doyto.query.web.demo.module.menu;

import com.fasterxml.jackson.core.type.TypeReference;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import win.doyto.query.web.controller.AbstractDynamicController;
import win.doyto.query.web.response.JsonBody;

@JsonBody
@RestController
@RequestMapping("{platform}/menu")
class MenuController extends AbstractDynamicController<MenuEntity, Integer, MenuQuery, MenuRequest, MenuResponse, MenuIdWrapper> {

    public MenuController(MenuService menuService) {
        super(menuService, new TypeReference<>() {});
    }

}
```

With a dynamic service `MenuService`:

```java
@Service
public class MenuService extends AbstractDynamicService<MenuEntity, Integer, MenuQuery> {

}
```


# Service


# Cache

There are two ways to enable caching for target entities.

## Configuration

Specify their class names using the `doyto.query.caches` property in the `application.yaml` file:

```yaml
doyto:
  query:
    caches: UserEntity, MenuEntity
```

## Programmatic

Override method `getCacheName()` in `AbstractDynamicService`:

```java
public class UserService extends AbstractCrudService<UserEntity, Integer, UserQuery> {
    @Override
    protected String getCacheName() {
        return "module:user"; // return any string except UserEntity 
    }
}
```


# Sorting

```yaml
doyto.query.config:
  sort-fields-map:
    win.doyto.query.test.TestQuery:
      - id
      - username
      - userLevel
```

| Query String                            | ORDER BY clause                         | Memo                                             |
| --------------------------------------- | --------------------------------------- | ------------------------------------------------ |
| ?sort=id,desc                           | ORDER BY id DESC                        |                                                  |
| ?sort.id=asc\&sort=id,desc              | ORDER BY id ASC                         | `sort` will be ignored since `sort.id` is passed |
| ?sort.username=asc\&sort.userLevel=desc | ORDER BY username ASC, user\_level DESC |                                                  |


# Validation


# User ID injection

AbstractEntity

```java
public interface CreateUserAware<I extends Serializable> {

    void setCreateUserId(I createUser);

}
```

```java
public interface UpdateUserAware<I extends Serializable> {

    void setUpdateUserId(I updateUser);

}
```


# Name Mapping

Automatically maps camelCase field names to snake\_case column names：

```yml
doyto:
  query:
    config:
      map-camel-case-to-underscore: true
```


# Dialect

### Add dependency

```xml
<dependency>
    <groupId>win.doyto</groupId>
    <artifactId>doyto-query-dialect</artifactId>
    <version>${doyto-query.version}</version>
</dependency>
```

### Configuration

#### File Configuration

```yml
doyto:
  query:
    config:
      dialect: win.doyto.query.dialect.PostgreSQLDialect
```

#### Static Method Configuration

```java
GlobalConfiguration.instance().setDialect(new HSQLDBDialect());
```

### Supported Databases

| Database   | Dialect                                   |
| ---------- | ----------------------------------------- |
| HSQLDB     | win.doyto.query.dialect.HSQLDBDialect     |
| MySQL 5    | win.doyto.query.dialect.MySQLDialect      |
| MySQL 8    | win.doyto.query.dialect.MySQL8Dialect     |
| Oracle     | win.doyto.query.dialect.OracleDialect     |
| PostgreSQL | win.doyto.query.dialect.PostgreSQLDialect |
| SQL Server | win.doyto.query.dialect.SQLServerDialect  |
| SQLite     | win.doyto.query.dialect.SQLiteDialect     |

### Interface Design

{% code title="Dialect.java" %}

```java
package win.doyto.query.core;

public interface Dialect {
    String buildPageSql(String sql, int limit, long offset);
    default String wrapLabel(String fieldName) {
        return fieldName;
    }
    // Other methods..
}
```

{% endcode %}


# SQL Logging

To view the executed SQL statements, simply configure the log level of `win.doyto.query.core.SqlAndArgs` to debug.

Configure as follows in a Spring YAML file:

{% code title="application.yml" %}

```yaml
logging:
  level:
    win.doyto.query.core.SqlAndArgs: debug
```

{% endcode %}

Log output is as follows:

```
...
2021-02-25 22:17:18.442 DEBUG 80237 --- [           main] win.doyto.query.core.SqlAndArgs          : SQL  : SELECT platform, parentId, menuName, memo, valid, id, createUserId, createTime, updateUserId, updateTime FROM menu WHERE id = ?
2021-02-25 22:17:18.442 DEBUG 80237 --- [           main] win.doyto.query.core.SqlAndArgs          : Param: 3(java.lang.Integer)
2021-02-25 22:17:18.447 DEBUG 80237 --- [           main] win.doyto.query.core.SqlAndArgs          : SQL  : DELETE FROM menu WHERE id = ?
2021-02-25 22:17:18.447 DEBUG 80237 --- [           main] win.doyto.query.core.SqlAndArgs          : Param: 3(java.lang.Integer)
...
```


# Articles