引入依赖
<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);
}
}