SoFunction
Updated on 2025-03-03

Sharing of using or in QueryWrapper

Scene description

Suppose we are developing a mail system where there is a table called MailInfo that is used to store mail information. There are two fields related to the user in this table: user_id represents the sender of the message, and to_user_id represents the receiver of the message. We need to write a query to find out all the emails related to the current user (userId), whether they are sent or received by him. In addition, we also need to make sure that these messages are not deleted and sorted by update time.

Demand disassembly

According to the above requirements, we need to implement the following points:

  • Filter out deleted messages (i.e. is_delete = 0).
  • Query conditions include user_id or to_user_id equals the current user ID.
  • Sort the results in descending order by update time (update_time) to ensure that the latest emails are shown in front.

These requirements can be implemented in SQL through a simple SELECT statement, similar to:

SELECT * FROM MailInfo
WHERE is_delete = 0
AND (user_id = ? OR to_user_id = ?)
ORDER BY update_time DESC;

However, in Java development, especially when using an ORM framework like MyBatis-Plus, we usually useQueryWrapperConstruct query conditions to improve the readability and maintainability of the code.

Build query criteria using MyBatis-Plus

In MyBatis-Plus, QueryWrapper is a very powerful query constructor that can dynamically construct SQL queries through streaming APIs. When dealing with complex query conditions, such as the combination of OR and AND, it also provides corresponding interfaces to meet the needs.

Let's take a look at a specific code example to show how to build our query conditions through QueryWrapper:

Page<MailInfo> pageParm = new Page<>();
(page);
(pageSize);
QueryWrapper<MailInfo> wrapper = new QueryWrapper<>();
("is_delete", 0)  // Only query undeleted mail       .and(w -> ("user_id", userId).or().eq("to_user_id", userId))  // Query the emails sent or received by the current user       .orderByDesc("update_time");  // Sort by downward order of update time
return (((pageParm, wrapper)));

Code parsing

1. Pagination parameters

Page<MailInfo> pageParm = new Page<>();
(page);
(pageSize);

In most business scenarios, we will paginate the query results to avoid returning too much data at once and affecting performance. MyBatis-Plus provides the Page class to handle paging logic. In this code, we set the current number of pages through the setCurrent method and set the number of records returned per page through setSize.

2. Construct query conditions

QueryWrapper<MailInfo> wrapper = new QueryWrapper<>();
("is_delete", 0)
       .and(w -> ("user_id", userId).or().eq("to_user_id", userId));

HereQueryWrapperIt is a query constructor provided by MyBatis-Plus, allowing us to construct query conditions through streaming.

  • eq("is_delete", 0): Add oneis_delete = 0The conditions to ensure that only mails that are not marked as deleted are queryed.
  • and(w -> ("user_id", userId).or().eq("to_user_id", userId)): This is the core part of the query criteria. We passedandMethods combine multiple conditions and use Lambda expressionswAs a parameter for internal query conditions. Inside the Lambda expression, first passeq("user_id", userId)Query the email sent by the sender is the current user, and then passor().eq("to_user_id", userId)Query the email that the recipient is the current user. In this way, the query results will contain all email records related to the current user.

3. Sort conditions

("update_time");

This line of code is used to specify sorting conditions. We want to sort from new to old by the update time of the email, so we usedorderByDescMethod andupdate_timeAs a sort field.

Result encapsulation

In actual development, we usually encapsulate the query results into a unified return structure. In this example, we encapsulate the query results into , and convert the database entity class MailInfo to MailInfoDTO by performing data conversion.

QueryWrapper's or usage tips

In useQueryWrapperWhen building a query,orMethods are a very useful tool. It allows us to add multiple possible conditions to the query, making the query result more flexible. In the example in this article, we usedorCome and queryuser_idorto_user_idEqual touserIdrecords, thus meeting business needs.

However, useorCare is also needed as it may affect the performance of the query. When large amounts of data are involved,ORThe query speed of conditions may be slower. To do this, we can consider the following optimization strategies:

  1. Create an index:make sureuser_idandto_user_idAn index is created on the field, which can greatly improve the query speed.
  2. Use cache: In scenarios with high frequency access, appropriate use of cache can reduce the number of database queries and improve the overall performance of the system.
  3. Table design:ifMailInfoThe amount of data in the table is very large, so you can consider reducing the query pressure of a single table by sub-table.

This is the article shared about the skills of using or in QueryWrapper. For more information about using QueryWrapper or, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!