问题描述
大家好。本人用了mybatis实现分页查询，数据库采用mysql。当执行sql语句为：selectID,USERID,RESOURCE_ID,RESOURCE_NAME,IP_ADDRESS,OPER_TIME,MAC_ADDRESS,TYPEfromT_PORTAL_LOG_OPERwhere1=1limit0,15
mybatis无异常，结果值能够注入到List中。但是当sql为：selectID,USERID,RESOURCE_ID,RESOURCE_NAME,IP_ADDRESS,OPER_TIME,MAC_ADDRESS,TYPEfromT_PORTAL_LOG_OPERwhere1=1limit15,15
mybatis无法把结果值注入到List中去。对应的日志如下：执行selectID,USERID,RESOURCE_ID,RESOURCE_NAME,IP_ADDRESS,OPER_TIME,MAC_ADDRESS,TYPEfromT_PORTAL_LOG_OPERwhere1=1limit0,15时的日志：[DEBUG][http-8080-1][org.mybatis.spring.SqlSessionUtils]ReleasingtransactionalSqlSession[org.apache.ibatis.session.defaults.DefaultSqlSession@358ba94a][DEBUG][http-8080-1][org.mybatis.spring.SqlSessionUtils]FetchedSqlSession[org.apache.ibatis.session.defaults.DefaultSqlSession@358ba94a]fromcurrenttransaction[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]==>Preparing:selectID,USERID,RESOURCE_ID,RESOURCE_NAME,IP_ADDRESS,OPER_TIME,MAC_ADDRESS,TYPEfromT_PORTAL_LOG_OPERwhere1=1limit0,15[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]==>Parameters:[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:15[DEBUG][http-8080-1][org.mybatis.spring.SqlSessionUtils]ReleasingtransactionalSqlSession[org.apache.ibatis.session.defaults.DefaultSqlSession@358ba94a]
执行selectID,USERID,RESOURCE_ID,RESOURCE_NAME,IP_ADDRESS,OPER_TIME,MAC_ADDRESS,TYPEfromT_PORTAL_LOG_OPERwhere1=1limit15,15时的日志：[DEBUG][http-8080-1][org.mybatis.spring.SqlSessionUtils]ReleasingtransactionalSqlSession[org.apache.ibatis.session.defaults.DefaultSqlSession@2394dd57][DEBUG][http-8080-1][org.mybatis.spring.SqlSessionUtils]FetchedSqlSession[org.apache.ibatis.session.defaults.DefaultSqlSession@2394dd57]fromcurrenttransaction[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]==>Preparing:selectID,USERID,RESOURCE_ID,RESOURCE_NAME,IP_ADDRESS,OPER_TIME,MAC_ADDRESS,TYPEfromT_PORTAL_LOG_OPERwhere1=1limit15,15[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]==>Parameters:[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][com.eiei.portal.log.mapper.TPortalLogOperMapper.list]<==Total:5[DEBUG][http-8080-1][org.mybatis.spring.SqlSessionUtils]ReleasingtransactionalSqlSession[org.apache.ibatis.session.defaults.DefaultSqlSession@2394dd57]
解决方案
解决方案二：
继续：第一种情况可以获取到15条数据。第二种情况理论上可以获取到5条数据的，但是结果值却为空。请教大神们，有谁遇到这种情况，谢谢。
解决方案三：
在这里贴上我的代码吧：mybatis.xml<?xmlversion="1.0"encoding="UTF-8"?><!DOCTYPEconfigurationPUBLIC"-//mybatis.org//DTDConfig3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><settings><!--changesfromthedefaultsfortesting--><settingname="cacheEnabled"value="false"/><settingname="useGeneratedKeys"value="true"/><settingname="defaultExecutorType"value="REUSE"/></settings><plugins><plugininterceptor="com.eiei.rebatis.interceptor.SqlPageInterceptor"><propertyname="dialect"value="com.eiei.rebatis.dialect.MySQLDialect"/></plugin></plugins></configuration>
com.eiei.rebatis.dialect.MySQLDialectpackagecom.eiei.rebatis.dialect;publicclassMySQLDialectextendsDialect{@OverridepublicStringgetLimitString(Stringsql,intoffset,intlimit){sql=trimDelimiter(sql);StringBufferstringBuffer=newStringBuffer(sql.length()+20);stringBuffer.append(sql);if(offset>0){stringBuffer.append("limit").append(offset).append(',').append(limit);}else{stringBuffer.append("limit0,").append(limit);}if(super.hasDelimiter()){stringBuffer.append(SQL_END_DELIMITER);}returnstringBuffer.toString();}}
com.eiei.rebatis.dialect.Dialectpackagecom.eiei.rebatis.dialect;publicclassDialect{publicstaticfinalStringSQL_END_DELIMITER=";";privatebooleandelimiterFlag=false;publicbooleansupportsLimit(){returntrue;}publicbooleansupportsLimitOffset(){returnsupportsLimit();}publicbooleanhasDelimiter(){returndelimiterFlag;}publicStringgetLimitString(Stringsql,intoffset,intlimit){thrownewUnsupportedOperationException("pagedqueriesnotsupported");}protectedStringtrimDelimiter(Stringsql){if(sql==null||sql.length()<=0){returnnull;}this.delimiterFlag=sql.endsWith(SQL_END_DELIMITER);returndelimiterFlag?sql.substring(0,sql.length()-1-SQL_END_DELIMITER.length()):sql;}}
com.eiei.rebatis.interceptor.SqlPageInterceptorpackagecom.eiei.rebatis.interceptor;importjava.sql.Connection;importjava.util.Properties;importorg.apache.ibatis.executor.statement.PreparedStatementHandler;importorg.apache.ibatis.executor.statement.RoutingStatementHandler;importorg.apache.ibatis.executor.statement.StatementHandler;importorg.apache.ibatis.mapping.BoundSql;importorg.apache.ibatis.plugin.Interceptor;importorg.apache.ibatis.plugin.Intercepts;importorg.apache.ibatis.plugin.Invocation;importorg.apache.ibatis.plugin.Plugin;importorg.apache.ibatis.plugin.Signature;importorg.apache.ibatis.session.RowBounds;importorg.springframework.util.StringUtils;importcom.eiei.rebatis.dialect.Dialect;importcom.eiei.rebatis.utils.ReflectUtils;@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})publicclassSqlPageInterceptorimplementsInterceptor{Dialectdialect;publicObjectintercept(Invocationinvocation)throwsThrowable{//TODOAuto-generatedmethodstubif(invocation.getTarget()instanceofRoutingStatementHandler){RoutingStatementHandlerstatement=(RoutingStatementHandler)invocation.getTarget();PreparedStatementHandlerdelegate=(PreparedStatementHandler)ReflectUtils.getValueByFieldName(statement,"delegate");RowBoundsrowBounds=(RowBounds)ReflectUtils.getValueByFieldName(delegate,"rowBounds");if(rowBounds!=null){intoffset=rowBounds.getOffset();intlimit=rowBounds.getLimit();if(limit>0&&limit<RowBounds.NO_ROW_LIMIT){BoundSqlboundSql=delegate.getBoundSql();//分页SQL<select>中parameterType属性对应的实体参数，即Mapper接口中执行分页方法的参数,该参数不得为空ObjectparameterObject=boundSql.getParameterObject();if(parameterObject==null){thrownewNullPointerException("parameterObject尚未实例化！");}else{StringpaginationSql=dialect.getLimitString(boundSql.getSql(),offset,limit);//将分页sql语句反射回BoundSqlReflectUtils.setValueByFieldName(boundSql,"sql",paginationSql);}}}}returninvocation.proceed();}publicObjectplugin(Objecttarget){returnPlugin.wrap(target,this);}publicvoidsetProperties(Propertiesproperties){StringdialectClass=properties.getProperty("dialect");if(StringUtils.isEmpty(dialectClass)){return;}try{dialect=(Dialect)Class.forName(dialectClass).newInstance();}catch(Exceptione){thrownewRuntimeException("cannotcreatedialectinstancebydialect:"+dialectClass,e);}}}
解决方案四：
继续：对应的Model类：//不用理会注解，注解只是本人用来自动生成代码的工具packagecom.eiei.portal.log.model;importjava.io.Serializable;importjava.util.Date;importcom.eiei.rebatis.an.annotation.AnColumn;importcom.eiei.rebatis.an.annotation.AnId;importcom.eiei.rebatis.an.annotation.AnTable;/***用户操作日志*@authoreiei**/@AnTable(name="t_portal_log_oper")publicclassTPortalLogOperimplementsSerializable{privatestaticfinallongserialVersionUID=8459316607010797753L;@AnId@AnColumn(length=32)privateStringid;@AnColumn(length=32)privateStringuserid;@AnColumn(length=32)privateStringresourceId;@AnColumn(length=255)privateStringresourceName;@AnColumn(length=50)privateStringipAddress;@AnColumn(type="timestamp")privateDateoperTime;@AnColumn(length=50)privateStringmacAddress;@AnColumn(length=10)privateStringtype;publicStringgetId(){returnid;}publicvoidsetId(Stringid){this.id=id;}publicStringgetUserid(){returnuserid;}publicvoidsetUserid(Stringuserid){this.userid=userid;}publicStringgetResourceId(){returnresourceId;}publicvoidsetResourceId(StringresourceId){this.resourceId=resourceId;}publicStringgetResourceName(){returnresourceName;}publicvoidsetResourceName(StringresourceName){this.resourceName=resourceName;}publicStringgetIpAddress(){returnipAddress;}publicvoidsetIpAddress(StringipAddress){this.ipAddress=ipAddress;}publicDategetOperTime(){returnoperTime;}publicvoidsetOperTime(DateoperTime){this.operTime=operTime;}publicStringgetMacAddress(){returnmacAddress;}publicvoidsetMacAddress(StringmacAddress){this.macAddress=macAddress;}publicStringgetType(){returntype;}publicvoidsetType(Stringtype){this.type=type;}@OverridepublicinthashCode(){finalintprime=31;intresult=1;result=prime*result+((id==null)?0:id.hashCode());returnresult;}@Overridepublicbooleanequals(Objectobj){if(this==obj)returntrue;if(obj==null)returnfalse;if(getClass()!=obj.getClass())returnfalse;TPortalLogOperother=(TPortalLogOper)obj;if(id==null){if(other.id!=null)returnfalse;}elseif(!id.equals(other.id))returnfalse;returntrue;}@OverridepublicStringtoString(){return"TPortalLogOper[id="+id+",userid="+userid+",resourceId="+resourceId+",resourceName="+resourceName+",ipAddress="+ipAddress+",operTime="+operTime+",macAddress="+macAddress+",type="+type+"]";}}
对应的mapper.xml代码：<?xmlversion="1.0"encoding="UTF-8"?><!--@authoreiei@since2014年10月28日23:20:30.092此代码由frameworkan生成。--><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTDMapper3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.eiei.portal.log.mapper.TPortalLogOperMapper"><resultMaptype="com.eiei.portal.log.model.TPortalLogOper"id="tPortalLogOper"><resultcolumn="ID"property="id"/><resultcolumn="USERID"property="userid"/><resultcolumn="RESOURCE_ID"property="resourceId"/><resultcolumn="RESOURCE_NAME"property="resourceName"/><resultcolumn="IP_ADDRESS"property="ipAddress"/><resultcolumn="OPER_TIME"property="operTime"/><resultcolumn="MAC_ADDRESS"property="macAddress"/><resultcolumn="TYPE"property="type"/></resultMap><sqlid="table_name">T_PORTAL_LOG_OPER</sql><sqlid="sql_columns_items">ID,USERID,RESOURCE_ID,RESOURCE_NAME,IP_ADDRESS,OPER_TIME,MAC_ADDRESS,TYPE</sql><sqlid="id_column">ID</sql><selectid="get"parameterType="String"resultMap="tPortalLogOper">select<includerefid="sql_columns_items"/>from<includerefid="table_name"/>where<includerefid="id_column"/>=#{id}</select><selectid="listAll"resultMap="tPortalLogOper">select<includerefid="sql_columns_items"/>from<includerefid="table_name"/></select><insertid="save"parameterType="com.eiei.portal.log.model.TPortalLogOper">insertinto<includerefid="table_name"/>(<includerefid="sql_columns_items"/>)values(#{id},#{userid},#{resourceId},#{resourceName},#{ipAddress},#{operTime},#{macAddress},#{type})</insert><selectid="list"resultMap="tPortalLogOper"parameterType="com.eiei.rebatis.bean.Condition">select<includerefid="sql_columns_items"/>from<includerefid="table_name"/>where1=1<iftest="id!=nullandid!=''">andID=#{id}</if></select><deleteid="delete"parameterType="String">deletefrom<includerefid="table_name"/>where<includerefid="id_column"/>=#{id}</delete><selectid="count"resultType="int"parameterType="com.eiei.rebatis.bean.Condition">selectcount(1)from<includerefid="table_name"/>where1=1<iftest="id!=nullandid!=''">andID=#{id}</if></select><updateid="update"></update></mapper>
解决方案五：
MyBatis的分页直接写在SQL里，不需要任何代码。你自己实现了一些类，如MySQLDialect，然后和MyBatis一起使用，估计问题就出现在这。