Author Introduction
-
Name: xuejm -
Member of dromara open source organization, author of dromara/easy query -
A novice with more than 10 years' experience in java/. net related development -
I love open source technology, and I am willing to research and share it. I have many open source projects~ -
Personal space: https://github.com/xuejmnet and https://gitee.com/xuejm
preface
Introduction to EasyQuery
background
1. The use of orm is a string that is not easy to maintain, and the ide cannot give intelligent prompts
2. The orm does not support join or the multi table join function is weak and cannot satisfy the group+join sub query or from sub query
3. The relationship between the objects of the orm model's cascade filtering is too weak. Only one-to-one one-to-one pull is supported, and filtering is not supported
4. There is no unified function to support mapping and translation to all databases
5. The weak function of the orm needs to add various external components to support the business
6. orm cannot be perfectly integrated and compatible with the existing pojo system
7. It does not support sub database and sub table. It needs to introduce sharding to support it
1. Easy to use and efficient api, the convenience and maintainability of streaming writing, and the rejection of magic value
2. Strong typing can only prompt that even if you are a beginner, Xiaobai can easily write the desired code under the hint of idea
3. Complex multi table joins, sub queries, and exists no longer need to worry about the need to introduce additional components in the face of various complex business scenarios
4. The model relation query will R It is not only simple to query the results, but also perfect to filter the object model, just like stream queries the database any , none And other methods to assert data related to me
5. Structured DTO can be pulled arbitrarily. After users create various model relationships, they can quickly pull out objects of any structure and turn them into json. There is no need to worry about one-to-one, many to one, one to many, and many to many retrieval in paging
6. Perfect elimination of performance problems caused by n+1
7. All functions are completely free and permanently open source, and the author writes his own idea plug-in to help everyone improve efficiency Happy code
Actual business case
User Filtering
Simple filtering
//Filter user names containing Xiaoming List<SysUser> users = easyEntityQuery.queryable(SysUser.class) . where (user ->user. name(). like ("Xiao Ming")) .toList() //Filter user names containing Xiaoming List<SysUser> users = easyEntityQuery.queryable(SysUser.class) //Ifnull (name, 'Xiao Wang') like 'Xiao Ming' . where (user ->user. name(). nullOrDefault ("Xiao Wang"). like ("Xiao Ming")) .toList() //Filter users whose name contains Xiao Ming and whose age is less than 18 List<SysUser> users = easyEntityQuery.queryable(SysUser.class) .where(user->{ //name like ? and age < ? User. name(). like ("Xiao Ming"); user.age().lt(18) }) .toList() //Return paging results EasyPageResult<SysUser> users = easyEntityQuery.queryable(SysUser.class) .where(user->{ //name like ? and age < ? User. name(). like ("Xiao Ming"); user.age().lt(18) }) . orderBy (user ->user. createTime(). desc())//Reverse creation time by user .toPageResult(1,20) //Filter users whose names include Xiao Ming or those younger than 18 List<SysUser> users = easyEntityQuery.queryable(SysUser.class) .where(user->{ user.or(()->{ //name like ? or age < ? User. name(). like ("Xiao Ming"); user.age().lt(18) }) }) .toList() //If the filtered user name contains Xiao Ming or is younger than 18 years old, only the user ID and name will be returned List<SysUser> users = easyEntityQuery.queryable(SysUser.class) .where(user->{ user.or(()->{ //name like ? or age < ? User. name(). like ("Xiao Ming"); user.age().lt(18) }) }) //Only return id and name .select(user-> user.FETCHER.id().name().fetchProxy()) .toList()
Implicit filtering
Because the relationship between users and enterprises is many to one, you can use the object relationship to filter users based on enterprises, so that you can join automatically
//Filter the users whose names include Xiao Ming and who work in JAVA enterprises List<SysUser> list = easyEntityQuery.queryable(SysUser.class) .where(user -> { User. name(). like ("Xiao Ming"); User. company(). name(). like ("JAVA Enterprise"); }).toList(); //from user left join company on ... where user.name like ? and company.name like ?
When we create a good enterprise relationship in the user object, we can use the enterprise as a condition when filtering users. On the contrary, when we create a good enterprise user relationship in the enterprise model, we can also use the user as a condition when filtering enterprises to help us quickly filter
//Filter the enterprise. The filter condition is that the name of the employee under the enterprise is Xiaoming List<Company> companies = easyEntityQuery.queryable(Company.class) .where(com -> { com.users().any(u -> { u. Name(). like ("Xiao Ming"); }); }).toList(); //from company where exists( from user where .... and user.name like ?) //Filter the user by the menu path where the user contains/admin List<SysUser> userWithMenuContainsAdminPath = easyEntityQuery.queryable(SysUser.class) .where(user -> { //The filter condition is the role under the user. Because the role does not need to be filtered, the judgment menu is directly expanded //If any path in the menu is'/admin ' user.roles().flatElement().menus().any(menu -> menu.route().like("/admin")); }).toList();
//Query the user join enterprise table if the enterprise name contains the words JAVA enterprise List<Company> companies = easyEntityQuery.queryable(SysUser.class) .leftJoin(Company.class, (user, com) -> user.companyId().eq(com.id())) . where ((user, com) ->com. name(). like ("JAVA Enterprise")) .select((user, com) -> com).toList(); //select company.* from user left join company on user.company_id = company.id where company.name like ?
Users and menus are not directly related. The middle and role tables are many to many. There are also two tables in the middle: user_role and role_menu
//Query the menu id set owned by Xiaoming user List<String> menuIds = easyEntityQuery.queryable(SysUser.class) . where (user ->user. name(). eq ("Xiao Ming")) .toList(user -> user.roles().flatElement().menus().flatElement().id()); //Query the menu set owned by Xiaoming user List<SysMenu> menus = easyEntityQuery.queryable(SysUser.class) . where (user ->user. name(). eq ("Xiao Ming")) .toList(user -> user.roles().flatElement().menus().flatElement());
Return Results
{user:{id:xxx,roles:[{...}]}}
//Return users under 18 years old with roles List<SysUser> userAndRoles = easyEntityQuery.queryable(SysUser.class) .includes(user -> user.roles()) .where(user -> user.age().lt(18)) .toList(); //Return users and roles owned by users Page returns one to many EasyPageResult<SysUser> userAndRolePage = easyEntityQuery.queryable(SysUser.class) .includes(user -> user.roles()) .where(user -> user.age().lt(18)) .toPageResult(1, 20);
Sometimes we need to obtain the intermediate results above to facilitate the internal calculation of Java. Most forms only support returning map objects at this time, which will lead to the spread of strings and the loss of specific types during use, making it very inconvenient to use
//The query returns the user ID, the user name and the enterprise name of the user. There is no need to define the intermediate object. It is suitable for temporary context acquisition or group aggregation results List<Draft3<String, String, String>> userInfo = easyEntityQuery.queryable(SysUser.class) .where(user -> { User. name(). like ("Xiao Ming"); User. company(). name(). like ("JAVA Enterprise"); }).select(user -> Select.DRAFT.of( user.id(), user.name(), user.company().name() )).toList(); for (Draft3<String, String, String> userAdnCom : userInfo) { String userId = userAdnCom.getValue1(); String userName = userAdnCom.getValue2(); String companyName = userAdnCom.getValue3(); } List<Draft2<String, Integer>> list2 = easyEntityQuery.queryable(SysUser.class) .where(user -> { User. name(). like ("Xiao Ming"); User. company(). name(). like ("JAVA Enterprise"); }) .groupBy(user -> GroupKeys.TABLE1.of(user.name())) .select(group -> Select.DRAFT.of( group.key1(),//user.name group.sum(group.groupTable().age())//sum(user.age) )).toList();
Structured DTO
-
First, we install it in the idea plug-in market EasyQueryAssistant Plug in latest version -
Part 2 Right click CreateStructDTO in the package where DTO needs to be created -
Step 3: Select the structured data to be returned 
The following dto will be generated
/** * this file automatically generated by easy-query struct dto mapping *The current file is a structured dto map automatically generated by easy query * {@link com.easy.query.test.entity.blogtest.SysUser } * * @author easy-query */ @Data public class UserRoleMenuDTO { private String id; private String name; @Navigate(value = RelationTypeEnum.ManyToMany) private List<InternalRoles> roles; /** * {@link com.easy.query.test.entity.blogtest.SysRole } */ @Data public static class InternalRoles { private String id; private String name; @Navigate(value = RelationTypeEnum.ManyToMany) private List<InternalMenus> menus; } /** * {@link com.easy.query.test.entity.blogtest.SysMenu } */ @Data public static class InternalMenus { private String id; private String name; private String route; private String icon; } } //One sentence return of the required structure DTO //It can be mapped to our DTO through selectAutoInclude, and can return any object relationship List<UserRoleMenuDTO> menus = easyEntityQuery.queryable(SysUser.class) .where(u -> { u. Name(). like ("Xiao Ming"); u.createTime().rangeClosed(LocalDateTime.now().plusDays(-100),LocalDateTime.now()); }) .selectAutoInclude(UserRoleMenuDTO.class) .toList();
Function translation
General function
| |
|
|---|---|
| |
|
| |
|
| |
|
| |
|
| |
|
String function
| |
|
|---|---|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
Time function
| |
|
|---|---|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
//Function nesting List<SysUser> users = easyEntityQuery.queryable(SysUser.class) .where(user -> { user.createTime() .nullOrDefault(LocalDateTime.now()) .plus(1, TimeUnit.DAYS) .format("yyyy-MM-dd HH🇲🇲ss") .eq("2024-01-01 00:00:00"); }).toList(); //SQL:FROM user WHERE DATE_FORMAT(date_add(IFNULL(`create_time`,?), interval (?) microsecond),'%Y-%m-%d %H:%i:%s') = ? //Parameters: 2024-05-20T14:40:08.152 (LocalDateTime), 86400000000 (Long), 2024-01-01 00:00:00 (String)
last
