最近在项目中使用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;
}