獻給轉java的c#和java程式員的資料庫orm框架
一個好的程式員不應被語言所束縛,正如我現在開源java的orm框架一樣,如果您是一位轉java的c#程式員,那么這個框架可以帶給你起碼沒有那么差的業務撰寫和強型別體驗,如果您是一位java程式員,那么該框架可以提供比Mybatis-Plus
功能更加豐富、性能更高,更加輕量和完全免費的體驗來做一個happy coding crud body,
背景
easy-query
該框架是我在使用Mybatis-Plus
(下面統稱MP) 2年后開發的,因為MP
不支持多表(不要提join插件(邏輯洗掉子表不支持)),并且Mybatis
原本的xml十分惡心,導致專案中有非常多的代碼需要撰寫sql,并且整體資料庫架構因為存在邏輯洗掉欄位和多租戶欄位所以撰寫的sql基本上多多少少都會有問題,我不相信大家沒遇到過,而且MP得一些功能還需要收費這大大讓我堅定還是自己開發一款,
介紹
easy-query
?? 是一款無任何依賴的JAVA ORM 框架,十分輕量,擁有非常高的性能,支持單表查詢、多表查詢、union、子查詢、分頁、動態表名、VO物件查詢回傳、邏輯刪、全域攔截、資料庫列加密(支持高性能like查詢)、資料追蹤差異更新、樂觀鎖、多租戶、自動分庫、自動分表、讀寫分離,支持框架全功能外部擴展定制,擁有強型別運算式,
-
GITHUB github地址
-
GITEE gitee地址
?? 檔案
GITHUB地址 | GITEE地址
缺點
先說一下缺點,目前只適配了MySql,不過基本上如果你是pgsql很少需要改動就直接可以用了,其他資料庫可能因為自己的語法和特性會需要稍微做一下修改但是整體而言無需過多的變動,框架已經全部抽象好了,
功能點
- 物體物件insert,update,delete全部支持
- 單表查詢、多表join查詢,in子查詢,exists子查詢,連表統計(select a,(select count(1) from b) from c),聯合查詢union | all,分組group | having
- 分頁
- 動態表名:運行時修改表名
- 原生sql執行,查詢
- select查詢map結果回傳
- select支持直接回傳DTO物件實作自定義列查詢回傳,而不是全部列回傳
- select支持標記large欄位不回傳(默認回傳)
- 邏輯洗掉,自定義邏輯洗掉,支持多欄位邏輯洗掉填充,支持運行時禁用
- 全域攔截器,支持運行時選擇性使用某幾個或者不使用,支持entity操作 insert,update,條件攔截 select、update、delete的where條件攔截,update set欄位攔截器
- 多租戶,支持表的列范圍多租戶模式
- 資料庫列加密,支持高性能的like模糊搜索匹配(不是單純的呼叫資料庫加密函式或者單純的呼叫框架加密解密函式)
- 資料追蹤差異更新,而不是全列更新,用過efcore的肯定很熟悉
- 版本號、樂觀鎖,支持自定義樂觀鎖
- 支持分庫分表(身為
sharding-core
作者不支持說不過去),全自動分庫分表,僅需用戶新增表和告知easy-query
系統中有的表 - 高性能分庫分表分頁,支持順序分頁,反向分頁,支持高性能順序分頁和反向分頁
- 分庫分表多欄位分片
- 分庫分表自定義分片路由規則
- 支持讀寫分離,一主多從支持分片下讀寫分離
目前專案正處于起步階段后續會隨著用戶不斷地完善各資料庫的適配和功能的支持
開始使用
安裝
以下是spring-boot
環境和控制臺模式的安裝
spring-boot
<properties>
<easy-query.version>0.8.10</easy-query.version>
</properties>
<dependency>
<groupId>com.easy-query</groupId>
<artifactId>sql-springboot-starter</artifactId>
<version>${easy-query.version}</version>
</dependency>
console
以mysql為例
<properties>
<easy-query.version>0.8.10</easy-query.version>
</properties>
<dependency>
<groupId>com.easy-query</groupId>
<artifactId>sql-mysql</artifactId>
<version>${easy-query.version}</version>
</dependency>
//初始化連接池
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/easy-query-test?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true");
dataSource.setUsername("root");
dataSource.setPassword("root");
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setMaximumPoolSize(20);
//創建easy-query
EasyQuery easyQuery = EasyQueryBootstrapper.defaultBuilderConfiguration()
.setDefaultDataSource(dataSource)
.useDatabaseConfigure(new MySQLDatabaseConfiguration())
.build();
開始
sql腳本
create table t_topic
(
id varchar(32) not null comment '主鍵ID'primary key,
stars int not null comment '點贊數',
title varchar(50) null comment '標題',
create_time datetime not null comment '創建時間'
)comment '主題表';
create table t_blog
(
id varchar(32) not null comment '主鍵ID'primary key,
deleted tinyint(1) default 0 not null comment '是否洗掉',
create_by varchar(32) not null comment '創建人',
create_time datetime not null comment '創建時間',
update_by varchar(32) not null comment '更新人',
update_time datetime not null comment '更新時間',
title varchar(50) not null comment '標題',
content varchar(256) null comment '內容',
url varchar(128) null comment '博客鏈接',
star int not null comment '點贊數',
publish_time datetime null comment '發布時間',
score decimal(18, 2) not null comment '評分',
status int not null comment '狀態',
`order` decimal(18, 2) not null comment '排序',
is_top tinyint(1) not null comment '是否置頂',
top tinyint(1) not null comment '是否置頂'
)comment '博客表';
查詢物件
@Data
public class BaseEntity implements Serializable {
private static final long serialVersionUID = -4834048418175625051L;
@Column(primaryKey = true)
private String id;
/**
* 創建時間;創建時間
*/
private LocalDateTime createTime;
/**
* 修改時間;修改時間
*/
private LocalDateTime updateTime;
/**
* 創建人;創建人
*/
private String createBy;
/**
* 修改人;修改人
*/
private String updateBy;
/**
* 是否洗掉;是否洗掉
*/
@LogicDelete(strategy = LogicDeleteStrategyEnum.BOOLEAN)
private Boolean deleted;
}
@Data
@Table("t_topic")
@ToString
public class Topic {
@Column(primaryKey = true)
private String id;
private Integer stars;
private String title;
private LocalDateTime createTime;
}
@Data
@Table("t_blog")
public class BlogEntity extends BaseEntity{
/**
* 標題
*/
private String title;
/**
* 內容
*/
private String content;
/**
* 博客鏈接
*/
private String url;
/**
* 點贊數
*/
private Integer star;
/**
* 發布時間
*/
private LocalDateTime publishTime;
/**
* 評分
*/
private BigDecimal score;
/**
* 狀態
*/
private Integer status;
/**
* 排序
*/
private BigDecimal order;
/**
* 是否置頂
*/
private Boolean isTop;
/**
* 是否置頂
*/
private Boolean top;
}
單表查詢
Topic topic = easyQuery
.queryable(Topic.class)
.where(o -> o.eq(Topic::getId, "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)
<== Time Elapsed: 15(ms)
<== Total: 1
多表查詢
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)
<== Time Elapsed: 2(ms)
<== Total: 1
復雜查詢
join + group +分頁
EasyPageResult<BlogEntity> page = easyQuery
.queryable(Topic.class).asTracking()
.innerJoin(BlogEntity.class, (t, t1) -> t.eq(t1, Topic::getId, BlogEntity::getId))
.where((t, t1) -> t1.isNotNull(BlogEntity::getTitle))
.groupBy((t, t1)->t1.column(BlogEntity::getId))
.select(BlogEntity.class, (t, t1) -> t1.column(BlogEntity::getId).columnSum(BlogEntity::getScore))
.toPageResult(1, 20);
==> 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` LIMIT 20
==> Parameters: false(Boolean)
<== Time Elapsed: 5(ms)
<== Total: 20
動態表名
String sql = easyQuery.queryable(BlogEntity.class)
.asTable(a->"aa_bb_cc")
.where(o -> o.eq(BlogEntity::getId, "123"))
.toSQL();
SELECT t.`id`,t.`create_time`,t.`update_time`,t.`create_by`,t.`update_by`,t.`deleted`,t.`title`,t.`content`,t.`url`,t.`star`,t.`publish_time`,t.`score`,t.`status`,t.`order`,t.`is_top`,t.`top` FROM `aa_bb_cc` t WHERE t.`deleted` = ? AND t.`id` = ?
新增
Topic topic = new Topic();
topic.setId(String.valueOf(0));
topic.setStars(100);
topic.setTitle("標題0");
topic.setCreateTime(LocalDateTime.now().plusDays(i));
long rows = easyQuery.insertable(topic).executeRows();
//回傳結果rows為1
==> Preparing: INSERT INTO `t_topic` (`id`,`stars`,`title`,`create_time`) VALUES (?,?,?,?)
==> Parameters: 0(String),100(Integer),標題0(String),2023-03-16T21:34:13.287(LocalDateTime)
<== Total: 1
修改
//物體更新
Topic topic = easyQuery.queryable(Topic.class)
.where(o -> o.eq(Topic::getId, "7")).firstNotNull("未找到對應的資料");
String newTitle = "test123" + new Random().nextInt(100);
topic.setTitle(newTitle);
long rows=easyQuery.updatable(topic).executeRows();
==> Preparing: UPDATE t_topic SET `stars` = ?,`title` = ?,`create_time` = ? WHERE `id` = ?
==> Parameters: 107(Integer),test12364(String),2023-03-27T22:05:23(LocalDateTime),7(String)
<== Total: 1
//運算式更新
long rows = easyQuery.updatable(Topic.class)
.set(Topic::getStars, 12)
.where(o -> o.eq(Topic::getId, "2"))
.executeRows();
//rows為1
easyQuery.updatable(Topic.class)
.set(Topic::getStars, 12)
.where(o -> o.eq(Topic::getId, "2"))
.executeRows(1,"更新失敗");
//判斷受影響行數并且進行報錯,如果當前操作不在事務內執行那么會自動開啟事務!!!會自動開啟事務!!!會自動開啟事務!!!來實作并發更新控制,例外為:EasyQueryConcurrentException
//拋錯后資料將不會被更新
==> Preparing: UPDATE t_topic SET `stars` = ? WHERE `id` = ?
==> Parameters: 12(Integer),2(String)
<== Total: 1
洗掉
long l = easyQuery.deletable(Topic.class)
.where(o->o.eq(Topic::getTitle,"title998"))
.executeRows();
==> Preparing: DELETE FROM t_topic WHERE `title` = ?
==> Parameters: title998(String)
<== Total: 1
Topic topic = easyQuery.queryable(Topic.class).whereId("997").firstNotNull("未找到當前主題資料");
long l = easyQuery.deletable(topic).executeRows();
==> Preparing: DELETE FROM t_topic WHERE `id` = ?
==> Parameters: 997(String)
<== Total: 1
聯合查詢
Queryable<Topic> q1 = easyQuery
.queryable(Topic.class);
Queryable<Topic> q2 = easyQuery
.queryable(Topic.class);
Queryable<Topic> q3 = easyQuery
.queryable(Topic.class);
List<Topic> list = q1.union(q2, q3).where(o -> o.eq(Topic::getId, "123321")).toList();
==> Preparing: SELECT t1.`id`,t1.`stars`,t1.`title`,t1.`create_time` FROM (SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t UNION SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t UNION SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t) t1 WHERE t1.`id` = ?
==> Parameters: 123321(String)
<== Time Elapsed: 19(ms)
<== Total: 0
子查詢
in子查詢
Queryable<String> idQueryable = easyQuery.queryable(BlogEntity.class)
.where(o -> o.eq(BlogEntity::getId, "1"))
.select(String.class,o->o.column(BlogEntity::getId));
List<Topic> list = easyQuery
.queryable(Topic.class, "x").where(o -> o.in(Topic::getId, idQueryable)).toList();
==> Preparing: SELECT x.`id`,x.`stars`,x.`title`,x.`create_time` FROM `t_topic` x WHERE x.`id` IN (SELECT t.`id` FROM `t_blog` t WHERE t.`deleted` = ? AND t.`id` = ?)
==> Parameters: false(Boolean),1(String)
<== Time Elapsed: 3(ms)
<== Total: 1
exists子查詢
Queryable<BlogEntity> where1 = easyQuery.queryable(BlogEntity.class)
.where(o -> o.eq(BlogEntity::getId, "1"));
List<Topic> x = easyQuery
.queryable(Topic.class, "x").where(o -> o.exists(where1, q -> q.eq(o, BlogEntity::getId, Topic::getId))).toList();
==> Preparing: SELECT x.`id`,x.`stars`,x.`title`,x.`create_time` FROM `t_topic` x WHERE EXISTS (SELECT 1 FROM `t_blog` t WHERE t.`deleted` = ? AND t.`id` = ? AND t.`id` = x.`id`)
==> Parameters: false(Boolean),1(String)
<== Time Elapsed: 10(ms)
<== Total: 1
分片
easy-query
支持分表、分庫、分表+分庫
分表
//創建分片物件
@Data
@Table(value = "https://www.cnblogs.com/xuejiaming/p/t_topic_sharding_time",shardingInitializer = TopicShardingTimeShardingInitializer.class)
@ToString
public class TopicShardingTime {
@Column(primaryKey = true)
private String id;
private Integer stars;
private String title;
@ShardingTableKey
private LocalDateTime createTime;
}
//分片初始化器很簡單 假設我們是2020年1月到2023年5月也就是當前時間進行分片那么要生成對應的分片表每月一張
public class TopicShardingTimeShardingInitializer extends AbstractShardingMonthInitializer<TopicShardingTime> {
@Override
protected LocalDateTime getBeginTime() {
return LocalDateTime.of(2020, 1, 1, 1, 1);
}
@Override
protected LocalDateTime getEndTime() {
return LocalDateTime.of(2023, 5, 1, 0, 0);
}
@Override
public void configure0(ShardingEntityBuilder<TopicShardingTime> builder) {
////以下條件可以選擇配置也可以不配置用于優化分片性能
// builder.paginationReverse(0.5,100)
// .ascSequenceConfigure(new TableNameStringComparator())
// .addPropertyDefaultUseDesc(TopicShardingTime::getCreateTime)
// .defaultAffectedMethod(false, ExecuteMethodEnum.LIST,ExecuteMethodEnum.ANY,ExecuteMethodEnum.COUNT,ExecuteMethodEnum.FIRST)
// .useMaxShardingQueryLimit(2,ExecuteMethodEnum.LIST,ExecuteMethodEnum.ANY,ExecuteMethodEnum.FIRST);
}
}
//分片時間路由規則按月然后bean分片屬性就是LocalDateTime也可以自定義實作
public class TopicShardingTimeTableRule extends AbstractMonthTableRule<TopicShardingTime> {
@Override
protected LocalDateTime convertLocalDateTime(Object shardingValue) {
return (LocalDateTime)shardingValue;
}
}
資料庫腳本參考原始碼
其中shardingInitializer
為分片初始化器用來初始化告訴框架有多少分片的表名(支持動態添加)
ShardingTableKey
表示哪個欄位作為分片鍵(分片鍵不等于主鍵)
執行sql
LocalDateTime beginTime = LocalDateTime.of(2021, 1, 1, 1, 1);
LocalDateTime endTime = LocalDateTime.of(2021, 5, 2, 1, 1);
Duration between = Duration.between(beginTime, endTime);
long days = between.toDays();
List<TopicShardingTime> list = easyQuery.queryable(TopicShardingTime.class)
.where(o->o.rangeClosed(TopicShardingTime::getCreateTime,beginTime,endTime))
.orderByAsc(o -> o.column(TopicShardingTime::getCreateTime))
.toList();
==> SHARDING_EXECUTOR_2, name:ds2020, Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic_sharding_time_202101` t WHERE t.`create_time` >= ? AND t.`create_time` <= ? ORDER BY t.`create_time` ASC
==> SHARDING_EXECUTOR_3, name:ds2020, Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic_sharding_time_202102` t WHERE t.`create_time` >= ? AND t.`create_time` <= ? ORDER BY t.`create_time` ASC
==> SHARDING_EXECUTOR_2, name:ds2020, Parameters: 2021-01-01T01:01(LocalDateTime),2021-05-02T01:01(LocalDateTime)
==> SHARDING_EXECUTOR_3, name:ds2020, Parameters: 2021-01-01T01:01(LocalDateTime),2021-05-02T01:01(LocalDateTime)
<== SHARDING_EXECUTOR_3, name:ds2020, Time Elapsed: 3(ms)
<== SHARDING_EXECUTOR_2, name:ds2020, Time Elapsed: 3(ms)
==> SHARDING_EXECUTOR_2, name:ds2020, Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic_sharding_time_202103` t WHERE t.`create_time` >= ? AND t.`create_time` <= ? ORDER BY t.`create_time` ASC
==> SHARDING_EXECUTOR_3, name:ds2020, Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic_sharding_time_202104` t WHERE t.`create_time` >= ? AND t.`create_time` <= ? ORDER BY t.`create_time` ASC
==> SHARDING_EXECUTOR_2, name:ds2020, Parameters: 2021-01-01T01:01(LocalDateTime),2021-05-02T01:01(LocalDateTime)
==> SHARDING_EXECUTOR_3, name:ds2020, Parameters: 2021-01-01T01:01(LocalDateTime),2021-05-02T01:01(LocalDateTime)
<== SHARDING_EXECUTOR_3, name:ds2020, Time Elapsed: 2(ms)
<== SHARDING_EXECUTOR_2, name:ds2020, Time Elapsed: 2(ms)
==> main, name:ds2020, Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic_sharding_time_202105` t WHERE t.`create_time` >= ? AND t.`create_time` <= ? ORDER BY t.`create_time` ASC
==> main, name:ds2020, Parameters: 2021-01-01T01:01(LocalDateTime),2021-05-02T01:01(LocalDateTime)
<== main, name:ds2020, Time Elapsed: 2(ms)
<== Total: 122
分庫
@Data
@Table(value = "https://www.cnblogs.com/xuejiaming/p/t_topic_sharding_ds",shardingInitializer = DataSourceAndTableShardingInitializer.class)
@ToString
public class TopicShardingDataSource {
@Column(primaryKey = true)
private String id;
private Integer stars;
private String title;
@ShardingDataSourceKey
private LocalDateTime createTime;
}
public class DataSourceShardingInitializer implements EntityShardingInitializer<TopicShardingDataSource> {
@Override
public void configure(ShardingEntityBuilder<TopicShardingDataSource> builder) {
EntityMetadata entityMetadata = https://www.cnblogs.com/xuejiaming/p/builder.getEntityMetadata();
String tableName = entityMetadata.getTableName();
List tables = Collections.singletonList(tableName);
LinkedHashMap> initTables = new LinkedHashMap>() {{
put("ds2020", tables);
put("ds2021", tables);
put("ds2022", tables);
put("ds2023", tables);
}};
builder.actualTableNameInit(initTables);
}
}
//分庫資料源路由規則
public class TopicShardingDataSourceRule extends AbstractDataSourceRouteRule<TopicShardingDataSource> {
@Override
protected RouteFunction<String> getRouteFilter(TableAvailable table, Object shardingValue, ShardingOperatorEnum shardingOperator, boolean withEntity) {
LocalDateTime createTime = (LocalDateTime) shardingValue;
String dataSource = "ds" + createTime.getYear();
switch (shardingOperator){
case GREATER_THAN:
case GREATER_THAN_OR_EQUAL:
return ds-> dataSource.compareToIgnoreCase(ds)<=0;
case LESS_THAN:
{
//如果小于月初那么月初的表是不需要被查詢的
LocalDateTime timeYearFirstDay = LocalDateTime.of(createTime.getYear(),1,1,0,0,0);
if(createTime.isEqual(timeYearFirstDay)){
return ds->dataSource.compareToIgnoreCase(ds)>0;
}
return ds->dataSource.compareToIgnoreCase(ds)>=0;
}
case LESS_THAN_OR_EQUAL:
return ds->dataSource.compareToIgnoreCase(ds)>=0;
case EQUAL:
return ds->dataSource.compareToIgnoreCase(ds)==0;
default:return t->true;
}
}
}
LocalDateTime beginTime = LocalDateTime.of(2020, 1, 1, 1, 1);
LocalDateTime endTime = LocalDateTime.of(2023, 5, 1, 1, 1);
Duration between = Duration.between(beginTime, endTime);
long days = between.toDays();
EasyPageResult<TopicShardingDataSource> pageResult = easyQuery.queryable(TopicShardingDataSource.class)
.orderByAsc(o -> o.column(TopicShardingDataSource::getCreateTime))
.toPageResult(1, 33);
==> SHARDING_EXECUTOR_23, name:ds2022, Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic_sharding_ds` t ORDER BY t.`create_time` ASC LIMIT 33
==> SHARDING_EXECUTOR_11, name:ds2021, Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic_sharding_ds` t ORDER BY t.`create_time` ASC LIMIT 33
==> SHARDING_EXECUTOR_2, name:ds2020, Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic_sharding_ds` t ORDER BY t.`create_time` ASC LIMIT 33
==> SHARDING_EXECUTOR_4, name:ds2023, Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic_sharding_ds` t ORDER BY t.`create_time` ASC LIMIT 33
<== SHARDING_EXECUTOR_4, name:ds2023, Time Elapsed: 4(ms)
<== SHARDING_EXECUTOR_23, name:ds2022, Time Elapsed: 4(ms)
<== SHARDING_EXECUTOR_2, name:ds2020, Time Elapsed: 4(ms)
<== SHARDING_EXECUTOR_11, name:ds2021, Time Elapsed: 6(ms)
<== Total: 33
最后
希望看到這邊的各位大佬給我點個star謝謝這對我很重要
-
GITHUB github地址
-
GITEE gitee地址
轉載請註明出處,本文鏈接:https://www.uj5u.com/houduan/553029.html
標籤:Java
下一篇:返回列表