easy-query

🚀 java下唯一一款同时支持强类型对象关系查询和强类型SQL语法查询的ORM,拥有对象模型筛选、隐式子查询、隐式join、显式子查询、显式join,支持Java/Kotlin

//筛选用户名称包含小明的
List<SysUser> users = easyEntityQuery.queryable(SysUser.class)
    .where(s->s.name().like("小明"))
    .toList()
//筛选用户名称包含小明并且是2020年以前创建的
List<SysUser> users = easyEntityQuery.queryable(SysUser.class)
.where(s->{
        s.name().like("小明");
        s.createTime().lt(LocalDateTime.of(2020,1,1,0,0))
})
.toList()
//筛选用户名称包含小明的或者名称包含小红的
List<SysUser> users = easyEntityQuery.queryable(SysUser.class)
    .where(s->{
        s.or(()->{
            s.name().like("小明");
            s.name().like("小红");
        })

    })
    .toList()

//匿名对象
List<Draft3<String, LocalDateTime, String>> userInfo = easyEntityQuery.queryable(SysUser.class)
        .leftJoin(SysUserAddress.class, (user, addr) -> user.id().eq(addr.userId()))
        .where((user, addr) -> {
            user.name().like("小明");
            addr.city().eq("杭州");
        })
        .select((user, addr) -> Select.DRAFT.of(
                user.id(),
                user.createTime(),
                addr.area()
        )).toList();


List<UserDTO> userInfo = easyEntityQuery.queryable(SysUser.class)
        .leftJoin(SysUserAddress.class, (user, addr) -> user.id().eq(addr.userId()))
        .where((user, addr) -> {
            user.name().like("小明");
            addr.city().eq("杭州");
        })
        .select(UserDTO.class,(user, addr) -> Select.DRAFT.of(
                user.FETCHER.id().createTime(),
                addr.area()
        )).toList();

//user和address一对一
//查询杭州或绍兴的用户
List<SysUser> userInHz = easyEntityQuery.queryable(SysUser.class)
                .where(s -> {
                    //隐式子查询会自动join用户表和地址表
                    s.or(()->{
                      //
                        s.address().city().eq("杭州市");
                        s.address().city().eq("绍兴市");
                    });
                }).toList();

//user和role多对多
//筛选用户角色是管理员的
List<SysUser> adminUsers = easyEntityQuery.queryable(SysUser.class)
            .where(s -> {
                //筛选条件为角色集合里面有角色名称叫做管理员的
                s.roles().where(role -> {
                    role.name().eq("管理员");
                }).any();
            }).toList();

//匿名返回用户id和用户所拥有的角色数量
List<Draft2<String, Long>> userIdAndRoleCount = easyEntityQuery.queryable(SysUser.class)
        .where(user -> user.name().like("小明"))
        .select(user -> Select.DRAFT.of(
                user.id(),
                user.roles().count()
        )).toList();


List<SysUser> userIn = easyEntityQuery.queryable(SysUser.class)
        .where(user -> {
            user.id().in(
                    easyEntityQuery.queryable(SysRole.class)
                            .select(s -> s.id())
            );
        }).toList();


List<SysUser> userExists = easyEntityQuery.queryable(SysUser.class)
        .where(user -> {
            
            user.expression().exists(()->{
                return easyEntityQuery.queryable(SysRole.class)
                        .where(role -> {
                            role.name().eq("管理员");
                            role.id().eq(user.id());
                        });
                    });
        }).toList();

  
//匿名返回用户id和用户所拥有的角色数量
List<Draft2<String, Long>> userIdAndRoleCount1 = easyEntityQuery.queryable(SysUser.class)
        .where(user -> user.name().like("小明"))
        .select(user -> Select.DRAFT.of(
                user.id(),
                user.expression().subQuery(() -> {
                    return easyEntityQuery.queryable(SysRole.class)
                            .where(r -> {
                                r.expression().exists(()->{
                                    return easyEntityQuery.queryable(UserRole.class)
                                            .where(u -> {
                                                u.roleId().eq(r.id());
                                                u.userId().eq(user.id());
                                            });
                                });
                            })
                            .selectCount();
                })
        )).toList();


//可以直接筛选出结构化DTO
List<StructSysUserDTO> users = easyEntityQuery.queryable(SysUser.class)
        .where(s -> s.name().like("小明"))
        .selectAutoInclude(StructSysUserDTO.class).toList();


{
	"id": "...",
	"createTime": "...",
	"roles": [{
		"id": "...",
		"name": "",
		"menus": [{
			"id": "....",
			"name": "...."
		}, {
			"id": "....",
			"name": "...."
		}]
	}]
}

//返回用户角色和菜单

@Data
public class StructSysUserDTO {

    private String id;
    private String name;
    private LocalDateTime createTime;
    @Navigate(value = RelationTypeEnum.ManyToMany)
    private List<InternalRoles> roles;
    @Data
    public static class InternalRoles {
        private String id;
        //....
        @Navigate(value = RelationTypeEnum.ManyToMany)
        private List<InternalMenus> menus;
    }
    @Data
    public static class InternalMenus {
        private String id;
        private String name;
        //....
    }
}


快速返回用户拥有的菜单,因为用户和菜单中间由角色进行关联并且两者都是多对多所以如果需要自行实现那么是非常麻烦的一件事情

用户和菜单之间隔着角色的多对多所以如果想要获取用户的菜单id直接可以通过这种方式快速筛选

方式1 仅获取用户拥有的菜单id

List<String> menuIds = easyEntityQuery.queryable(SysUser.class)
        .where(s -> s.name().like("小明"))
        .toList(s -> s.roles().flatElement().menus().flatElement().id());


方式2 仅获取用户拥有的菜单id和菜单名称


List<SysMenu> menuIdNames = easyEntityQuery.queryable(SysUser.class)
        .where(s -> s.name().like("小明"))
        .toList(s -> s.roles().flatElement().menus().flatElement(x->x.FETCHER.id().name()));

List<SysUserFlatDTO> users = easyEntityQuery.queryable(SysUser.class)
        .where(s -> s.name().like("小明"))
        .selectAutoInclude(SysUserFlatDTO.class).toList();

@Data
public class SysUserFlatDTO {
    private String id;
    private String name;
    private LocalDateTime createTime;
    
    //穿透获取用户下的roles下的menus下的id 如果穿透获取的是非基本类型那么对象只能是数据库对象而不是dto对象
    @NavigateFlat(value = RelationMappingTypeEnum.ToMany,mappingPath = {
            SysUser.Fields.roles,
            SysRole.Fields.menus,
            SysMenu.Fields.id
    })
    private List<String> menuIds;

//非基本对象也可以直接返回数据库对象
//    @NavigateFlat(value = RelationMappingTypeEnum.ToMany,mappingPath = {
//            SysUser.Fields.roles,
//            SysRole.Fields.menus
//    })
//    private List<SysMenu> menu;

    @NavigateFlat(value = RelationMappingTypeEnum.ToMany,mappingPath = {
            SysUser.Fields.roles,
            SysMenu.Fields.id
    })
    private List<String> roleIds;
}

//原生sql执行

List<SysUser> filterTime = easyEntityQuery.queryable(SysUser.class)
        .where(user -> {
            user.expression().sql("{0} > {1}", c -> {
                c.value(LocalDateTime.now()).expression(user.createTime());
            });
        }).toList();

//原生sql片段


List<Draft2<String, String>> idAndName = easyEntityQuery.queryable(SysUser.class)
        .where(user -> {
            user.createTime().gt(LocalDateTime.now());
        }).select(user -> Select.DRAFT.of(
                user.id(),
                user.expression().sqlType("IFNULL({0},'')", c -> c.expression(user.name())).setPropertyType(String.class)
        )).toList();