Why use orm
As we all know, the emergence of orm greatly simplifies the complex and cumbersome functions originally implemented by sql. For most programmers, the emergence of a framework is to improve productivity. DBC defines the specification of interactive database, any database operation only needs to meet the jdbc specification, and orm is to simplify the operation of jdbc. I am personally “fortunate” to experience the two big ORMs of .net and java. I can only say that there is a big gap. Of course, some characteristics of the language also make java have a relatively slow progress in implementing ORM, such as the emergence of generics, lambda appear.
I think a good orm needs to meet the following points
- Strong type, if it does not support strong type, it is no different from handwritten sql
- It can realize 80% of the functions of pure handwritten sql, and a good orm needs to cover common business functions
- Support generics, “If an orm does not even support generics, then there is no need to exist” This is a realistic but cruel conclusion, but generics will greatly reduce the developer’s writing error rate
- You should not rely on too many components. Of course, this is not unique to orm. In fact, the less dependencies you have on any library, the less prone to bugs
In fact, having said so much, a summary is that a good orm should have ide prompts and generic constraints to help developers write down the code very smoothly, and the error part can be completely brought out during compilation, and the runtime error should be as far as possible Probably less to avoid.
Why give up mybatis
First of all, if you have used orm in other languages, then using java mybatis is like you are used to java stream and then go to process data filtering by yourself, just like you are used to kotlin syntax and then return to java syntax, it is very uncomfortable. This discomfort is not the gap between automatic transmission and manual transmission, but the gap between automatic transmission and trolley.
xml configures sql and I don’t know which “little genius” came up with it. Let’s not talk about java code and xml code jumping around when writing code, and xml under >, < must cooperate with CDATA, otherwise the xml parsing will fail, let alone escaping, I wrote that thing and added escaping, you are sure to make my eyes not tired after watching it Is it dead? The name is that xml and code are separated for easy maintenance, but no matter how convenient you modify the code, you need to restart, and because the code is written in xml, the ability to dynamically condition is relatively weak. And I don't know why mybatis doesn't support paging by nature, it needs a paging plug-in to support it, is it still necessary for a 3202 orm, it's hard to understand that the author of mybatis doesn't write crud code? Sometimes simplicity is not the reason for laziness, of course, it may also be caused by architectural problems.
Tombstone function I think a little more normal enterprises will have this function, but because of the use of myabtis, because handwritten sql, so often forget to add tombstone field in sql, resulting in some strange bugs need to be checked, Because these are errors that the compiler cannot reflect, because it is a string, because mybatis points the cause of this problem to the user, which is very smart, this is the user's error rather than the framework, but what the framework has to do is Encapsulate and hide some repetitive work as much as possible and complete it automatically.
Maybe some users will say what you see is what you get so that I can know how he executes it, but now which orm does not have the sql printing function, which orm framework executes sql and prints sql is different, not what you see is what you get . Generally speaking, I think mybatis is sqltemlate at best, and it is better than sqlhelper because it is parameterized to prevent sql injection. Of course, the most important thing is that java programmers do not need to modify the table, do not need to move the table structure, and do not need post-maintenance? There are unit tests. After I switched to java, I understood a little bit. It turns out that this is what you often say that overtime in java is serious. Using this framework to work overtime is not serious, there will be ghosts.
Why give up mybatis derived framework
I was fortunate enough to see the mybatis-plus framework on the Internet in 201 years. This framework attracted me as soon as it appeared, because it is very similar to .net's orm in the way it handles sql, at least both Strong type, at least you don’t need java files and xml files to jump around. Usually 50% of the code can also be realized through the lambda expression of the framework. I personally reject its string mode querywrapper , because a strongly typed language lacks strong type hints, it will be very strange when writing code. Including later refactoring, of course, if your code does not require you to maintain in the future, then I think it is ok whichever method you use. Anyway, it is a one-off, and the result will be fine.
Continue to talk about mybatis-plus, because the work needs to remodel the internal framework around 2020, and let mybatis-plus support strongly typed group by, sum, min, max, any and other APIs.
At this time, in most cases, it can already be dealt with, and this framework has been used for about a year, including the increment and decrement of subsequent updates.
update table set column=column-1 where id=xxx and column>1
All use lambda strong type syntax, which can deal with most situations, but there is still no good method for join. Until I met mpj, which is mybatis-plus-join, but there is also a problem with this framework, that is, this logical deletion does not take effect on the join sub-table and needs to be processed manually. If it takes effect, then on where, I don’t know how it is now. At that time, I also implemented it by myself to make it appear after the on of the join, but because the implementation needs to implement a certain interface, there is no pr code.
First define an interface
public interface ISoftDelete {
Boolean getDeleted();
}
//The join mapper is my own implementation, mainly the definition of `WrapperFunction`
@Override
public Scf4jBaseJoinLinq<T1,TR> on(WrapperFunction<MPJAbstractLambdaWrapper<T1, ?>> onFunction) {
WrapperFunction<MPJAbstractLambdaWrapper<T1, ?>> join= on->{
MPJAbstractLambdaWrapper<T1, ?> apply = onFunction.apply(on);
if(ISoftDelete. class. isAssignableFrom(joinClass)){
SFunction deleted = LambdaHelper.getFunctionField(joinClass, "deleted", Boolean.class);
apply.eq(deleted,false);
}
return apply;
};
joinMapper.setJoinOnFunction(query->{
query. innerJoin(joinClass, join);
});
return joinMapper;
}
Although join is implemented, there are still many problems and bugs.
- For example, the return of vo objects is not supported, only the custom return columns of database objects can be returned, otherwise all columns are queried
- For another example, if you want your object to be updated with null filled into the database, you can only add it to the entity field, which will cause this field to either take effect or not take effect at all.
- Batch insertion does not support the default is foreach one by one, of course, it doesn't matter, but if you really want to implement batch processing, you need to write very complicated code yourself and need to support all fields. Instead of null columns don't populate
MetaObjectHandler, supportsinsertandupdateofentitybut does not supportlambdaUpdateWrapper, sometimes Both the current updater and the update time are required. You can also say that the database can set the last update time, but what about the last updater?- Very complicated dynamic table name, please brother, I just want to change the table name. The current solution is to clean up the current thread every time try-finally is used up, because tomcat will reuse threads, and realize it through threadlocal. In other words, pagehelper It must have been done this way.
Of course, there are many other problems that caused me to be unbearable in the end. I chose the self-developed framework. Of course, my self-developed framework refers to a part of the freesql and sqlsuagr APIs, and there are also java beetsql implementations and some methods. After all, you can see farther by standing on the shoulders of giants. Don’t ask me why I don’t refer to mybatis. I think mybatis has complicated simple problems. If you need to understand his code, it’s not worth the loss. In the end, I I found that my choice was correct. I quickly understood what I should do on the java side by referring to the source code ofbeetsql, which saved me a lot of time in writing the follow-up framework.beetsqladvertisehttps://gitee.com/xiandafu/beetlsql
What are the characteristics of self-developed orm
easy-query A brand-new high-performance orm without any dependencies in java supports single-table multi-table sub-query logical deletion multi-tenant difference update cascade one-to-one one-to-many-to-one-to-many sub-database sub-table (Support cross-table query paging, etc.) Efficient encryption and decryption of dynamic table name database columns Support like crud interceptor atomic update vo object returns directly
Document Address Project Homepage | Document Demo
GITHUB address GitHub - xuejmnet/easy-query: java/kotlin high performance lightweight solution for jdbc query,support sharding table and database support master-replica
GITEE address easy-query: java/kotlin high performance lightweight solution for jdbc query, support sharding table and database support master-replica
- Strong typing can help the team have id hints when building and querying data, and it is easy to maintain later.
- Generics can control some low-level errors when we write code. For example, I only query one table, but in the where statement, a table without context can be used as a condition to further limit and strengthen the expression
- easy-query provides three modes: lambda, property, and apt proxy, where lambda expression is convenient for refactoring and maintenance, property is only the best performance, and apt proxy is easy for maintenance, but refactoring requires refactoring apt files together
Single table query
//Query the first record in the table according to the condition
List<Topic> topics = easyQuery
.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
//Query the set whose id is 3 according to the condition
List<Topic> topics = easyQuery
.queryable(Topic.class)
.where(o->o.eq(Topic::getId,"3").eq(Topic::geName,"4")
.toList();
==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM t_topic t WHERE t.`id` = ? AND t.`name` = ?
==> Parameters: 3(String), 4(String)
<== Total: 1
Multiple tables
Topic topic = easyQuery
.queryable(Topic.class)
//join is followed by a two-parameter delegate, and the order of the parameters indicates the order of the join table, which can be switched by the then function
.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 t.`id` = t1.`id` WHERE t .`id` = ? LIMIT 1
==> Parameters: 3 (String)
<== Total: 1
List<BlogEntity> blogEntities = easyQuery
.queryable(Topic.class)
//join is followed by a two-parameter delegate, and the order of the parameters indicates the order of the join table, which can be switched by the then function
.innerJoin(BlogEntity.class, (t, t1) -> t.eq(t1, Topic::getId, BlogEntity::getId))
.where((t, t1) -> t1.isNotNull(BlogEntity::getTitle).then(t).eq(Topic::getId, "3"))
//The join query select must have the corresponding return result, which can be a custom dto or an entity object. If there is no object, it will return the main table data of the t table
.select(BlogEntity.class, (t, t1) -> t1.columnAll())
.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
subquery
```java
//SELECT * FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?
Queryable<BlogEntity> subQueryable = easyQuery. queryable(BlogEntity. class)
.where(o -> o.eq(BlogEntity::getId, "1"));
List<Topic> x = easyQuery
.queryable(Topic.class).where(o -> o.exists(subQueryable.where(q -> q.eq(o, BlogEntity::getId, Topic::getId)))).toList();
==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t WHERE EXISTS (SELECT 1 FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ? AND t1.`id` = t.`id`)
==> Parameters: false(Boolean),1(String)
<== Time Elapsed: 3(ms)
<== Total: 1
//SELECT t1.`id` FROM `t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?
Queryable<String> idQueryable = easyQuery. queryable(BlogEntity. class)
.where(o -> o.eq(BlogEntity::getId, "123"))
.select(String.class, o -> o.column(BlogEntity::getId));//If the subquery is in string, then you need to select string, if it is integer, then select integer, both sides need to be consistent
List<Topic> list = easyQuery
.queryable(Topic.class).where(o -> o.in(Topic::getId, idQueryable)).toList();
==> Preparing: SELECT t.`id`,t.`stars`,t.`title`,t.`create_time` FROM `t_topic` t WHERE t.`id` IN (SELECT t1.`id` FROM ` t_blog` t1 WHERE t1.`deleted` = ? AND t1.`id` = ?)
==> Parameters: false(Boolean),123(String)
<== Time Elapsed: 2(ms)
<== Total: 0
Custom Tombstone
//@Component //If it is spring
public class MyLogicDelStrategy extends AbstractLogicDeleteStrategy {
/**
* Allow attributes of datetime type
*/
private final Set<Class<?>> allowTypes=new HashSet<>(Arrays. asList(LocalDateTime. class));
@Override
protected SQLExpression1<WherePredicate<Object>> getPredicateFilterExpression(LogicDeleteBuilder builder, String propertyName) {
return o->o.isNull(propertyName);
}
@Override
protected SQLExpression1<ColumnSetter<Object>> getDeletedSQLExpression(LogicDeleteBuilder builder, String propertyName) {
// LocalDateTime now = LocalDateTime. now();
// return o->o.set(propertyName,now);
//The above is a wrong usage, after getting the now value, then this now is a fixed value instead of a dynamic value
return o->o.set(propertyName,LocalDateTime.now())
.set("deletedUser",CurrentUserHelper.getUserId());
}
@Override
public String getStrategy() {
return "MyLogicDelStrategy";
}
@Override
public Set<Class<?>> allowedPropertyTypes() {
return allowTypes;
}
}
//In order to test to prevent data from being deleted, a non-existent id is used here
logicDelTopic.setId("11xx");
//test the current person
CurrentUserHelper.setUserId("easy-query");
long l = easyQuery.deletable(logicDelTopic).executeRows();
==> Preparing: UPDATE t_logic_del_topic_custom SET `deleted_at` = ?, `deleted_user` = ? WHERE `deleted_at` IS NULL AND `id` = ?
==> Parameters: 2023-04-01T23:15:13.944(LocalDateTime),easy-query(String),11xx(String)
<== Total: 0
Diff Update
- Pay attention to whether the tracking is enabled under
spring-bootand use the@EasyQueryTrackannotation to enable it- Whether to add the current object to the tracking context Query to add
asTrackingor manually performeasyQuery.addTracking(Object entity)on the queried object
TrackManager trackManager = easyQuery.getRuntimeContext().getTrackManager();
try {
trackManager.begin();
Topic topic = easyQuery. queryable(Topic. class)
.where(o -> o.eq(Topic::getId, "7")).asTracking().firstNotNull("No corresponding data found");
String newTitle = "test123" + new Random(). nextInt(100);
topic.setTitle(newTitle);
long l = easyQuery.updatable(topic).executeRows();
} finally {
trackManager. release();
}
==> Preparing: UPDATE t_topic SET `title` = ? WHERE `id` = ? ==> Parameters: test1239(String),7(String) <== Total: 1
Associated query
one to one
Students and Student Addresses
//database object query
List<SchoolStudent> list1 = easyQuery. queryable(SchoolStudent. class)
.include(o -> o.one(SchoolStudent::getSchoolStudentAddress).asTracking().disableLogicDelete())
.toList();
//vo custom column mapping returns
List<SchoolStudentVO> list1 = easyQuery. queryable(SchoolStudent. class)
.include(o -> o.one(SchoolStudent::getSchoolStudentAddress).asTracking().disableLogicDelete())
.select(SchoolStudentVO.class,o->o.columnAll()
.columnInclude(SchoolStudent::getSchoolStudentAddress,SchoolStudentVO::getSchoolStudentAddress))
.toList();
many to one
students and classes
//database object query
List<SchoolStudent> list1 = easyQuery. queryable(SchoolStudent. class)
.include(o -> o.one(SchoolStudent::getSchoolClass))
.toList();
//custom column
List<SchoolStudentVO> list1 = easyQuery. queryable(SchoolStudent. class)
.include(o -> o.one(SchoolStudent::getSchoolClass))
.select(SchoolStudentVO.class,o->o
.columnAll()
.columnInclude(SchoolStudent::getSchoolClass,SchoolStudentVO::getSchoolClass,s->s.column(SchoolClassVO::getId))
)
.toList();
//vo custom column mapping returns
List<SchoolStudentVO> list1 = easyQuery. queryable(SchoolStudent. class)
.include(o -> o.one(SchoolStudent::getSchoolClass))
.select(SchoolStudentVO.class,o->o
.columnAll()
.columnInclude(SchoolStudent::getSchoolClass, SchoolStudentVO::getSchoolClass)
)
.toList();
one-to-many
classes and students
//database object query
List<SchoolClass> list1 = easyQuery. queryable(SchoolClass. class)
.include(o -> o.many(SchoolClass::getSchoolStudents))
.toList();
//vo custom column mapping return
List<SchoolClassVO> list1 = easyQuery. queryable(SchoolClass. class)
.include(o -> o.many(SchoolClass::getSchoolStudents))
.select(SchoolClassVO.class,o->o.columnAll()
.columnIncludeMany(SchoolClass::getSchoolStudents,SchoolClassVO::getSchoolStudents))
.toList();
many to many
class and teacher
List<SchoolClass> list2 = easyQuery. queryable(SchoolClass. class)
.include(o -> o.many(SchoolClass::getSchoolTeachers,1))
.toList();
List<SchoolClassVO> list2 = easyQuery. queryable(SchoolClass. class)
.include(o -> o.many(SchoolClass::getSchoolTeachers))
.select(SchoolClassVO.class,o->o.columnAll()
.columnIncludeMany(SchoolClass::getSchoolTeachers,SchoolClassVO::getSchoolTeachers))
.toList();
Dynamic registration
List<BlogEntity> blogEntities = easyQuery. queryable(BlogEntity. class)
.asTable(a -> "aa_bb_cc")
.where(o -> o.eq(BlogEntity::getId, "123")).toList();
==> Preparing: 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` = ?
==> Parameters: false(Boolean),123(String)
<== Total: 0
List<BlogEntity> blogEntities = easyQuery. queryable(BlogEntity. class)
.asTable(a->{
if("t_blog".equals(a)){
return "aa_bb_cc1";
}
return "xxx";
})
.where(o -> o.eq(BlogEntity::getId, "123")).toList();
==> Preparing: 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_cc1 t WHERE t.`deleted` = ? AND t.`id` = ?
==> Parameters: false(Boolean),123(String)
<== Total: 0
List<BlogEntity> x_t_blog = easyQuery
.queryable(Topic.class)
.asTable(o -> "t_topic_123")
.innerJoin(BlogEntity.class, (t, t1) -> t.eq(t1, Topic::getId, BlogEntity::getId))
.asTable("x_t_blog")
.where((t, t1) -> t1.isNotNull(BlogEntity::getTitle).then(t).eq(Topic::getId, "3"))
.select(BlogEntity.class, (t, t1) -> t1.columnAll()).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_123 t INNER JOIN x_t_blog t1 ON t1.`deleted` = ? AND t.`id` = t1.`id` WHERE t1.`title` IS NOT NULL AND t.`id` = ?
==> Parameters: false(Boolean),3(String)
<== Total: 0