代码拉取完成,页面将自动刷新
同步操作将从 gzghde/sqltoy-plus 强制同步,此操作会覆盖自 Fork 仓库以来所做的任何修改,且无法恢复!!!
确定后同步将在后台操作,完成时将刷新页面,请耐心等待。
sqltoy增强框架,sql对象化,支持lamada表达式形式条件拼装,支持多表关联。
使用之前需要对sqltoy有一定的了解
函数式编程思想简化一些数据库通用操作
maven依赖(以5.6.8版本为例):
<dependency>
<groupId>com.sagframe</groupId>
<artifactId>sagacity-sqltoy-spring-starter</artifactId>
<!-- jdk1.8 用 5.6.8.jre8 -->
<version>5.6.8</version>
</dependency>
<dependency>
<groupId>com.sagframe</groupId>
<artifactId>sqltoy-plus-core</artifactId>
<!-- jdk1.8 用 5.6.8.jre8 -->
<version>5.6.8</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启动注解
@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
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。