放棄老舊的Mybatis，強型別替換字串，這款ORM框架很強！！！

2024-03-06碼農

大家好，我是磊哥。

1、背景

轉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中。

2、查詢

查詢第一條數據

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` = ? LIMIT1 ==> 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` = ? LIMIT1 ==> 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: SELECTCOUNT(1) FROM t_topic t WHERE t.`id`ISNOTNULL <== Total: 1 ==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time`FROM t_topic t WHERE t.`id`ISNOTNULLLIMIT20 <== 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 LEFTJOIN`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 LEFTJOIN`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 WHEREEXISTS (SELECT1FROM`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 LEFTJOIN t_blog t1 ON t1.`deleted` = ? AND t.`id` = t1.`id`WHERE t.`id` = ? LIMIT1 ==> 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) { thrownew 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(); SELECTid,name,edition,price,store_id FROM t_book_test WHEREregexp_like(price,?)