这应该是java最好用的orm之一了
生活随笔
收集整理的這篇文章主要介紹了
这应该是java最好用的orm之一了
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
這應該是java最好用的orm之一了
說起orm大家肯定都不會陌生,作者是一個.net菜鳥。并且是在.net繁榮的orm圈子下成長的,所以這次給大家帶來的是媲美efcore,freesql,sqlsugar的java的orm.如果你是一位.net轉java的開發,或者是一名需要經常和數據庫打交道的開發者和作者一樣是一名crud仔那么這個orm肯定是你不應該錯過的,我愿稱之為java最好用的orm之一。
介紹
easy-query
文檔地址 https://xuejm.gitee.io/easy-query-doc/
GITHUB地址 https://github.com/xuejmnet/easy-query
GITEE地址 https://gitee.com/xuejm/easy-query
復雜sql一
話不多說來一個復雜sql
SELECT
YEAR(日期) AS 年份，
MONTH(日期) AS 月份
SUM(收入) AS 月收入
FROM
your_table
WHERE
日期 >= CURDATE()- INTERVAL 3 MONTH
GROUP BY
年份，月份
ORDER BY
年份，月份;
用java的寫法寫sql,并且函數自適應easy-query支持的所有數據庫,切庫0成本
List<Draft3<Integer, Integer, Integer>> list = easyEntityQuery.queryable(BlogEntity.class)
.where(o -> o.createTime().gt(o._now().plusMonths(-3))) //WHERE 日期 >= CURDATE()- INTERVAL 3 MONTH
.groupBy(o -> GroupKeys.TABLE1.of(o.createTime().year(), o.createTime().month()))//GROUP BY 年份，月份
.orderBy(o -> {
o.key1().asc(); // ORDER BY 年份，月份;
o.key2().asc();
}).selectDraft(o -> Select.draft( //采用草稿類型
o.key1(), //YEAR(日期) AS 年份，
o.key2(), //MONTH(日期) AS 月份
o.sum(o.group().star()) //SUM(收入) AS 月收入
)).toList();
==> Preparing: SELECT YEAR(t.`create_time`) AS `value1`,MONTH(t.`create_time`) AS `value2`,SUM(t.`star`) AS `value3` FROM `t_blog` t WHERE t.`deleted` = ? AND t.`create_time` > date_add(NOW(), interval (?) month) GROUP BY YEAR(t.`create_time`),MONTH(t.`create_time`) ORDER BY YEAR(t.`create_time`) ASC,MONTH(t.`create_time`) ASC
==> Parameters: false(Boolean),-3(Integer)
<== Time Elapsed: 4(ms)
<== Total: 0
復雜sql二
select a.id,a.name
from table a
where (select count(*) as num from table b where b.box_id=a.id ) = 0
//條件里面不直接使用列
List<Draft2<String, String>> list = easyEntityQuery.queryable(BlogEntity.class)
.where(o -> {
Query<Long> longQuery = easyEntityQuery.queryable(Topic.class)
.where(x -> x.id().eq(o.id())).selectCount();//創建子查詢的count然后和0常量進行比較
o.SQLParameter().valueOf(0L)
.eq(longQuery);
}).selectDraft(o -> Select.draft(
o.id(),
o.url()
)).toList();
==> Preparing: SELECT t.`id` AS `value1`,t.`url` AS `value2` FROM `t_blog` t WHERE t.`deleted` = ? AND ? = (SELECT COUNT(*) FROM `t_topic` t1 WHERE t1.`id` = t.`id`)
==> Parameters: false(Boolean),0(Long)
復雜sql三
//select 查詢子表聯結
select a,b,c,(select count(*) from a t1 where t.id=b.id) as xx from b
List<Draft3<String, String, Long>> list = easyEntityQuery.queryable(BlogEntity.class)
.where(o -> {
o.id().eq("123");
}).selectDraft(o -> Select.draft(
o.id(),
o.url(),
o.subQuery(() -> easyEntityQuery.queryable(Topic.class).where(x -> x.id().eq(o.id())).selectCount())
)).toList();
==> Preparing: SELECT t.`id` AS `value1`,t.`url` AS `value2`,(SELECT COUNT(*) FROM `t_topic` t1 WHERE t1.`id` = t.`id`) AS `value3` FROM `t_blog` t WHERE t.`deleted` = ? AND t.`id` = ?
==> Parameters: false(Boolean),123(String)
可能會有人說這不就是拼sql嗎，對的你沒有說錯就是但是這是強類型的并且是支持所有庫的,還有一點jpa你說無法控制sql你不想用,我這個框架完全自主控制sql支持強類型我想你應該沒有拒絕的理由。
單表
//根據條件查詢表中的第一條記錄
List<Topic> topics = easyEntityProxy
.queryable(Topic.class)
.limit(1)
.toList();
==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM t_topic t LIMIT 1
<== Total: 1
//根據條件查詢表中的第一條記錄
Topic topic = easyEntityProxy
.queryable(Topic.class)
.firstOrNull();
==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM t_topic t LIMIT 1
<== Total: 1
//根據條件查詢id為3的記錄
Topic topic = easyEntityProxy
.queryable(Topic.class)
.where(o->o.id().eq("3"))
.firstOrNull();
==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM t_topic t WHERE t.`id` = ? LIMIT 1
==> Parameters: 3(String)
<== Total: 1
Topic topic = easyEntityProxy
.queryable(Topic.class)
.where(o->{
o.id().eq("3");
o.title().like("3");
})
.firstOrNull();
==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM t_topic t WHERE t.`id` = ? AND t.`title` like ? LIMIT 1
==> Parameters: 3(String),%3%(String)
<== Total: 1
多表
Topic topic = easyEntityQuery
.queryable(Topic.class)
.leftJoin(BlogEntity.class, (t,b) -> t.id().eq(b.id()))
.where((t,b) -> t.id().eq("3"))
.firstOrNull();
==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM t_topic t LEFT JOIN t_blog t1 ON t.`id` = t1.`id` WHERE t.`id` = ? LIMIT 1
==> Parameters: 3(String)
<== Total: 1
List<BlogEntity> blogEntities = easyEntityQuery
.queryable(Topic.class)
//join 后面是多參數委托,第一個主表,第二個參數為join表
.innerJoin(BlogEntity.class, (t,b) -> t.id().eq(b.id()))
.where((t,b) -> {
t.title().isNotNull();
b.id().eq("3");
})
//select 參數個數和join表個數一樣,group后參數為一個,返回一個對象代理
//可以對其進行自定義賦值比如id().set(t.title())將title賦值給id屬性
.select((t,b)->new BlogEntityProxy().selectAll(t))
.toList();
==> Preparing: SELECT t1.`id`,t1.`create_time`,t1.`update_time`,t1.`create_by`,t1.`update_by`,t1.`deleted`,t1.`title`,t1.`content`,t1.`url`,t1.`star`,t1.`publish_time`,t1.`score`,t1.`status`,t1.`order`,t1.`is_top`,t1.`top` FROM t_topic t INNER JOIN t_blog t1 ON t.`id` = t1.`id` WHERE t1.`title` IS NOT NULL AND t.`id` = ?
==> Parameters: 3(String)
<== Total: 1
數據庫函數支持
提供了常用的字符串trim,leftPad,subString,toLower,isBank,nullOrDefault等,時間類型的format,druation,plus,year,month等函數...
List<Topic> list2 = easyEntityQuery.queryable(Topic.class)
.where(o -> {
o.createTime().le(o.createTime().nullOrDefault(LocalDateTime.of(2022, 1, 1, 1, 1)));
o.id().isNotBank();
o.id().nullOrDefault("" ).eq(o.title().nullOrDefault(c -> c.column(o.id())));
o.title().isEmpty();
})
.toList();
==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `create_time` <= IFNULL(`create_time`,?) AND (`id` IS NOT NULL AND `id` <> '' AND LTRIM(`id`) <> '') AND IFNULL(`id`,?) = IFNULL(`title`,`id`) AND (`title` IS NULL OR `title` = '')
==> Parameters: 2022-01-01T01:01(LocalDateTime),(String)
<== Time Elapsed: 4(ms)
<== Total: 2
//pgsql
Draft7<Long, Long, Long, Long, Long, Long, Long> draft3 = entityQuery.queryable(BlogEntity.class)
.whereById(id)
.selectDraft(o -> Select.draft(
o.createTime().duration(o.updateTime(), DateTimeDurationEnum.Days).abs(),//計算createTime和updateTime相差的天數如果createTime小則返回負數 因為是abs所以返回的是肯定是相差天數
o.createTime().duration(o.updateTime(), DateTimeDurationEnum.Hours),//同理返回的是小時數
o.createTime().duration(o.updateTime(), DateTimeDurationEnum.Minutes),//同理返回分鐘數
o.createTime().duration(o.updateTime(), DateTimeDurationEnum.Seconds),//同理返回秒數
o.createTime().duration(o.createTime().plus(1,TimeUnit.DAYS), DateTimeDurationEnum.Days),//計算createTime和createTime加上1天后的相差天數
o.createTime().duration(o.createTime().plus(2,TimeUnit.SECONDS),DateTimeDurationEnum.Seconds),
o.createTime().duration(o.createTime().plus(3,TimeUnit.MINUTES),DateTimeDurationEnum.Minutes)
)).firstOrNull();
==> Preparing: SELECT ABS((extract(epoch from (t."create_time")::timestamp-(t."update_time")::timestamp)/86400)::int) AS "value1",(extract(epoch from (t."create_time")::timestamp-(t."update_time")::timestamp)/3600)::int AS "value2",(extract(epoch from (t."create_time")::timestamp-(t."update_time")::timestamp)/60)::int AS "value3",(extract(epoch from (t."create_time")::timestamp-(t."update_time")::timestamp))::int AS "value4",(extract(epoch from (t."create_time")::timestamp-((t."create_time" + INTERVAL '86400 second'))::timestamp)/86400)::int AS "value5",(extract(epoch from (t."create_time")::timestamp-((t."create_time" + INTERVAL '2 second'))::timestamp))::int AS "value6",(extract(epoch from (t."create_time")::timestamp-((t."create_time" + INTERVAL '180 second'))::timestamp)/60)::int AS "value7" FROM "t_blog" t WHERE t."deleted" = ? AND t."id" = ? LIMIT 1
==> Parameters: false(Boolean),123456zz9(String)
<== Time Elapsed: 3(ms)
<== Total: 1
匿名類型平替
List<Draft4<String, String, String, String>> list = easyEntityQuery.queryable(Topic.class)
.where(o -> {
o.title().subString(1, 2).eq("123");
o.title().toLower().subString(1, 2).eq("123");
o.title().toLower().toUpper().toLower().subString(1, 2).eq("123");
o.createTime()
.format("yyyy-MM")//日期先格式化
.toLower()//然后轉成小寫
.subString(1, 10)//分割從第一位
.like("023-01");
})
.selectDraft(o -> Select.draft(
o.id(),
o.title().toLower().replace("123","456"),
o.title().toUpper(),
o.title().toLower().subString(1, 2)
))
.toList();
==> Preparing: SELECT t.`id` AS `value1`,REPLACE(LOWER(t.`title`),?,?) AS `value2`,UPPER(t.`title`) AS `value3`,SUBSTR(LOWER(t.`title`),2,2) AS `value4` FROM `t_topic` t WHERE SUBSTR(t.`title`,2,2) = ? AND SUBSTR(LOWER(t.`title`),2,2) = ? AND SUBSTR(LOWER(UPPER(LOWER(t.`title`))),2,2) = ? AND SUBSTR(LOWER(DATE_FORMAT(t.`create_time`,'%Y-%m')),2,10) LIKE ?
==> Parameters: 123(String),456(String),123(String),123(String),123(String),%023-01%(String)
<== Time Elapsed: 2(ms)
<== Total: 0
強類型
因為topicType為枚舉類型所以后續的操作都用枚舉類型并且有智能提示如果使用其他類型ide會進行報錯無法編譯通過
@Data
@Table("t_topic_type")
@EntityFileProxy
@ToString
public class TopicTypeTest1 implements ProxyEntityAvailable<TopicTypeTest1 , TopicTypeTest1Proxy> {
@Column(primaryKey = true)
private String id;
private Integer stars;
private String title;
@Column(value = "topic_type",conversion = EnumConverter.class)
private TopicTypeEnum topicType;
private LocalDateTime createTime;
@Override
public Class<TopicTypeTest1Proxy> proxyTableClass() {
return TopicTypeTest1Proxy.class;
}
}
public enum TopicTypeEnum implements IEnum<TopicTypeEnum> {
STUDENT(1),
TEACHER(3),
CLASSER(9);
@EnumValue
private final Integer code;
TopicTypeEnum(Integer code){
this.code = code;
}
@Override
public Integer getCode() {
return code;
}
//......省略
}
List<TopicTypeTest1> list1 = easyEntityQuery.queryable(TopicTypeTest1.class)
.where(o -> {
o.topicType().nullOrDefault(TopicTypeEnum.CLASSER).eq(TopicTypeEnum.STUDENT);
o.topicType().eq(TopicTypeEnum.STUDENT);
}).toList();
==> Preparing: SELECT `id`,`stars`,`title`,`topic_type`,`create_time` FROM `t_topic_type` WHERE IFNULL(`topic_type`,?) = ? AND `topic_type` = ?
==> Parameters: 9(Integer),1(Integer),1(Integer)
<== Time Elapsed: 2(ms)
<== Total: 0
開箱即用的api
第一條
Topic topic = easyEntityQuery.queryable(Topic.class)
.where(o -> o.id().eq("123"))
.firstOrNull();
==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ? LIMIT 1
==> Parameters: 123(String)
至多一條
Topic topic = easyEntityQuery.queryable(Topic.class)
.where(o -> o.id().eq("123"))
.singleOrNull();
==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ?
==> Parameters: 123(String)
多條
List<Topic> topics = easyEntityQuery.queryable(Topic.class)
.where(o -> o.id().eq("123"))
.toList();
==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ?
==> Parameters: 123(String)
分頁
EasyPageResult<Topic> topicPageResult = easyEntityQuery
.queryable(Topic.class)
.where(o -> o.id().isNotNull())
.toPageResult(1, 20);
==> Preparing: SELECT COUNT(*) 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
streamApi配合
Optional<Topic> traceId1 = easyProxyQuery.queryable(TopicProxy.createTable())
.filterConfigure(NotNullOrEmptyValueFilter.DEFAULT)
.where(o -> o.eq(o.t().id(), "1"))
.fetch(o -> {//o為Stream<Topic>類型
return o.findFirst();
},1);
==> Preparing: SELECT `id`,`stars`,`title`,`create_time` FROM `t_topic` WHERE `id` = ?
==> Parameters: 1(String)
<== Time Elapsed: 2(ms)
所見所得的sql
@Data
@EntityFileProxy
public class QueryVO {
private String id;
private String field1;
private String field2;
}
List<QueryVO> list = easyEntityQuery.queryable(Topic.class)
//第一個join采用雙參數,參數1表示第一張表Topic 參數2表示第二張表 BlogEntity
.leftJoin(BlogEntity.class, (t, t1) -> t.id().eq(t1.id()))
//第二個join采用三參數,參數1表示第一張表Topic 參數2表示第二張表 BlogEntity 第三個參數表示第三張表 SysUser
.leftJoin(SysUser.class, (t, t1, t2) -> t.id().eq(t2.id()))
.where(o -> o.id().eq("123"))//單個條件where參數為主表Topic
//支持單個參數或者全參數,全參數個數為主表+join表個數 鏈式寫法期間可以通過then來切換操作表
.where((t, t1, t2) -> {
t.id().eq("123");
t1.title().like("456");
t2.createTime().eq(LocalDateTime.of(2021, 1, 1, 1, 1));
})
.select((t, t1, t2) -> new QueryVOProxy().adapter(r->{
r.selectAll(t);//因為結果只有并沒有其他屬性所以能夠映射上的只有t.id所以只會查詢t.id
r.selectIgnores(t.title());//可寫可不寫因為VO沒有title所以不會映射查詢
r.field1().set(t1.title());//別名映射
r.field2().set(t2.id());//別名映射
})).toList();
==> Preparing: SELECT t.`id`,t1.`title` AS `field1`,t2.`id` AS `field2` FROM `t_topic` t LEFT JOIN `t_blog` t1 ON t1.`deleted` = ? AND t.`id` = t1.`id` LEFT JOIN `easy-query-test`.`t_sys_user` t2 ON t.`id` = t2.`id` WHERE t.`id` = ? AND t.`id` = ? AND t1.`title` LIKE ? AND t2.`create_time` = ?
==> Parameters: false(Boolean),123(String),123(String),%456%(String),2021-01-01T01:01(LocalDateTime)
<== Time Elapsed: 2(ms)
<== Total: 0
group感知
因為group后無法對結果進行展開所以需要有group感知
List<BlogEntity> page = easyEntityQuery
.queryable(Topic.class)
.innerJoin(BlogEntity.class, (t, t1) -> t.id().eq(t1.id()))
.where((t, t1) -> t1.title().isNotNull())
.groupBy((t, t1) -> GroupKeys.TABLE2.of(t1.id()))
.select((g) -> new BlogEntityProxy().adapter(r->{
r.selectExpression(g.key1());//group只對t1.id進行了分組所以這邊只有key1可以選擇
r.score().set(g.sum(g.group().t2.score()));//因為是join了一張表所以g.group里面其實是tuple2里面有t1和t2兩張表
}))
.toList();
==> Preparing: SELECT t1.`id`,SUM(t1.`score`) AS `score` FROM `t_topic` t INNER JOIN `t_blog` t1 ON t1.`deleted` = ? AND t.`id` = t1.`id` WHERE t1.`title` IS NOT NULL GROUP BY t1.`id`
==> Parameters: false(Boolean)
<== Time Elapsed: 5(ms)
<== Total: 100
匿名類型平替
無需定義別名可以直接返回并且擁有強類型,可以作為匿名表繼續查詢無需定義中間表
List<Draft2<String, String>> list = easyEntityQuery.queryable(Topic.class)
.where(o -> {
o.title().trimEnd().trimStart().eq(o.id().trimStart());
o.createTime().format("yyyy-MM-dd").subString(0, 4).eq("2021");
})
.selectDraft(o -> Select.draft(
o.id(),
o.title().toLower()
))
.toList();
==> Preparing: SELECT t.`id` AS `value1`,LOWER(t.`title`) AS `value2` FROM `t_topic` t WHERE LTRIM(RTRIM(t.`title`)) = LTRIM(t.`id`) AND SUBSTR(DATE_FORMAT(t.`create_time`,'%Y-%m-%d'),1,4) = ?
==> Parameters: 2021(String)
<== Time Elapsed: 2(ms)
<== Total: 0
之前看到有人發過.net的orm語法實現，這次我們通過模擬那個作者的orm語法來看看easy-query和其相差多少
var query = rep.GetLambdaQuery().Take(100);
var join = query.Select(b => new { a1 = b.Id, a2 = b.F_String }).Join<TestEntityItem>((a, b) => a.a1 == b.TestEntityId);//第一次關聯
varjoin2 = join.Select((a, b) => new { a3 = a.a1, a4 = b.Name })
.Join<TestEntity>((a, b) => a.a3 == b.Id);//第二次關聯
join2.Select((a, b) => new
{
a.a4,
b.Id
});
這個SQL是一個很明顯的匿名sql之間的join處理,一眼看過去基本就大致猜到具體的sql含義所以在表達式這方面其實還是很容易只曉得
select
t4.[a4],
t1.[Id]
from
(
select
t2.[a1] as a3,
t3.[Name] as a4
from
(
select
t1.[Id] as a1,
t1.[F_String] as a2
from
[TestEntity] t1
LIMIT 0, 100
) t2
Inner join [TestEntityItem] t3 on t2.a1 = t3.[TestEntityId]
) t4
Inner join [TestEntity] t1 on t4.a3 = t1.[Id]
//selectDraft簡單理解為.net的匿名類型但是在java這邊沒有匿名類型所以通過一種草稿類型來對應可以防止簡單功能需要重新定義類本質是元祖tuple1-10
EntityQueryable<TopicProxy, Topic> query = easyEntityQuery.queryable(Topic.class).limit(100);
EntityQueryable2<Draft2Proxy<String, Integer>, Draft2<String, Integer>, BlogEntityProxy, BlogEntity> join = query.selectDraft(o -> Select.draft(o.id(), o.stars()))
.leftJoin(BlogEntity.class, (t, t1) -> t.value1().eq(t1.id()));
EntityQueryable2<Draft2Proxy<String, String>, Draft2<String, String>, BlogEntityProxy, BlogEntity> join2 = join.selectDraft((a, b) -> Select.draft(a.value1(), b.url()))
.innerJoin(BlogEntity.class, (t, t1) -> t.value2().eq(t1.id()));
List<Draft2<String, String>> list = join2.selectDraft((a, b) -> Select.draft(a.value1(), b.url())).toList();
//我們把局部變量去掉
List<Draft2<String, String>> list = easyEntityQuery.queryable(Topic.class).limit(100)
.selectDraft(o -> Select.draft(o.id(), o.stars()))
.leftJoin(BlogEntity.class, (t, t1) -> t.value1().eq(t1.id()))
.selectDraft((a, b) -> Select.draft(a.value1(), b.url()))
.innerJoin(BlogEntity.class, (t, t1) -> t.value2().eq(t1.id()))
.selectDraft((a, b) -> Select.draft(a.value1(), b.url())).toList();
SELECT
t3.`value1` AS `value1`,
t4.`url` AS `value2`
FROM
(SELECT
t1.`value1` AS `value1`,
t2.`url` AS `value2`
FROM
(SELECT
t.`id` AS `value1`,
t.`stars` AS `value2`
FROM
`t_topic` t LIMIT 100) t1
LEFT JOIN
`t_blog` t2
ON t2.`deleted` = false
AND t1.`value1` = t2.`id`
) t3
INNER JOIN
`t_blog` t4
ON t4.`deleted` = false
AND t3.`value2` = t4.`id`
或許你是一位java原住民,或許你是一位c#開發,在java語言貧瘠的時候我相信一款優雅的orm能夠讓你在編寫crud的時候放松神經不需要去考慮mybatis這種sql模版帶來的心智負擔,因為mybatis把所有問題都拋給了用戶所以你們一直覺得mybatis好,其實是你們被mybatis調教的好,因為一個框架只要提供的功能足夠少那么他需要維護的就足夠少,出問題也會足夠少,所以你拿諾基亞半個月不需要充電和2024年的智能機一天一沖比較哪個優秀我覺得你應該是贏了
最后
可能有很多小伙伴會推薦我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
總結
以上是生活随笔為你收集整理的这应该是java最好用的orm之一了的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： hdu 4882 ZCC Loves C
- 下一篇： MSSQL 数据库无法打开恢复操作将数据