According to the idea of the following page, it is easy to implement the multi-tenant design of Mybitas.
Use the interceptor provided by Mybatis. The paged SQL statements are processed into different paged SQLs through encapsulation.
This example has implemented the pagination function of Mysql and Oracle. Pay attention to the following quotation package and do not quote it incorrectly.
import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.Properties;import org.apache.ibatis.executor.parameter.ParameterHandler;import org.apache.ibatis.executor.statement.RoutingStatementHandler;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.mapping.ParameterMapping;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.scripting.defaults.DefaultParameterHandler;import com.yidao.utils.Page;import com.yidao.utils.ReflectHelper;/** * * Pagination interceptor, used to intercept operations that require paging query and then paginate them. * The principle of Mybatis paging is implemented using interceptors: * To use JDBC to operate on the database, you must have a corresponding Statement object. Before Mybatis executes the Sql statement, it will generate a Statement object containing the Sql statement, and the corresponding Sql statement* is generated before the Statement, so we can start with the Sql statement used to generate the Statement before it generates the Statement. In Mybatis Statement statement is generated by the * prepare method of the RoutingStatementHandler object. Therefore, one of the ideas for using an interceptor to implement Mybatis paging is to intercept the prepare method of the StatementHandler interface, and then change the Sql statement into the corresponding pagination query Sql statement in the interceptor method, and then call the prepare method of the * StatementHandler object, that is, call invocation.proceed(). * For pagination, one of the operations we need to do in the interceptor is to count the total number of records that meet the current conditions. This is by obtaining the original Sql statement, changing it to the corresponding statistical statement, and then replacing the parameters in the Sql statement using the Mybatis encapsulated parameters and setting * parameters. Then, the Sql statement querying the number of records is executed to count the total number of records. * */ @Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})public class PageInterceptor implements Interceptor { private String dialect = ""; //Database dialect private String pageSqlId = ""; //ID that needs to be intercepted in mapper.xml (regular match) public Object intercept(Invocation invocation) throws Throwable { //There are actually only two implementation classes for StatementHandler, one is RoutingStatementHandler, and the other is the abstract class BaseStatementHandler. //BaseStatementHandler has three subclasses, namely SimpleStatementHandler, PreparedStatementHandler and CallableStatementHandler. //SimpleStatementHandler is used to process Statement, PreparedStatementHandler handles PreparedStatement, and CallableStatementHandler is //processes CallableStatement. Mybatis creates a RoutingStatementHandler when processing Sql statements. In the RoutingStatementHandler, there is a delegate property of //StatementHandler type. The RoutingStatementHandler will create a corresponding BaseStatementHandler according to the different Statements, that is, SimpleStatementHandler, //PreparedStatementHandler or CallableStatementHandler. In the RoutingStatementHandler, all StatementHandler interface methods are implemented by the delegate corresponding to the called delegate. //We have marked the Interceptor only intercepts the prepare method of the StatementHandler interface with @Signature on the PageInterceptor class. Because Mybatis only wraps it through the Interceptor plugin method when establishing the RoutingStatementHandler, so the target object we intercept here must be the RoutingStatementHandler object. if(invocation.getTarget() instanceof RoutingStatementHandler){ RoutingStatementHandler statementHandler = (RoutingStatementHandler)invocation.getTarget(); StatementHandler delegate = (StatementHandler) ReflectHelper.getFieldValue(statementHandler, "delegate"); BoundSql boundSql = delegate.getBoundSql(); Object obj = boundSql.getParameterObject(); if (obj instanceof Page<?>) { Page<?> page = (Page<?>) obj; //Retrieve the mappedStatement property of the BaseStatementHandler of the delegate parent class MappedStatement mappedStatement = (MappedStatement)ReflectHelper.getFieldValue(delegate, "mappedStatement"); //The parameter of the intercepted prepare method is a Connection object Connection connection = (Connection)invocation.getArgs()[0]; //Get the currently executed Sql statement, that is, the Sql statement we write directly in the Mapper mapping statement String sql = boundSql.getSql(); //Set the total number of records for the current page parameter object this.setTotalRecord(page, mappedStatement, connection); //Get the paged Sql statement String pageSql = this.getPageSql(page, sql); //Use reflection to set the sql attribute corresponding to the current BoundSql to create a good paged Sql statement for us ReflectHelper.setFieldValue(boundSql, "sql", pageSql); } } return invocation.proceed(); } /** * Set the total number of records for the current parameter object page* * @param page Mapper mapping statement* @param mappedStatement Mapper mapping statement* @param connection Current database connection*/ private void setTotalRecord(Page<?> page, MappedStatement mappedStatement, Connection connection) { //Get the corresponding BoundSql. This BoundSql is actually the same object as the BoundSql we obtained using StatementHandler. //The boundSql in delegate is also obtained through the mappedStatement.getBoundSql(paramObj) method. BoundSql boundSql = mappedStatement.getBoundSql(page); //Get the corresponding Sql statement String sql = boundSql.getSql(); //Get the corresponding sql statement that calculates the total number of records by querying the Sql statement String countSql = this.getCountSql(sql); //Get the corresponding parameter map through BoundSql List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); //Use Configuration, Sql statement countSql for querying records, parameter mapping relationship parameterMappings and parameter object page to create a BoundSql object corresponding to query records. BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql, parameterMappings, page); //Create a ParameterHandler object for setting parameters through mappedStatement, parameter object page and BoundSql object countBoundSql. ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, page, countBoundSql); //Create a PreparedStatement object corresponding to countSql through connection. PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = connection.prepareStatement(countSql); //Set parameters parameterHandler for the PreparedStatement object through parameterHandler.setParameters(pstmt); //Then it is executed to obtain the total number of records and obtain the results. rs = pstmt.executeQuery(); if (rs.next()) { int totalRecord = rs.getInt(1); //Set the total number of records for the current parameter page object.setTotalRecord(totalRecord); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * Sql statement that obtains the corresponding total number of records in the query* @param sql * @return */ private String getCountSql(String sql) { int index = sql.indexOf("from"); return "select count(*) " + sql.substring(index); } /** * Get the corresponding pagination query Sql statement based on the page object. Only two database types are made here, Mysql and Oracle * No other databases are paging* * @param page paging object* @param sql Original sql statement* @return */ private String getPageSql(Page<?> page, String sql) { StringBuffer sqlBuffer = new StringBuffer(sql); if ("mysql".equalsIgnoreCase(dialect)) { return getMysqlPageSql(page, sqlBuffer); } else if ("oracle".equalsIgnoreCase(dialect)) { return getOraclePageSql(page, sqlBuffer); } return sqlBuffer.toString(); } /** * Get the paged query statement for the Mysql database* @param page paging object* @param sqlBuffer StringBuffer object containing the original sql statement* @return Mysql database paging statement */ private String getMysqlPageSql(Page<?> page, StringBuffer sqlBuffer) { // Calculate the position of the first record. The position of the record in Mysql starts from 0. // System.out.println("page:"+page.getPage()+"--------"+page.getRows()); int offset = (page.getPage() - 1) * page.getRows(); sqlBuffer.append("limit ").append(offset).append(",").append(page.getRows()); return sqlBuffer.toString(); } /** * Get the paged query statement for Oracle database* @param page paging object* @param sqlBuffer StringBuffer object containing the original sql statement* @return Pagination query statement for Oracle database */ private String getOraclePageSql(Page<?> page, StringBuffer sqlBuffer) { // Calculate the location of the first record. Oracle pagination is performed through rownum, and rownum starts from 1 int offset = (page.getPage() - 1) * page.getRows() + 1; sqlBuffer.insert(0, "select u.*, rownum r from (").append(") u where rownum < ").append(offset + page.getRows()); sqlBuffer.insert(0, "select * from (").append(") where r >= ").append(offset); //The above Sql statement looks like this: //select * from (select u.*, rownum r from (select * from t_user) u where rownum < 31) where r >= 16 return sqlBuffer.toString(); } /** * Method for encapsulating the original object corresponding to the interceptor*/ public Object plugin(Object arg0) { // TODO Auto-generated method stub if (arg0 instanceof StatementHandler) { return Plugin.wrap(arg0, this); } else { return arg0; } } /** * Set the properties set when registering the interceptor*/ public void setProperties(Properties p) { } public String getDialect() { return dialect; } public void setDialect(String dialect) { this.dialect = dialect; } public String getPageSqlId() { return pageSqlId; } public void setPageSqlId(String pageSqlId) { this.pageSqlId = pageSqlId; } } xml configuration:
<!-- MyBatis interface programming configuration --> <bean> <!-- basePackage specifies the package to be scanned. The mappers under this package will be searched. Multiple packages can be specified, separated by commas or semicolons --> <property name="basePackage" value="com.yidao.mybatis.dao" /> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" /> </bean> <!-- MyBatis pagination interceptor--> <bean id="paginationInterceptor"> <property name="dialect" value="mysql"/> <!-- Intercept the statement with id containing query characters in the Mapper.xml file --> <property name="pageSqlId" value=".*query$"/> </bean>
Page class
package com.yidao.utils;/**Look at it yourself, what fields are needed to add to it*/public class Page { private Integer rows; private Integer page = 1; private Integer totalRecord; public Integer getRows() { return rows; } public void setRows(Integer rows) { this.rows = rows; } public Integer getPage() { return page; } public void setPage(Integer page) { this.page = page; } public Integer getTotalRecord() { return totalRecord; } public void setTotalRecord(Integer totalRecord) { this.totalRecord = totalRecord; } } ReflectHelper class
package com.yidao.utils;import java.lang.reflect.Field;import org.apache.commons.lang3.reflect.FieldUtils;public class ReflectHelper { public static Object getFieldValue(Object obj, String fieldName ){ if(obj == null){ return null ; } Field targetField = getTargetField(obj.getClass(), fieldName); try { return FieldUtils.readField(targetField, obj, true ) ; } catch (IllegalAccessException e) { e.printStackTrace(); } return null ; } public static Field getTargetField(Class<?> targetClass, String fieldName) { Field field = null; try { if (targetClass == null) { return field; } if (Object.class.equals(targetClass)) { return field; } field = FieldUtils.getDeclaredField(targetClass, fieldName, true); if (field == null) { field = getTargetField(targetClass.getSuperclass(), fieldName); } } catch (Exception e) { } return field; } public static void setFieldValue(Object obj, String fieldName, Object value ){ if(null == obj){return;} Field targetField = getTargetField(obj.getClass(), fieldName); try { FieldUtils.writeField(targetField, obj, value) ; } catch (IllegalAccessException e) { e.printStackTrace(); } } }The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.