exists

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 一张表的数据

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 去重

List<Integer> roleIds = QueryChain.of(sysUserMapper)
        .selectDistinct()
        .select(SysUser::getRole_id)
        .from(SysUser.class)
        .returnType(Integer.TYPE)
        .list();

union 和 union all 查询

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();

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 操作

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.$(withQuery, SysRole::getId))
    .orderBy(withQuery, SysRole::getId)
    .list();

或 join

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.$(withQuery, SysRole::getId)))
    .orderBy(withQuery, SysRole::getId)
    .list();

with recursive(递归) 操作

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

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.$(withQuery, 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