jpa+querydsl flat alternative domestic easy-query best use orm
a domestic strongest java orm, perfect support for controllable strong type dsl, plus perfect support for the object model filtering pull orm, has a very smart include(s) one-to-many, one-to-one, many-to-many, many-to-many implementation of the orm
Address github./dromara/easy-query
Address gitee./dromara/easy-query
Documentation Address./easy-query-doc/
A has a high degree of abstraction to shield the major database dialects orm, so that you change the library as a fish out of water is very convenient , integrated to achieve a variety of database dialects , so you can easily cope with a variety of needs , and in the object model so that you can save a lot of time in the multi-multiple back and forth between the filter .
Scene 1
Users and roles, roles and menus we all know that this is the most basic of a scenario, where users and roles are many-to-many, roles and menus are many-to-many.
often common orm in the demonstration of the current table will only be the attributes of filtering sorting and other operations, but if you encounter a query main table filter conditions are sub-table then most orm is very troublesome, want to write often very time-consuming and laborious and does not support dynamic conditions
- Filtering Users
- The conditional role contains the administrator's
List<SysUser> managers = ()
.where(s -> {
// Filter for roles in the roles collection that have a role name called managers
().any(role -> {
().eq("administrators"); }); {//Filter for roles that have a role name called administrator inside the role collection.
});
}).toList();
What's more, if you only have one condition that you can expand on roles to assert that
if and only ifone (modulo)-to-many (math.)then the association model will be the set if you wish to assert the existence of a single attribute within the set Conditional judgment can be made by theflatElementExpand Properties Direct Judgment
List<SysUser> managers = ()
.where(s -> {
// Filter for roles in the Roles collection that have a role name called managers.
().flatElement().name().eq("Administrator"); }).toList().
}).toList();
Let's take a look at the exact sql executed
-- 1st sql data
SELECT
t.`id`,
t.`company_id`,
t.`name`,
t.`create_time`
FROM
`t_user` t
WHERE
EXISTS (
SELECT
1
FROM
`t_role` t1
WHERE
EXISTS (
SELECT
1
FROM
`t_user_role` t2
WHERE
t2.`role_id` = t1.`id`
AND t2.`user_id` = t.`id` LIMIT 1
)
AND t1.`name` = 'administrator' LIMIT 1
)
If you want to execute this sql with dynamic conditions then it's really quite desperate.
Some people say how to do dynamic conditions
List<SysUser> managers = ()
.where(s -> {
// Filter for roles in the Roles collection that have a role name called managers.
if(false){
().any(role -> {
().eq("administrator");
});
}
}).toList();
Yes, you read it right so simple a condition can be made to support dynamic many-to-many filtering, so if the condition is false then the generated sql will be how it is!
SELECT `id`,`company_id`,`name`,`age`,`create_time` FROM `t_user`
Is not easy-query so smart conditional processing surprised, if you need to write by hand then it will be a disaster not to mention the logic of deletion and a variety of interceptors
So let's move on to the next scenario
Scene 2
Users and roles and menus
Many-to-many and many-to-many.
- Filtering Users
- Conditional on the user having the /admin menu
List<SysUser> managers = ()
.where(s -> {
// Filter for the menu inside the roles collection being /admin
().any(role -> {
().any(menu -> {
().eq("/admin");
});
});
}).toList();
Wow, it's perfectly simple, and if you think it's still too complicated, then let's simplify it.
List<SysUser> managers = ()
.where(s -> {
// Filter for the menu inside the roles collection being /admin
().flatElement().menus().any(menu-> {
().eq("/admin");
});
}).toList();
//both can work, because we don't conditionally filter the roles.
// So you can just expand the element by twice flatElement to directly assert the menu
List<SysUser> managers = ()
.where(s -> {
// Filter for the menu inside the roles collection being /admin
().flatElement().menus().flatElement().route().eq("/admin");
}).toList();
Next, let's take a look at the generated sql
-- 1st sql data
SELECT
t.`id`,
t.`company_id`,
t.`name`,
t.`age`, `create_time`, `create_time`
t.`create_time`
FROM
`t_user` t
WHERE
EXISTS (
SELECT
1
FROM
`t_role` t1
WHERE
EXISTS (
SELECT
1
FROM
`t_user_role` t2
WHERE
t2.`role_id` = t1.`id`
AND t2.`user_id` = t.`id` LIMIT 1
)
AND EXISTS (
SELECT
1
FROM
`t_menu` t3
WHERE
EXISTS (
SELECT
1
FROM
`t_role_menu` t4
WHERE
t4.`menu_id` = t3.`id`
AND t4.`role_id` = t1.`id` LIMIT 1
)
AND t3.`route` = '/admin' LIMIT 1
) LIMIT 1
)
I'm already numb. I can't imagine what it would be like if I didn't have orm.
Scene 3
- user search
- The condition is that the user has no fewer than three roles in the
List<SysUser> managers = ()
.where(s -> {
// Filter for a collection of roles with a count greater than or equal to 3
().count().ge(3L);
}).toList();
Very intuitive.
What about the generated sql?
-- 1st sql data
SELECT
t.`id`,
t.`company_id`,
t.`name`,
t.`create_time`
FROM
`t_user` t
WHERE
(
SELECT
COUNT(*)
FROM
`t_role` t1
WHERE
EXISTS (
SELECT
1
FROM
`t_user_role` t2
WHERE
t2.`role_id` = t1.`id`
AND t2.`user_id` = t.`id` LIMIT 1
)
) >= 3
Scene 4
- Query Role
- The condition is that the average age of the users associated with the role is 15 years old or that there are at least 2 people with the last name Kim.
List<SysRole> roles = ()
.where(role -> {
(()->{
().avg(u->()).ge((15));
().where(u->().likeMatchLeft("generic term for lustrous and ductile metals")).count().ge(2L);
});
}).toList();
Let's take a look at the generated sql
-- 1st sql data
SELECT
t.`id`,
t.`name`, `create_time`, `create_time`, `create_time`
t.`create_time`
FROM
`t_role` t
WHERE
(
IFNULL((SELECT
AVG(t1.`age`)
FROM
`t_user` t1
WHERE
EXISTS (SELECT
1
FROM
`t_user_role` t2
WHERE
t2.`user_id` = t1.`id`
AND t2.`role_id` = t.`id` LIMIT 1)),0) >= '15'
OR (
SELECT
COUNT(*)
FROM
`t_user` t4
WHERE
EXISTS (
SELECT
1
FROM
`t_user_role` t5
WHERE
t5.`user_id` = t4.`id`
AND t5.`role_id` = t.`id` LIMIT 1
)
AND t4.`name` LIKE 'gold%'
) >= 2
)
Don't look at this sql so complicated this is a many-to-many query normal people simply can't write this kind of sql!
ultimate
This side shows a very powerful OLTP query pattern , OLAP is also very powerful can group + join, to achieve from (anonymous sql) can also join (anonymous sql)
A perfect solution with a strong type of OLTP OLAP , and perfect support for mybatis series of any architecture step-by-step build migration , there will be no conflict , because easy-query itself is zero dependency , and completely free , completely open source (including documentation!!!)! Including documentation! Including documentation!)
I'm sure.easy-queryis a can completely impress your ORM works , but also the whole java is the only one full sql alternative products