流畅的orm让我发现我抵触的是mybatis而不是java背景介绍

  开发.net 也快10年了,到第三年的时候我已经渐渐瓶颈了,于是我在网上找各种资料但是大部分c#资料全是皮毛资料,稍微深一点点就再讲表达式expression,感觉完全没有那个深度,但是同时期的java讲解的都是基本原理,和框架思想,所以遇到瓶颈了我就会看java,我也是那个时候渐渐地掌握了两门语言,对我而言我学的是java的思想(计算机的思想)主要是数据结构和算法思想,这在同时期的c#资料是很难找到相同价值的。但是在使用java的3-4年时间里面那种恶心的orm让我也渐渐对其产生厌恶,因为java在那个时期对orm的需求仅仅只是能实现功能和结果集转对象,更多的精力都是在大数据方向上,所以对我们这些crud仔而言orm及其不友好,尤其是用过c#的orm后,但是在工作不久后除了mybatis就是mybatis-plus,这让业务开发的效率大大降低,bug率大大提升(c#的orm转到java的orm而言),强类型和复杂sql不能共存仿佛成为了javaer口中的理所应当。

  经过不断的努力终于在今年4月份正式发布easy-query orm,这款orm参考了大量的c#的orm框架 efcorefreesqlsqlsugar等,也参考了大量的java的orm框架。站在各位大佬的肩膀上让这个orm的开发周期大大降低,虽然java没有c#的expression(非官方的有但是稳定性和安全性等堪忧),但是通过另辟蹊径我也是找到了一条新的出路也算是让java在编写业务的时候可以流畅一把。

框架介绍

`easy-query`一款轻量级、高性能、强类型、易扩展符合C#开发者的JAVA自研ORM,拥有动态条件动态排序,自定义软删除,自定义条件拦截,单表多表,自定义sql,自定义函数,差异更新,分表分库(支持跨库跨表聚合查询),支持高性能加密解密字段模糊搜索等一系列功能

github地址 easy-query https://github.com/xuejmnet/easy-query

gitee地址 easy-query https://gitee.com/xuejm/easy-query

api预览

新版本api entity-query拥有非常流畅和语义化的api,并且继承所有之前的api可用,配合插件做到无需apt既可以动态变更代理对象实现无感开发编程

数据库对象

@Data@Table("t_topic")@EntityFileProxypublic class Topic implements ProxyEntityAvailable {    @Column(primaryKey = true)    private String id;    private Integer stars;    private String title;    private LocalDateTime createTime;    @Override    public Class proxyTableClass() {        return TopicProxy.class;    }}

按id查询

Topic topic = entityQuery.queryable(Topic.class)                .whereById("1").firstOrNull();==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ? LIMIT 1==> Parameters: 1(String)

自定义条件查询

List list = entityQuery.queryable(Topic.class)        .where(o -> {            o.id().eq("1");            o.createTime().le(LocalDateTime.now());        })        .toList();==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ? AND `create_time`  Parameters: 1(String),2023-12-16T14:17:04.065(LocalDateTime)

count查询

long count = entityQuery.queryable(Topic.class)        .where(o -> {            o.title().like("11");            o.createTime().le(LocalDateTime.now());        }).count();==> Preparing: SELECT COUNT(*) FROM `t_topic` WHERE `title` LIKE ? AND `create_time`  Parameters: %11%(String),2023-12-16T14:17:04.065(LocalDateTime)

返回自定义列

List list = entityQuery.queryable(Topic.class)        .where(o->{            o.title().like("123");            o.createTime().ge(LocalDateTime.of(2022,2,1,3,4));        })        .orderBy(o -> {            o.id().asc();            o.createTime().desc();        })        .select(o->o.FETCHER.id().title())//仅返回id和title        .toList();==> Preparing: SELECT t.`id`,t.`title` FROM `t_topic` t WHERE t.`title` LIKE ? AND t.`create_time` >= ? ORDER BY t.`id` ASC,t.`create_time` DESC==> Parameters: %123%(String),2022-02-01T03:04(LocalDateTime)List list = entityQuery.queryable(Topic.class)        .where(o->{            o.title().like("123");            o.createTime().ge(LocalDateTime.of(2022,2,1,3,4));        })        .orderBy(o -> {            o.id().asc();            o.createTime().desc();        })        .select(o->o.FETCHER.allFieldsExclude(o.id()))//返回所有字段除了id        .toList();==> Preparing: SELECT t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t WHERE t.`title` LIKE ? AND t.`create_time` >= ? ORDER BY t.`id` ASC,t.`create_time` DESC==> Parameters: %123%(String),2022-02-01T03:04(LocalDateTime)

分组

List list = entityQuery.queryable(Topic.class)        .where(o->{            o.title().like("123");            o.createTime().ge(LocalDateTime.of(2022,2,1,3,4));        })        .groupBy(o-> o.id())//多个用GroupBy.of(.....)        .select(Topic.class,(o,tr)->Select.of(                o.id(),                o.id().count().as(tr.stars())//count(id) as stars        ))        .toList();        ==> Preparing: SELECT t.`id`,COUNT(t.`id`) AS `stars` FROM `t_topic` t WHERE t.`title` LIKE ? AND t.`create_time` >= ? GROUP BY t.`id`==> Parameters: %123%(String),2022-02-01T03:04(LocalDateTime)

分页

EasyPageResult pageResult = entityQuery.queryable(Topic.class)        .where(o -> {            o.title().like("123");            o.createTime().ge(LocalDateTime.of(2022, 2, 1, 3, 4));        })        .orderBy(o -> {            o.id().asc();            o.createTime().desc();        })        .select(o -> o.FETCHER.id().title())        .toPageResult(1, 20);==> Preparing: SELECT COUNT(*) FROM `t_topic` t WHERE t.`title` LIKE ? AND t.`create_time` >= ?==> Parameters: %123%(String),2022-02-01T03:04(LocalDateTime)<== Time Elapsed: 2(ms) Preparing: SELECT t.`id`,t.`title` FROM `t_topic` t WHERE t.`title` LIKE ? AND t.`create_time` >= ? ORDER BY t.`id` ASC,t.`create_time` DESC LIMIT 20==> Parameters: %123%(String),2022-02-01T03:04(LocalDateTime)<== Time Elapsed: 3(ms)<== Total: 20

join多表查询

List list = entityQuery.queryable(Topic.class)        .leftJoin(Topic.class, (t, t1) -> {//第一个参数t表示第一个表,第二个参数t1表示第二个表            t.id().eq(t1.id());        })        .where((t, t1) -> {            t.title().like("11");            t1.createTime().le(LocalDateTime.of(2021, 1, 1, 1, 1));        }).select(Topic.class, (t, t1, tr) -> Select.of(//t表示sql的第一个表,t1表示第二个表,tr表示返回的结果匿名表                    t.FETCHER.id().stars(),//这两者写法是一样的`FETCHER`是为了链式你也可以不用fetcher                    t1.FETCHER.id().as(tr.title())            )).toList();==> Preparing: SELECT t.`id`,t.`stars`,t1.`id` AS `title` FROM `t_topic` t LEFT JOIN `t_topic` t1 ON t.`id` = t1.`id` WHERE t.`title` LIKE ? AND t1.`create_time`  Parameters: %11%(String),2021-01-01T01:01(LocalDateTime)

可能第一眼觉得select过于复杂

List list = entityQuery.queryable(Topic.class)        .leftJoin(Topic.class, (t, t1) -> {            t.id().eq(t1.id());        })        .where((t, t1) -> {            t.title().like("11");            t1.createTime().le(LocalDateTime.of(2021, 1, 1, 1, 1));        }).select(Topic.class, (t, t1, tr) -> Select.of(                    t.id(),//不使用`FETCHER`直接返回也是可以的                    t1.stars(),                    t1.id().as(tr.title())            )).toList();

排序

List list = entityQuery.queryable(Topic.class)        .leftJoin(Topic.class, (t, t1) -> {            t.id().eq(t1.id());        })        .orderBy((t, t1) -> {            t.id().asc();            t1.createTime().desc();        })        //查询t表的所有除了id和title,并且返回t1的title取别名为id        .select(Topic.class,(t,t1,tr)->t.allFieldsExclude(t.id(),t.title())._concat(t1.title().as(tr.id())))        .toList();==> Preparing: SELECT t.`stars`,t.`create_time`,t1.`title` AS `id` FROM `t_topic` t LEFT JOIN `t_topic` t1 ON t.`id` = t1.`id` ORDER BY t.`id` ASC,t1.`create_time` DESC<== Time Elapsed: 6(ms)<== Total: 101

子表统计查询

        List list = entityQuery.queryable(BlogEntity.class)                .where(o -> {                    //先对createTime进行格式化之后进行左匹配                    o.createTime().dateTimeFormat("yyyy-MM-dd").likeMatchLeft("2023");                })                .select(o -> {                    //构建子表统计                    SQLSelectAsExpression subQuery = Select.subQueryAs(() -> {                        return entityQuery.queryable(BlogEntity.class)                                .where(x -> {                                    x.id().eq(o.id());//条件就是主表的id和自己一样                                })                                .select(x -> x.id().count());                    }, o.createTime());//别名                    return Select.of(                            o.FETCHER.allFieldsExclude(o.title(), o.top()),                            subQuery                    );                }).toList();

生成的sql

-- 第1条sql数据SELECT    t.`id`,    t.`create_time`,    t.`update_time`,    t.`create_by`,    t.`update_by`,    t.`deleted`,    t.`content`,    t.`url`,    t.`star`,    t.`publish_time`,    t.`score`,    t.`status`,    t.`order`,    t.`is_top`,    (SELECT        COUNT(t1.`id`)     FROM        `t_blog` t1     WHERE        t1.`deleted` = false         AND t1.`id` = t.`id`) AS `create_time` FROM    `t_blog` t WHERE    t.`deleted` = false     AND DATE_FORMAT(t.`create_time`,'%Y-%m-%d') LIKE '2023%'

动态条件动态排序

后端管理往往需要复杂的动态条件组合和动态排序,稍不注意就会产生sql注入等问题

本框架给大伙带来的动态解决方案可以说非常完美,支持单表,多表,单字段排序,多字段排序,并且不会出现sql注入等一系列问题

动态查询1

//前段上传的json对象@Datapublic class SysUserQueryRequest {    private String name;    private String account;    private String departName;    private String phone;    private LocalDateTime createTimeBegin;    private LocalDateTime createTimeEnd;}//由前端上传jsonSysUserQueryRequest sysUserQueryRequest = new SysUserQueryRequest();sysUserQueryRequest.setName("小明");sysUserQueryRequest.setCreateTimeBegin(LocalDateTime.now().plusDays(-10));sysUserQueryRequest.setCreateTimeEnd(LocalDateTime.now());sysUserQueryRequest.setPhone("180");//快速实现分页查询 条件过滤默认非null不加入条件如果是字符串还需满足非空List pageResult = entityQuery.queryable(SysUser.class)                .filterConfigure(NotNullOrEmptyValueFilter.DEFAULT)//非null并且字符串非空即加入条件                .where(o -> {                        o.name().like(sysUserQueryRequest.getName());                        o.account().like(sysUserQueryRequest.getAccount());                        o.phone().like(sysUserQueryRequest.getPhone());                        o.departName().like(sysUserQueryRequest.getDepartName());                        o.createTime().rangeClosed(sysUserQueryRequest.getCreateTimeBegin(), sysUserQueryRequest.getCreateTimeEnd());                })                .toList();==> Preparing: SELECT `id`,`name`,`account`,`depart_name`,`phone`,`create_time` FROM `t_sys_user` WHERE `name` LIKE ? AND `phone` LIKE ? AND `create_time` >= ? AND `create_time`  Parameters: %小明%(String),%180%(String),2023-11-11T21:51:34.740(LocalDateTime),2023-11-21T21:51:34.740(LocalDateTime)

动态查询2

@Datapublic class SysUserQueryRequest {    @EasyWhereCondition    private String name;    @EasyWhereCondition    private String account;    @EasyWhereCondition    private String departName;    @EasyWhereCondition    private String phone;    @EasyWhereCondition(type = EasyWhereCondition.Condition.RANGE_LEFT_CLOSED,propName = "createTime" )    private LocalDateTime createTimeBegin;    @EasyWhereCondition(type = EasyWhereCondition.Condition.RANGE_RIGHT_CLOSED,propName = "createTime" )    private LocalDateTime createTimeEnd;}//由前端上传jsonSysUserQueryRequest sysUserQueryRequest = new SysUserQueryRequest();sysUserQueryRequest.setName("小明");sysUserQueryRequest.setCreateTimeBegin(LocalDateTime.now().plusDays(-10));sysUserQueryRequest.setCreateTimeEnd(LocalDateTime.now());sysUserQueryRequest.setPhone("180");//快速实现分页查询 动态对象条件EasyPageResult pageResult = entityQuery.queryable(SysUser.class)                        .whereObject(sysUserQueryRequest)                        .toPageResult(1, 10);==> Preparing: SELECT `id`,`name`,`account`,`depart_name`,`phone`,`create_time` FROM `t_sys_user` WHERE `name` LIKE ? AND `phone` LIKE ? AND `create_time` >= ? AND `create_time`  Parameters: %小明%(String),%180%(String),2023-11-11T21:51:34.740(LocalDateTime),2023-11-21T21:51:34.740(LocalDateTime)

动态查询3

最原始的方法

//由前端上传jsonSysUserQueryRequest sysUserQueryRequest = new SysUserQueryRequest();sysUserQueryRequest.setName("小明");sysUserQueryRequest.setCreateTimeBegin(LocalDateTime.now().plusDays(-10));sysUserQueryRequest.setCreateTimeEnd(LocalDateTime.now());sysUserQueryRequest.setPhone("180");//快速实现分页查询 手动处理是否需要添加到查询条件中List pageResult = entityQuery.queryable(SysUser.class)        .where(o -> {//条件里面判断是否要继续                o.name().like(EasyStringUtil.isNotBlank(sysUserQueryRequest.getName()),sysUserQueryRequest.getName());                o.account().like(EasyStringUtil.isNotBlank(sysUserQueryRequest.getAccount()),sysUserQueryRequest.getAccount());                o.phone().like(EasyStringUtil.isNotBlank(sysUserQueryRequest.getPhone()),sysUserQueryRequest.getPhone());                o.departName().like(EasyStringUtil.isNotBlank(sysUserQueryRequest.getDepartName()),sysUserQueryRequest.getDepartName());                o.createTime().rangeClosed(sysUserQueryRequest.getCreateTimeBegin() != null,sysUserQueryRequest.getCreateTimeBegin(),sysUserQueryRequest.getCreateTimeEnd() != null, sysUserQueryRequest.getCreateTimeEnd());        })        .toList();

动态排序

public class UISort implements ObjectSort {    private final Map sort;    public UISort(Map sort){        this.sort = sort;    }    @Override    public void configure(ObjectSortBuilder builder) {        for (Map.Entry s : sort.entrySet()) {            //自行判断key和value是否为null 因为是包装类型可能会出现npe            // key为需要排序的属性,value表示需要排序是不是asc            builder.orderBy(s.getKey(),s.getValue());        }    }}HashMap propertySortMap = new HashMap() {{    put("id", true);//id正序    put("title", false);//标题倒序}};String sql = easyQuery.queryable(BlogEntity.class)        .orderByObject(new UISort(propertySortMap))        .toSQL();Assert.assertEquals("SELECT `id`,`create_time`,`update_time`,`create_by`,`update_by`,`deleted`,`title`,`content`,`url`,`star`,`publish_time`,`score`,`status`,`order`,`is_top`,`top` FROM `t_blog` WHERE `deleted` = ? ORDER BY `id` ASC,`title` DESC",sql);

whereObject配合orderByObject将form表单查询的难度降低到了一个人人可用的水平

最后

可能有很多小伙伴会推荐我jpa或者jooq我想说如果我没能力那么我可能会选择他们,如果他们支持国产数据库我可能会选择他们,但是你我更愿意推荐easy-query因为我会聆听开发者的声音起码你叫的动我,我是一个在crud混的菜鸟开发,crud的困难,orm的困难必须是一个混迹在业务开发的程序员才能开发出来的好框架,在没开发出这个api的时候已经有很多小伙伴使用lambda的api进行了开发反向非常不错,期待您的使用。

easy-query

文档地址 https://xuejm.gitee.io/easy-query-doc/

GITHUB地址 https://github.com/xuejmnet/easy-query

GITEE地址 https://gitee.com/xuejm/easy-query