您的位置:

mybatisplus多表查询分页

一、不用vo的mybatisplus多表查询分页

mybatisplus是一个基于mybatis的增强工具,它提供了很多方便的查询操作。在进行多表查询分页时,我们可以通过使用mybatisplus提供的Wrapper,来进行多表条件查询。

首先,我们需要引入mybatisplus和分页插件的依赖:

``` com.baomidou mybatis-plus-boot-starter 最新版本 com.baomidou mybatis-plus-extension 最新版本 com.baomidou mybatis-plus 最新版本 com.github.pagehelper pagehelper 最新版本 ```

然后,定义实体类,并在实体类中使用@TableField注解来指定映射关系:

``` @Data public class User implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String username; private String password; @TableField(exist = false) private List roles; @TableField(exist = false) private Integer pageNum; @TableField(exist = false) private Integer pageSize; ... } ```

接着,在mapper接口中定义查询方法,使用@Select注解,并将查询条件通过Wrapper进行设置:

``` @Select("select u.*, r.role_name\n" + "from user u\n" + "left join user_role ur on u.id = ur.user_id\n" + "left join role r on ur.role_id = r.id\n" + "${ew.customSqlSegment}") List getUserPageList(@Param(Constants.WRAPPER) Wrapper wrapper); ```

最后,在Service中调用mapper方法,将条件设置到Wrapper中,并使用PageHelper进行分页操作:

``` @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; @Override public List getUserPageList(Integer pageNum, Integer pageSize, String username, String roleName) { Page page = PageHelper.startPage(pageNum, pageSize); LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>(); wrapper.eq(User::getUsername, username) .nested(i -> i.eq(Role::getRoleName, roleName)); List userList = userMapper.getUserPageList(wrapper); userList.forEach(user -> user.setRoles(userMapper.getUserRoles(user.getId()))); return userList; } } ```

二、mybatisplus多表查询

在进行多表查询时,我们可以使用mybatisplus提供的Wrapper来设置多个表的条件查询。在Wrapper中,我们可以使用innerJoin、leftJoin和rightJoin方法来进行联表查询操作。

首先,我们需要定义实体类,并在实体类中使用@TableField注解来指定映射关系:

``` @Data public class User implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String username; private String password; @TableField(exist = false) private List roles; ... } @Data public class Role implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String roleName; ... } ```

接着,在mapper接口中定义查询方法,使用@Select注解,并将查询条件通过Wrapper进行设置:

``` @Select("select u.*, r.role_name\n" + "from user u\n" + "left join user_role ur on u.id = ur.user_id\n" + "left join role r on ur.role_id = r.id\n" + "${ew.customSqlSegment}") List getUserList(@Param(Constants.WRAPPER) Wrapper wrapper); ```

最后,在Service中调用mapper方法,将条件设置到Wrapper中,即可获取到对应的查询结果:

``` @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; @Override public List getUserList(String roleName) { LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>(); wrapper.nested(i -> i.eq(Role::getRoleName, roleName)); List userList = userMapper.getUserList(wrapper); userList.forEach(user -> user.setRoles(userMapper.getUserRoles(user.getId()))); return userList; } } ```

三、mybatisplus自定义sql查询

在有些情况下,我们需要自定义一些复杂的SQL语句来进行查询。这时,我们可以使用mybatisplus提供的SqlInjector来进行自定义SQL查询。

首先,我们需要实现一个继承了AbstractMethod的类,并在其中定义自己的SQL方法:

``` @Component public class UserSqlInjector extends AbstractMethod { @Override public MappedStatement injectMappedStatement(Class mapperClass, Class modelClass, TableInfo tableInfo, SqlMethod sqlMethod) { String sql = "select * from user where username = #{username}"; SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass); return addSelectMappedStatementForTable(mapperClass, sqlMethod.getMethod(), sqlSource, tableInfo); } } ```

然后,在mapper接口中定义查询方法,并使用@SqlParser注解来告诉mybatisplus,该方法不需要进行自动分页处理:

``` @SqlParser(filter = true) List selectUserByUsername(@Param("username") String username); ```

最后,在Service中调用mapper方法即可:

``` @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; @Override public List selectUserByUsername(String username) { return userMapper.selectUserByUsername(username); } } ```

四、mybatisplus多表分页

在进行多表分页查询时,我们可以使用mybatisplus提供的Page对象和Wrapper条件来进行分页操作。

首先,我们需要定义实体类,并在实体类中使用@TableField注解来指定映射关系:

``` @Data public class User implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String username; private String password; @TableField(exist = false) private List roles; ... } @Data public class Role implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String roleName; @TableField(exist = false) private List users; ... } ```

接着,在mapper接口中定义查询方法,使用@Select注解,并将查询条件通过Wrapper进行设置:

``` @Select("select u.*, r.role_name\n" + "from user u\n" + "left join user_role ur on u.id = ur.user_id\n" + "left join role r on ur.role_id = r.id\n" + "${ew.customSqlSegment}") List getUserPageList(@Param(Constants.WRAPPER) Wrapper wrapper); ```

最后,在Service中调用mapper方法,将条件设置到Wrapper中,并使用PageHelper进行分页操作:

``` @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; @Override public List getUserPageList(Integer pageNum, Integer pageSize, String roleName) { Page page = PageHelper.startPage(pageNum, pageSize); LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>(); wrapper.nested(i -> i.eq(Role::getRoleName, roleName)); List userList = userMapper.getUserPageList(wrapper); userList.forEach(user -> user.setRoles(userMapper.getUserRoles(user.getId()))); return userList; } } ```

五、mybatisplus多表连接查询

在进行多表连接查询时,我们可以使用mybatisplus提供的Wrapper来进行设置多个表的条件查询。在Wrapper中,我们可以使用innerJoin、leftJoin和rightJoin方法来进行联表查询操作。

首先,我们需要定义实体类,并在实体类中使用@TableField注解来指定映射关系:

``` @Data public class User implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String username; private String password; @TableField(exist = false) private List roles; ... } @Data public class Role implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String roleName; @TableField(exist = false) private List users; ... } ```

接着,在mapper接口中定义查询方法,使用@Select注解,并将查询条件通过Wrapper进行设置:

``` @Select("select u.*, r.role_name\n" + "from user u\n" + "left join user_role ur on u.id = ur.user_id\n" + "left join role r on ur.role_id = r.id\n" + "${ew.customSqlSegment}") List getUserList(@Param(Constants.WRAPPER) Wrapper wrapper); ```

最后,在Service中调用mapper方法,将条件设置到Wrapper中,即可获取到对应的查询结果:

``` @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; @Override public List getUserList(String roleName) { LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>(); wrapper.nested(i -> i.eq(Role::getRoleName, roleName)); List userList = userMapper.getUserList(wrapper); userList.forEach(user -> user.setRoles(userMapper.getUserRoles(user.getId()))); return userList; } } ```

六、mybatisplus联表查询

在进行联表查询时,我们可以使用mybatisplus提供的Wrapper来进行设置多个表的条件查询。在Wrapper中,我们可以使用innerJoin、leftJoin和rightJoin方法来进行联表查询操作。

首先,我们需要定义实体类,并在实体类中使用@TableField注解来指定映射关系:

``` @Data public class User implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String username; private String password; @TableField(exist = false) private List roles; ... } @Data public class Role implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String roleName; @TableField(exist = false) private List users; ... } ```

接着,在mapper接口中定义查询方法,使用@Select注解,并将查询条件通过Wrapper进行设置:

``` @Select("select * from user u left join user_role ur on u.id = ur.user_id left join role r on ur.role_id = r.id ${ew.customSqlSegment}") List getUserList(@Param(Constants.WRAPPER) Wrapper wrapper); ```

最后,在Service中调用mapper方法,将条件设置到Wrapper中,即可获取到对应的查询结果:

``` @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; @Override public List getUserList(String roleName) { LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>(); wrapper.nested(i -> i.eq(Role::getRoleName, roleName)); List userList = userMapper.getUserList(wrapper); userList.forEach(user -> user.setRoles(userMapper.getUserRoles(user.getId()))); return userList; } } ```

七、mybatisplus多表id查询

在有些情况下,我们需要根据多个id进行查询操作。这时,我们可以使用mybatisplus提供的Wrapper来进行条件查询。

首先,我们需要定义实体类,并在实体类中使用@TableField注解来指定映射关系:

``` @Data public class User implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String username; private String password; @TableField(exist = false) private List roles; ... } @Data public class Role implements Serializable { @TableId(value = "id", type = IdType.AUTO) private Long id; private String roleName; @TableField(exist = false) private List users; ... } ```

接着,在mapper接口中定义查询方法,使用@Select注解,并将查询条件通过Wrapper进行设置:

``` @Select("select * from user where id in (${ids})") List getUserListByIds(@Param("ids") String ids); ```

最后,在Service中调用mapper方法