xbatis

Appearance

支持开源

exists子查询 ​

单条件exists子查询 ​

java
 int count = QueryChain.of(sysUserMapper)
        .exists(SysUser::getRole_id, SysRole::getId)
        .count();

多条件exists子查询 ​

java
int count = QueryChain.of(sysUserMapper)
    .exists(SysUser::getRole_id,SysRole::getId,(query, existsQuery) -> {
        existsQuery.eq(SysUser::getId, 1);
    })
    .count();

not exists子查询 ​

单条件not exists子查询 ​

java
 int count = QueryChain.of(sysUserMapper)
        .notExists(SysUser::getRole_id, SysRole::getId)
        .count();

多条件not exists子查询 ​

java
int count = QueryChain.of(sysUserMapper)
    .notExists(SysUser::getRole_id,SysRole::getId, (query, existsQuery) -> {
        existsQuery.eq(SysUser::getId, 1);
    })
    .count();

in 子查询的数据 ( 子查询没有 where 情况) ​

这里是查已经被用户表使用的所有角色

java
List<SysRole> list = QueryChain.of(sysRoleMapper)
    .in(SysRole::getId, SysUser::getRoleId)
    .list();

in 子查询的数据 ( 子查询 还有 其他 where 情况) ​

这里是查已经被用户表使用并且用户密码是123456的所有角色

java
List<SysRole> list = QueryChain.of(sysRoleMapper)
    .in(SysRole::getId, SysUser::getRoleId, (query, inQuery) -> {
        inQuery.like(SysUser::getPassword, "123456");
    })
    .list();

in 子查询的数据 ( 子查询 与 父级查询有 参数where交叉) ​

方法1：自己追加

java
List<SysRole> list = QueryChain.of(sysRoleMapper)
    .in(SysRole::getId, SysUser::getRole_id, (query, inQuery) -> {
        inQuery.eq(SysUser::getRole_id, query.$(SysRole::getId));
    })
    .list();

方法2：框架追加

java
List<SysRole> list = QueryChain.of(sysRoleMapper)
    .in(SysRole::getId, SysUser::getRoleId, SysRole::getId, SysUser::getRoleId)
    .list();

.in(SysRole::getId, SysUser::getRoleId, SysRole::getId, SysUser::getRoleId) 中 第1个是 需要in的字段；第2个是需要select的字段，第3个和第4是父query与子查询需要eq的字段

in 子查询的数据 ( 子查询 与 父级查询有 参数where交叉，还有额外条件的情况) ​

这里只比（方法2：框架追加）多了一个lamdba 操作

java
List<SysRole> list = QueryChain.of(sysRoleMapper)
    .in(SysRole::getId, SysUser::getRoleId, SysRole::getId, SysUser::getRoleId,(query, inQuery) -> {
        inQuery.like(SysUser::getPassword, "123456");
    })
    .list();

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