https://github.com/pyloque/ormkids
假期花了几天时间撸了一个支持分库分表的 MySQL 单表 ORM 框架,暂用于学习
对于复杂的多表查询和批量数据处理,可以使用该方法。 用户可以获得原生的 jdbc 链接,通过编写 jdbc 代码来实现。
用户可以使用 Q 对象构建复杂的 SQL 查询
暂时没有
@Table
public class BookShelf implements IEntity {
public final static int PARTITIONS = 4;
@Column(name = "user_id", type = "varchar(255)", nullable = false)
private String userId;
@Column(name = "book_id", type = "varchar(255)", nullable = false)
private String bookId;
@Column(name = "comment", type = "varchar(255)")
private String comment;
@Column(name = "created_at", type = "datetime", nullable = false, defaultValue = "now()")
private Date createdAt;
public BookShelf() {
}
public BookShelf(String userId, String bookId, String comment, Date createdAt) {
this.userId = userId;
this.bookId = bookId;
this.comment = comment;
this.createdAt = createdAt;
}
public String getUserId() {
return userId;
}
public String getBookId() {
return bookId;
}
public void setComment(String comment) {
this.comment = comment;
}
public String getComment() {
return comment;
}
public Date getCreatedAt() {
return createdAt;
}
@Override
public String table() {
return "book_shelf";
}
@Override
public TableOptions options() {
return new TableOptions().option("engine", "innodb");
}
@Override
public TableIndices indices() {
return new TableIndices().primary("user_id", "book_id");
}
/*
* 分表策略
*/
@Override
public String suffix() {
var crc32 = new CRC32();
crc32.update(userId.getBytes(Utils.UTF8));
return String.valueOf(Math.abs(crc32.getValue()) % PARTITIONS);
}
/**
* 分库策略
*/
public static class GridStrategy implements IGridable<BookShelf> {
@Override
public int select(int dbs, BookShelf t) {
return Math.abs(t.getUserId().hashCode()) % dbs;
}
@Override
public int select(int dbs, Object... params) {
String userId = (String) params[0];
return Math.abs(userId.hashCode()) % dbs;
}
}
}
public class DemoDB extends DB {
private DataSource ds;
public DemoDB(String name, String uri) {
this(name, new HashMap<>(), uri);
}
public DemoDB(String name, Map<Class<? extends IEntity>, Meta> metas, String uri) {
super(name, metas);
var ds = new MysqlConnectionPoolDataSource(); // 连接池
ds.setUrl(uri);
this.ds = ds;
}
@Override
protected Connection conn() { // 获取链接
try {
return ds.getConnection();
} catch (SQLException e) {
throw new KidsException(e);
}
}
}
public class GridDemoDB extends GridDB<DemoDB> {
/**
* 传进来多个 DB 对象
*/
public GridDemoDB(DemoDB[] dbs) {
super(dbs);
this.registerGridables();
}
/*
* 注册实体类的分库策略
*/
@Override
public void registerGridables() {
this.gridWith(BookShelf.class, new BookShelf.GridStrategy<DemoDB>());
}
}
public class DemoSharding {
private static DemoDB[] dbs = new DemoDB[3];
static {
Map<Class<? extends IEntity>, Meta> metas = new HashMap<>();
dbs[0] = new DemoDB("demo-0", metas,
"jdbc:mysql://localhost:3306/mydrc?user=mydrc&password=mydrc&useUnicode=true&characterEncoding=UTF8");
dbs[1] = new DemoDB("demo-1", metas,
"jdbc:mysql://localhost:3307/mydrc?user=mydrc&password=mydrc&useUnicode=true&characterEncoding=UTF8");
dbs[2] = new DemoDB("demo-2", metas,
"jdbc:mysql://localhost:3308/mydrc?user=mydrc&password=mydrc&useUnicode=true&characterEncoding=UTF8");
}
public static void main(String[] args) {
var grid = new GridDemoDB(dbs); // 构造 Grid 实例
try {
for (int k = 0; k < BookShelf.PARTITIONS; k++) {
grid.create(BookShelf.class, String.valueOf(k)); // 创建所有分库中的分表
}
var bss = new ArrayList<BookShelf>();
for (int i = 0; i < 100; i++) {
var bs = new BookShelf("user" + i, "book" + i, "comment" + i, new Date());
bss.add(bs);
grid.insert(bs); // 插入,自动分发到相应的分库中的分表
}
for (int k = 0; k < grid.size(); k++) {
for (int i = 0; i < BookShelf.PARTITIONS; i++) {
System.out.printf("db %d partition %d count %d\n", k, i,
grid.count(BookShelf.class, k, String.valueOf(i))); // 依次查询出所有分库的分表的行数
}
}
Random random = new Random();
for (var bs : bss) {
bs.setComment("comment_update_" + random.nextInt(100));
grid.update(bs); // 更新,自动分发到相应的分库中的分表
}
for (var bs : bss) {
bs = grid.get(BookShelf.class, bs.getUserId(), bs.getBookId()); // 主键查询,自动分发到相应的分库中的分表
System.out.println(bs.getComment());
}
for (var bs : bss) {
grid.delete(bs); // 删除,自动分发到相应的分库中的分表
}
for (int k = 0; k < grid.size(); k++) {
for (int i = 0; i < BookShelf.PARTITIONS; i++) {
System.out.printf("db %d partition %d count %d\n", k, i,
grid.count(BookShelf.class, k, String.valueOf(i))); // 依次查询出所有分库的分表的行数
}
}
} finally {
for (int k = 0; k < BookShelf.PARTITIONS; k++) {
grid.drop(BookShelf.class, String.valueOf(k)); // 删除所有分库中的分表
}
}
}
}
public class Context {
private DB db; // 数据库实例
private Connection conn; // 当前的链接
private Class<? extends IEntity> clazz; // 当前的实体类
private Q q; // 查询 sql
private Object[] values; // 查询的绑定参数
private boolean before; // before or after
private Exception error; // 异常
private long duration; // 耗时 microsecond
}
public class DemoEvent {
private final static String URI = "jdbc:mysql://localhost:3306/mydrc?user=mydrc&password=mydrc&useUnicode=true&characterEncoding=UTF8";
public static void main(String[] args) {
var db = new DemoDB("demo", URI);
db.on(ctx -> { // 全局事件回调
System.out.printf("db=%s sql=%s cost=%dus\n", ctx.db().name(), ctx.q().sql(), ctx.duration());
return true; // 返回 false 会导致事件链终止,后续的 ORM 操作也不会执行
});
try {
db.create(User.class);
db.scope(ctx -> { // 范围回调,execute 方法内部的所有 ORM 操作都会回调
System.out.printf("db=%s sql=%s cost=%dus\n", ctx.db().name(), ctx.q().sql(), ctx.duration());
return true;
}).execute(() -> {
db.count(User.class);
db.find(User.class);
});
} finally {
db.drop(User.class); // 删除表
}
}
}
鉴于文章太长花费 v 币太多,更多内容请阅读 github 的 README
1
enhancer 2018-05-03 09:51:43 +08:00
支持分表之间的 JOIN 吗?
|
2
shellquery OP @enhancer 同是天涯沦落人,何苦为难自家人
|
3
ylcc 2018-05-03 10:13:30 +08:00 1
看了楼主的 gayhub,觉得非常有趣,小孩有点多哈哈哈,赞学习精神
|
4
micean 2018-05-03 10:16:40 +08:00
以前也造过轮子
现在觉得小的用表分区就搞完了,大的就直接上中间件了…… |
5
shellquery OP @micean 中间件也是一种常见的方案
|
6
admol 2018-05-03 11:31:36 +08:00
var 用的 JDK10 吗?
|
7
shellquery OP @admol 没错,提前尝鲜了
|
8
lihongjie0209 2018-05-03 11:48:54 +08:00
同样的文章为什么昨天发今天也发
|
9
THP301 2018-05-03 11:49:11 +08:00 1
难得见这么简洁优雅的代码,已经收藏了
|
10
shellquery OP @lihongjie0209 昨天的文章是依赖注入
|
11
tianzx 2018-05-03 12:16:21 +08:00 via Android
m
|
12
sethverlo 2018-05-03 12:18:38 +08:00
楼主的头像!
good news everyone (自带音效) |
13
shellquery OP @sethverlo 老司机
|