Preface
When tables are involved in web development, such as dataTable, the need for paging will arise. Usually we divide the paging methods into two types: front-end paging and back-end paging.
Front-end pagination
All records (ajax) in the data table are requested at one time, and then cached on the front end and calculated count and paging logic. Generally, front-end components (such as dataTable) will provide paging actions.
Features are: simple, very suitable for small-scale web platforms; performance problems will arise when the amount of data is large, and the time for query and network transmission will be long.
Backend pagination
Specify the page number (pageNum) and the size of each page (pageSize) in the ajax request. The backend querys the data of the page and returns it, and the frontend is only responsible for rendering.
Features are: more complex; the performance bottleneck is in the query performance of MySQL, which can of course be optimized and solved. Generally speaking, web development uses this method.
We are talking about backend pagination.
MySQL support for pagination
Simply put, MySQL supports pagination through limit clause. Please see the example below.
The usage of limit keyword is
LIMIT [offset,] rows
offset is the offset relative to the first row (the first row is 0), and rows is the number of rows.
# Each page has 10 records, take the first page, and return the first 10 records select * from tableA limit 0,10;# Each page has 10 records, take the second page, and return the 11th record, to the 20th record, select * from tableA limit 10,10;
What I want to mention here is that when MySQL handles pagination:
limit 1000,10 - Filter out 1010 pieces of data, then discard the first 1000 pieces, and keep 10 pieces. When the offset is large, the performance will decrease.
limit 100000,10 - will filter 10w+10 pieces of data, and then discard the first 10w pieces. If you find performance problems in the pagination, you can adjust them according to this idea.
Mybatis paging plugin PageHelper
When using Java Spring development, Mybatis is a powerful tool for database operations. However, when dealing with paging, Mybatis does not have any special methods. Generally, you need to write the limit clause yourself to implement it, which is relatively expensive. Fortunately, there is a PageHelper plugin.
1. POM dependency
I won't mention the configuration of Mybatis. PageHelper's dependencies are as follows. If you need a new version, you can choose on maven
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>4.1.4</version> </dependency>
2. Mybatis configuration of PageHelper
Open the Mybatis configuration file, usually under the Resource path. My name is mybatis-config.xml.
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><!-- Global Parameters--><settings> <!-- Make the global mapper enable or disable cache. --> <setting name="cacheEnabled" value="true"/> <!-- Enable or disable lazy loading globally. When disabled, all associated objects are loaded instantly. --> <setting name="lazyLoadingEnabled" value="true"/> <!-- When enabled, an object with a lazy loading property will fully load any property when it is called. Otherwise, each property will be loaded as needed. --> <setting name="aggressiveLazyLoading" value="true"/> <!-- Whether to allow a single sql to return multiple datasets (depending on the compatibility of the driver) default:true --> <setting name="multipleResultSetsEnabled" value="true"/> <!-- Whether alias for columns can be used (depending on the compatibility of the driver) default:true --> <setting name="useColumnLabel" value="true"/> <!-- Allow JDBC to generate primary keys. Drive support is required. If set to true, this setting will force the generated primary key, some drives are incompatible but can still be executed. default:false --> <setting name="useGeneratedKeys" value="true"/> <!-- Specify how MyBatis automatically maps columns of data base table NONE: not obscuring PARTIAL: part FULL: all--> <setting name="autoMappingBehavior" value="PARTIAL"/> <!-- This is the default execution type (SIMPLE: simple; REUSE: the executor may reuse prepared statements statements; BATCH: the executor can repeat statements and batch updates) --> <setting name="defaultExecutorType" value="SIMPLE"/> <!-- Convert fields using camel nomenclature. --> <setting name="mapUnderscoreToCamelCase" value="true"/> <!-- Setting local cache range session: There will be data sharing statement: Statement scope (this will not be data sharing) defalut:session --> <setting name="localCacheScope" value="SESSION"/> <!-- Setting but the JDBC type is empty, some drivers need to specify the value, default:OTHER, and there is no need to specify the type when inserting a null value --> <setting name="jdbcTypeForNull" value="NULL"/></settings><plugins> <plugin interceptor="com.github.pagehelper.PageHelper"> <property name="dialect" value="mysql"/> <property name="offsetAsPageNum" value="false"/> <property name="rowBoundsWithCount" value="false"/> <property name="pageSizeZero" value="true"/> <property name="reasonable" value="false"/> <property name="supportMethodsArguments" value="false"/> <property name="returnPageInfo" value="none"/> </plugin></plugins></configuration>
What you need to pay attention to here is the configuration related to PageHelper.
If you do not load the Mybatis configuration file, then you are using the Mybatis default configuration. How to load Mybatis configuration file?
Go to your dataSrouce configuration.
When configuring sqlSessionFactory, specify the Mybatis core configuration file and mapper path, the code is as follows
@Bean(name = "moonlightSqlSessionFactory") @Primary public SqlSessionFactory moonlightSqlSessionFactory(@Qualifier("moonlightData") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis-mapper/*.xml")); bean.setConfigLocation(new ClassPathResource("mybatis-config.xml")); return bean.getObject(); }illustrate:
The mapper.xml storage path configured here is in the Resource/mybatis-mapper folder
The mybatis-config.xml file configured here is under Resource/
3. Pagination
Prepare a mapper.xml, just write one for the test, just use one from the project.
This query here is a typical multi-condition query. What we need to do is paginate the records matched by multiple conditions.
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.kangaroo.studio.moonlight.dao.mapper.MoonlightMapper"> <resultMap id="geoFenceList" type="com.kangaroo.studio.moonlight.dao.model.GeoFence"> <constructor> <idArg column="id" javaType="java.lang.Integer" jdbcType="INTEGER" /> <arg column="name" javaType="java.lang.String" jdbcType="VARCHAR" /> <arg column="type" javaType="java.lang.Integer" jdbcType="INTEGER" /> <arg column="group" javaType="java.lang.String" jdbcType="VARCHAR" /> <arg column="geo" javaType="java.lang.String" jdbcType="VARCHAR" /> <arg column="createTime" javaType="java.lang.String" jdbcType="VARCHAR" /> <arg column="updateTime" javaType="java.lang.String" jdbcType="VARCHAR" /> </constructor> </resultMap> <sql id="base_column">id, name, type, `group`, geo, createTime, updateTime </sql> <select id="queryGeoFence" parameterType="com.kangaroo.studio.moonlight.dao.model.GeoFenceQueryParam" resultMap="geoFenceList"> select <include refid="base_column"/> from geoFence where 1=1 <if test="type != null"> and type = #{type} </if> <if test="name != null"> and name like concat('%', #{name},'%') </if> <if test="group != null"> and `group` like concat('%', #{group},'%') </if> <if test="startTime != null"> and createTime >= #{startTime} </if> <if test="endTime != null"> and createTime <= #{endTime} </if> </select></mapper>Write corresponding methods in the Mapper.java interface
List<GeoFence> queryGeoFence(GeoFenceQueryParam geoFenceQueryParam);
First, add the page code and then explain it later
@RequestMapping(value = "/fence/query", method = RequestMethod.POST) @ResponseBody public Response queryFence(@RequestBody GeoFenceQueryParam geoFenceQueryParam) { try { Integer pageNum = geoFenceQueryParam.getPageNum()!=null?geoFenceQueryParam.getPageNum():1; Integer pageSize = geoFenceQueryParam.getPageSize()!=null?geoFenceQueryParam.getPageSize():10; PageHelper.startPage(pageNum, pageSize); List<GeoFence> list = moonlightMapper.queryGeoFence(geoFenceQueryParam); return new Response(ResultCode.SUCCESS, "Query geoFence Success", list); } catch (Exception e) { logger.error("Query geoFence Failed", e); return new Response(ResultCode.EXCEPTION, "Query geoFence failed", null); } }illustrate:
1. The advantage of PageHelper is that pagination and Mapper.xml are completely decoupled. The implementation method is to strengthen the Mybatis execution process in the form of a plug-in, adding the total count and limit queries. Belongs to physical page.
2. There is a security issue that you need to pay attention to, otherwise it may lead to paging disorder. I have directly pasted a passage from this blog here.
4. When will it lead to unsafe paging?
The PageHelper method uses a static ThreadLocal parameter, and the paging parameters and threads are bound.
As long as you can ensure that the PageHelper method is called immediately followed by the MyBatis query method, this is safe. Because PageHelper automatically clears the ThreadLocal stored object in the finally snippet.
If an exception occurs before entering the Executor, the thread will be unavailable, which is a human bug (such as a mismatch between the interface method and XML, which will lead to the MappedStatement not being found). This situation will not cause the ThreadLocal parameter to be used incorrectly due to the unavailability of the thread.
But if you write the following code, it is an unsafe usage:
PageHelper.startPage(1, 10);List<Country> list;if(param1 != null){ list = countryMapper.selectIf(param1);} else { list = new ArrayList<Country>();}In this case, because param1 has null, PageHelper will produce a paging parameter, but it is not consumed, and this parameter will remain on this thread. When this thread is used again, it may cause methods that should not be paging to consume the paging parameters, which results in inexplicable paging.
The above code should be written as follows:
List<Country> list;if(param1 != null){ PageHelper.startPage(1, 10); list = countryMapper.selectIf(param1);} else { list = new ArrayList<Country>();}This way of writing can ensure safety.
If you are not at ease with this, you can manually clean up the paging parameters stored in ThreadLocal, which can be used like this:
List<Country> list;if(param1 != null){ PageHelper.startPage(1, 10); try{ list = countryMapper.selectAll(); } finally { PageHelper.clearPage(); }} else { list = new ArrayList<Country>();}It's not good to write like this, and it's not necessary.
Summarize
The above is the configuration and simple usage method of the Mybatis paging plugin PageHelper introduced to you by the editor (recommended). I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!