Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

When using MyBatis Plus for database operations, we sometimes need to get data from multiple tables. This article will introduce how to implement left join queries in MyBatis Plus. We will take two tables as examples, one is the department leader table (system_dept_leader), and the other is the user table (system_user), and demonstrates how to use a left join to obtain detailed information about the department head, including the user's nickname and work number.

Data table structure

Suppose we have the following two data tables:

Department Leaders Table (system_dept_leader)

 

user table (system_user)

Implementation steps

We will implement the left join query by following the steps below:

1. Define the query result class
2. Writing SQL Providers
3. Define query methods in the Mapper interface

1. Define the query result class

First, we need to define a query result class to store query results. Here we define a DeptLeaderDORespVO Class, inherited from the base classDeptLeaderDOBaseVO, and contains the user's nickname and work number fields.

@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
public class DeptLeaderDORespVO extends DeptLeaderDOBaseVO {
 
    private Long id;
 
    @NotNull(message = "Department不能为空")
    private Long deptId;
 
    @NotNull(message = "User不能为空")
    private Long userId;
 
    @NotNull(message = "Is Primary不能为空")
    private Integer isPrimary;
 
    private String nickname;
    private String workerCode;
}

2. Write a SQL provider

Next, we write a SQL provider class to dynamically generate SQL queries. SelectProvider Annotation to specify custom SQL.

public class DeptLeaderSqlProvider {
 
    public String selectDeptLeaderWithUserDetails(Map<String, Object> params) {
        Long deptId = (Long) params.get("deptId");
        Long userId = (Long) params.get("userId");
        Integer isPrimary = (Integer) params.get("isPrimary");
 
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT dl.*, su.nickname, su.worker_code AS workerCode ");
        sql.append("FROM system_dept_leader dl ");
        sql.append("LEFT JOIN system_user su ON dl.user_id = su.id ");
        sql.append("WHERE 1=1 ");
        if (deptId != null) {
            sql.append("AND dl.dept_id = #{deptId} ");
        }
        if (userId != null) {
            sql.append("AND dl.user_id = #{userId} ");
        }
        if (isPrimary != null) {
            sql.append("AND dl.is_primary = #{isPrimary} ");
        }
        sql.append("ORDER BY dl.id DESC");
        return sql.toString();
    }
}

3. Define query methods in the Mapper interface

Finally, define the query method in the Mapper interface and use SelectProvider Annotation specifies the SQL provider.

@Mapper
public interface DeptLeaderMapper extends BaseMapperX<DeptLeaderDO> {
 
    @SelectProvider(type = DeptLeaderSqlProvider.class, method = "selectDeptLeaderWithUserDetails")
    List<DeptLeaderDORespVO> selectDeptLeaderWithUserDetails(@Param("deptId") Long deptId,
                                                             @Param("userId") Long userId,
                                                             @Param("isPrimary") Integer isPrimary);
}

4. Calling service layer methods

The query method is called in the service layer and the results are returned to the front end or for further processing.

@Service
public class DeptLeaderService {
 
    @Autowired
    private DeptLeaderMapper deptLeaderMapper;
 
    public List<DeptLeaderDORespVO> getDeptLeaders(Long deptId, Long userId, Integer isPrimary) {
        return deptLeaderMapper.selectDeptLeaderWithUserDetails(deptId, userId, isPrimary);
    }
}

Summarize

Through the above steps, we have implemented a method to query the detailed information of department leaders using left join in MyBatis Plus. This method can dynamically generate SQL queries based on the passed conditions to obtain the information of department leaders and their associated users. This method is very useful when data queries need to be performed across multiple tables, ensuring the flexibility and efficiency of data queries.

I hope this article can help you better understand and use MyBatis Plus for complex queries. If you have any questions or further requirements, please feel free to discuss and communicate.