# sqltoy-plus

**Repository Path**: it-xiaofan/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**: 2022-11-08
- **Last Updated**: 2023-11-27

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# sqltoy-plus

#### 介绍
sqltoy增强框架，sql对象化，支持lamada表达式形式条件拼装，支持多表关联。

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

### 软件架构
函数式编程思想简化一些数据库通用操作


### 安装教程

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

        <dependency>
            <groupId>com.sagframe</groupId>
            <artifactId>sagacity-sqltoy-starter</artifactId>
            <version>5.2.67</version>
        </dependency>

        <dependency>
            <groupId>com.sagframe</groupId>
            <artifactId>sqltoy-plus-core</artifactId>
            <version>5.2.67</version>
        </dependency>

### 使用说明

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

    AND("AND"),
    OR("OR"),
    NOT("NOT"),
    IN("IN"),
    NOT_IN("NOT IN"),
    LIKE("LIKE"),
    EQ("="),
    NE("<>"),
    GT(">"),
    GE(">="),
    LT("<"),
    LE("<="),
    IS_NULL("IS NULL"),
    IS_NOT_NULL("IS NOT NULL"),
    GROUP_BY("GROUP BY"),
    HAVING("HAVING"),
    ORDER_BY("ORDER BY"),
    EXISTS("EXISTS"),
    NOT_BETWEEN("NOT BETWEEN"),
    ASC("ASC"),
    DESC("DESC");

### 使用示例

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

#####类示例（Permission）

    @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.批量查询

    //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：select id,type,method from permission where type = 5

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

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

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

3.分页查询

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

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

4.更新(使用LambdaUpdateWrapper/UpdateWrapper)

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

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

5.更新(使用map)

    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：update permission set method = 34  where type = 5

6.更新(使用Entity)

    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：update permission set method = 96  where type = 5

7.删除

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

    输出sql：delete from permission where id = 1

8.统计

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

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

9.嵌套条件查询

    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：select id,type,method from permission where type = 5  AND (method = 5  OR id >= 1 )

10.多字段组合in条件查询
    
    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：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.lambda多表联查单表查询

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

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

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

    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：SELECT COUNT(p1.sort) AS sort FROM permission p1 WHERE p1.type = 5

13.lambda多表联查批量查询

    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：SELECT * FROM permission p1 LEFT JOIN permission p2 ON p1.id = p2.id WHERE p1.type = 5  AND p2.method = 34

14.lambda多表联查分页查询

    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：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多表关联更新

    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：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多表关联删除

     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：DELETE p1, p2 FROM permission p1 LEFT JOIN permission p2 ON p1.id = p2.id WHERE p1.method = 2222  AND p2.method = 2222

16.多表联查分页查询

    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：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  


#### 参与贡献

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/)