放棄老舊的Mybatis,強型別替換字串,這是一款你不應該錯過的ORM

薛家明發表於2023-10-30
MyBatis型別字串ORM

一款輕量級、高效能、強型別、易擴充套件符合C#開發者的JAVA自研ORM

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

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

背景

轉java後的幾年時間裡面一直在尋找一個類.net的orm,不需要很特別的功能僅希望90%的場景都可以透過強型別語法來編寫符合直覺的sql,來運算元據庫編寫業務,但是一直沒有找到僅Mybatis-Plus的單表讓我在最初的時間段內看到了希望，不過隨著使用的深入越發的發現Mybatis-Plus只是一個殘缺的orm,因為大部分場景不支援表示式或者強型別會導致它本身的很多特性都無法使用,比如你配置了軟刪除,那麼如果你遇到了join不好意思軟刪除你需要自己處理,很多配置會隨著手寫sql的加入變的那麼的不智慧,甚至表現得和sqlhelper沒區別,別說Mybatis-Plus-Join了,這玩意更逆天,如果一個orm想寫出符合自己的sql需要不斷地除錯嘗試來“拼接”出想要的語句那麼他就稱不上一個ORM連sqlbuilder也算不上Mybatis-Plus-Join就是這樣.

所以在4-5年後我終於忍受不了了,決定自研一款orm,參考現有.net生態十分完整的orm程式碼,和幾乎完美符合擴充套件性和語義性的鏈式表示式讓.net的orm帶到java中。

查詢

查詢第一條資料

Topic topic = easyQuery.queryable(Topic.class)
                    .where(o -> o.eq(Topic::getId, "123"))
                    .firstOrNull();

==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ? LIMIT 1
==> Parameters: 123(String)
<== Time Elapsed: 2(ms)
<== Total: 0

查詢並斷言至多一條資料

Topic topic = easyQuery.queryable(Topic.class)
                    .where(o -> o.eq(Topic::getId, "123"))
                    .singleOrNull();

==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ?
==> Parameters: 123(String)
<== Time Elapsed: 2(ms)
<== Total: 0

查詢多條資料

List<Topic> topics = easyQuery.queryable(Topic.class)
                    .where(o -> o.eq(Topic::getId, "123"))
                    .toList();

==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ?
==> Parameters: 123(String)
<== Time Elapsed: 2(ms)
<== Total: 0

查詢自定義列

Topic topic = easyQuery.queryable(Topic.class)
                    .where(o -> o.eq(Topic::getId, "1"))
                    .select(o->o.column(Topic::getId).column(Topic::getTitle))
                    .firstOrNull();

==> Preparing: SELECT `id`,`title` FROM `t_topic` WHERE `id` = ? LIMIT 1
==> Parameters: 1(String)
<== Time Elapsed: 2(ms)
<== Total: 1

分頁查詢

 EasyPageResult<Topic> topicPageResult = easyQuery
                .queryable(Topic.class)
                .where(o -> o.isNotNull(Topic::getId))
                .toPageResult(1, 20);

==> Preparing: SELECT  COUNT(1)  FROM t_topic t WHERE t.`id` IS NOT NULL
<== Total: 1
==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM t_topic t WHERE t.`id` IS NOT NULL LIMIT 20
<== Total: 20

將表示式轉成匿名錶巢狀查詢

//  SELECT `id`,`title` FROM `t_topic` WHERE `id` = ? 
Queryable<Topic> query = easyQuery.queryable(Topic.class)
                    .where(o -> o.eq(Topic::getId, "1"))
                    .select(Topic.class, o -> o.column(Topic::getId).column(Topic::getTitle));

List<Topic> list = query.leftJoin(Topic.class, (t, t1) -> t.eq(t1, Topic::getId, Topic::getId))
                    .where((t, t1) -> {
                        t1.eq(Topic::getId, "123");
                        t.eq(Topic::getId, "456");
                    }).toList();

SELECT t1.`id`,t1.`title` 
FROM (SELECT t.`id`,t.`title` FROM `t_topic` t WHERE t.`id` = ?) t1 
LEFT JOIN `t_topic` t2 ON t1.`id` = t2.`id` WHERE t2.`id` = ? AND t1.`id` = ?

==> Preparing: SELECT t1.`id`,t1.`title` FROM (SELECT t.`id`,t.`title` FROM `t_topic` t WHERE t.`id` = ?) t1 LEFT JOIN `t_topic` t2 ON t1.`id` = t2.`id` WHERE t2.`id` = ? AND t1.`id` = ?
==> Parameters: 1(String),123(String),456(String)
<== Time Elapsed: 5(ms)
<== Total: 0

子查詢

//SELECT * FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?
 Queryable<BlogEntity> subQueryable = easyQuery.queryable(BlogEntity.class)
                .where(o -> o.eq(BlogEntity::getId, "1"));


List<Topic> x = easyQuery
        .queryable(Topic.class).where(o -> o.exists(subQueryable.where(q -> q.eq(o, BlogEntity::getId, Topic::getId)))).toList();


==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t WHERE EXISTS (SELECT 1 FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ? AND t1.`id` = t.`id`)
==> Parameters: false(Boolean),1(String)
<== Time Elapsed: 3(ms)
<== Total: 1

多表join查詢

Topic topic = easyQuery
                .queryable(Topic.class)
                .leftJoin(BlogEntity.class, (t, t1) -> t.eq(t1, Topic::getId, BlogEntity::getId))
                .where(o -> o.eq(Topic::getId, "3"))
                .firstOrNull();

==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM t_topic t LEFT JOIN t_blog t1 ON t1.`deleted` = ? AND t.`id` = t1.`id` WHERE t.`id` = ? LIMIT 1
==> Parameters: false(Boolean),3(String)
<== Total: 1

流式結果大資料迭代返回

try(JdbcStreamResult<BlogEntity> streamResult = easyQuery.queryable(BlogEntity.class).where(o -> o.le(BlogEntity::getStar, 100)).orderByAsc(o -> o.column(BlogEntity::getCreateTime)).toStreamResult()){

            LocalDateTime begin = LocalDateTime.of(2020, 1, 1, 1, 1, 1);
            int i = 0;
            for (BlogEntity blog : streamResult.getStreamIterable()) {
                String indexStr = String.valueOf(i);
                Assert.assertEquals(indexStr, blog.getId());
                Assert.assertEquals(indexStr, blog.getCreateBy());
                Assert.assertEquals(begin.plusDays(i), blog.getCreateTime());
                Assert.assertEquals(indexStr, blog.getUpdateBy());
                Assert.assertEquals(begin.plusDays(i), blog.getUpdateTime());
                Assert.assertEquals("title" + indexStr, blog.getTitle());
//            Assert.assertEquals("content" + indexStr, blog.getContent());
                Assert.assertEquals("http://blog.easy-query.com/" + indexStr, blog.getUrl());
                Assert.assertEquals(i, (int) blog.getStar());
                Assert.assertEquals(0, new BigDecimal("1.2").compareTo(blog.getScore()));
                Assert.assertEquals(i % 3 == 0 ? 0 : 1, (int) blog.getStatus());
                Assert.assertEquals(0, new BigDecimal("1.2").multiply(BigDecimal.valueOf(i)).compareTo(blog.getOrder()));
                Assert.assertEquals(i % 2 == 0, blog.getIsTop());
                Assert.assertEquals(i % 2 == 0, blog.getTop());
                Assert.assertEquals(false, blog.getDeleted());
                i++;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

==> Preparing: 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` = ? AND `star` <= ? ORDER BY `create_time` ASC
==> Parameters: false(Boolean),100(Integer)
<== Time Elapsed: 6(ms)

自定義VO返回

List<QueryVO> list = easyQuery
                .queryable(Topic.class)
                //第一個join採用雙引數,引數1表示第一張表Topic 引數2表示第二張表 BlogEntity
                .leftJoin(BlogEntity.class, (t, t1) -> t.eq(t1, Topic::getId, BlogEntity::getId))
                //第二個join採用三引數,引數1表示第一張表Topic 引數2表示第二張表 BlogEntity 第三個引數列示第三張表 SysUser
                .leftJoin(SysUser.class, (t, t1, t2) -> t.eq(t2, Topic::getId, SysUser::getId))
                .where(o -> o.eq(Topic::getId, "123"))//單個條件where引數為主表Topic
                //支援單個引數或者全引數,全引數個數為主表+join表個數 鏈式寫法期間可以透過then來切換操作表
                .where((t, t1, t2) -> t.eq(Topic::getId, "123").then(t1).like(BlogEntity::getTitle, "456")
                        .then(t2).eq(BaseEntity::getCreateTime, LocalDateTime.now()))
                //如果不想用鏈式的then來切換也可以透過lambda 大括號方式執行順序就是程式碼順序,預設採用and連結
                .where((t, t1, t2) -> {
                    t.eq(Topic::getId, "123");
                    t1.like(BlogEntity::getTitle, "456");
                    t1.eq(BaseEntity::getCreateTime, LocalDateTime.now());
                })
                .select(QueryVO.class, (t, t1, t2) ->
                        //將第一張表的所有屬性的列對映到vo的列名上,第一張表也可以透過columnAll將全部欄位對映上去
                        // ,如果後續可以透過ignore方法來取消掉之前的對映關係
                        t.column(Topic::getId)
                                .then(t1)
                                //將第二張表的title欄位對映到VO的field1欄位上
                                .columnAs(BlogEntity::getTitle, QueryVO::getField1)
                                .then(t2)
                                //將第三張表的id欄位對映到VO的field2欄位上
                                .columnAs(SysUser::getId, QueryVO::getField2)
                ).toList();

表單條件動態查詢

BlogQuery2Request query = new BlogQuery2Request();
query.setContent("標題");
query.setPublishTimeEnd(LocalDateTime.now());
query.setStatusList(Arrays.asList(1,2));

List<BlogEntity> queryable = easyQuery.queryable(BlogEntity.class)
        .whereObject(query).toList();


==> Preparing: 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` = ? AND `content` LIKE ? AND `publish_time` <= ? AND `status` IN (?,?)
==> Parameters: false(Boolean),%標題%(String),2023-07-14T22:37:47.880(LocalDateTime),1(Integer),2(Integer)
<== Time Elapsed: 2(ms)
<== Total: 0

基本型別結果返回

List<String> list = easyQuery.queryable(Topic.class)
                .where(o -> o.eq(Topic::getId, "1"))
                .select(String.class, o -> o.column(Topic::getId))
                .toList();

==> Preparing: SELECT t.`id` FROM `t_topic` t WHERE t.`id` = ?
==> Parameters: 1(String)
<== Time Elapsed: 2(ms)
<== Total: 1

分組查詢


List<TopicGroupTestDTO> topicGroupTestDTOS = easyQuery.queryable(Topic.class)
                .where(o -> o.eq(Topic::getId, "3"))
                .groupBy(o->o.column(Topic::getId))
                .select(TopicGroupTestDTO.class, o->o.columnAs(Topic::getId,TopicGroupTestDTO::getId).columnCount(Topic::getId,TopicGroupTestDTO::getIdCount))
                .toList();


==> Preparing: SELECT t.`id` AS `id`,COUNT(t.`id`) AS `idCount` FROM t_topic t WHERE t.`id` = ? GROUP BY t.`id`
==> Parameters: 3(String)
<== Total: 1

//groupKeysAs快速選擇並且給別名
List<TopicGroupTestDTO> topicGroupTestDTOS = easyQuery.queryable(Topic.class)
                .where(o -> o.eq(Topic::getId, "3"))
                .groupBy(o->o.column(Topic::getId))
                .select(TopicGroupTestDTO.class, o->o.groupKeysAs(0, TopicGroupTestDTO::getId).columnCount(Topic::getId,TopicGroupTestDTO::getIdCount))
                .toList();


==> Preparing: SELECT t.`id` AS `id`,COUNT(t.`id`) AS `idCount` FROM t_topic t WHERE t.`id` = ? GROUP BY t.`id`
==> Parameters: 3(String)
<== Total: 1

原生sql片段

String sql = easyQuery.queryable(H2BookTest.class)
            .where(o -> o.sqlNativeSegment("regexp_like({0},{1})", it -> it.expression(H2BookTest::getPrice)
                            .value("^Ste(v|ph)en$")))
            .select(o -> o.columnAll()).toSQL();

SELECT id,name,edition,price,store_id FROM t_book_test WHERE regexp_like(price,?)

資料庫函式列

使用者儲存的資料是base64結果,但是記憶體中是普通的字串或者其他資料,easy-query提供了無感的使用，譬如pgsql的geo等地理相關資料
資料庫函式列 https://xuejm.gitee.io/easy-query-doc/guide/adv/column-sql-func-auto.html

支援like的高效能加密解密

用來實現支援like模式的高效能加密解密,支援emoji和非emoji兩種使用者可以自行選擇
資料庫加密解密 https://xuejm.gitee.io/easy-query-doc/guide/adv/column-encryption.html

更多功能比如資料追蹤差異更新,資料原子更新,分庫分表(老行當了肯定要支援),一款本無依賴雙語(java/kotlin)都支援的高效能orm

相關文章