MyBatis Plus Multi-Table Joins

1 year ago
Olivia Parker
2 minutes

MyBatis-Plus offers various methods to achieve multiple table joins, which depends on the type of query you want to use and the level of complexity.

Here are some commonly used methods:

1. Use the @Join annotation: Define the relationship of multi-table join by using the @Join annotation in the entity class, and then perform the query using MyBatis-Plus query methods.

@Data
public class User {
    @TableId
    private Long id;
    private String name;
    private Long roleId;

    @Join(column = "role_id", type = Join.Type.LEFT)
    private Role role;
}

@Data
public class Role {
    @TableId
    private Long id;
    private String roleName;
}

// 查询代码
User user = userMapper.selectById(1L);

2. Using the Wrapper query builder: Utilizing the Wrapper query builder provided by MyBatis-Plus allows for easily combining multiple conditions for multi-table joins.

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("user.id", 1L)
        .leftJoin("role", "role.id = user.role_id")
        .select("user.*, role.role_name");

User user = userMapper.selectOne(queryWrapper);

Use custom SQL: If the above methods do not meet the requirements, you can use custom SQL to achieve joins across multiple tables.

@Select("SELECT u.*, r.role_name FROM user u LEFT JOIN role r ON u.role_id = r.id WHERE u.id = #{id}")
User selectUserWithRoleById(Long id);

Here are some common methods for querying multiple tables. You can choose the appropriate method based on your specific needs.

bannerAds