Java ORM framework on the open source market is not easy to use, so I spent a few days on my OrmKids, welcome to download and learn. Please pay attention to the public number into the group to discuss together.

OrmKids

MySQL single-table ORM framework that supports sub-database and sub-table is temporarily used for learning and will be tested in the production environment later

features

1. Code concise, no dependencies, in addition to the use of the need to use MySQL driver
2. Easy to use, no complex configuration required
3. Provides automatic table creation
4. Support sub – library and sub – table, can only sub – library, also can only sub – table
5. Support groupby/having
6. Support for native SQL
7. Event callbacks are supported for service trace debugging and dynamic SQL rewriting

Multiple table association is not supported

1. Multiple tables are more complex, high implementation cost, high learning cost, easy to make mistakes
2. Common multi-table operations can be combined with multiple single-table operations
3. In the case of separate database and table, multi-table operation is rarely used
4. Do not use foreign keys and focus on SQL logic

db.withinTx

This approach can be used for complex multi-table queries and bulk data processing. Users can obtain native JDBC links by writing JDBC code.

Q

Users can use Q objects to build complex SQL queries

Other database support

Temporarily no

The entity interface

/** * All entity classes must implement this interface */
public interface IEntity {

	/** * table name *@return* /
	String table(a);

	/** * subtables must override this method *@return* /
	default String suffix(a) {
		return null;
	}

	default String tableWithSuffix(a) {
		return tableWith(suffix());
	}

	default String tableWith(String suffix) {
		return Utils.tableWithSuffix(table(), suffix);
	}
	
	/** * defines table physical structure attributes such as engine=innodb for dynamically creating tables *@return* /
	TableOptions options(a);

	/** * defines the primary key and index information for the table to be dynamically created *@return* /
	TableIndices indices(a);

}
Copy the code

Single form primary key

@Table
public class User implements IEntity {

	@Column(name = "id", type = "int", autoincrement = true, nullable = false)
	private Integer id;
	@Column(name = "name", type = "varchar(255)", nullable = false)
	private String name;
	@Column(name = "nick", type = "varchar(255)", nullable = false)
	private String nick;
	@Column(name = "passwd", type = "varchar(255)")
	private String passwd;
	@Column(name = "created_at", type = "datetime", nullable = false, defaultValue = "now()")
	private Date createdAt;

	public User(a) {}public User(String name, String nick, String passwd) {
		this.name = name;
		this.nick = nick;
		this.passwd = passwd;
	}

	public Integer getId(a) {
		return id;
	}

	public String getName(a) {
		return name;
	}

	public String getNick(a) {
		return nick;
	}

	public String getPasswd(a) {
		return passwd;
	}

	public Date getCreatedAt(a) {
		return createdAt;
	}

	@Override
	public TableOptions options(a) {
		return new TableOptions().option("engine"."innodb");
	}

	@Override
	public TableIndices indices(a) {
		return new TableIndices().primary("id").unique("name");
	}

	@Override
	public String table(a) {
		return "user"; }}Copy the code

Single table compound primary key

@Table
public class Member implements IEntity {

	@Column(name = "user_id", type = "int", nullable = false)
	private Integer userId;
	@Column(name = "group_id", type = "int", nullable = false)
	private Integer groupId;
	@Column(name = "title", type = "varchar(255)")
	private String title;
	@Column(name = "created_at", type = "datetime", nullable = false, defaultValue = "now()")
	private Date createdAt;

	public Member(a) {}public Member(Integer userId, Integer groupId, String title, Date createdAt) {
		this.userId = userId;
		this.groupId = groupId;
		this.title = title;
		this.createdAt = createdAt;
	}

	public Integer getUserId(a) {
		return userId;
	}

	public Integer getGroupId(a) {
		return groupId;
	}

	public String getTitle(a) {
		return title;
	}

	public Date getCreatedAt(a) {
		return createdAt;
	}

	@Override
	public TableOptions options(a) {
		return new TableOptions().option("engine"."innodb");
	}

	@Override
	public TableIndices indices(a) {
		return new TableIndices().primary("user_id"."group_id");
	}

	@Override
	public String table(a) {
		return "member"; }}Copy the code

Depots interface

public interface IGridable<T extends IEntity> {

	/** * Select index */ based on entity object
	int select(int dbs, T t);

	/** * Select the sub-index */ based on specific parameters
	int select(int dbs, Object... params);

}
Copy the code

Depots table

@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(a) {}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(a) {
		return userId;
	}

	public String getBookId(a) {
		return bookId;
	}

	public void setComment(String comment) {
		this.comment = comment;
	}

	public String getComment(a) {
		return comment;
	}

	public Date getCreatedAt(a) {
		return createdAt;
	}

	@Override
	public String table(a) {
		return "book_shelf";
	}

	@Override
	public TableOptions options(a) {
		return new TableOptions().option("engine"."innodb");
	}

	@Override
	public TableIndices indices(a) {
		return new TableIndices().primary("user_id"."book_id");
	}

	/* * Split table policy */
	@Override
	public String suffix(a) {
		var crc32 = new CRC32();
		crc32.update(userId.getBytes(Utils.UTF8));
		return String.valueOf(Math.abs(crc32.getValue()) % PARTITIONS);
	}

	/** ** branch library policy */
	public static class GridStrategy<D extends DB> 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];
			returnMath.abs(userId.hashCode()) % dbs; }}}Copy the code

Defining a single database

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(); / / the connection pool
		ds.setUrl(uri);
		this.ds = ds;
	}

	@Override
	protected Connection conn(a) {  // Get the link
		try {
			return ds.getConnection();
		} catch (SQLException e) {
			throw newKidsException(e); }}}Copy the code

Define grid database – sub – library

public class GridDemoDB extends GridDB<DemoDB> {

	/** * pass in multiple DB objects */
	public GridDemoDB(DemoDB[] dbs) {
		super(dbs);
		this.registerGridables();
	}

	/* * Register the entity class branch policy */
	@Override
	public void registerGridables(a) {
		this.gridWith(BookShelf.class, newBookShelf.GridStrategy<DemoDB>()); }}Copy the code

Single form primary key add delete change check

public class DemoSimplePk {

	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);
		try {
			db.create(User.class); / / create a table
			var user = new User("test1"."nick1"."passwd1");
			db.insert(user); / / insert
			System.out.println(user.getId());
			user = db.get(User.class, user.getId());  // Primary key query
			System.out.printf("%s %s %s %s %s\n", user.getId(), user.getName(), user.getNick(), user.getPasswd(),
					user.getCreatedAt());
			user = new User("test2"."nick2"."passwd2");
			db.insert(user); / / insert again
			var count = db.count(User.class); // Query the total number of rows
			System.out.println(count);
			var users = db.find(User.class);  // Lists all rows
			System.out.println(users.size());
			for (var u : users) {
				System.out.printf("%s %s %s %s %s\n", u.getId(), u.getName(), u.getNick(), u.getPasswd(),
						u.getCreatedAt());
			}
			users = db.find(User.class, Q.eq_("nick"), "nick2"); // Conditional query
			System.out.println(users.size());
			var setters = new HashMap<String, Object>();
			setters.put("passwd"."whatever");
			db.update(User.class, setters, 2); / / modify
			users = db.find(User.class); // List all rows again
			System.out.println(users.size());
			for (var u : users) {
				System.out.printf("%s %s %s %s %s\n", u.getId(), u.getName(), u.getNick(), u.getPasswd(),
						u.getCreatedAt());
			}
			db.delete(User.class, 1); / / delete
			db.delete(User.class, 2); / / delete again
			count = db.count(User.class); // Count all rows
			System.out.println(count);
		} finally {
			db.drop(User.class); / / delete table}}}Copy the code

Single table compound primary key add delete change check

public class DemoCompoundPk {
	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);
		try {
			db.create(Member.class);  / / table
			var member = new Member(1.2."boss".null);
			db.insert(member); / / insert
			member = db.get(Member.class, 1.2); // Primary key query
			System.out.println(member.getTitle());
			member = new Member(2.2."manager".new Date());
			db.insert(member); / / insert again
			var count = db.count(Member.class);  // Get the total number of rows
			System.out.println(count);
			var members = db.find(Member.class); // Get all rows
			for (var m : members) {
				System.out.printf("%d %d %s %s\n", m.getUserId(), m.getGroupId(), m.getTitle(), m.getCreatedAt());
			}
			member = new Member(2.3."manager".new Date());
			db.insert(member); / / insert again
			members = db.find(Member.class, Q.eq_("group_id"), 2);  // Conditional query
			for (var m : members) {
				System.out.printf("%d %d %s %s\n", m.getUserId(), m.getGroupId(), m.getTitle(), m.getCreatedAt());
			}
			var setters = new HashMap<String, Object>();
			setters.put("title"."employee");
			db.update(Member.class, setters, 2.3); / / modify
			member = db.get(Member.class, 2.3); // Primary key query
			System.out.println(member.getTitle());
			db.delete(Member.class, 1.2); / / delete
			db.delete(Member.class, 2.2); / / delete
			db.delete(Member.class, 2.3); / / delete
			count = db.count(Member.class); // Get the total number of rows
			System.out.println(count);
		} finally {
			db.drop(Member.class); / / delete table}}}Copy the code

Complex queries

public class DemoComplexQuery {
	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);
		try {
			db.create(Exam.class); / / table
			var random = new Random();
			for (var i = 0; i < 100; i++) {
				var userId = Math.abs(random.nextLong());
				var exam = new Exam(userId, random.nextInt(100), random.nextInt(100), random.nextInt(100),
						random.nextInt(100), random.nextInt(100), random.nextInt(100));
				db.insert(exam); / / insert
			}
			System.out.println(db.count(Exam.class)); // Query the total number of rows
			// math >= 50
			var exams = db.find(Exam.class, Q.ge_("math"), 50); // Conditional query
			System.out.println(exams.size());
			var count = db.count(Exam.class, Q.ge_("math"), 50); // Total number of conditional rows
			System.out.println(count);
			// math > 50 & english >= 50
			exams = db.find(Exam.class, Q.and(Q.gt_("math"), Q.ge_("english")), 50.50); // Conditional query
			System.out.println(exams.size());
			count = db.count(Exam.class, Q.and(Q.gt_("math"), Q.ge_("english")), 50.50); // Total number of conditional rows
			System.out.println(count);
			// math > 50 || english >= 50
			exams = db.find(Exam.class, Q.or(Q.gt_("math"), Q.ge_("english")), 50.50); // Conditional query
			System.out.println(exams.size());
			count = db.count(Exam.class, Q.or(Q.gt_("math"), Q.ge_("english")), 50.50); // Total number of conditional rows
			System.out.println(count);
			// math > 50 && (english >= 50 || chinese > 60)
			exams = db.find(Exam.class, Q.and(Q.gt_("math"), Q.or(Q.ge_("english"), Q.gt_("chinese"))), 50.50.60); // Conditional query
			System.out.println(exams.size());
			count = db.count(Exam.class, Q.and(Q.gt_("math"), Q.or(Q.ge_("english"), Q.gt_("chinese"))), 50.50.60); // Total number of conditional rows
			System.out.println(count);
			// math > 50 || physics between 60 and 80 || chemistry < 60
			exams = db.find(Exam.class, Q.or(Q.gt_("math"), Q.between_("physics"), Q.lt_("chemistry")), 50.60.80.60); // Conditional query
			System.out.println(exams.size());
			count = db.count(Exam.class, Q.or(Q.gt_("math"), Q.between_("physics"), Q.lt_("chemistry")), 50.60.80.60); // Total number of conditional rows
			System.out.println(count);
			// group by math / 10
			var q = Q.select().field("(math div 10) * 10 as mathx"."count(1)").table("exam").groupBy("mathx")
					.having(Q.gt_("count(1)")).orderBy("count(1)"."desc"); // Complex SQL constructs
			var rank = new LinkedHashMap<Integer, Integer>();
			db.any(Exam.class, q, stmt -> { // Native SQL query
				stmt.setInt(1.0);
				ResultSet rs = stmt.executeQuery();
				while (rs.next()) {
					rank.put(rs.getInt(1), rs.getInt(2));
				}
				return rs;
			});
			rank.forEach((mathx, c) -> {
				System.out.printf("[%d-%d) = %d\n", mathx, mathx + 10, c);
			});
		} finally{ db.drop(Exam.class); }}}Copy the code

table

public class DemoPartitioning {
	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);
		try {
			for (int i = 0; i < BookShelf.PARTITIONS; i++) {
				db.create(BookShelf.class, String.valueOf(i)); // Create all subtables
			}
			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);
				db.insert(bs); // Insert the corresponding table automatically
			}
			for (int i = 0; i < BookShelf.PARTITIONS; i++) {
				System.out.printf("partition %d count %d\n", i, db.count(BookShelf.class, String.valueOf(i)));
			}
			Random random = new Random();
			for (var bs : bss) {
				bs.setComment("comment_update_" + random.nextInt(100));
				db.update(bs); // Update, automatically update the corresponding table data
			}
			bss = new ArrayList<BookShelf>();
			for (int i = 0; i < BookShelf.PARTITIONS; i++) {
				bss.addAll(db.find(BookShelf.class, String.valueOf(i))); // Specify a table to list all rows
			}
			for (var bs : bss) {
				System.out.println(bs.getComment());
			}
			for (var bs : bss) {
				db.delete(bs); // Delete table data one by one}}finally {
			for (int i = 0; i < BookShelf.PARTITIONS; i++) {
				db.drop(BookShelf.class, String.valueOf(i)); // Delete all subtables}}}}Copy the code

depots

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); // Construct Grid instance
		try {
			for (int k = 0; k < BookShelf.PARTITIONS; k++) {
				grid.create(BookShelf.class, String.valueOf(k)); // Create tables in all branches
			}
			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); // insert, automatically distributed to the corresponding sub-table in the sub-library
			}
			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))); // select * from table where row number = 1
				}
			}
			Random random = new Random();
			for (var bs : bss) {
				bs.setComment("comment_update_" + random.nextInt(100));
				grid.update(bs); // Update, automatically distributed to the corresponding sub-table in the sub-library
			}
			for (var bs : bss) {
				bs = grid.get(BookShelf.class, bs.getUserId(), bs.getBookId()); // the primary key query is automatically distributed to the corresponding sub-table in the sub-database
				System.out.println(bs.getComment());
			}
			for (var bs : bss) {
				grid.delete(bs); // delete, automatically distribute to the corresponding sub-table in the sub-library
			}
			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))); // select * from table where row number = 1}}}finally {
			for (int k = 0; k < BookShelf.PARTITIONS; k++) {
				grid.drop(BookShelf.class, String.valueOf(k)); // Delete all subtables in the database}}}}Copy the code

Event context object

public class Context {

	private DB db; // Database instance
	private Connection conn;  // The current link
	private Class<? extends IEntity> clazz; // The current entity class
	private Q q; / / query SQL
	private Object[] values; // Query binding parameters
	private boolean before; // before or after
	private Exception error; / / exception
	private long duration; / / take microsecond

}
Copy the code

Event callback

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 -> { // Global event callback
			System.out.printf("db=%s sql=%s cost=%dus\n", ctx.db().name(), ctx.q().sql(), ctx.duration());
			return true; // Returning false causes the chain of events to terminate and subsequent ORM operations will not be performed
		});
		try {
			db.create(User.class);
			db.scope(ctx -> { // Scope callback. All ORM operations inside the execute method are called back
				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); / / delete table}}}Copy the code