MyBatis use (ii) paging query

Because MyBatis does not have the relevant paging query, the paging query function of hibernate is extracted, the local SQL dialect is distinguished by filter, and the SQL statements are spliced and encapsulated.

Steps are divided into: 1, first check out the total

2, through the splicing of SQL and then to query the relevant information

/** * Query with paging information * @param sqlmapid mybatis Mapping ID * @param pagerequest paging request parameter information * @return */@SuppressWarnings ({"Rawtypes", "u Nchecked "}) public page findforpage (String sqlmapid, pagerequest pagerequest) {//Total queries Pagerequest.getfilters (). Put ( Selectcountsqlinterceptor.count, NULL); Sets whether SQL needs to be converted to total queries sqlnumber TotalCount = (number) Findforobject (Sqlmapid, Pagerequest.getfilters ()); if (TotalCount = = NULL | | Totalcount.intvalue () <= 0) {return new Page (pagerequest, 0);} if (totalcount! = null && totalcount.intvalue () <= (Pagerequest.getpagenumber ()-1) * Pagerequest.getpagesize ()) {return new Page (Pagerequest.getpagenumber (), Pagerequest.getpagesize (), Totalcount.intvalue (), new ArrayList (0)) ;} Pagerequest.getfilters (). Remove (Selectcountsqlinterceptor.count); Map filters = new HashMap (); Filters.putall (Pagerequest.getfilters ()); Page page = new page (Pagerequest, Totalcount.intvalue ()); List List = Findforlist (Sqlmapid, Filters, Page.getfirstresult (), Page.getpagesize ());p Age.setresult (liST); return page;} 

　　

/** * Paging Request Information */public class Pagerequest implements Serializable {private static final long Serialversionuid = 909218683891 8641382l;/** * Filter Parameter */private Map filters;/** * page number, page number starting from 1 */private int pagenumber;/** * Paging size */private int pageSize;}

　　

/** * Turn query SQL into total number of queries sql<br> * * Config file contents: * * <pre> * <plugins> * <plugin interceptor= "Ewell.nis.common . Ibatis.plugin.SelectCountSqlInterceptor "/> * </plugins> * </pre> * * @Intercepts ({@Signature (type = Executor.class, method = "Query", args = {mappedstatement.class, object.class, Rowbounds.class,resulthandler.class})}) public class Selectcountsqlinterceptor implements interceptor {public static String COUNT = "_count";p rivate static int MA Pped_statement_index = 0;private static int parameter_index = 1; @Overridepublic Object intercept (invocation invocation) T Hrows throwable {processcountsql (Invocation.getargs ()); return Invocation.proceed ();} private void Processcountsql (final object[] Queryargs) {if (Queryargs[parameter_index] instanceof Map) {Map PARAMETER = (M AP) queryargs[parameter_index];if (Parameter.containskey (COUNT)) {mappedstatement ms = (mappedstatement) queryArgs[ Mapped_statement_index]; Boundsql boundsql = ms.getboundsql (parameter); String sQL = ms.getboundsql (parameter). GetSQL (). Trim (); Boundsql newboundsql = new Boundsql (Ms.getconfiguration (), Getcountsql (SQL), Boundsql.getparametermappings (), Boundsql.getparameterobject ()); Mappedstatement newms = copyfrommappedstatement (ms,new boundsqlsqlsource (Newboundsql)); queryArgs[MAPPED_STATEMENT_ INDEX] = newms;}}} See:mapperbuilderassistantprivate mappedstatement copyfrommappedstatement (mappedstatement ms,SqlSource Newsqlsource) {Builder builder = new Mappedstatement.builder (Ms.getconfiguration (), Ms.getid (), Newsqlsource, Ms.getsqlcommandtype ()); Builder.resource (Ms.getresource ()); Builder.fetchsize (Ms.getfetchsize ()); Builder.statementtype (Ms.getstatementtype ()); Builder.keygenerator (Ms.getkeygenerator ()); Builder.keyProperty ( getKeyProperty (Ms.getkeyproperties ()));//Setstatementtimeout () Builder.timeout (Ms.gettimeout ());// Setparametermap () Builder.parametermap (Ms.getparametermap ());//Setstatementresultmap () list<resultmap> Resultmaps = new arraylist<resultmap> (); String ID = "-inline"; if (ms.getresultmaps () = null) {id = ms.getresultmaps (). Get (0). GetId () + "-inline";} Resultmap Resultmap = new Resultmap.builder (null, ID, long.class,new ArrayList ()). build (); Resultmaps.add (RESULTMAP); Builder.resultmaps (resultmaps); Builder.resultsettype (Ms.getresultsettype ());//Setstatementcache () Builder.cache (Ms.getcache ()); Builder.flushcacherequired (ms.isflushcacherequired ()); Builder.usecache (Ms.isUseCache ()); return Builder.build ();} Private String getKeyProperty (string[] keyproperties) {StringBuilder builder = new StringBuilder (); if (keyproperties! = N ull && keyproperties.length > 0) {int length = keyproperties.length;for (int i = 0; i < length; i++) {Builde R.append (Keyproperties[i]); if (I < length-1) {builder.append (",");}} return builder.tostring ();} return null;}  private string Getcountsql (String sql) {string lowercasesql = Sql.tolowercase (). replace ("\ n", ""). Replace ("\ T", ""); int index = Lowercasesql.indexof ("order"); if (Index! =-1) {sql = Sql.sUbstring (0, index);} if (Lowercasesql.indexof ("group")! =-1) {return "SELECT COUNT (*) from (SELECT COUNT (*) as Count_" + sql.substring (lower Casesql.indexof ("from")) + "Table_";} Return "SELECT COUNT (*) as Count_" + sql.substring (Lowercasesql.indexof ("from");} @Overridepublic Object Plugin (object target) {return Plugin.wrap (target, this);} @Overridepublic void SetProperties (Properties properties) {}}

/** * provides IBATIS3 based on dialect (dialect) for paging query plug-in <br> * * will intercept Executor.query () method to achieve the insertion of paging dialect <br> * * Configuration file Contents: * * &LT;PRE&GT ; * <plugins> * <plugin interceptor= "Ewell.nis.common.ibatis.plugin.OffsetLimitInterceptor" > * < Property Name= "Dialectclass" value= "Ewell.nis.common.ibatis.dialect.MySQLDialect"/> * </plugin> * </ plugins> * </pre> * * * * * @Intercepts ({@Signature (type = Executor.class, method = "Query", args = {Mappedstatem Ent.class, Object.class, rowbounds.class,resulthandler.class}) public class Offsetlimitinterceptor implements Interceptor {private static int mapped_statement_index = 0;private static int parameter_index = 1;private static int ROWBO Unds_index = 2;private dialect dialect; @Overridepublic Object intercept (invocation invocation) throws Throwable { Processintercept (Invocation.getargs ()); return Invocation.proceed ();} void Processintercept (Final object[] Queryargs) {//Queryargs = query (mappedstatement MS, Object parameter, rowboundsRowbounds, Resulthandler resulthandler) mappedstatement ms = (mappedstatement) queryargs[mapped_statement_index];o Bject parameter = queryargs[parameter_index];final rowbounds rowbounds = (rowbounds) queryargs[rowbounds_index];int offset = Rowbounds.getoffset (); int limit = Rowbounds.getlimit (); if (Dialect.supportslimit () && (offset! = Rowbounds.no_row_offset | | Limit! = Rowbounds.no_row_limit)) {Boundsql Boundsql = ms.getboundsql (parameter); String sql = Boundsql.getsql (). Trim (); if (Dialect.supportslimitoffset ()) {sql = dialect.getlimitstring (SQL, offset, limit); offset = Rowbounds.no_row_offset;} else {sql = dialect.getlimitstring (sql, 0, offset+limit);} Limit = Rowbounds.no_row_limit;queryargs[rowbounds_index] = new Rowbounds (offset, limit); Boundsql newboundsql = new Boundsql (Ms.getconfiguration (), sql,boundsql.getparametermappings (), Boundsql.getparameterobject ()); Mappedstatement newms = copyfrommappedstatement (ms,new boundsqlsqlsource (Newboundsql)); queryArgs[MAPPED_STATEMENT_ InchDEX] = newms;}} See:mapperbuilderassistantprivate mappedstatement copyfrommappedstatement (mappedstatement ms,SqlSource Newsqlsource) {Builder builder = new Mappedstatement.builder (Ms.getconfiguration (), Ms.getid (), Newsqlsource, Ms.getsqlcommandtype ()); Builder.resource (Ms.getresource ()); Builder.fetchsize (Ms.getfetchsize ()); Builder.statementtype (Ms.getstatementtype ()); Builder.keygenerator (Ms.getkeygenerator ()); Builder.keyProperty ( getKeyProperty (Ms.getkeyproperties ()));//Setstatementtimeout () Builder.timeout (Ms.gettimeout ());// Setstatementresultmap () Builder.parametermap (Ms.getparametermap ());//Setstatementresultmap () Builder.resultMaps ( Ms.getresultmaps ()); Builder.resultsettype (Ms.getresultsettype ());//Setstatementcache () Builder.cache ( Ms.getcache ()); Builder.flushcacherequired (ms.isflushcacherequired ()); Builder.usecache (Ms.isUseCache ()); return Builder.build ();} public object Plugin (object target) {return Plugin.wrap (target, this);} public void SetProperties (properties properties) {StrinG Dialectclass = new Propertieshelper (properties). Getrequiredstring ("Dialectclass"); try {dialect = (dialect) Class.forName (Dialectclass). newinstance ();} catch (Exception e) {throw new RuntimeException ("Cannot create dialect instance by Dialectclass:" + Dialectclass, e);} System.out.println (OffsetLimitInterceptor.class.getSimpleName () + ". dialect=" + Dialectclass);} Private String getKeyProperty (string[] keyproperties) {StringBuilder builder = new StringBuilder (); if (keyproperties! = N ull && keyproperties.length > 0) {int length = keyproperties.length;for (int i = 0; i < length; i++) {Builde R.append (Keyproperties[i]); if (I < length-1) {builder.append (",");}} return builder.tostring ();} return null;} public static class Boundsqlsqlsource implements Sqlsource {private Boundsql boundsql;public Boundsqlsqlsource (boundsql Boundsql) {this.boundsql = Boundsql;} Public Boundsql Getboundsql (Object parameterobject) {return boundsql;}}

　　

　　

MyBatis use (ii) paging query