Mybatis插件_分页_读写分离

待我称王封你为后i 2021-10-06 03:08 290阅读 0赞

### Mybatis插件\_分页\_读写分离 ###

*  引入依赖
 *  分页插件
 *  读写分离插件
 *  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);
    	}
    }