MyBatis Packet Paging query Java public class

MyBatis Packet Paging query Java public class
paging----for queries with very large amounts of data. is indispensable.

MyBatis the underlying paging SQL statement because it requires us to write manually. In the case of paging, we need to query the total number of records and records according to the condition of the paging query. Therefore, if we do not implement encapsulation, we need to write two SQL statements to implement it. Used once to query the number of records. A specific record for querying the paging display at once.

When we encounter a lot of paging in the project, we need to write two SQL statements for each mapper.xml file.

Extremely troublesome. Code reuse----must be reused. Therefore, a public method of paging needs emerged.

Directly on the paging public code, in fact, the principle is to use the interception of interceptors. Intercepts requests for a class of paged queries. We are based on whether the passed in parameter is required to intercept the Interceptor () method, then intercept and run the corresponding SQL append, otherwise, do not append. Direct release. It is considered as a normal query.

The public class needs to be loaded in the MyBatis configuration file when it is loaded into the server: 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>
<properties></properties>
<plugins>
<plugin interceptor= "Com.iboxpay.clear.filter.PaginationInterceptor" ></plugin>
</plugins>
</configuration>


Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.Properties;

Import Org.apache.ibatis.executor.parameter.ParameterHandler;
Import Org.apache.ibatis.executor.statement.StatementHandler;
Import ORG.APACHE.IBATIS.MAPPING.BOUNDSQL;
Import org.apache.ibatis.mapping.MappedStatement;
Import Org.apache.ibatis.plugin.Interceptor;
Import org.apache.ibatis.plugin.Intercepts;
Import org.apache.ibatis.plugin.Invocation;
Import Org.apache.ibatis.plugin.Plugin;
Import Org.apache.ibatis.plugin.Signature;
Import Org.apache.ibatis.reflection.MetaObject;
Import Org.apache.ibatis.reflection.factory.DefaultObjectFactory;
Import Org.apache.ibatis.reflection.factory.ObjectFactory;
Import Org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
Import Org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;
Import Org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
Import Org.apache.ibatis.session.RowBounds;
Import Org.slf4j.Logger;
Import Org.slf4j.LoggerFactory;

/**
* Paging Interceptor
* @since 10.20.2014
*/
@Intercepts ({@Signature (type=statementhandler.class,method= "Prepare", Args={connection.class})})
public class Paginationinterceptor implements interceptor {

Private final Logger Logger = Loggerfactory.getlogger (Paginationinterceptor.class);

private static final Objectfactory default_object_factory = new Defaultobjectfactory ();
private static final Objectwrapperfactory default_object_wrapper_factory = new Defaultobjectwrapperfactory ();

Public Object intercept (invocation invocation) throws Throwable {

Statementhandler Statementhandler = (statementhandler) invocation.gettarget ();
MetaObject Metastatementhandler = Metaobject.forobject (Statementhandler,
Default_object_factory, default_object_wrapper_factory);

Detaches the proxy object chain (because the target class can be intercepted by multiple interceptors, resulting in multiple proxies.) Through the following two cycles
Able to isolate the most primitive target classes)
while (Metastatementhandler.hasgetter ("H")) {
Object object = Metastatementhandler.getvalue ("h");
Metastatementhandler = Metaobject.forobject (object, Default_object_factory,
Default_object_wrapper_factory);
}
//Detach the target class of the last proxy object
while (Metastatementhandler.hasgetter ("target")) {
Object object = Metastatementhandler.getvalue ("target");
Metastatementhandler = Metaobject.forobject (object, Default_object_factory,
Default_object_wrapper_factory);
}

Mappedstatement mappedstatement = (mappedstatement)
Metastatementhandler.getvalue ("Delegate.mappedstatement");
//Rewrite only the SQL statements that need paging. Match by ID of Mappedstatement. The default override is the end of the page
Mappedstatement's SQL
Boundsql Boundsql = (boundsql) metastatementhandler.getvalue ("Delegate.boundsql");
Object parameterobject = Boundsql.getparameterobject ();

if (parameterobject instanceof Pageparam) {
if (Parameterobject = = null) {
throw new NullPointerException ("Parameterobject is null!");
} else {
Pageparam page = (pageparam) parameterobject;
String sql = Boundsql.getsql ();
//Rewrite SQL
String pagesql = buildpagesql (sql, page);
Metastatementhandler.setvalue ("Delegate.boundSql.sql", pagesql);
Metastatementhandler.setvalue ("Delegate.rowBounds.offset",
Rowbounds.no_row_offset);
Metastatementhandler.setvalue ("Delegate.rowBounds.limit", rowbounds.no_row_limit);
Connection Connection = (Connection) invocation.getargs () [0];
//Reset the total number of pages in the page count, etc.
Setpageparameter (SQL, Connection, mappedstatement, Boundsql, page);
}
}
//Pass the operation to the next interceptor
return Invocation.proceed ();
}

private string Buildpagesql (String sql, Pageparam page) {
if (page! = null) {
StringBuilder pagesql = new StringBuilder ();
Pagesql = buildpagesqlfororacle (sql, page);
return pagesql.tostring ();
} else {
return SQL;
}
}

Public StringBuilder buildpagesqlfororacle (String sql, Pageparam page) {
StringBuilder pagesql = new StringBuilder (100);
String Beginrow = string.valueof ((Page.getcurrentpage ()-1) * page.getpagesize ());
String Endrow = string.valueof (Page.getcurrentpage () * page.getpagesize ());
Pagesql.append ("Select * from" (select Temp.*, RowNum row_id from (");
Pagesql.append (SQL);
Pagesql.append (") Temp where rownum <="). Append (Endrow);
Pagesql.append (") where row_id >"). Append (Beginrow);
return pagesql;
}

/**
* Query the total number of records from the database and calculate the total pages, write back to the page number of <code>pageparam</code>
* You can use the page Count <code>PageParam</code> to get the relevant information.

*
* @param sql
* @param connection
* @param mappedstatement
* @param boundsql
* @param page
* @throws SQLException
*/
private void Setpageparameter (String sql, Connection Connection, Mappedstatement mappedstatement,
Boundsql boundsql, Pageparam page) throws SQLException {
Record Total Record Count
String countsql = "SELECT count (0) from (" + SQL + ")";
PreparedStatement countstmt = null;
ResultSet rs = null;
try {
countstmt = Connection.preparestatement (Countsql);
Boundsql Countbs = new Boundsql (Mappedstatement.getconfiguration (), Countsql,
Boundsql.getparametermappings (), Boundsql.getparameterobject ());
Setparameters (countstmt, Mappedstatement, Countbs, Boundsql.getparameterobject ());
rs = Countstmt.executequery ();
int totalcount = 0;
if (Rs.next ()) {
TotalCount = Rs.getint (1);
}
Page.settotalcount (TotalCount);
int totalpage = totalcount/page.getpagesize () + ((totalcount% page.getpagesize () = = 0)? 0:1);
Page.settotalpage (Totalpage);
} catch (SQLException e) {
Logger.error ("Exception", e);
} finally {
try {
Rs.close ();
} catch (SQLException e) {
Logger.error ("Exception", e);
}
try {
Countstmt.close ();
} catch (SQLException e) {
Logger.error ("Exception", e);
}
}
}

private void Setparameters (PreparedStatement PS, mappedstatement mappedstatement, Boundsql Boundsql,
Object parameterobject) throws SQLException {
Parameterhandler Parameterhandler = new Defaultparameterhandler (mappedstatement, Parameterobject, BOUNDSQL);
Parameterhandler.setparameters (PS);
}

@Override
public object Plugin (object target) {
if (target instanceof Statementhandler) {
Return Plugin.wrap (target, this);
} else {
return target;
}
}

@Override
public void SetProperties (Properties arg0) {
}
}
The filter mode of the log is transferred to debug mode, and the console can print out the SQL
A paged query processed using the above method, which requires only one SQL statement (SQL for the Mapper.xml file)
<select id= "Selectchannelsettlebyparam" parametertype= "Pageparam" resultmap= "Rs_channelsettle" >
<! [cdata[
SELECT *
]]>
From Channlsettle where 1=1
<if test= "Params.channelSettleModel.channelId! = null and! = params.channelSettleModel.channelId" >and Channl_id=#{params.channelsettlemodel.channelid}</if>
<if test= "Params.channelSettleModel.clearStartTime! = null and '! = Params.channelSettleModel.clearStartTime" >
<! [cdata[
and To_number (clear_date) >= to_number (substr (#{params.channelsettlemodel.clearstarttime},0,8))
]]>
</if>
<if test= "Params.channelSettleModel.clearEndTime! = null and '! = Params.channelSettleModel.clearEndTime" >
<! [cdata[
and To_number (clear_date) <= to_number (substr (#{params.channelsettlemodel.clearendtime},0,8))
]]>
</if>
ORDER BY instdate Desc
</select>

SQL for console printing:
First article:Select COUNT (0) from (SELECT * from Channlsettle where 1=1 and channl_id=? and To_number (clear_date) >= to_number (substr (?

, 0,8)) and To_number (clear_date) <= to_number (substr (?

, 0,8)) (Order by instdate Desc)

Article Two:select * FROM (select temp.*, RowNum row_id from (SELECT * from Channlsettle where 1=1 and channl_id=? and To_number (clear_date) >= to_number (substr (?

, 0,8)) and To_number (clear_date) <= to_number (substr (?

, 0,8)) (Order by instdate Desc) temp where rownum <=) where row_id > 0

This allows the public class to implement the work that we need to write two SQL repeatedly in the Mapper.xml configuration file, which is not available after a paging query.

Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced.

MyBatis Packet Paging query Java public class