# sqltoy-plus

**Repository Path**: momoljw/sqltoy-plus

## Basic Information

- **Project Name**: sqltoy-plus
- **Description**: sqltoy增强框架   支持流操作，支持lamada表达式形式条件拼装
- **Primary Language**: Java
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 8
- **Created**: 2023-05-19
- **Last Updated**: 2024-10-24

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# sqltoy-plus

### 介绍
#### sqltoy增强框架,采用函数式编程思想简化一些数据库通用，支持对象化sql操作，支持lamada表达式，让条件拼装更加动态和灵活，支持对象化多表关联操作。
```
1.对象化SQL操作：通过将SQL操作封装为对象操作。
```
```
2.函数式编程支持：支持Lambda表达式，使得条件拼装更加灵活和动态。
```
```
3.多表关联支持：简化多表关联操作，支持对象化多表关联操作。
```
```
4.简化数据库操作：通过提供通用的数据库操作方法。
```
```
5.分页查询支持：内置的分页查询支持，方便实现大数据集的分页浏览。
```
```
6.灵活的查询构建：通过链式调用构建查询条件，使得查询构建过程更加直观。
```
```
7.易于集成：通过扩展类和注解，可以方便地集成到现有的Spring项目中。
```


 **使用之前需要对sqltoy有一定的了解** 
## [sqltoy项目gitee地址](https://gitee.com/sagacity/sagacity-sqltoy)
## [sqltoy项目github地址](https://github.com/sagframe/sagacity-sqltoy)

### 安装教程

maven依赖(以5.6.22版本为例)：

```xml
        <dependency>
            <groupId>com.sagframe</groupId>
            <artifactId>sagacity-sqltoy-spring-starter</artifactId>
             <!-- jdk1.8 用 5.6.22.jre8 -->
            <version>5.6.22</version>
        </dependency>

        <dependency>
            <groupId>com.sagframe</groupId>
            <artifactId>sqltoy-plus-core</artifactId>
             <!-- jdk1.8 用 5.6.22.jre8 -->
            <version>5.6.22</version>
        </dependency>
```

### 使用说明

支持的数据库操作语法关键词：

|       |                |                 |                 | |   | |     || |
|----------|----------------|-----------------|-----------------|--------|----------|--------|-------------|-------|----------|
| AND      | OR             | NOT             | IN              | NOT IN | LIKE     | 等于(=)  | 不等于(<>)     | 大于(>) | 大于等于(>=) |
| 小于等于(<=) | IS NULL        | IS NOT NULL     | GROUP BY        | HAVING | ORDER BY | EXISTS | NOT BETWEEN | ASC   | DESC     |
|     BETWEEN     | 左连接（LEFT JOIN） | 右连接（RIGHT JOIN） | 内连接（INNER JOIN） |     SELECT   |     DELETE     |    UPDATE    |             |       |          |


### 使用示例

在使用的类里面注入SqlToyHelperDao扩展类, 项目启动类添加@EnableSqlToyPlus启动注解

##### 类示例（Permission）

```java
    @Column(name = "id", type = -5, nullable = false, precision = 19, autoIncrement = true)
    private Long id;

    @Column(name = "type", type= Types.INTEGER)
    private Integer type;

    @Column(name = "method", type= Types.INTEGER)
    private Integer method;
```


1.批量查询

```java
    //lambda形式
    LambdaQueryWrapper<Permission> queryWrapper = Wrappers.lambdaWrapper(Permission.class).eq(Permission::getType, 5);

    //string形式（其他接口雷同）
    //LambdaQueryWrapper<Permission> queryWrapper = Wrappers.wrapper(Permission.class).eq("type", 5);

    List<?> list = sqlToyHelperDao.findList(queryWrapper);
```

输出sql：
```sql
    select id,type,method from permission where type = 5
```

2.批量查询指定查询的字段

```java
    LambdaQueryWrapper<Permission> queryWrapper = Wrappers.lambdaWrapper(Permission.class)
            .select(Permission::getType)
            .select(Permission::getMethod)
            .eq(Permission::getType, 5);
    List<?> list = sqlToyHelperDao.findList(queryWrapper);
```

输出sql：
```sql
    select type,method from permission where type = 5
```

3.分页查询

```java
    LambdaQueryWrapper<Permission> queryWrapper = Wrappers.lambdaWrapper(Permission.class)
            .eq(Permission::getType, 5);
    Page<?> page = sqlToyHelperDao.findPage(queryWrapper, new Page<>(5, 1));
```

输出sql：
```sql
    select id,type,method from permission where type = 5 limit 5 offset 0
```

4.更新(使用LambdaUpdateWrapper/UpdateWrapper)

```java
    LambdaUpdateWrapper<Permission> updateWrapper = Wrappers.lambdaUpdateWrapper(Permission.class)
            .set(Permission::getMethod, 56)
            .eq(Permission::getType, 5);
    long count = sqlToyHelperDao.update(updateWrapper);
```

输出sql：
```sql
    update permission set method = 56  where type = 5
```

5.更新(使用map)

```java
    LambdaQueryWrapper<Permission> queryWrapper = Wrappers.lambdaWrapper(Permission.class)
            .eq(Permission::getType, 5);
    Map<String, Object> map = new HashMap<>();
    map.put("method", 34);
    long count = sqlToyHelperDao.update(map, queryWrapper);
```

输出sql：
```sql
    update permission set method = 34  where type = 5
```

6.更新(使用Entity)

```java
    LambdaQueryWrapper<Permission> queryWrapper = Wrappers.lambdaWrapper(Permission.class)
            .eq(Permission::getType, 5);
    Permission permission = new Permission();
    permission.setMethod(96);
    long count = sqlToyHelperDao.update(permission, queryWrapper);
```

输出sql：
```sql
    update permission set method = 96  where type = 5
```

7.删除

```java
    LambdaQueryWrapper<Permission> wrapper = Wrappers.lambdaWrapper(Permission.class)
            .eq(Permission::getId, 1);
    long count = sqlToyHelperDao.delete(wrapper);
```

输出sql：
```sql
    delete from permission where id = 1
```

8.统计

```java
    LambdaQueryWrapper<Permission> wrapper = Wrappers.lambdaWrapper(Permission.class)
            .eq(Permission::getType, 5);
    long count = sqlToyHelperDao.count(wrapper);
```

输出sql：
```sql
    select count(1) from permission where type = 5
```

9.嵌套条件查询

```java
    LambdaQueryWrapper<Permission> queryWrapper = Wrappers.lambdaWrapper(Permission.class)
        .eq(Permission::getType, 5)
        .and(wrapper1 -> wrapper1.eq(Permission::getMethod, 5).or().ge(Permission::getId, 1));
    List<?> list = sqlToyHelperDao.findList(queryWrapper);
```

输出sql：
```sql
    select id,type,method from permission where type = 5  AND (method = 5  OR id >= 1 )
```

10.多字段组合in条件查询

```java
    List<Permission> permissions = new ArrayList<>();
    Permission permission = new Permission();
    permission.setMethod(12);
    permission.setType(1);
    Permission permission1 = new Permission();
    permission1.setMethod(13);
    permission1.setType(2);
    Permission permission2 = new Permission();
    permission2.setMethod(13);
    permission2.setType(3);
    permissions.add(permission);
    permissions.add(permission1);
    permissions.add(permission2);
    LambdaQueryWrapper<Permission> queryWrapper = Wrappers.lambdaWrapper(Permission.class)
            .eq(Permission::getType, 2)
            .inb(ColumnUtils.of(Permission::getMethod, Permission::getType), BatchValueOperation.from(permissions).toListArray(Permission::getMethod, Permission::getType))
            .isNotNull(Permission::getSort)
            .and(wrapper1 -> wrapper1.eq(Permission::getMethod, 12).or().ge(Permission::getId, 1))
            .isNull(Permission::getIsShow)
            ;
    List<?> list = sqlToyHelperDao.findList(queryWrapper);
```

输出sql：
```sql
select id,type,method,sort,is_show from permission where type = 2  AND (method,type) in ((12,1),(13,2),(13,3))  AND sort IS NOT NULL AND (method = 12  OR id >= 1 ) AND is_show IS NULL)
```

11.MultiWrapper中lambda单表查询

```java
    MultiWrapper multiQuery = Wrappers.lambdaMultiWrapper(Permission.class)
                .select()
                .from(Permission.class).where()
                .eq(Permission::getType, 5)
               ;
    List<?> list = sqlToyHelperDao.findList(multiQuery);
```

输出sql：
```sql
SELECT * FROM permission p1 WHERE p1.type = 5
```

12.lambda多表联查查询字段使用函数

```java
    MultiWrapper multiQuery = Wrappers.lambdaMultiWrapper(Permission.class)
                .select(LambdaColumn.count(Permission::getSort).as("sort")).from(Permission.class)
                .where()
                .eq(Permission::getType, 5);
    List<?> list = sqlToyHelperDao.findList(multiQuery);
```

输出sql：
```sql
SELECT COUNT(p1.sort) AS sort FROM permission p1 WHERE p1.type = 5
```

13.lambda多表联查批量查询

```java
    MultiWrapper multiQuery = Wrappers.lambdaMultiWrapper(Permission.class).select().from(Permission.class)
                .leftJoin(Permission1.class).on().eq(Permission::getId, Permission1::getId)
                .where()
                .eq(Permission::getType, 5)
                .eq(Permission1::getMethod, 34);
    List<?> list = sqlToyHelperDao.findList(multiQuery);
```

输出sql：
```sql
SELECT * FROM permission p1 LEFT JOIN permission p2 ON p1.id = p2.id WHERE p1.type = 5  AND p2.method = 34
```

14.lambda多表联查分页查询

```java
    MultiWrapper multiQuery = Wrappers.lambdaMultiWrapper(Permission.class).select().from(Permission.class)
                .leftJoin(Permission1.class).on().eq(Permission::getId, Permission1::getId).where()
                .eq(Permission::getType, 5)
                .eq(Permission1::getMethod, 34);
    Page<?> page = sqlToyHelperDao.findPage(multiQuery, new Page<>(5, 1));
```

输出sql：
```sql
SELECT * FROM permission p1 LEFT JOIN permission p2 ON p1.id = p2.id WHERE p1.type = 5  AND p2.method = 34  limit 5  offset 0
```

15.lambda多表关联更新

```java
    MultiWrapper updateWrapper = Wrappers.lambdaUpdateMultiWrapper()
                .set(Permission::getMethod, 2222)
                .set(Permission1::getMethod, 2222)
                .from(Permission.class).leftJoin(Permission1.class).on().eq(Permission::getId, Permission1::getId)
                .where()
                .eq(Permission::getId, 570);
    long count = sqlToyHelperDao.update(deleteWrapper);
```

输出sql：
```sql
UPDATE permission p1 LEFT JOIN permission p2 ON p1.id = p2.id SET p1.method = 2222, p2.method = 2222  WHERE p1.id = 570
```

15.lambda多表关联删除

```java
     MultiWrapper deleteWrapper = Wrappers.lambdaDeleteMultiWrapper()
                .delete(Permission.class, Permission1.class)
                .from(Permission.class).leftJoin(Permission1.class).on().eq(Permission::getId, Permission1::getId)
                .where()
                .eq(Permission::getMethod, 2222)
                .eq(Permission1::getMethod, 2222);
    long count = sqlToyHelperDao.delete(deleteWrapper);
```

输出sql：
```sql
DELETE p1, p2 FROM permission p1 LEFT JOIN permission p2 ON p1.id = p2.id WHERE p1.method = 2222  AND p2.method = 2222
```

16.多表联查分页查询

```java
    MultiWrapper multiQuery = Wrappers.multiWrapper(ShopGoods.class)
                .select()
                .from(ShopGoods.class, "s1")
                .leftJoin(ShopGoodsSku.class, "s2")
                .on()
                .eq("s1.code", "s2.goodsCode")
                .where()
                .like("s2.goodsSkuCode", 68)
                .groupBy("s2.goodsCode")
                .orderByAsc("s2.goodsCode");
    Page<?> page = sqlToyHelperDao.findPage(multiQuery, new Page<>(5, 1));
```

输出sql：
```sql
SELECT s1.code as code,s1.name as name, s1.is_tax as isTax FROM t_shop_goods s1 LEFT JOIN t_shop_goods_sku s2 ON s1.code = s2.goods_code WHERE s2.goods_sku_code LIKE CONCAT('%',CONCAT(68  ,'%')) GROUP BY s2.goods_code ORDER BY s2.goods_code ASC  limit 5  offset 0
```  

17.删改查链式调用

```java
    List<Period> periods = sqlToyHelperDao.lambdaQueryChain(Period.class)
        .eq(Period::getStatus, queryDto.getStatus())
        .eq(Period::getPeriodType, queryDto.getPeriodType())
        .list();
```

输出sql：
```sql
SELECT * FROM Period WHERE status = 1 and periodType = 2;
```  


#### 参与贡献

1.  Fork 本仓库
2.  新建 名称前缀_YYYYMMDD_变更简要描述名称 分支
3.  提交代码
4.  新建 Pull Request


#### 特技

1.  使用 Readme\_XXX.md 来支持不同的语言，例如 Readme\_en.md, Readme\_zh.md
2.  Gitee 官方博客 [blog.gitee.com](https://blog.gitee.com)
3.  你可以 [https://gitee.com/explore](https://gitee.com/explore) 这个地址来了解 Gitee 上的优秀开源项目
4.  [GVP](https://gitee.com/gvp) 全称是 Gitee 最有价值开源项目，是综合评定出的优秀开源项目
5.  Gitee 官方提供的使用手册 [https://gitee.com/help](https://gitee.com/help)
6.  Gitee 封面人物是一档用来展示 Gitee 会员风采的栏目 [https://gitee.com/gitee-stars/](https://gitee.com/gitee-stars/)