MyBatis Wrapper Java Common class for paged queries
Paging----is essential for queries that have a large amount of data. MyBatis the underlying paging SQL statement because we need to write manually. When paging is implemented, we need to query the total number of records and the details of the records according to the paged query criteria. 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 detailed 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 cumbersome, code reuse----must be reused. Therefore, a public method of paging needs emerged.
Directly on the paging public Code, the implementation of the principle is to use the interception of interceptors. Intercepts requests for a class of paged queries. We will intercept the parameter according to whether it is required to intercept in the Interceptor () method, and execute the corresponding SQL append, otherwise, do not append. Direct release. It is considered as a normal query.
You need to load the public class when you configure the load server in the MyBatis configuration file: 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);
Detach the proxy object chain (since the target class may be intercepted by multiple interceptors, resulting in multiple proxies, through the following two loops
Can isolate the most primitive target class)
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 require paging. By Mappedstatement ID match, the default override is the end of 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 total pages in paging parameters, etc.
Setpageparameter (SQL, Connection, mappedstatement, Boundsql, page);
}
}
//Give execution 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 into the paging parameter <code>pageparam</code>
* Users can use the paging parameters <code>PageParam</code> get 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 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 II: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 will not be available after a paging query.
MyBatis wrapper Java Common class for paged queries
