PageHelper一对多collection分页查询，总条数不正确问题

旧城以西林

已于 2023-02-23 14:09:57 修改

阅读量1.6k

点赞数 1

分类专栏： MyBatis 文章标签：数据库 sql java mybatis Powered by 金山文档

于 2023-02-23 14:08:03 首次发布

本文链接：https://blog.csdn.net/zhaoraolin/article/details/129179928

版权

MyBatis 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

最近在项目中使用PageHelper进行一对多关联查询的时候，出现查询总条目数和实际的不对应

例：A实例对应6个B实例(如下代码)，应该查询出来只有一条数据，但PageHelper显示6条数据

public class A{
    private List<B> appletList;
}

查询SQL编写如下：

实例A对应request表，实例B对应record表

<resultMap autoMapping="true" type="com.admin.pojos.question.A" id="queryRequestListByUids">
    <result column="id" property="id"/>
    <collection autoMapping="true" ofType="com.admin.entity.B" property="appletList">
        <result column="arid" property="id"/>
        <result column="appletId" property="appletId"/>
    </collection>
</resultMap>
<select id="selectRequestByUids" resultMap="queryRequestListByUids">
    select r.*,toar.applet_type appletType,toar.create_dt toarCreateDt
    from request r
    left join record toar on r._uid = toar.request_uid
    where r.is_del = 0 and r._uid in
</select>

查询结果：

返回数据能够正常将一对多的多放到List<B>里面，也只有一条数据，但是在使用page.getTotal()获取总条目的时候，得到的值，不是1，而是更多

解决方案（两种）：

第一种：修改PageHelper工具

第二种（我使用的）：使用子查询进行关联一对多，具体如下：

<resultMap autoMapping="true" type="com.admin.pojos.question.A" id="queryRequestListByUids">
    <result column="id" property="id"/>
   <!--子查询需传递多个参数，在column字段追加即可（column="{id=id,name=name}）-->
    <collection property="appletList" ofType="com.admin.entity.B"
                select="selectAppletRecord" column="{requestUid=_uid}"/>
</resultMap>
<select id="selectRequestByUids" resultMap="queryRequestListByUids">
    select r.*
    from request r
</select>


<resultMap id="BatchListResultMap" type="com.admin.entity.B">
    <result column="arid" property="id"/>
    <result column="appletId" property="appletId"/>
</resultMap>
<select id="selectAppletRecord" resultMap="BatchListResultMap">
    select
        toar.applet_type appletType,toar.create_dt toarCreateDt
    from record toar
    where toar.request_uid = #{requestUid}
</select>

通过上述操作就可以成功实现一对多的映射查询，page.getTotal()获取总条目也是正确的。

但我在实际过程中，在实现上述操作之后，返回数据的时候，报了如下错误：

org.springframework.http.converter.HttpMessageConversionException: Type definition error: [simple type, class org.apache.ibatis.executor.loader.javassist.JavassistProxyFactory$EnhancedResultObjectProxyImpl]; nested exception is com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.apache.ibatis.executor.loader.javassist.JavassistProxyFactory$EnhancedResultObjectProxyImpl and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) (through reference chain: com.core.common.RestResult["result"]->com.core.common.OutputListResult["data"]->com.github.PageHelperr.Page[0]->com.admin.pojos.question.OutputRequestInfo_$$_jvste21_0["handler"])
    at org.springframework.http.converter.json.AbstractJackson2HttpMessageConverter.writeInternal(AbstractJackson2HttpMessageConverter.java:293)
    at org.springframework.http.converter.AbstractGenericHttpMessageConverter.write(AbstractGenericHttpMessageConverter.java:103)
    at org.springframework.web.servlet.mvc.method.annotation.AbstractMessageConverterMethodProcessor.writeWithMessageConverters(AbstractMessageConverterMethodProcessor.java:289)
    at org.springframework.web.servlet.mvc.method.annotation.RequestResponseBodyMethodProcessor.handleReturnValue(RequestResponseBodyMethodProcessor.java:180)
    at org.springframework.web.method.support.HandlerMethodReturnValueHandlerComposite.handleReturnValue(HandlerMethodReturnValueHandlerComposite.java:82)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:119)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:998)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:890)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.apache.ibatis.executor.loader.javassist.JavassistProxyFactory$EnhancedResultObjectProxyImpl and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) (through reference chain: com.core.common.RestResult["result"]->com.core.common.OutputListResult["data"]->com.github.PageHelperr.Page[0]->com.admin.pojos.question.OutputRequestInfo_$$_jvste21_0["handler"])
    at com.fasterxml.jackson.databind.exc.InvalidDefinitionException.from(InvalidDefinitionException.java:77)
    at com.fasterxml.jackson.databind.SerializerProvider.reportBadDefinition(SerializerProvider.java:1191)
    at com.fasterxml.jackson.databind.DatabindContext.reportBadDefinition(DatabindContext.java:313)
    at org.springframework.http.converter.json.AbstractJackson2HttpMessageConverter.writeInternal(AbstractJackson2HttpMessageConverter.java:287)
    ... 95 more

从报错可以看出OutputRequestInfo中没有对应的handler字段（没有找到get/set方法），使用上述操作，会多了handler字段，我们需要忽略这个字段，只需在实例A上加上一个注解即可：

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@JsonIgnoreProperties(value = "handler")
public class A{
    private List<B> appletList;
}