One. How to use:
1. Introduce the JAR package mode
Download:
http://repo1.maven.org/maven2/com/github/pagehelper/pagehelper/
Since you are using a sql parsing tool, you will also need to download the jsqlparser.jar (which needs to be the same as the version that PageHelper depends)
Download:
http://repo1.maven.org/maven2/com/github/jsqlparser/jsqlparser/
2. Use Maven
pom.xml introduced in the file
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>Pagehelper</artifactId> <version>latest version</version></dependency>
Two. Configure the interceptor plugin
Special attention is paid to the new version of the interceptor
com.github.pagehelper.PageInterceptor。 com.github.pagehelper.PageHelper is now a special dialect implementation class, which is the default implementation class for pagination plugins, and provides the same usage as before.
1. Configure the interceptor plugin in the MyBatis configuration xml
<!– plugins must be located in the configuration file as required, otherwise an error will be reported, in the following order: properties?, settings?, typeAliases?, typeHandlers?, objectFactory?, objectWrapperFactory?, plugins?, environments?, databaseIdProvider? , mappers?–><plugins> <!– com.github.pagehelper is the package name of the PageHelper class –> <plugin interceptor=”com.github.pagehelper.PageInterceptor”> <!– Use the following method to configure the parameters, and all the parameters will be described later –> <property name=”param1″ value=”value1″/></plugin></plugins>
2. Configure the interceptor plugin in the Spring configuration file
Using Spring’s property configuration, you can use the plugins property to configure it like this:
<bean id=”sqlSessionFactory” class=”org.mybatis.spring.SqlSessionFactoryBean”> <!– Note the other configuration –> <property name=”plugins” <array> > <bean class=”com.github.pagehelper.PageInterceptor”> <property name=”properties”> <!– configure the parameters in the following way: configure a –> <value> params=value1 </value> on a row </property> </bean> </array> </property></bean>
3. Introduction to pagination plug-in parameters
The pagination plug-in provides several optional parameters, which can be configured according to the examples in the above two configuration methods.
The optional parameters of the pagination plug-in are as follows: dialect: by default, the PageHelper method will be used for pagination, if you want to implement your own pagination logic, you can implement Dialect (
com.github.pagehelper.Dialect) interface, and then configure the property to implement the fully qualified name of the class.
The following parameters are for the default dialect case. Use a custom dialect to realize
At the moment, the following parameters have no effect.
1.helperDialect: The pagination plugin will automatically detect the current database link and automatically select the appropriate pagination method. You can configure the helperDialect property to specify which dialect the pagination plugin uses.
When configuring, you can use the following abbreviated values: Oracle, MySQL, MariaDB, SQLITE, HsqlDB, PostgreSQL, DB2, SQLServ, Informix, H2, SQLServer2012, DERBY
Note: When using a SqlServer2012 database, you need to manually specify sqlserver2012, otherwise you will use SqlServer2005 for pagination.
You can also implement AbstractHelperDialect and then configure that property to implement the fully qualified name of the class to use a custom implementation.
2.offsetAsPageNum: The default value is false, which is valid when using RowBounds as a pagination parameter. When this parameter is set to true, the offset parameter in RowBounds will be used as pageNum, and two parameters can be used to pagination with page number and page size.
3. rowBoundsWithCount: The default value is false, which is valid when using RowBounds as a pagination parameter. When this parameter is set to true, using RowBounds pagination will perform a count query.
4.pageSizeZero: The default value is false, when this parameter is set to true, if pageSize=0 or RowBounds.limit = 0 will query all the results (equivalent to no pagination query is executed, but the returned results are still of type Page).
5.reasonable: pagination rationalization parameter, the default value is false. When this parameter is set to true, pageNum<=0 queries the first page, and pageNum>pages (when the total number is exceeded), the last page is queried. If the default parameter is false, the query is performed based on the parameters.
6. params: In order to support the startPage(Object params) method, this parameter is added to configure the parameter mapping, which is used to take the value from the object according to the attribute name, you can configure pageNum,pageSize,count,pageSizeZero,reasonable, and the default value is not configured for mapping, the default value is pageNum=pageNum; pageSize=pageSize; count=countSql; reasonable=reasonable; pageSizeZero=pageSizeZero。
7.supportMethodsArguments: Support passing pagination parameters through Mapper interface parameters, the default value is false, the pagination plug-in will automatically take the value from the parameter value of the query method, according to the field configured in the above params, and it will be automatically pagination when it finds the appropriate value. For more information, see the test code
com.github.pagehelper.test.basic package.
8. autoRuntimeDialect: The default value is false. When set to true, it allows the pagination of the corresponding dialect to be automatically recognized at runtime based on multiple data sources (automatic selection of sqlserver2012 is not supported, only sqlserver can be used), and the usage and precautions refer to scenario 5 below.
9.closeConn: The default value is true. When using a runtime dynamic data source or without setting the helperDialect property to automatically obtain the database type, a database connection will be automatically obtained, and the default is true to close, and the default value is true, and the set of this parameter will not be closed according to the data source you choose.
10.aggregateFunctions(5.1.5+): The default is the aggregate function of all common databases, allowing you to manually add the aggregate function (affecting the number of rows), all functions starting with the aggregate function, when performing the count conversion, will set a layer. Other functions and columns are replaced with count(0), where the count column can be configured by itself.
4. How to choose to configure these parameters
Scenario 1
If you’re still using an ibatis-like namespace call, you might want to use rowBoundsWithCount, the pagination plugin supports RowBounds in the same way as MyBatis by default, and doesn’t do count queries by default, and if you want to do count queries when pagination, and use the more powerful PageInfo class, you need to set this parameter to true.
Note: PageRowBounds also needs to be configured to true if you want to query the total number of queries.
Scenario two
If you are still using ibatis namespace calls, and you think that the two parameters in RowBounds, offset, limit are not as easy to understand as pageNum, and pageSize is easier to understand, you can use the offsetAsPageNum parameter, and when the parameter is set to true, the offset will be used as pageNum, and the limit and pageSize mean the same thing.
Scenario three
If you feel that you still want to query all the results through the control parameter after using pagination somewhere, you can set pageSizeZero to true, and when pageSize=0 or RowBounds.limit = 0 will query all the results.
Scenario four
If your pagination plugin is used to view tabular data like pagination, such as news lists, software lists, and you want the user to enter a page that is not within the legal range (outside the first page to the last page) to respond correctly to the correct result page, then you can configure reasonable to true, then if pageNum<=0 will query the first page, if pageNum >Total Pages queries the last page.
Scenario five
If you have configured a dynamic data source in Spring and are connecting to a different type of database, you can set autoRuntimeDialect to true so that when using different data sources, the matching pagination will be used for querying. In this case, you also need to pay special attention to the closeConn parameter, which is used to control whether to close the connection after the connection is obtained, since getting the data source type will get a database connection. The default is true, and some database connections are closed and no subsequent database operations can be performed. However, if some database connections are not closed, the database will become unresponsive quickly because the number of connections is used up. Therefore, when using this feature, you need to pay special attention to whether the data source you are using needs to close the database connection.
When you don’t use a dynamic data source and just automatically fetch helperDialect, the database connection will only be fetched once, so you don’t need to worry about whether the occupied connection will cause the database to fail, but it is also best to choose whether to close the connection according to the characteristics of the data source.
Three. Use in code
The pagination plug-in supports the following calls:
First, Call List<User> list = sqlSession.selectList(“x.y.selectIf”, null, new RowBounds(0, 10)) in RowBounds mode. The second method is to call the Mapper interface, which is recommended. PageHelper.startPage(1, 10); List<User> list = userMapper.selectIf(1); The third method is to call the Mapper interface, which is recommended. PageHelper.offsetPage(1, 10); List<User> list = userMapper.selectIf(1); Fourth, the parameter method calls // there are the following Mapper interface methods, and you don’t need to process the two parameters public interface CountryMapper { List after xml processing<User> selectByPageNumSize( @Param(“user”) User user, @Param(“pageNum”) int pageNum, @Param(“pageSize”) int pageSize); }//Configure supportMethodsArguments=true//Call directly in the code: List<User> list = userMapper.selectByPageNumSize(user, 1, 10); Fifth, if pageNum and pageSize exist in the User object, as long as the parameter has a value, it will also be pagination//There is the following User objectpublic class User { //Other fields //The names of the following two parameters are the same as the names of the params configuration private Integer pageNum; private Integer pageSize; }//There are the following Mapper interface methods, you don’t need to process the last two parameters in xml public interface CountryMapper { List<User> selectByPageNumSize(User user); } } }//When pageNum!= null && pageSize!= null in user, it will automatically paginate List<User> list = userMapper.selectByPageNumSize(user); Sixth, ISelect interface mode //jdk6,7 usage, create interface Page<User> page = PageHelper.startPage(1, 10).doSelectPage(new ISelect() { @Override public void doSelect() { userMapper.selectGroupBy(); } } }}); jdk8 lambda usagePage<User> page = PageHelper.startPage(1, 10).doSelectPage(()-> userMapper.selectGroupBy()); You can also return PageInfo directly, note the doSelectPageInfo method and doSelectPagePageInfo = PageHelper.startPage(1, 10).doSelectPageInfo(new ISelect() { @Override public void doSelect() { userMapper.selectGroupBy(); }}); corresponding lambda usage pageInfo = PageHelper.startPage(1, 10).doSelectPageInfo(() -> userMapper.selectGroupBy()); count query, the count number of a query statement is returned long total = PageHelper.count(new ISelect() { @Override public void doSelect() { userMapper.selectLike(user); } }}); //lambdatotal = PageHelper.count(()->userMapper.selectLike(user));
The most commonly used methods are described in detail below
1. Call RowBounds
List<User> list = sqlSession.selectList(“x.y.selectIf”, null, new RowBounds(1, 10));
When using this call, you can use the RowBounds parameter for pagination, which is the least intrusive, and we can see that the call via RowBounds just uses this parameter and doesn’t add anything else.
When the pagination plugin detects that the RowBounds parameter is used, it physically pagination the query.
There are two special arguments for RowBounds for this call, as you can see Scene 1 and Scene 2 above
Note: RowBounds can be used not only in namespace mode, but also in the interface when using the RowBounds parameter, for example:
In this case, a physical pagination query is also performed: List<User> selectAll(RowBounds, rowBounds);
2. PageHelper.startPage static method call
In addition to the PageHelper.startPage method, a similar usage of the PageHelper.offsetPage method is provided.
Call the PageHelper.startPage static method before you need to paginate the MyBatis query method, and the first MyBatis query method after this method will be paginated.
Get page 1, 10 contents, default query total countPageHelper.startPage(1, 10); The first select method will be paginated List<User> list = userMapper.selectIf(1); assertEquals(2, list.get(0).getId()); assertEquals(10, list.size()); <E>If you want to retrieve the pagination information, you need to cast it to Page<E>assertEquals(182, ((Page) list).getTotal()).
request: url?pageNum=1&pageSize=10//ServletRequest, Map, POJO objects are supported, and the params parameter PageHelper.startPage(request) is required. The first select method will be paginated List<User> list = userMapper.selectIf(1); The latter ones will not be paginated unless PageHelper.startPageList<User> list2 = userMapper.selectIf(null) is called again; //list1assertEquals(2, list.get(0).getId()); assertEquals(10, list.size()); <E>If you want to get out the pagination information, you need to cast it to Page<E>, // or use the PageInfo class (the following example is introduced)assertEquals(182, ((Page) list).getTotal()); //list2assertEquals(1, list2.get(0).getId()); assertEquals(182, list2.size());
3. Parameter calling
To use the parameter method, you need to set the supportMethodsArguments parameter to true, and also configure the params parameter. For example, the following configuration:
<plugins> <!– com.github.pagehelper is the package name of the PageHelper class –> <plugin interceptor=”com.github.pagehelper.PageInterceptor”> <!– Use the following method to configure the parameters, and all the parameters will >be described later <property name=”supportMethodsArguments” value=”true”/> <property name=”params” value=”pageNum=pageNumKey;pageSize=pageSizeKey;”/></plugin></plugins>
In the MyBatis method:
List<User> selectByPageNumSize( @Param(“user”) User user, @Param(“pageNumKey”) int pageNum, @Param(“pageSizeKey”) int pageSize);
When this method is called, it will be paginated because both the pageNumKey and pageSizeKey parameters are found. Several of the parameters provided by params can be used in this way.
In addition to the above method, if the User object contains these two parameter values, you can also have the following methods:
List<User> selectByPageNumSize(User user);
When both pageNumKey and pageSizeKey parameters are found in User, the method is paginated.
Note: The pageNum and pageSize properties exist at the same time to trigger the pagination operation, and the other pagination parameters will only take effect under this premise.
