Shulou(Shulou.com)06/02 Report--

This article mainly introduces the use of PageHelper in Mybatis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

PageHelper is an easy to use open source free Mybatis third-party physical paging plug-in, in fact, I do not want to add the word easy-to-use, but in order to praise the plug-in author's noble spirit of open source free, I do not hesitate to add the word easy-to-use as a compliment.

Originally thought that the paging plug-in, should be very simple, but PageHelper is much more complex than I thought, it is very powerful, but also very thorough, so powerful that users may not need so many functions, thoroughly so that a reference can be dual-use. However, I think, as a paging plug-in, to complete the physical paging task is fundamental, a lot of other intelligence is not necessary, keep it stupid enough, the technical term is stupid, simple is beautiful.

We will briefly introduce the basic use of PageHelper and the meaning of configuration parameters, focusing on the implementation of PageHelper as a Mybatis paging plug-in.

1. Maven dependency and plug-in configuration of PageHelper

Com.github.pagehelper pagehelper 4.1.6

PageHelper in addition to its own jar package, it also relies on a jar package called jsqlparser, when using, we do not need to specify jsqlparser's maven dependency separately, maven's indirect dependency will help us introduce.

The above is the official configuration and comments given by PageHelper. Although it is written a lot, it does describe it very clearly.

Dialect: to identify what kind of database it is, it must be designed.

OffsetAsPageNum: use offset, the first parameter of RowBounds, as the pageNum page number. This is the "one parameter and two uses" mentioned above. Personally, I think it is completely unnecessary. Offset = pageSize * pageNum is fine. Why mix parameters?

RowBoundsWithCount: when set to true, RowBounds paging will be used for count query, which I don't think is necessary. In actual development, every list paging query is equipped with a count quantity query.

When reasonable:value=true, pageNum < 1 will query the first page, and if pageNum is greater than pageSize, the last page will be queried. Personally, parameter verification should be completed before entering the Mybatis business system, and it is impossible to reach the parameters in the Mybatis business system with illegal values.

In this way, we only need to remember the parameter dialect = mysql. In fact, there are a few related parameters that can be configured.

AutoDialect:true or false, whether to automatically detect dialect.

AutoRuntimeDialect:true or false, whether to automatically detect dialect in case of multiple data sources.

CloseConn:true or false, whether to close the Connection connection after the dialect is detected.

The above three intelligent parameters should not be used in the system until we have to. All we need is a dialect = mysql or dialect = oracle. If we need to use them in the system, we still have to ask ourselves whether we really have to use them.

2. PageHelper source code analysis

@ Intercepts (@ Signature (type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}) public class PageHelper implements Interceptor {/ / sql tool class private SqlUtil sqlUtil; / / attribute parameter information private Properties properties; / / configure object mode private SqlUtilConfig sqlUtilConfig / / obtain dialect automatically. If there is no setProperties or setSqlUtilConfig, you can also perform private boolean autoDialect = true; / / when the runtime automatically acquires dialect private boolean autoRuntimeDialect; / / multiple data sources, whether to close the data source private boolean closeConn = true; / / cache private Map urlSqlUtilMap = new ConcurrentHashMap () after obtaining jdbcurl; private ReentrantLock lock = new ReentrantLock (); / /.}

The above is the official source code and the comments brought by the source code, let's add it.

SqlUtil: database type specific sql tool class. A database url corresponds to a SqlUtil instance. There is a Parser object in the SqlUtil. If it is mysql, it is MysqlParser. If it is oracle, it is OracleParser. This Parser object is the main existence value of different instances of SqlUtil. SqlUtil completes the functions of executing count query, setting Parser object, executing paging query, saving Page paging object and so on.

Used in SqlUtilConfig:Spring Boot, ignore.

AutoRuntimeDialect: when multiple data sources are switched, such as mysql and oracle data sources exist at the same time, you cannot simply specify dialect, which requires the runtime to automatically detect the current dialect.

Map urlSqlUtilMap: it is used to cache the automatic detection results of autoRuntimeDialect. Key is the database's url,value is SqlUtil. Since this automatic detection only needs to be performed once, it is cached.

ReentrantLock lock: this lock object is a more interesting phenomenon, urlSqlUtilMap is obviously a synchronous ConcurrentHashMap, but also created a lock to synchronize ConcurrentHashMap to do? Is it icing on the cake?

It is discussed in detail in the book "Java concurrent programming practice". To put it simply, ConcurrentHashMap can guarantee that put or remove methods must be thread-safe, but it can not guarantee that the combined operation of put, get and remove is thread-safe. In order to ensure that the combined operation is thread-safe, lock is used.

Com.github.pagehelper.PageHelper.java source code.

/ / Mybatis interceptor method public Object intercept (Invocation invocation) throws Throwable {if (autoRuntimeDialect) {/ / multiple data sources SqlUtil sqlUtil = getSqlUtil (invocation); return sqlUtil.processPage (invocation);} else {/ / single data source if (autoDialect) {initSqlUtil (invocation) } / / specified dialect return sqlUtil.processPage (invocation);}} public synchronized void initSqlUtil (Invocation invocation) {if (this.sqlUtil = = null) {this.sqlUtil = getSqlUtil (invocation); if (! autoRuntimeDialect) {properties = null; sqlUtilConfig = null } autoDialect = false;}} public void setProperties (Properties p) {checkVersion (); / / whether to close the data source String closeConn = p.getProperty ("closeConn") after obtaining jdbcurl; / / solve # 97 if (StringUtil.isNotEmpty (closeConn)) {this.closeConn = Boolean.parseBoolean (closeConn) } / / initialize PARAMS SqlUtil.setParams of SqlUtil (p.getProperty ("params")); / / Database dialect String dialect = p.getProperty ("dialect"); String runtimeDialect = p.getProperty ("autoRuntimeDialect"); if (StringUtil.isNotEmpty (runtimeDialect) & & runtimeDialect.equalsIgnoreCase ("TRUE")) {this.autoRuntimeDialect = true; this.autoDialect = false This.properties = p;} else if (StringUtil.isEmpty (dialect)) {autoDialect = true; this.properties = p;} else {autoDialect = false; sqlUtil = new SqlUtil (dialect); sqlUtil.setProperties (p) }} public SqlUtil getSqlUtil (Invocation invocation) {MappedStatement ms = (MappedStatement) invocation.getArgs () [0]; / change to cache dataSource DataSource dataSource = ms.getConfiguration (). GetEnvironment (). GetDataSource (); String url = getUrl (dataSource); if (urlSqlUtilMap.containsKey (url)) {return urlSqlUtilMap.get (url) } try {lock.lock (); if (urlSqlUtilMap.containsKey (url)) {return urlSqlUtilMap.get (url);} if (StringUtil.isEmpty (url)) {throw new RuntimeException ("cannot get jdbcUrl automatically, please configure the dialect parameter in the paging plug-in!") } String dialect = Dialect.fromJdbcUrl (url); if (dialect = = null) {throw new RuntimeException ("cannot get the database type automatically, please specify it through the dialect parameter!");} SqlUtil sqlUtil = new SqlUtil (dialect); if (this.properties! = null) {sqlUtil.setProperties (properties) } else if (this.sqlUtilConfig! = null) {sqlUtil.setSqlUtilConfig (this.sqlUtilConfig);} urlSqlUtilMap.put (url, sqlUtil); return sqlUtil;} finally {lock.unlock ();}}

AutoRuntimeDialect: multiple data sources, multiple SqlUtil will be created.

AutoDialect: single data source, only 1 SqlUtil will be created. Single data sources can also be used as multiple data sources.

Dialect is specified: only 1 SqlUtil will be created.

3. PageSqlSource

Public abstract class PageSqlSource implements SqlSource {/ * get the normal BoundSql * * @ param parameterObject * @ return * / protected abstract BoundSql getDefaultBoundSql (Object parameterObject); / * get the BoundSql * * @ param parameterObject * @ return * / protected abstract BoundSql getCountBoundSql (Object parameterObject) of the Count query / * get the BoundSql * * @ param parameterObject * @ return * / protected abstract BoundSql getPageBoundSql (Object parameterObject) of the paging query; / * get BoundSql * * @ param parameterObject * @ return * / @ Override public BoundSql getBoundSql (Object parameterObject) {Boolean count = getCount () If (count = = null) {return getDefaultBoundSql (parameterObject);} else if (count) {return getCountBoundSql (parameterObject);} else {return getPageBoundSql (parameterObject);}

GetDefaultBoundSql: get the original unmodified BoundSql.

GetCountBoundSql: there is no need to write count queries, plug-ins query sql according to paging, and intelligently query BoundSql for your generated count.

GetPageBoundSql: gets the BoundSql of the paging query.

For example:

DefaultBoundSql:

Select stud_id as studId, name, email, dob, phone from students

CountBoundSql:

Select count (0) from students-- done by PageHelper intelligently

PageBoundSql:

Select stud_id as studId, name, email, dob, phone from students limit?,?

Public class PageStaticSqlSource extends PageSqlSource {private String sql; private List parameterMappings; private Configuration configuration; private SqlSource original; @ Override protected BoundSql getDefaultBoundSql (Object parameterObject) {String tempSql = sql; String orderBy = PageHelper.getOrderBy (); if (orderBy! = null) {tempSql = OrderByParser.converToOrderBySql (sql, orderBy);} return new BoundSql (configuration, tempSql, parameterMappings, parameterObject) } @ Override protected BoundSql getCountBoundSql (Object parameterObject) {/ / localParser refers to MysqlParser or OracleParser / / localParser.get () .getCountSql (sql), which can generate a count query sql return new BoundSql (configuration, localParser.get (). GetCountSql (sql), parameterMappings, parameterObject) based on the original sql;} @ Override protected BoundSql getPageBoundSql (Object parameterObject) {String tempSql = sql String orderBy = PageHelper.getOrderBy (); if (orderBy! = null) {tempSql = OrderByParser.converToOrderBySql (sql, orderBy);} / / getPageSql can generate a sql with paging parameter information based on the original sql, such as limit? TempSql = localParser.get () .getPageSql (tempSql); / / because sql adds the paging parameter? The number placeholder, getPageParameterMapping (), is based on the original List and adds two ParameterMapping objects corresponding to the paging parameters. Assign values to the paging parameters using return new BoundSql (configuration, tempSql, localParser.get (). GetPageParameterMapping (configuration, original.getBoundSql (parameterObject)), parameterObject);}}

Suppose the original size=2 of List, after adding paging parameters, how many size=4 will be added, depending on the paging parameters? The number of numbers.

Other PageSqlSource, the principle is exactly the same as PageStaticSqlSource.

Parsing sql and adding paging parameter placeholders, or generating sql for count queries, all rely on Parser.

4. Com.github.pagehelper.parser.Parser

Public class MysqlParser extends AbstractParser {@ Override public String getPageSql (String sql) {StringBuilder sqlBuilder = new StringBuilder (sql.length () + 14); sqlBuilder.append (sql); sqlBuilder.append ("limit?,?"); return sqlBuilder.toString () @ Override public Map setPageParameter (MappedStatement ms, Object parameterObject, BoundSql boundSql, Page page) {Map paramMap = super.setPageParameter (ms, parameterObject, boundSql, page); paramMap.put (PAGEPARAMETER_FIRST, page.getStartRow ()); paramMap.put (PAGEPARAMETER_SECOND, page.getPageSize ()); return paramMap;}}

We can clearly see how MysqlParser adds paging placeholders and paging parameters.

Public abstract class AbstractParser implements Parser, Constant {public String getCountSql (final String sql) {return sqlParser.getSmartCountSql (sql);}}

Generating count sql, which is done by the jsqlparser toolkit mentioned earlier, is another open source sql parsing toolkit.

5. SqlUtil.doProcessPage () paging query

/ / PageSqlSource decorates the original SqlSource public void processMappedStatement (MappedStatement ms) throws Throwable {SqlSource sqlSource = ms.getSqlSource (); MetaObject msObject = SystemMetaObject.forObject (ms); SqlSource pageSqlSource; if (sqlSource instanceof StaticSqlSource) {pageSqlSource = new PageStaticSqlSource ((StaticSqlSource) sqlSource);} else if (sqlSource instanceof RawSqlSource) {pageSqlSource = new PageRawSqlSource ((RawSqlSource) sqlSource) } else if (sqlSource instanceof ProviderSqlSource) {pageSqlSource = new PageProviderSqlSource ((ProviderSqlSource) sqlSource);} else if (sqlSource instanceof DynamicSqlSource) {pageSqlSource = new PageDynamicSqlSource ((DynamicSqlSource) sqlSource);} else {throw new RuntimeException ("unable to handle SqlSource of this type [" + sqlSource.getClass () + "]);} msObject.setValue (" sqlSource ", pageSqlSource) / / since the return value of the count query needs to be modified, here you want to create a MS msCountMap.put (ms.getId (), MSUtils.newCountMappedStatement (ms)) of a Count query;} / / execute a paged query private Page doProcessPage (Invocation invocation, Page page, Object [] args) throws Throwable {/ / Save RowBounds status RowBounds rowBounds = (RowBounds) args [2] / / get the original ms MappedStatement ms = (MappedStatement) args [0]; / / determine and process it as PageSqlSource if (! isPageSqlSource (ms)) {processMappedStatement (ms);} / / set the current parser so that the value of set,ThreadLocal will not have an adverse effect before each use ((PageSqlSource) ms.getSqlSource ()) .setParser (parser) Try {/ / ignore RowBounds- otherwise Mybatis comes with memory paging args [2] = RowBounds.DEFAULT; / / if only sorting or pageSizeZero judgment is performed, if (isQueryOnly (page)) {return doQueryOnly (page, invocation) } / / simply judge whether to query count if (page.isCount ()) {page.setCountSignal (Boolean.TRUE) by the value of total; / / replace MS args [0] = msCountMap.get (ms.getId ()) / / Total number of queries Object result = invocation.proceed (); / / restore ms args [0] = ms; / / set the total number of page.setTotal ((Integer) ((List) result) .get (0)) If (page.getTotal () = = 0) {return page;}} else {page.setTotal (- 1l) Execute a paging query when} / / pageSize > 0. PageSize 0 & & ((rowBounds = = RowBounds.DEFAULT & & page.getPageNum () > 0) | | rowBounds! = RowBounds.DEFAULT) {/ / replace the MappedStatement in the parameter with the new qs page.setCountSignal (null) BoundSql boundSql = ms.getBoundSql (args [1]); args [1] = parser.setPageParameter (ms, args [1], boundSql, page); page.setCountSignal (Boolean.FALSE); / / execute paging query Object result = invocation.proceed () / / get the processing result page.addAll ((List) result);}} finally {((PageSqlSource) ms.getSqlSource ()) .removeParser ();} / / return the result return page;}

Just pay attention to four key points in the source code:

1. MsCountMap.put (ms.getId (), MSUtils.newCountMappedStatement (ms)), create a MappedStatement object for count query and cache it in msCountMap.

2. If count=true, the count query is executed, the resulting total value is saved in the page object, and the paging query continues.

3. Execute the paging query and save the query results in the page object, where page is an ArrayList object.

4. Args [2] = RowBounds.DEFAULT to change the original paging behavior of Mybatis

Args [1] = parser.setPageParameter (ms, args [1], boundSql, page), change the original parameter list (add paging parameters).

6. Two ways of using PageHelper

First, the paging query is completed directly through the RowBounds parameter.

List list = studentMapper.find (new RowBounds (0,10)); Page page = ((Page) list

The second, PageHelper.startPage () static method

/ / get page 1, 10 items, the default total number of queries count PageHelper.startPage (1,10); / / the first select method that follows will be paged List list = studentMapper.find (); Page page = ((Page) list)

Note: return the result list, which is already a Page object, and the Page object is an ArrayList.

Principle: use ThreadLocal to pass and save Page objects, and the PageHelper.startPage () method needs to be set separately for each query.

Public class SqlUtil implements Constant {private static final ThreadLocal LOCAL_PAGE = new ThreadLocal ();}

The count query often mentioned in this article is actually a MappedStatement memory object that PageHelper helps us to generate. It eliminates the need for us to declare a sql count query separately in XXXMapper.xml. We only need to write a sql paging business query.

Thank you for reading this article carefully. I hope the article "what is the use of PageHelper in Mybatis" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.