exists
java
boolean exists = QueryChain.of(sysUserMapper)
.select(SysUser::getId, SysUser::getUserName, SysUser::getRole_id)
.from(SysUser.class)
.join(SysUser.class, SysRole.class)
.like(SysUser::getUserName, "test")
.exists();
in 一张表的数据
java
List<SysUser> list = QueryChain.of(sysUserMapper)
.select(SysUser::getId, SysUser::getUserName, SysUser::getRole_id)
.from(SysUser.class)
.connect(queryChain -> {
queryChain.in(SysUser::getId, new SubQuery()
.select(SysUser::getId)
.from(SysUser.class)
.connect(subQuery -> {
subQuery.eq(SysUser::getId, queryChain.$().field(SysUser::getId));
})
.isNotNull(SysUser::getPassword)
.limit(1)
);
})
.list();
sql 如下:
SELECT
t.id, t.user_name, t.role_id
FROM
t_sys_user t
WHERE
t.id IN (SELECT
st.id
FROM
t_sys_user st
WHERE
st.id = t.id AND st.password IS NOT NULL
LIMIT 1 OFFSET 0)
select 去重
java
List<Integer> roleIds = QueryChain.of(sysUserMapper)
.selectDistinct()
.select(SysUser::getRole_id)
.from(SysUser.class)
.returnType(Integer.TYPE)
.list();
union 和 union all 查询
java
List<SysUser> list = QueryChain.of(sysUserMapper)
.select(SysUser::getRole_id, SysUser::getId)
.from(SysUser.class)
.eq(SysUser::getId, 1)
.union(Query.create()
.select(SysUser::getRole_id, SysUser::getId)
.from(SysUser.class)
.lt(SysUser::getId, 3)
.list();
java
List<SysUser> list = QueryChain.of(sysUserMapper)
.select(SysUser::getRole_id, SysUser::getId)
.from(SysUser.class)
.eq(SysUser::getId, 1)
.unionAll(Query.create()
.select(SysUser::getRole_id, SysUser::getId)
.from(SysUser.class)
.lt(SysUser::getId, 3)
.list();
with 操作
java
WithQuery withQuery = WithQuery.create("sub")
.select(SysRole.class)
.from(SysRole.class)
.eq(SysRole::getId, 1);
List<SysUser> list = QueryChain.of(sysUserMapper)
.with(withQuery)
.select(withQuery, SysRole::getId, c -> c.as("xx"))
.select(withQuery, "id")
.select(SysUser.class)
.from(SysUser.class)
.from(withQuery)
.eq(SysUser::getRole_id, withQuery.$outerField(SysRole::getId))
.orderBy(withQuery, SysRole::getId)
.list();
或 join
java
WithQuery withQuery = WithQuery.create("sub")
.select(SysRole.class)
.from(SysRole.class)
.eq(SysRole::getId, 1);
List<SysUser> list = QueryChain.of(sysUserMapper)
.with(withQuery)
.select(withQuery, SysRole::getId, c -> c.as("xx"))
.select(withQuery, "id")
.select(SysUser.class)
.from(SysUser.class)
.join(SysUser.class,withQuery,on->on.eq(SysUser::getRole_id, withQuery.$outerField(SysRole::getId)))
.orderBy(withQuery, SysRole::getId)
.list();
with recursive(递归) 操作
java
WithQuery withQuery = WithQuery.create("sub")
.recursive("n", "n2")
.select("1,1");
withQuery.unionAll(Query.create()
.select("n+1,n2+1")
.from(withQuery)
.lt(Methods.column("n"), 2)
.lt(Methods.column("n2"), 3)
);
List<Map<String, Object>> mapList = QueryChain.of(sysUserMapper)
.with(withQuery)
.selectAll()
.from(withQuery)
.returnMap()
.list();
WITH RECURSIVE sub(n , n2) AS ( SELECT 1,1 UNION ALL SELECT n+1,n2+1 FROM sub WHERE n < ? AND n2 < ?) SELECT * FROM sub
java
WithQuery withQuery = WithQuery.create("dept_with")
.recursive()
.select(SysUser::getId, SysUser::getRole_id)
.from(SysUser.class)
.eq(SysUser::getRole_id, 100);
withQuery.unionAll(Query.create()
.select(SysUser::getId, SysUser::getRole_id)
.from(SysUser.class)
.join(SysUser.class, withQuery, on -> on.eq(SysUser::getRole_id, withQuery.$outerField(SysUser::getRole_id)))
);
List<Map<String, Object>> mapList = QueryChain.of(sysUserMapper)
.with(withQuery)
.selectAll()
.from(withQuery)
.returnMap()
.list();
WITH RECURSIVE dept_with AS ( SELECT wt.id , wt.role_id FROM t_sys_user wt WHERE wt.role_id = ? UNION ALL SELECT t.id , t.role_id FROM t_sys_user t INNER JOIN dept_with ON t.role_id = dept_with.role_id) SELECT * FROM dept_with