1.献给转java的c#和java程序员的数据库orm框架2.javaer你还在手写分表分库?来看看这个框架怎么做的 干货满满3.你没见过的分库分表原理解析和解决方案(一)4.你没见过的分库分表原理解析和解决方案(二)5.我真的不想再用mybatis和其衍生框架了选择自研亦是一种解脱6.数据安全之数据库字段加解密检索和前端返回脱敏？看看我这个最强解决方案7.解放生产力orm并发更新下应该这么处理求求你别再用UpdateById了8.放弃老旧的Mybatis,强类型替换字符串,这是一款你不应该错过的ORM9.流畅的orm让我发现我抵触的是mybatis而不是java
10.这应该是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> 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> list = easyEntityQuery.queryable(BlogEntity.class)
.where(o -> {
Query 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> 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 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 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 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 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> 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 {
@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 proxyTableClass() {
return TopicTypeTest1Proxy.class;
}
}
public enum TopicTypeEnum implements IEnum {
STUDENT(1),
TEACHER(3),
CLASSER(9);
@EnumValue
private final Integer code;
TopicTypeEnum(Integer code){
this.code = code;
}
@Override
public Integer getCode() {
return code;
}
//......省略
}
List 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 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 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 traceId1 = easyProxyQuery.queryable(TopicProxy.createTable())
.filterConfigure(NotNullOrEmptyValueFilter.DEFAULT)
.where(o -> o.eq(o.t().id(), "1"))
.fetch(o -> {//o为Stream类型
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 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 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> 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((a, b) => a.a1 == b.TestEntityId);//第一次关联
varjoin2 = join.Select((a, b) => new { a3 = a.a1, a4 = b.Name })
.Join((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 query = easyEntityQuery.queryable(Topic.class).limit(100);
EntityQueryable2, Draft2, BlogEntityProxy, BlogEntity> join = query.selectDraft(o -> Select.draft(o.id(), o.stars()))
.leftJoin(BlogEntity.class, (t, t1) -> t.value1().eq(t1.id()));
EntityQueryable2, Draft2, BlogEntityProxy, BlogEntity> join2 = join.selectDraft((a, b) -> Select.draft(a.value1(), b.url()))
.innerJoin(BlogEntity.class, (t, t1) -> t.value2().eq(t1.id()));
List> list = join2.selectDraft((a, b) -> Select.draft(a.value1(), b.url())).toList();
//我们把局部变量去掉
List> 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/