package io.github.kiryu1223.example;

import com.zaxxer.hikari.HikariDataSource;
import io.github.kiryu1223.drink.base.DbType;
import io.github.kiryu1223.drink.base.converter.SnakeNameConverter;
import io.github.kiryu1223.drink.core.SqlBuilder;
import io.github.kiryu1223.drink.core.SqlClient;

public class DrinkExample {
    public static void main(String[] args) {
        // 1. 配置数据源
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/test_db?rewriteBatchedStatements=true");
        dataSource.setUsername("root");
        dataSource.setPassword("password");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");

        // 2. 创建 SqlClient
        SqlClient client = SqlBuilder.bootStrap()
                .setDbType(DbType.MySQL)                    // 数据库类型
                .setNameConverter(new SnakeNameConverter()) // 命名转换策略
                .setDataSource(dataSource)                  // 数据源
                .build();

        // 3. 开始使用
        // 查询示例
        List<User> users = client.query(User.class)
                .where(u -> u.getAge() > 18)
                .toList();

        System.out.println("查询到 " + users.size() + " 个用户");
    }
}

// 查询
List<User> users = client.query(User.class)
        .where(u -> u.getName().contains("张"))
        .orderBy(u -> u.getAge())
        .limit(10)
        .toList();

// 插入
User newUser = new User();
newUser.setName("张三");
newUser.setAge(25);
long insertCount = client.insert(newUser).executeRows();

// 更新
long updateCount = client.update(User.class)
        .set(u -> u.setAge(26))
        .where(u -> u.getName().equals("张三"))
        .executeRows();

// 删除
long deleteCount = client.delete(User.class)
        .where(u -> u.getAge() < 18)
        .executeRows();

@RestController
@RequestMapping("/api/users")
public class UserController {

    @Autowired
    private DrinkClient drinkClient;

    @GetMapping
    public List<User> getUsers(@RequestParam(defaultValue = "0") int page,
                              @RequestParam(defaultValue = "10") int size) {
        return drinkClient.query(User.class)
                .orderBy(u -> u.getId())
                .limit(page * size, size)
                .toList();
    }

    @GetMapping("/{id}")
    public User getUser(@PathVariable Long id) {
        return drinkClient.query(User.class)
                .where(u -> u.getId().equals(id))
                .first();
    }

    @PostMapping
    public ResponseEntity<String> createUser(@RequestBody User user) {
        long row = drinkClient.insert(user).executeRows();
        return ResponseEntity.ok("创建成功，影响行数：" + row);
    }

    @PutMapping("/{id}")
    public ResponseEntity<String> updateUser(@PathVariable Long id,
                                           @RequestBody User user) {
        long row = drinkClient.update(User.class)
                .set(u -> {
                    u.setName(user.getName());
                    u.setAge(user.getAge());
                })
                .where(u -> u.getId().equals(id))
                .executeRows();
        return ResponseEntity.ok("更新成功，影响行数：" + row);
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<String> deleteUser(@PathVariable Long id) {
        long row = drinkClient.delete(User.class)
                .where(u -> u.getId().equals(id))
                .executeRows();
        return ResponseEntity.ok("删除成功，影响行数：" + row);
    }
}

### 方式三：Solon 框架集成

适用于 Solon 框架项目，支持多数据源配置。

#### 1. 添加依赖

```xml
<dependency>
    <groupId>io.github.kiryu1223</groupId>
    <artifactId>drink-solon-plugin</artifactId>
    <version>${project.version}</version>
</dependency>

<build>
    <plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.8.1</version>
            <configuration>
                <compilerArgs>
                    <arg>-Xplugin:ExpressionTree</arg>
                </compilerArgs>
                <annotationProcessorPaths>
                    <path>
                        <groupId>io.github.kiryu1223</groupId>
                        <artifactId>drink-core</artifactId>
                        <version>${project.version}</version>
                    </path>
                </annotationProcessorPaths>
            </configuration>
        </plugin>
    </plugins>
</build>

@Table("user_info")  // 指定表名
@Table(schema = "test", value = "users")  // 指定 schema 和表名
public class User {
    // ...
}

public class User {
    @Column(value = "user_id", primaryKey = true)
    private Long id;

    @Column("user_name")
    private String name;

    @Column(value = "age", notNull = true)
    private Integer age;

    @Column(value = "created_at", generatedKey = true)
    private LocalDateTime createTime;
}

public class User {
    @Column(value = "user_id", primaryKey = true)
    private Long id;

    // 一对多：一个用户有多个订单
    @Navigate(value = RelationType.OneToMany,
              self = "id",
              target = "userId")
    private List<Order> orders;
}

public class Order {
    @Column(value = "order_id", primaryKey = true)
    private Long id;

    @Column("user_id")
    private Long userId;

    // 多对一：多个订单属于一个用户
    @Navigate(value = RelationType.ManyToOne,
              self = "userId",
              target = "id")
    private User user;

    // 多对多：订单和商品的关系（通过中间表）
    @Navigate(value = RelationType.ManyToMany,
              self = "id",
              selfMapping = "orderId",
              mappingTable = OrderProduct.class,
              targetMapping = "productId",
              target = "id")
    private List<Product> products;
}

public class User {
    private Long id;
    private String name;

    @IgnoreColumn  // 该字段不参与数据库映射
    private String tempField;
}

@Data
@Table("users")
public class User {
    @Column(value = "user_id", primaryKey = true)
    private Long id;

    @Column("user_name")
    private String name;

    @Column(value = "age", notNull = true)
    private Integer age;

    @Column("email")
    private String email;

    @Column(value = "created_at", generatedKey = true)
    private LocalDateTime createTime;

    @Navigate(value = RelationType.OneToMany, self = "id", target = "userId")
    private List<Order> orders;

    @IgnoreColumn
    private String displayName;  // 计算字段，不存储到数据库
}

// 查询所有用户
List<User> allUsers = client.query(User.class).toList();

// 条件查询
List<User> adults = client.query(User.class)
        .where(u -> u.getAge() >= 18)
        .toList();

// 多条件查询
List<User> result = client.query(User.class)
        .where(u -> u.getAge() >= 18)
        .where(u -> u.getName().contains("张"))
        .toList();

// OR 条件
List<User> result = client.query(User.class)
        .where(u -> u.getAge() >= 18)
        .orWhere(u -> u.getName().contains("管理员"))
        .toList();

// 排序
List<User> users = client.query(User.class)
        .orderBy(u -> u.getAge())           // 升序
        .orderBy(u -> u.getName(), false)   // 降序
        .toList();

// 分页
List<User> users = client.query(User.class)
        .orderBy(u -> u.getId())
        .limit(10)          // 限制 10 条
        .toList();

List<User> users = client.query(User.class)
        .orderBy(u -> u.getId())
        .limit(20, 10)      // 跳过 20 条，取 10 条
        .toList();

// 选择特定字段
List<? extends Result> result = client.query(User.class)
        .select(u -> new Result() {
            String name = u.getName();
            Integer age = u.getAge();
        })
        .toList();

// 选择单个字段
List<String> names = client.query(User.class)
        .endSelect(u -> u.getName())
        .toList();

// 聚合查询
long row = client.query(User.class)
        .where(u -> u.getAge() >= 18)
        .row();

Integer maxAge = client.query(User.class).max(u -> u.getAge());
Integer minAge = client.query(User.class).min(u -> u.getAge());
BigDecimal avgAge = client.query(User.class).avg(u -> u.getAge());

// 内连接
List<? extends Result> result = client.query(User.class)
        .innerJoin(Order.class, (u, o) -> u.getId().equals(o.getUserId()))
        .select((u, o) -> new Result() {
            String userName = u.getName();
            String orderNo = o.getOrderNo();
            BigDecimal amount = o.getAmount();
        })
        .toList();

// 左连接
List<? extends Result> result = client.query(User.class)
        .leftJoin(Order.class, (u, o) -> u.getId().equals(o.getUserId()))
        .where((u, o) -> u.getAge() >= 18)
        .select((u, o) -> new Result() {
            String userName = u.getName();
            Long orderCount = SqlFunctions.row(o.getId());
        })
        .toList();

User user = new User();
user.setName("张三");
user.setAge(25);
user.setEmail("[email protected]");

long insertCount = client.insert(user).executeRows();
System.out.println("插入成功，影响行数：" + insertCount);

List<User> users = Arrays.asList(
    new User("张三", 25, "[email protected]"),
    new User("李四", 30, "[email protected]"),
    new User("王五", 28, "[email protected]")
);

// 自动批量插入（当数量 >= 2 时）
long insertCount = client.insert(users).executeRows();
System.out.println("批量插入成功，影响行数：" + insertCount);

User user1 = new User("张三", 25, "[email protected]");
User user2 = new User("李四", 30, "[email protected]");

long insertCount = client.insert(user1)
        .insert(user2)
        .executeRows();

// 更新单个字段
long updateCount = client.update(User.class)
        .set(u -> u.setAge(26))
        .where(u -> u.getId().equals(1L))
        .executeRows();

// 更新多个字段
long updateCount = client.update(User.class)
        .set(u -> {
            u.setName("新名字");
            u.setAge(30);
            u.setEmail("[email protected]");
        })
        .where(u -> u.getId().equals(1L))
        .executeRows();

// 批量更新
long updateCount = client.update(User.class)
        .set(u -> u.setAge(u.getAge() + 1))  // 年龄加1
        .where(u -> u.getAge() < 30)
        .executeRows();

// 连表更新
long updateCount = client.update(User.class)
        .leftJoin(Order.class, (u, o) -> u.getId().equals(o.getUserId()))
        .set((u, o) -> u.setLastOrderTime(o.getCreateTime()))
        .where((u, o) -> o.getStatus().equals("COMPLETED"))
        .executeRows();

// 根据ID删除
long deleteCount = client.delete(User.class)
        .where(u -> u.getId().equals(1L))
        .executeRows();

// 条件删除
long deleteCount = client.delete(User.class)
        .where(u -> u.getAge() < 18)
        .executeRows();

// 删除没有订单的用户
long deleteCount = client.delete(User.class)
        .leftJoin(Order.class, (u, o) -> u.getId().equals(o.getUserId()))
        .where((u, o) -> o.getId() == null)
        .executeRows();

// 指定删除的表
long deleteCount = client.delete(User.class)
        .leftJoin(Order.class, (u, o) -> u.getId().equals(o.getUserId()))
        .selectDelete((u, o) -> o)  // 删除订单表的记录
        .where((u, o) -> u.getStatus().equals("INACTIVE"))
        .executeRows();

// 简单分组
List<? extends Result> result = client.query(Order.class)
        .groupBy(o -> o.getUserId())
        .select(g -> new Result() {
            Long userId = g.key;
            Long orderCount = g.row();
            BigDecimal totalAmount = g.sum(o -> o.getAmount());
        })
        .toList();

// 多字段分组
List<? extends Result> result = client.query(Order.class)
        .groupBy(o -> new Grouper() {
            Long userId = o.getUserId();
            String status = o.getStatus();
        })
        .select(g -> new Result() {
            Long userId = g.key.userId;
            String status = g.key.status;
            Long row = g.row();
            BigDecimal avgAmount = g.avg(o -> o.getAmount());
        })
        .toList();

List<? extends Result> result = client.query(Order.class)
        .groupBy(o -> o.getUserId())
        .having(g -> g.row() > 5)  // 订单数量大于5的用户
        .select(g -> new Result() {
            Long userId = g.key;
            Long orderCount = g.row();
        })
        .toList();

// 查询用户及其订单
List<User> users = client.query(User.class)
        .includes(u -> u.getOrders())  // 自动加载订单
        .toList();

// 用户的订单列表会被自动填充
for (User user : users) {
    System.out.println("用户: " + user.getName());
    System.out.println("订单数量: " + user.getOrders().size());
}

// 只加载已完成的订单
List<User> users = client.query(User.class)
        .includes(u -> u.getOrders(), o -> o.getStatus().equals("COMPLETED"))
        .toList();

// 复杂条件 Include
List<User> users = client.query(User.class)
        .includesByCond(u -> u.getOrders(), query -> query
                .where(o -> o.getAmount().compareTo(new BigDecimal("100")) > 0)
                .orderBy(o -> o.getCreateTime(), false)
                .limit(5)
        )
        .toList();

// 查询用户 -> 订单 -> 订单项
List<User> users = client.query(User.class)
        .includes(u -> u.getOrders())
        .includes(u -> u.getOrders(), o -> o.getOrderItems())
        .toList();

// 查询有订单的用户
List<User> users = client.query(User.class)
        .where(u -> client.query(Order.class)
                .where(o -> o.getUserId().equals(u.getId()))
                .exists())
        .toList();

// 查询订单数量最多的用户
List<User> users = client.query(User.class)
        .where(u -> client.query(Order.class)
                .where(o -> o.getUserId().equals(u.getId()))
                .row() == client.query(Order.class)
                        .groupBy(o -> o.getUserId())
                        .max(g -> g.row()))
        .toList();

// Union 查询
LQuery<User> query1 = client.query(User.class)
        .where(u -> u.getAge() > 30);

LQuery<User> query2 = client.query(User.class)
        .where(u -> u.getName().contains("管理员"));

List<User> users = client.union(query1, query2).toList();

// Union All
List<User> users = client.unionAll(query1, query2).toList();

// 基本事务使用
try (Transaction transaction = client.beginTransaction()) {
    // 执行多个操作
    client.insert(user).executeRows();
    client.update(Order.class)
            .set(o -> o.setStatus("PROCESSING"))
            .where(o -> o.getUserId().equals(user.getId()))
            .executeRows();

    // 提交事务
    transaction.commit();
} catch (Exception e) {
    // 异常时自动回滚
    e.printStackTrace();
}

import java.sql.Connection;

try (Transaction transaction = client.beginTransaction(Connection.TRANSACTION_READ_COMMITTED)) {
    // 在指定隔离级别下执行操作
    // ...
    transaction.commit();
}

@Service
public class UserService {

    @Autowired
    private DrinkClient client;

    @Transactional
    public void createUserWithOrder(User user, Order order) {
        // 插入用户
        client.insert(user).executeRows();

        // 设置订单的用户ID
        order.setUserId(user.getId());

        // 插入订单
        client.insert(order).executeRows();

        // 如果发生异常，整个事务会自动回滚
    }

    @Transactional(readOnly = true)
    public List<User> getActiveUsers() {
        return client.query(User.class)
                .where(u -> u.getStatus().equals("ACTIVE"))
                .toList();
    }
}

// 驼峰命名转下划线
.setNameConverter(new SnakeNameConverter())

// 保持原样
.setNameConverter(new NoConverter())

// 自定义转换器
.setNameConverter(new NameConverter() {
    @Override
    public String convertTableName(String entityName) {
        return "t_" + entityName.toLowerCase();
    }

    @Override
    public String convertColumnName(String fieldName) {
        return fieldName.toUpperCase();
    }
})

// 当前时间
LocalDateTime now = SqlFunctions.now();
LocalDate today = SqlFunctions.nowDate();

// 日期计算
LocalDate futureDate = SqlFunctions.addDate(LocalDate.now(), 30, DateUnit.DAY);
long daysDiff = SqlFunctions.dateTimeDiff(date1, date2, DateUnit.DAY);

// 日期格式化
String formatted = SqlFunctions.dateFormat(LocalDateTime.now(), "yyyy-MM-dd HH:mm:ss");

// 字符串操作
String concat = SqlFunctions.concat("Hello", " ", "World");
String upper = SqlFunctions.toUpperCase("hello");
String sub = SqlFunctions.subString("Hello World", 0, 5);
int length = SqlFunctions.length("Hello");

// 数学计算
double abs = SqlFunctions.abs(-10.5);
double round = SqlFunctions.round(3.14159, 2);
double max = SqlFunctions.max(value1, value2, value3);

// 条件判断
String result = SqlFunctions.If(condition, "真值", "假值");
String notNull = SqlFunctions.ifNull(nullableValue, "默认值");

@Data
@Table("users")
public class User {
    // 主键使用 Long 类型
    @Column(value = "user_id", primaryKey = true)
    private Long id;

    // 必填字段标记 notNull
    @Column(value = "user_name", notNull = true)
    private String name;

    // 自增字段标记 generatedKey
    @Column(value = "created_at", generatedKey = true)
    private LocalDateTime createTime;

    // 使用合适的数据类型
    private BigDecimal balance;  // 金额使用 BigDecimal
    private LocalDate birthDate; // 日期使用 LocalDate
}

// ✅ 好的做法：使用索引字段查询
List<User> users = client.query(User.class)
        .where(u -> u.getId().equals(userId))  // 主键查询
        .toList();

// ✅ 好的做法：限制查询结果数量
List<User> users = client.query(User.class)
        .orderBy(u -> u.getId())
        .limit(100)  // 限制结果集大小
        .toList();

// ❌ 避免：全表扫描
List<User> users = client.query(User.class)
        .where(u -> u.getName().contains("张"))  // 可能导致全表扫描
        .toList();

// ✅ 好的做法：使用批量插入
List<User> users = Arrays.asList(user1, user2, user3);
client.insert(users).executeRows();  // 自动批量执行

// ✅ 好的做法：批量更新
client.update(User.class)
        .set(u -> u.setStatus("ACTIVE"))
        .where(u -> u.getCreateTime().isAfter(yesterday))
        .executeRows();

// ✅ 好的做法：使用 try-with-resources
try (Transaction transaction = client.beginTransaction()) {
    // 执行多个相关操作
    client.insert(user).executeRows();
    client.insert(order).executeRows();

    transaction.commit();
} catch (Exception e) {
    // 异常自动回滚
    log.error("事务执行失败", e);
}

// ✅ 好的做法：按需加载关联数据
List<User> users = client.query(User.class)
        .includes(u -> u.getOrders(), o -> o.getStatus().equals("ACTIVE"))
        .limit(10)
        .toList();

// ❌ 避免：无条件加载大量关联数据
List<User> users = client.query(User.class)
        .includes(u -> u.getOrders())  // 可能加载大量数据
        .toList();

// 方式1：使用类型处理器
@UseTypeHandler(GenderHandler.class)
private Gender gender;

// 方式2：使用转换器
@Column(converter = GenderConverter.class)
private Gender gender;

// 自定义转换器
public class GenderConverter implements IConverter<Gender, String> {
    @Override
    public String toDb(Gender gender) {
        return gender == null ? null : gender.name();
    }

    @Override
    public Gender fromDb(String value) {
        return value == null ? null : Gender.valueOf(value);
    }
}

@Column("extra_info")
@UseTypeHandler(JsonTypeHandler.class)
private Map<String, Object> extraInfo;

// 自定义 JSON 处理器
public class JsonTypeHandler implements ITypeHandler<Map<String, Object>> {
    private final ObjectMapper objectMapper = new ObjectMapper();

    @Override
    public void setParameter(PreparedStatement ps, int i, Map<String, Object> parameter) throws SQLException {
        if (parameter == null) {
            ps.setNull(i, Types.VARCHAR);
        } else {
            try {
                ps.setString(i, objectMapper.writeValueAsString(parameter));
            } catch (JsonProcessingException e) {
                throw new SQLException(e);
            }
        }
    }

    @Override
    public Map<String, Object> getResult(ResultSet resultSet, String columnName) throws SQLException {
        String json = resultSet.getString(columnName);
        if (json == null) return null;
        try {
            return objectMapper.readValue(json, new TypeReference<Map<String, Object>>() {});
        } catch (JsonProcessingException e) {
            throw new SQLException(e);
        }
    }
}

@Data
@Table("users")
public class User {
    @Column(value = "user_id", primaryKey = true)
    private Long id;

    private String name;

    @Column("is_deleted")
    private Boolean deleted = false;

    @Column("delete_time")
    private LocalDateTime deleteTime;
}

// 查询时自动过滤已删除记录
List<User> users = client.query(User.class)
        .where(u -> u.getDeleted() == false)
        .toList();

// 软删除
client.update(User.class)
        .set(u -> {
            u.setDeleted(true);
            u.setDeleteTime(LocalDateTime.now());
        })
        .where(u -> u.getId().equals(userId))
        .executeRows();

// 基本分页
public PageResult<User> getUsers(int page, int size) {
    // 查询总数
    long total = client.query(User.class).row();

    // 查询数据
    List<User> users = client.query(User.class)
            .orderBy(u -> u.getId())
            .limit(page * size, size)
            .toList();

    return new PageResult<>(users, total, page, size);
}

// 分页结果类
@Data
public class PageResult<T> {
    private List<T> data;
    private long total;
    private int page;
    private int size;
    private int totalPages;

    public PageResult(List<T> data, long total, int page, int size) {
        this.data = data;
        this.total = total;
        this.page = page;
        this.size = size;
        this.totalPages = (int) Math.ceil((double) total / size);
    }
}

public List<User> searchUsers(UserSearchCriteria criteria) {
    LQuery<User> query = client.query(User.class);

    // 动态添加条件
    if (criteria.getName() != null) {
        query = query.where(u -> u.getName().contains(criteria.getName()));
    }

    if (criteria.getMinAge() != null) {
        query = query.where(u -> u.getAge() >= criteria.getMinAge());
    }

    if (criteria.getMaxAge() != null) {
        query = query.where(u -> u.getAge() <= criteria.getMaxAge());
    }

    if (criteria.getStatus() != null) {
        query = query.where(u -> u.getStatus().equals(criteria.getStatus()));
    }

    return query.orderBy(u -> u.getId()).toList();
}

// 用户订单统计
List<? extends Result> stats = client.query(User.class)
        .leftJoin(Order.class, (u, o) -> u.getId().equals(o.getUserId()))
        .groupBy((u, o) -> new Grouper() {
            Long userId = u.getId();
            String userName = u.getName();
        })
        .select(g -> new Result() {
            Long userId = g.key.userId;
            String userName = g.key.userName;
            Long orderCount = g.row((u, o) -> o.getId());
            BigDecimal totalAmount = g.sum((u, o) -> o.getAmount());
            BigDecimal avgAmount = g.avg((u, o) -> o.getAmount());
            LocalDateTime lastOrderTime = g.max((u, o) -> o.getCreateTime());
        })
        .toList();

// ✅ 使用主键或索引字段
.where(u -> u.getId().equals(userId))

// ❌ 避免在非索引字段上使用 LIKE
.where(u -> u.getDescription().contains("关键词"))

// 总是使用 limit 限制结果数量
.limit(100)

// 只加载需要的关联数据
.includes(u -> u.getOrders(), o -> o.getStatus().equals("ACTIVE"))

// 批量插入而不是循环单条插入
client.insert(userList).executeRows();

@Data
@Table("employees")
public class Employee
{
    //员工id
    @Column("emp_no")
    private int number;
    //生日
    @Column("birth_date")
    private LocalDate birthDay;
    @Column("first_name")
    private String firstName;
    @Column("last_name")
    private String lastName;
    //性别
    @Column(converter = GenderConverter.class)
    private Gender gender;
    //入职日期
    @Column("hire_date")
    private LocalDate hireDay;
}

public class DisplayTest extends BaseTest
{
    public void d1()
    {
        int id = 10001;
        List<Employee> list = client.query(Employee.class) // FROM `employees` AS t0
                .where(e -> e.getNumber() == id) // WHERE t0.`emp_no` = ?
                // 因为没有select，默认选择了全字段
                // SELECT t0.`birth_date`,t0.`first_name`,t0.`last_name`,t0.`emp_no`,t0.`hire_date`,t0.`gender`
                .toList();
    }
}

public class DisplayTest extends BaseTest
{
    public void d2()
    {
        List<Employee> list = client.query(Employee.class) // FROM `employees` AS t0
                .where(e -> e.getGender() == Gender.F && e.getFirstName() == "lady") // WHERE t0.`gender` = ? AND t0.`first_name` = ?
                // 因为没有select，默认选择了全字段
                // SELECT t0.`birth_date`, t0.`first_name`, t0.`last_name`, t0.`emp_no`, t0.`hire_date`, t0.`gender`
                .toList();
    }
}

@Data
@Table("salaries")
public class Salary
{
    //员工id
    @Column("emp_no")
    private int empNumber;
    //薪资
    private int salary;
    //何时开始的
    @Column("from_date")
    private LocalDate from;
    //何时为止的
    @Column("to_date")
    private LocalDate to;
}

public class DisplayTest extends BaseTest
{
    public void d3()
    {
        int id = 10001;
        List<? extends Result> list = client.query(Employee.class) // FROM `employees` AS t0
                .leftJoin(Salary.class, (e, s) -> e.getNumber() == s.getEmpNumber()) // LEFT JOIN `salaries` AS t1 ON t0.`emp_no` = t1.`emp_no`
                .where((e, s) -> e.getNumber() == id) // WHERE t0.`emp_no` = ?
                // SELECT
                .select((e, s) -> new Result()
                {
                    // CONCAT(t0.`first_name`, ?, t0.`last_name`) AS `name`
                    String name = SqlFunctions.concat(e.getFirstName(), " ", e.getLastName());
                    // MAX(t1.`salary`)                           AS `maxSalary`,
                    int maxSalary = SqlFunctions.max(s.getSalary());
                    // AVG(t1.`salary`)                           AS `avgSalary`
                    BigDecimal avgSalary = SqlFunctions.avg(s.getSalary());
                })
                .toList();
    }
}

@Table("departments")
@Data
public class Department
{
    // 部门编号
    @Column("dept_no")
    private String number;
    // 部门名称
    @Column("dept_name")
    private String name;
}

@Data
@Table("dept_emp")
public class DeptEmp
{
    // 员工编号
    @Column("emp_no")
    private int empNumber;
    // 部门编号
    @Column("dept_no")
    private String deptNumber;
    // 什么时候加入的
    @Column("from_date")
    private LocalDate from;
    // 什么时候离开的
    @Column("to_date")
    private LocalDate to;
}

public class DisplayTest extends BaseTest
{
    public void d4()
    {
        String departmentId = "d009";

        List<? extends Result> list = client.query(DeptEmp.class) // FROM `dept_emp` AS t0
                .innerJoin(Salary.class, (de, s) -> de.getEmpNumber() == s.getEmpNumber()) // INNER JOIN `salaries` AS t1 ON t0.`emp_no` = t1.`emp_no`
                .innerJoin(Department.class, (de, s, d) -> de.getDeptNumber() == d.getNumber()) // INNER JOIN `departments` AS t2 ON t0.`dept_no` = t2.`dept_no`
                .where((de, s, d) -> de.getDeptNumber() == departmentId && s.getTo() == LocalDate.of(9999, 1, 1)) // WHERE t0.`dept_no` = ? AND t1.`to_date` = ?
                // GROUP BY
                .groupBy((de, s, d) -> new Grouper()
                {
                    // t0.`dept_no`,
                    String id = de.getDeptNumber();
                    // t2.`dept_name`
                    String name = d.getName();
                })
                // SELECT
                .select(g -> new Result()
                {
                    // t0.`dept_no` AS `deptId`
                    String deptId = g.key.id;
                    // t2.`dept_name` AS `deptName`
                    String deptName = g.key.name;
                    // AVG(t1.`salary`) AS `avgSalary`
                    BigDecimal avgSalary = g.avg((de, s, d) -> s.getSalary());
                })
                .toList();
    }
}

public class DisplayTest extends BaseTest
{
    public void i1()
    {
        Department department = new Department();
        department.setNumber("101");
        department.setName("ddd");

        client.insert(department).executeRows();
    }
}

public class DisplayTest extends BaseTest
{
    public void i2()
    {
        Department department1 = new Department();
        department1.setNumber("101");
        department1.setName("ddd");
        Department department2 = new Department();
        department2.setNumber("102");
        department2.setName("eee");
        Department department3 = new Department();
        department3.setNumber("103");
        department3.setName("fff");

        List<Department> list = Arrays.asList(department1, department2, department3);

        client.insert(list).executeRows();
    }
}

public class DisplayTest extends BaseTest
{
    public void i3()
    {
        Department d = new Department();

        Department department1 = new Department();
        department1.setNumber("101");
        department1.setName("ddd");
        Department department2 = new Department();
        department2.setNumber("102");
        department2.setName("eee");
        Department department3 = new Department();
        department3.setNumber("103");
        department3.setName("fff");

        List<Department> ds = Arrays.asList(department1, department2, department3);

        client.insert(d).insert(ds).executeRows();
    }
}

public class UpdateTest extends BaseTest
{
    public void display0()
    {
        long l2 = client.update(Department.class)
                .set(s -> s.setName("newName"))
                .where(w -> w.getNumber() == "100")
                .executeRows();
    }
}

@SuppressWarnings("all")
public class UpdateTest extends BaseTest
{
    long l = client.update(Department.class)
            .leftJoin(DeptEmp.class, (a, b) -> a.getNumber() == b.getDeptNumber())
            .set((a, b) -> a.setName(b.getDeptNumber()))
            .where((a, b) -> 1 == 1)
            .executeRows();
}

public class DeleteTest extends BaseTest
{
    @Test
    public void d1()
    {
        long executeRows = client.delete(Department.class)
                .where(w -> w.getNumber() == "10009")
                .executeRows();
    }
}

public class DeleteTest extends BaseTest
{
    @Test
    public void d2()
    {
        String sql = client.delete(Department.class)
                .leftJoin(DeptEmp.class, (d, dm) -> d.getNumber() == dm.getDeptNumber())
                .where((d, dm) -> d.getNumber() == "d009")
//                .selectDeleteTable((d, dm) -> d)
                .selectDelete((d, dm) -> dm)
                .toSql();
        System.out.println(sql);
    }
}

@Data
@Table(value = "salaries")
public class Salary
{
    @Column(value = "emp_no", primaryKey = true)
    private int empNumber;
    private int salary;
    @Column("from_date")
    private LocalDate from;
    @Column("to_date")
    private LocalDate to;
}

@Data
@Table("employees")
public class Employee
{
    @Column(value = "emp_no", primaryKey = true)
    private int number;
    @Column("birth_date")
    private LocalDate birthDay;
    @Column("first_name")
    private String firstName;
    @Column("last_name")
    private String lastName;
    @Column(converter = GenderConverter.class)
    private Gender gender;
    @Column("hire_date")
    private LocalDate hireDay;
    // 一对多，self为自身的number字段，target为Salary的empNumber字段
    @Navigate(value = RelationType.OneToMany, self = "number", target = "empNumber")
    private List<Salary> salaries;
}

public class IncludeTest extends BaseTest
{
    @Test
    public void oneManyTest()
    {
        //获取编号为10001的员工并且查询出该员工的所有工资信息
        Employee employee = client.query(Employee.class)
                .where(e -> e.getNumber() == 10001)
                .includes(e -> e.getSalaries())
                .first();

        Assert.assertEquals(17, employee.getSalaries().size());
    }
}

public class IncludeTest extends BaseTest
{
    @Test
    public void oneManyCondTest()
    {
        //获取编号为10001的员工并且查询出该员工最后一次调整工资（9999-01-01）以外的历史工资
        Employee employee = client.query(Employee.class)
                .where(e -> e.getNumber() == 10001)
                .includes(e -> e.getSalaries(), s -> s.getTo().isBefore(LocalDate.of(9999, 1, 1)))
                .first();

        Assert.assertEquals(16, employee.getSalaries().size());
    }
}

public class IncludeTest extends BaseTest
{
    @Test
    public void oneManyCond2Test()
    {
        //获取编号为10001的员工并且查询出该员工最后一次调整工资（9999-01-01）以外的历史工资
        //同时只获取前10条
        //并且按工资排序
        Employee employee = client.query(Employee.class)
                .includesByCond(e -> e.getSalaries(), query -> query
                        .orderBy(s -> s.getSalary())
                        .where(s -> s.getTo().isBefore(LocalDate.of(9999, 1, 1)))
                        .limit(10)
                )
                .first();

        Assert.assertEquals(10, employee.getSalaries().size());
    }
}