Mybatis-plus 是基于mybatis的增强工具,是一款稳定强大的工具。
配置安装参考:https://baomidou.com/pages/226c21/
连表分页查询
entity的创建
@TableName("sys_user")//指定表名
public class User{
@TableId(type = IdType.AUTO)//指定主键
private Long id;
//非空判断,NOT_NULL / NOT_EMPTY / IGNORED / DEFAULT
@TableField(strategy = FieldStrategy.NOT_NULL)
private String name;
private String password;
@TableField(exist = false)//冗余字段
@DateTimeFormat(
pattern = "yyyy-MM-dd HH:mm:ss"
)
@JsonFormat(
pattern = "yyyy-MM-dd HH:mm:ss"
)
private LocalDateTime createTime;
//省略get set 方法和 toString
}
mapper层
@Mapper
public interface UserMapper extends BaseMapper<User> {
//IPage 分页器, QueryWrapper 条件构造器
IPage<User> findByPage(IPage<User> page, @Param("ew") QueryWrapper<User> wrapper) throws Exception;
}
QueryWrapper条件构造器对象的sqlSegment属性拼接了 不带where 的筛选条件sql,而customSqlSegment属性拼接了 完整的 where语句 。
mapper代码
<!--通过自定义sql并拼接条件构造器的形成的sql语句实现连表查询,分页只需要传入Page对象即可 -->
<select id="findByPage" resultType="com.clown.entity.User">
SELECT
user.* ,
dept.detp_name
FROM
user as user
INNER JOIN dept_rela as rela ON rela.user_id = user.id
INNER JOIN dept as dept on dept.dept_no = rela.dept_no
${ew.customSqlSegment}
</select>
service层把筛选字段和值set进条件构造器对象后,在mapper中直接在查询语句后面使用$符号直接进行拼接,需要注意的是mapper的入参需要在@param注解上表明
@Param("ew") QueryWrapper<User> queryWrapper
而mapper文件对应的是
${ew.customSqlSegment}
service接口代码
public interface IUserService extends IService<User> {
IPage<User> findByPage(Long current, Long size, String name) throws Exception;
}
service实现类代码
public class UserServiceImpl extends ServiceImpl<IUserMapper,User>{
@Override
public IPage<User> findByPage(Long current, Long size, Long unitId, String name) throws Exception {
//分页器对象
IPage<User> page = new Page<User>(current, size);
//条件构造器对象
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
//入参
if (name!=null){
queryWrapper.eq("user.name", name);
}
queryWrapper.orderByAsc("create_time");
IPage<User> page = null;
try {
page = baseMapper.findByPage(page, queryWrapper);
} catch (Exception e) {
LOGGER.error(e.getMessage());
throw e;
}
return page;
}
}