流暢的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框架 efcore、freesql、sqlsugar等，也參考了大量的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")
@EntityFileProxy
public class Topic implements ProxyEntityAvailable<Topic , TopicProxy> {
@Column(primaryKey = true)
private String id;
private Integer stars;
private String title;
private LocalDateTime createTime;
@Override
public Class<TopicProxy> 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<Topic> 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<Topic> 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<Topic> 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<Topic> 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<Topic> 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)
<== Total: 1
==> 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<Topic> 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<Topic> 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<Topic> 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<BlogEntity> 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物件
@Data
public class SysUserQueryRequest {
private String name;
private String account;
private String departName;
private String phone;
private LocalDateTime createTimeBegin;
private LocalDateTime createTimeEnd;
}
//由前端上傳json
SysUserQueryRequest sysUserQueryRequest = new SysUserQueryRequest();
sysUserQueryRequest.setName("小明");
sysUserQueryRequest.setCreateTimeBegin(LocalDateTime.now().plusDays(-10));
sysUserQueryRequest.setCreateTimeEnd(LocalDateTime.now());
sysUserQueryRequest.setPhone("180");
//快速實現分頁查詢 條件過濾預設非null不加入條件如果是字串還需滿足非空
List<SysUser> 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` <= ? LIMIT 10
==> Parameters: %小明%(String),%180%(String),2023-11-11T21:51:34.740(LocalDateTime),2023-11-21T21:51:34.740(LocalDateTime)
動態查詢2
@Data
public 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;
}
//由前端上傳json
SysUserQueryRequest sysUserQueryRequest = new SysUserQueryRequest();
sysUserQueryRequest.setName("小明");
sysUserQueryRequest.setCreateTimeBegin(LocalDateTime.now().plusDays(-10));
sysUserQueryRequest.setCreateTimeEnd(LocalDateTime.now());
sysUserQueryRequest.setPhone("180");
//快速實現分頁查詢 動態物件條件
EasyPageResult<SysUser> 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` <= ? LIMIT 10
==> Parameters: %小明%(String),%180%(String),2023-11-11T21:51:34.740(LocalDateTime),2023-11-21T21:51:34.740(LocalDateTime)
動態查詢3
最原始的方法
//由前端上傳json
SysUserQueryRequest sysUserQueryRequest = new SysUserQueryRequest();
sysUserQueryRequest.setName("小明");
sysUserQueryRequest.setCreateTimeBegin(LocalDateTime.now().plusDays(-10));
sysUserQueryRequest.setCreateTimeEnd(LocalDateTime.now());
sysUserQueryRequest.setPhone("180");
//快速實現分頁查詢 手動處理是否需要新增到查詢條件中
List<SysUser> 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<String, Boolean> sort;
public UISort(Map<String,Boolean> sort){
this.sort = sort;
}
@Override
public void configure(ObjectSortBuilder builder) {
for (Map.Entry<String, Boolean> s : sort.entrySet()) {
//自行判斷key和value是否為null 因為是包裝型別可能會出現npe
// key為需要排序的屬性,value表示需要排序是不是asc
builder.orderBy(s.getKey(),s.getValue());
}
}
}
HashMap<String, Boolean> propertySortMap = new HashMap<String, Boolean>() {{
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/