Mybatis Plus annotation-based multi-table paging query

preface

The basic paging query provided by Mybatis Plus can only meet the requirements of a single table, not enough to support multiple tables, if you want to multi-table associated query and paging, you can only manually implement SQL, today we will introduce how to quickly achieve multi-table paging query based on annotations.

Implementation scheme

You can manually spell the SQL and submit it to Mybatis Plus.

public interface CustomizeQueryMapper extends BaseMapper<CustomizeEntity> {
    String querySql = "SELECT a.*, b.role_name FROM t_user AS a LEFT JOIN t_user_role AS b ON b.u_id = a.id ";
    String wrapperSql = "SELECT * from ( " + querySql + " ) AS q ${ew.customSqlSegment}";
    
    / * * * paging query, query * / does not support conditions
    @Select(wrapperSql)
    List<CustomizeEntity> list(@Param("ew") Wrapper queryWrapper);
    
    /** ** support Mybatis Plus conditional query */
    @Select(wrapperSql)
    Page<CustomizeEntity> pageList(Page page, @Param("ew") Wrapper queryWrapper);


    /** * select * from */
    @Select(wrapperSql)
    CustomizeEntity findOne(@Param("ew") Wrapper queryWrapper);
}
Copy the code

Note: There is no problem in using this method if the table field is inconsistent and the entity can be one-to-one. If the table field is inconsistent, for example:

SELECT a.*, b.role_name as userRoleName FROM t_user AS a LEFT JOIN t_user_role AS b ON b.u_id = a.id 
Copy the code

Alias is used for query:

QueryWrapper qw = new QueryWrapper();

qw.lambda().eq("q.userRoleName",customizeEntity.getUserRoleName);
Copy the code

The key points

1. ${ew.customSQLSegment}, Mybatis Plus can use QueryWrapper or LambdaQueryWrapper to output query SQL.

2. Where cannot appear in the custom SQL, if you want to use it, please wrap it in the outermost layer, similar to the way I wrapped a temporary table Q in the outermost layer, that is, you can use the custom WHERE condition can also use Mybatis Plus own query condition

3. The returned entity must be the same as the generic entity of the custom Mapper class, otherwise a type mismatch will be reported, for example

   public interface UserRoleMapper extends BaseMapper<UserRoleEntity> {
       String querySql = "SELECT a.*, b.role_name FROM t_user AS a LEFT JOIN t_user_role AS b ON b.u_id = a.id ";
       String wrapperSql = "SELECT * from ( " + querySql + " ) AS q ${ew.customSqlSegment}";
       
       @Select(wrapperSql)
       CustomizeEntity findOne(@Param("ew") Wrapper queryWrapper);
   }
   Copy the code

   BaseMapper
   
   does not match the return value CustomizeEntity.
   

4. Although Mybatis Plus provides @TableField(exist = false) to distinguish whether a database field is annotated or not, it is recommended that custom queries create separate Mapper and response entities.

conclusion

The above for the Mybatis Plus for you to achieve annotated multi-table paging query, if there is a better way or method welcome everyone comment pointed out, thank you.