代码拉取完成,页面将自动刷新
同步操作将从 gzghde/sqltoy-plus 强制同步,此操作会覆盖自 Fork 仓库以来所做的任何修改,且无法恢复!!!
确定后同步将在后台操作,完成时将刷新页面,请耐心等待。
1.对象化SQL操作:通过将SQL操作封装为对象操作。
2.函数式编程支持:支持Lambda表达式,使得条件拼装更加灵活和动态。
3.多表关联支持:简化多表关联操作,支持对象化多表关联操作。
4.简化数据库操作:通过提供通用的数据库操作方法。
5.分页查询支持:内置的分页查询支持,方便实现大数据集的分页浏览。
6.灵活的查询构建:通过链式调用构建查询条件,使得查询构建过程更加直观。
7.易于集成:通过扩展类和注解,可以方便地集成到现有的Spring项目中。
使用之前需要对sqltoy有一定的了解
maven依赖(以5.6.22版本为例):
<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启动注解
@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.MultiWrapper中lambda单表查询
MultiWrapper multiQuery = Wrappers.lambdaMultiWrapper(Permission.class)
.select()
.from(Permission.class).where()
.eq(Permission::getType, 5)
;
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
17.删改查链式调用
List<Period> periods = sqlToyHelperDao.lambdaQueryChain(Period.class)
.eq(Period::getStatus, queryDto.getStatus())
.eq(Period::getPeriodType, queryDto.getPeriodType())
.list();
输出sql:
SELECT * FROM Period WHERE status = 1 and periodType = 2;
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。