Springboot+Mybatis+Pagehelper+Aop动态配置Oracle、Mysql数据源
Springboot+Mybatis+Pagehelper+Aop动态配置Oracle、Mysql数据源
用公司新搭的maven脚手架创建springboot工程,因为脚手架功能未完善,创建出的工程主要就是引了springboot基础包并创建了目录结构,所以需要自己添加框架来搭建工程,也能通过这个过程来更深入了解相关框架,提升自己。
* springboot程序入口:TianlianModelServerApplication.java
package com.tianlian.server;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.context.annotation.ImportResource;
import org.springframework.context.annotation.PropertySource;
//因为用springboot默认的数据源只能配置一套,而我们需要从多个数据源来查询数据,
//因此用@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})来关闭自动配置功能。
@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
//用@PropertySource来加载properties文件
@PropertySource(value={"classpath:dev/properties/dubbo.properties","classpath:dev/properties/mysql.properties","classpath:dev/properties/oracle.properties"})
//加载xml配置文件
@ImportResource(locations={"classpath:configs/dubbo-customers.xml","classpath:configs/dubbo-server.xml"})
@ComponentScan(basePackages = "com.tianlian.server")
//开启AOP代理自动配置
@EnableAspectJAutoProxy
public class TianlianModelServerApplication {
public static void main(String[] args) {
SpringApplication.run(TianlianModelServerApplication.class, args);
}
}
- 读取mysql配置:
MySqlDataSourceConfig.java
(OracleDataSourceConfig.java类似)
package com.tianlian.server.configs;
import lombok.Data;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Component;
/**
*用@Value注解来给属性赋值,分别创建oracle数据源的bean、mysql数据源的bean,
*并用@componen把bean交给spring管理,方便后面创建不同数据源时使用。
*/
@Data
@Component
public class MySqlDataSourceConfig {
@Value("${mysql.jdbc.driverClassName}")
private String driverClassName;
@Value("${mysql.jdbc.url}")
private String url;
@Value("${mysql.jdbc.username}")
private String username;
@Value("${mysql.jdbc.password}")
private String password;
@Value("${mysql.jdbc.initialSize}")
private Integer initialSize;
@Value("${mysql.jdbc.maxActive}")
private Integer maxActive;
@Value("${mysql.jdbc.minPoolSize}")
private Integer minPoolSize;
@Value("${mysql.jdbc.maxWait}")
private Long maxWait;
@Value("${mysql.jdbc.minIdle}")
private Integer minIdle;
@Value("${mysql.jdbc.timeBetweenEvictionRunsMillis}")
private Long timeBetweenEvictionRunsMillis;
@Value("${mysql.jdbc.minEvictableIdleTimeMillis}")
private Long minEvictableIdleTimeMillis;
@Value("${mysql.jdbc.validationQuery}")
private String validationQuery;
@Value("${mysql.jdbc.testWhileIdle}")
private Boolean testWhileIdle;
@Value("${mysql.jdbc.testOnBorrow}")
private Boolean testOnBorrow;
@Value("${mysql.jdbc.testOnReturn}")
private Boolean testOnReturn;
@Value("${mysql.jdbc.maxOpenPreparedStatements}")
private Integer maxOpenPreparedStatements;
@Value("${mysql.jdbc.removeAbandoned}")
private Boolean removeAbandoned;
@Value("${mysql.jdbc.removeAbandonedTimeout}")
private Integer removeAbandonedTimeout;
@Value("${mysql.jdbc.logAbandoned}")
private Boolean logAbandoned;
@Value("${mysql.jdbc.poolPreparedStatements}")
private Boolean poolPreparedStatements;
@Value("${mysql.jdbc.filters}")
private String filters;
}
- 配置mybatis:
MyBatisConfigNew.java
package com.tianlian.server.configs;
import com.alibaba.druid.pool.DruidDataSource;
import com.github.pagehelper.PageHelper;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
@Configuration
@MapperScan(basePackages = "com.tianlian.server.dao.mapper")
public class MyBatisConfigNew {
@Autowired
private MySqlDataSourceConfig mysqlJdbcMapper;
@Autowired
private OracleDataSourceConfig oracleJdbcMapper;
/**
*根据oracle和mysql的配置属性bean分别创建oracle数据源bean、mysql数据源bean
*/
@Bean
public DataSource mysqlDataSource() throws Exception {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(mysqlJdbcMapper.getUrl());
datasource.setUsername(mysqlJdbcMapper.getUsername());
datasource.setPassword(mysqlJdbcMapper.getPassword());
datasource.setDriverClassName(mysqlJdbcMapper.getDriverClassName());
datasource.setInitialSize(mysqlJdbcMapper.getInitialSize());
datasource.setMinIdle(mysqlJdbcMapper.getMinIdle());
datasource.setMaxActive(mysqlJdbcMapper.getMaxActive());
datasource.setMaxWait(mysqlJdbcMapper.getMaxWait());
datasource.setTimeBetweenEvictionRunsMillis(mysqlJdbcMapper.getTimeBetweenEvictionRunsMillis());
datasource.setMinEvictableIdleTimeMillis(mysqlJdbcMapper.getMinEvictableIdleTimeMillis());
datasource.setValidationQuery(mysqlJdbcMapper.getValidationQuery());
datasource.setTestWhileIdle(mysqlJdbcMapper.getTestWhileIdle());
datasource.setTestOnBorrow(mysqlJdbcMapper.getTestOnBorrow());
datasource.setTestOnReturn(mysqlJdbcMapper.getTestOnReturn());
datasource.setPoolPreparedStatements(mysqlJdbcMapper.getPoolPreparedStatements());
return datasource;
}
@Bean
public DataSource oracleDataSource() throws Exception {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(oracleJdbcMapper.getUrl());
datasource.setUsername(oracleJdbcMapper.getUsername());
datasource.setPassword(oracleJdbcMapper.getPassword());
datasource.setDriverClassName(oracleJdbcMapper.getDriverClassName());
datasource.setInitialSize(oracleJdbcMapper.getInitialSize());
datasource.setMinIdle(oracleJdbcMapper.getMinIdle());
datasource.setMaxActive(oracleJdbcMapper.getMaxActive());
datasource.setMaxWait(oracleJdbcMapper.getMaxWait());
datasource
.setTimeBetweenEvictionRunsMillis(oracleJdbcMapper.getTimeBetweenEvictionRunsMillis());
datasource.setMinEvictableIdleTimeMillis(oracleJdbcMapper.getMinEvictableIdleTimeMillis());
datasource.setValidationQuery(oracleJdbcMapper.getValidationQuery());
datasource.setTestWhileIdle(oracleJdbcMapper.getTestWhileIdle());
datasource.setTestOnBorrow(oracleJdbcMapper.getTestOnBorrow());
datasource.setTestOnReturn(oracleJdbcMapper.getTestOnReturn());
datasource.setPoolPreparedStatements(oracleJdbcMapper.getPoolPreparedStatements());
return datasource;
}
/**
* 创建动态数据源,将上面创建的数据源交给动态数据源管理(即放到抽象类AbstractRoutingDataSource中的targetDataSources管理)
*/
@Bean
@Primary
public DynamicDataSource dataSource(
@Qualifier("mysqlDataSource") DataSource mysqlDataSource,
@Qualifier("oracleDataSource") DataSource oracleDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DatabaseType.mysqlDb, mysqlDataSource);
targetDataSources.put(DatabaseType.oracleDb, oracleDataSource);
DynamicDataSource dataSource = new DynamicDataSource();
// 该方法是AbstractRoutingDataSource的方法
dataSource.setTargetDataSources(targetDataSources);
// 默认的datasource设置为myTestDbDataSource
dataSource.setDefaultTargetDataSource(mysqlDataSource);
return dataSource;
}
/**
*将动态数据源交给mybatis的SqlSessionFactoryBean,并添加PageHelper分页插件。
*因为要切换数据源,必须要把PageHelper的autoRuntimeDialect属性设置为true才能在不同类新的数据源切换时,
*使用不同数据源的分页方式。
*/
@Bean
public SqlSessionFactory sqlSessionFactory(
@Qualifier("mysqlDataSource") DataSource mysqlDataSource,
@Qualifier("oracleDataSource") DataSource oracleDataSource) throws Exception {
SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
fb.setDataSource(this.dataSource(mysqlDataSource, oracleDataSource));
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
//分页插件
PageInterceptor pageHelper = new
PageInterceptor
();
Properties properties = new Properties();
properties.setProperty("reasonable", "true");
properties.setProperty("supportMethodsArguments", "true");
properties.setProperty("returnPageInfo", "check");
properties.setProperty("params", "count=countSql");
properties.setProperty("autoRuntimeDialect", "true");
pageHelper.setProperties(properties);
//添加插件
fb.setPlugins(new Interceptor[]{pageHelper});
//添加XML目录
try {
fb.setMapperLocations(resolver.getResources("classpath:mapper/**/*.xml"));
return fb.getObject()