Mybatis插件_分页_读写分离

引入依赖

	<dependency>
		<groupId>org.mybatis</groupId>
		<artifactId>mybatis</artifactId>
		<version>3.4.6</version>
	</dependency>
	...

分页插件

（1）编写分页拦截器PageInterceptor

/**
 * 
 * Description: mybatis-分页拦截器
 * @author vander
 */
@Intercepts({ @Signature(method = "prepare", type = StatementHandler.class, args = { Connection.class,Integer.class }) })
public class PageInterceptor implements Interceptor {   
	public static final String PAGE_NUM_KEY = "_page"; //当前页 
	public static final String PAGE_SIZE_KEY = "_pageSize"; //每页条数
	static final Integer DEF_PAGE_SIZE = 10; //默认每页条数
	private String dialect = "mysql";  //默认应用数据库
	private String pattern = "PAGE";  //默认匹配规则	
	public void setDialect(String dialect) {
		this.dialect = dialect;
	}
	public void setPattern(String pattern) {
		this.pattern = pattern;
	}
	@SuppressWarnings("unchecked")
	public Object intercept(Invocation invocation) throws Throwable {
		final RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget();
		final StatementHandler delegate = (StatementHandler) ReflectUtils.getFieldValue(handler, "delegate");
		final BoundSql boundSql = delegate.getBoundSql();
		final MappedStatement mappedStatement = (MappedStatement) ReflectUtils.getFieldValue(delegate, "mappedStatement");
		final String methodName = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf("."));
		Object params = boundSql.getParameterObject();
		if (methodName.toUpperCase().contains(pattern) && !"".equals(pattern) && params instanceof Map) {
			Map<String, Object> paramObj = (Map<String, Object>) params;
			Integer pageNum = paramObj.get(PAGE_NUM_KEY) == null ? 1 : Integer.parseInt(paramObj.get(PAGE_NUM_KEY).toString());
			if (pageNum < 1) {
				pageNum = 1;
			}
			Integer pageSize = paramObj.get(PAGE_SIZE_KEY) == null ? DEF_PAGE_SIZE : Integer.parseInt(paramObj.get(PAGE_SIZE_KEY).toString());
			final String sql = boundSql.getSql();
			final String pageSql = this.getPageSql(sql, pageNum, pageSize);
			ReflectUtils.setFieldValue(boundSql, "sql", pageSql);
		}
		return invocation.proceed();
	}
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}
	public void setProperties(Properties properties) {
		this.dialect = properties.getProperty("dialect");
		this.pattern = properties.getProperty("pattern");
		if (this.pattern == null) {
			this.pattern = "page";
		}
		this.pattern = this.pattern.toUpperCase();
	}   
	private String getPageSql(String sql, Integer pageNum, Integer pageSize) {
		final StringBuilder buffer = new StringBuilder(sql);
		if ("oracle".equalsIgnoreCase(dialect)) {
			return getOraclePageSql(buffer, pageNum, pageSize);
		} else {
			return getMysqlPageSql(buffer, pageNum, pageSize);
		}
	}
	private String getOraclePageSql(StringBuilder sqlBuffer, Integer pageNum, Integer pageSize) {
		// 计算第一条记录的位置，Oracle分页是通过rownum进行的，而rownum是从1开始的
		int offset = (pageNum - 1) * pageSize + 1;
		sqlBuffer.insert(0, "select u.*, rownum r from (").append(") u where rownum < ").append(offset + pageSize);
		sqlBuffer.insert(0, "select * from (").append(") where r >= ").append(offset);
		// 上面的Sql语句拼接之后大概是这个样子：
		// select * from (select u.*, rownum r from (select * from t_user) u where rownum < 31) where r >= 16
		return sqlBuffer.toString();
	}
	private String getMysqlPageSql(StringBuilder buffer, Integer pageNum, Integer pageSize) {
		buffer.append(" limit ").append((pageNum - 1) * pageSize).append(",").append(pageSize);
		return buffer.toString();
	}
	//反射工具类
	private static class ReflectUtils {
		static Logger LOG = LoggerFactory.getLogger(ReflectUtils.class);
		public static final Object getFieldValue(Object source, String fieldName) {
			try {
				Field field = getField(source, fieldName);
				if (field != null) {
					field.setAccessible(true);
					return field.get(source);
				}
			} catch (Throwable e) {
				LOG.error(e.getMessage());
			}
			return null;
		}
		public static final void setFieldValue(Object source, String fieldName, Object value) {
			try {
				Field field = getField(source, fieldName);
				if (field != null) {
					field.setAccessible(true);
					field.set(source, value);
				}
			} catch (Throwable e) {
				LOG.error(e.getMessage());
			}
		}
		private static Field getField(Object obj, String fieldName) {
			Field field = null;
			for (Class<?> clazz = obj.getClass(); clazz != Object.class; clazz = clazz.getSuperclass()) {
				try {
					field = clazz.getDeclaredField(fieldName);
					break;
				} catch (NoSuchFieldException e) {
				}
			}
			return field;
		}
	}
}

读写分离插件

（1）创建DynamicDataSource

public class DynamicDataSource extends AbstractRoutingDataSource{
	@Override
	protected Object determineCurrentLookupKey() {
		return DynamicDataSourceHolder.getDbType();
	}
}

（2）创建存储数据源DynamicDataSourceHolder

public class DynamicDataSourceHolder{
	private static Logger logger = LoggerFactory.getLogger(DynamicDataSourceHolder.class);
	private static ThreadLocal<String> contextHolder = new ThreadLocal<>();
	public static final String DB_MASTER = "master";
	public static final String DB_SLAVE = "slave";
	public static String getDbType() {
		String db = contextHolder.get();
		if (db == null) {
			db = DB_MASTER;
		}
		return db;
	}
	public static void setDbType(String str) {
		logger.debug("use datasource："+str);
		contextHolder.set(str);
	}
	public static void clearDbType() {
		contextHolder.remove();
	}
}

（3）拦截器DynamicDataSourceInterceptor

@Intercepts({@Signature(type=Executor.class,method="update",args={MappedStatement.class,Object.class}),
	@Signature(type=Executor.class,method="query",args={MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class})})
public class DynamicDataSourceInterceptor implements Interceptor {
	private static Logger logger = LoggerFactory.getLogger(DynamicDataSourceInterceptor.class);
	private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";
	
	@Override
	public Object intercept(Invocation arg0) throws Throwable {
		boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
		Object[] objects = arg0.getArgs();
		MappedStatement mStatement = (MappedStatement) objects[0];
		String lookupKey = DynamicDataSourceHolder.DB_MASTER;
		if (synchronizationActive!=true) {
			if (mStatement.getSqlCommandType().equals(SqlCommandType.SELECT)) {
				lookupKey = DynamicDataSourceHolder.DB_MASTER;
			}else {
				BoundSql boundSql = mStatement.getSqlSource().getBoundSql(objects[1]);
				String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " ");
				if (sql.matches(REGEX)) {
					lookupKey = DynamicDataSourceHolder.DB_MASTER;
				}else {
					lookupKey = DynamicDataSourceHolder.DB_SLAVE;
				}
			}
		}else {
			lookupKey = DynamicDataSourceHolder.DB_MASTER;
		}
		logger.debug("Method [{}] use [{}] sqlCommanType [{}]",mStatement.getId(),lookupKey,mStatement.getSqlCommandType().name());
		DynamicDataSourceHolder.setDbType(lookupKey);
		return arg0.proceed();
	}
	@Override
	public Object plugin(Object arg0) {
		if (arg0 instanceof Executor) {
			return Plugin.wrap(arg0, this);
		}else {
			return arg0;
		}
	}
	@Override
	public void setProperties(Properties arg0) {
	}
}

Mybatis配置

@Configuration 
@MapperScan(basePackages = "com.plxc.mybaits.mapper")
@EnableTransactionManagement //开启注解事务
public class TestConfig {
	@Autowired
	private Environment env;	
	@Bean
	public DataSource masterDataSource() throws Exception {
		 Properties props = new Properties();
	        props.put("driverClassName", env.getProperty("master.jdbc.driverClassName"));
	        props.put("url", env.getProperty("master.jdbc.url"));
	        props.put("username", env.getProperty("master.jdbc.username"));
	        props.put("password", env.getProperty("master.jdbc.password"));
	        return DruidDataSourceFactory.createDataSource(props);
	}	
	@Bean
	public DataSource slaveDataSource() throws Exception {
		 Properties props = new Properties();
	        props.put("driverClassName", env.getProperty("slave.jdbc.driverClassName"));
	        props.put("url", env.getProperty("slave.jdbc.url"));
	        props.put("username", env.getProperty("slave.jdbc.username"));
	        props.put("password", env.getProperty("slave.jdbc.password"));
	        return DruidDataSourceFactory.createDataSource(props);
	}
	@Bean
	public DynamicDataSource targetDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
            @Qualifier("slaveDataSource") DataSource slaveDataSource) {
		DynamicDataSource dynamicDataSource = new DynamicDataSource();
		Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
		targetDataSources.put("master", masterDataSource);
		targetDataSources.put("slave", slaveDataSource);
		dynamicDataSource.setTargetDataSources(targetDataSources);
		return dynamicDataSource;
	}	
	@Bean
	public SqlSessionFactory sqlSessionFactory(@Qualifier("DynamicDataSource") DynamicDataSource dynamicDataSource) throws Exception {
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		// 设置数据源
		sqlSessionFactoryBean.setDataSource(dynamicDataSource);
		// 设置mybatis的主配置文件
		ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
		Resource[] mapperXml = resolver.getResources("classpath:mappers/*.xml");
		PageInterceptor pagePlugin = new PageInterceptor();// 分页插件
		DynamicDataSourceInterceptor dynamicDataSourceInterceptor = new DynamicDataSourceInterceptor();// 读写分离
		sqlSessionFactoryBean.setPlugins(new Interceptor[]{pagePlugin,dynamicDataSourceInterceptor});
		sqlSessionFactoryBean.setMapperLocations(mapperXml);
		return sqlSessionFactoryBean.getObject();
	}
	@Bean
	public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}