目录
1.1 、采用config配置模式,指定数据库操作层的路径实现多数据源
一. 前言
采用多数据源主要原因是因为最近需要对老项目进行升级改造,特别是数据库方面,老数据库性能跟不上,需要更换性能更好的数据库,然后需要新增数据源,这里采用两种模式。
二. 数据准备
2个数据库,分别命名 dbmysql、dbrich
数据源都新建表
Create Table |
CREATE TABLE `studentinfo` ( `stuId` int(4) NOT NULL DEFAULT '1001', `name` varchar(50) NOT NULL DEFAULT '', `age` int(4) NOT NULL DEFAULT '10', `sex` varchar(10) NOT NULL DEFAULT '男', `stuClass` int(11) NOT NULL DEFAULT '1', PRIMARY KEY (`stuId`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='学生表' |
实体类
@Data public class StudentBo { private int stuId; private String name; private int age; private String sex; private int stuClass; }
三、两种模式连接多数据源
1.1 、采用config配置模式,指定数据库操作层的路径实现多数据源
yml 添加多数据源,dbmysql 和 dbrich
spring:
datasource:
dbmysql:
url: jdbc:mysql://XXXX
username: XXX
password: XXX
typ: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
filters: stat
maxActive: 2
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
dbrich:
url: jdbc:mysql://XXXX
username: XXXX
password: XXXX
typ: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
filters: stat
maxActive: 2
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
1.2、新增两个config配置
数据源 dbmysql配置
@Configuration @MapperScan(basePackages = {"com.example.demo.**.mapper"}, sqlSessionFactoryRef = "sqlSessionFactorydbmysql",sqlSessionTemplateRef = "sqlSessionTemplatedbmysql") public class DatasourcedbmysqlConfiguration { @Value("${mybatis.mapper-locations}") private String mapperLocation; @Value("${spring.datasource.dbmysql.url}") private String jdbcUrl; @Value("${spring.datasource.dbmysql.driver-class-name}") private String driverClassName; @Value("${spring.datasource.dbmysql.username}") private String username; @Value("${spring.datasource.dbmysql.password}") private String password; @Value("${spring.datasource.dbmysql.initialSize}") private int initialSize; @Value("${spring.datasource.dbmysql.minIdle}") private int minIdle; @Value("${spring.datasource.dbmysql.maxActive}") private int maxActive; @Bean(name = "dbmysql") @Primary public DataSource dataSource() { DruidDataSource dataSource = new DruidDataSource(); dataSource.setUrl(jdbcUrl); dataSource.setDriverClassName(driverClassName); dataSource.setUsername(username); dataSource.setPassword(password); dataSource.setInitialSize(initialSize); dataSource.setMinIdle(minIdle); dataSource.setMaxActive(maxActive); return dataSource; } @Bean("sqlSessionFactorydbmysql") public SqlSessionFactory sqlSessionFactory(@Qualifier("dbmysql") DataSource dataSource) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setMapperLocations( new PathMatchingResourcePatternResolver().getResources(mapperLocation)); return sqlSessionFactoryBean.getObject(); } @Bean("sqlSessionTemplatedbmysql") public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactorydbmysql") SqlSessionFactory sqlSessionFactory) { return new SqlSessionTemplate(sqlSessionFactory); } @Bean("transactionManagerdbmysql") public DataSourceTransactionManager transactionManager(@Qualifier("dbmysql")DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } }
数据源 dbrich 配置
@Configuration @MapperScan(basePackages = {"com.example.demo.**.dao"}, sqlSessionFactoryRef = "sqlSessionFactorydbrich") public class DatasourcedbadbConfiguration { @Value("${mybatis.mapper-locations}") private String mapperLocation; @Value("${spring.datasource.dbrich.url}") private String jdbcUrl; @Value("${spring.datasource.dbrich.driver-class-name}") private String driverClassName; @Value("${spring.datasource.dbrich.username}") private String username; @Value("${spring.datasource.dbrich.password}") private String password; @Value("${spring.datasource.dbrich.initialSize}") private int initialSize; @Value("${spring.datasource.dbrich.minIdle}") private int minIdle; @Value("${spring.datasource.dbrich.maxActive}") private int maxActive; @Bean(name = "dbrich") public DataSource dataSource() { DruidDataSource dataSource = new DruidDataSource(); dataSource.setUrl(jdbcUrl); dataSource.setDriverClassName(driverClassName); dataSource.setUsername(username); dataSource.setPassword(password); dataSource.setInitialSize(initialSize); dataSource.setMinIdle(minIdle); dataSource.setMaxActive(maxActive); return dataSource; } @Bean("sqlSessionFactorydbrich") public SqlSessionFactory sqlSessionFactory(@Qualifier("dbrich") DataSource dataSource) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); sqlSessionFactoryBean.setMapperLocations( new PathMatchingResourcePatternResolver().getResources(mapperLocation)); return sqlSessionFactoryBean.getObject(); } @Bean("sqlSessionTemplatedbrich") public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactorydbrich") SqlSessionFactory sqlSessionFactory) { return new SqlSessionTemplate(sqlSessionFactory); } @Bean("transactionManagerdbrich") public DataSourceTransactionManager transactionManager(@Qualifier("dbrich")DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } }
其中数据源 dbmysql 加上了 @Primary ,代表是默认使用的数据源
@MapperScan(basePackages = {"com.example.demo.**.mapper"}
这个注解是指定对应的数据源操作层的路径 ,dbmysql 指定的是mapper 包下的,dbrich 指定的是 dao 包下的
1.3 逻辑层
新建 dao 和mapper 两个包 ,
public interface StudentinfoDao { List<StudentBo> selectStudentdbrich(); }
public interface StudentMapper { List<StudentBo> selectStudent(); }
@Service public class StudentService { @Resource private StudentinfoDao studentinfoDao; @Resource private StudentMapper studentMapper; public List<StudentBo> selectStudentdbrich() { return studentinfoDao.selectStudentdbrich(); } public List<StudentBo> selectStudent() { return studentMapper.selectStudent(); } }
@RestController @RequestMapping("/studentinfoController") public class StudentinfoController { @Autowired private StudentService studentService ; @RequestMapping("/selectStudent") public List<StudentBo> selectStudent(){ return studentService.selectStudent(); } @RequestMapping("/selectStudentdbrich") public List<StudentBo> selectStudentdbrich(){ return studentService.selectStudentdbrich(); } }
@SpringBootApplication @Import({DatasourcedbadbConfiguration.class, DatasourcedbmysqlConfiguration.class}) public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } }
注意:启动类只需要将两个配置类加上不需要添加注解@MapperScan
1.4、启动查看
数据源dbmysql的方法
数据源dbadb的方法
到这里就实现了分包多数据源整合了,下面换另一种方式。
2、采用dynamic注解的形式添加多数据源
2.1 pom.xml 新增dynamic 引用
<dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.6</version> </dependency>
2.2 application.yml 新增多数据源
# 多数据源 spring: datasource: dynamic: primary: dbmysql #设置默认的数据源或者数据源组,默认值即为master strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候会抛出异常,不启动则使用默认数据源. datasource: # 主库数据源 dbmysql: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.jdbc.Driver #本地 url: jdbc:mysql://XXXX username: XXXX password: XXXX # 从库数据源 dbadb: # 从数据源开关/默认关闭 enabled: true type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://XXXX username: XXXXX password: XXXXX # 初始连接数 initialSize: 5 # 最小连接池数量 minIdle: 10 # 最大连接池数量 maxActive: 20 # 配置获取连接等待超时的时间 maxWait: 60000 # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 timeBetweenEvictionRunsMillis: 60000 # 配置一个连接在池中最小生存的时间,单位是毫秒 minEvictableIdleTimeMillis: 300000 # 配置一个连接在池中最大生存的时间,单位是毫秒 maxEvictableIdleTimeMillis: 900000 # 配置检测连接是否有效 #SELECT 1 FROM DUAL validationQuery: select 'x' testWhileIdle: true testOnBorrow: false testOnReturn: false webStatFilter: enabled: true statViewServlet: enabled: true # 设置白名单,不填则允许所有访问 allow: url-pattern: #/druid/* # 控制台管理用户名和密码 login-username: login-password: filter: stat: enabled: true # 慢SQL记录 log-slow-sql: true slow-sql-millis: 1000 merge-sql: true wall: config: multi-statement-allow: true
2.3 逻辑层
因为是注解的方式,所以启动类需要指定dao层的路径 @SpringBootApplication @MapperScan(basePackages = {"com.example.demo.**.dao", "com.example.demo.**.mapper"}) public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } }
controller、service都一样,不同的是dao
@DS("dbadb") public interface StudentinfoDao { List<StudentBo> selectStudentdbrich(); }
@DS("admysql") public interface StudentMapper { List<StudentBo> selectStudent(); }
这里只需要直接在dao层加上注解DS 配置不同的数据源就可以
默认的数据源可以不需要加注解
2.4启动查看
dbmysql
dbadb
到这里就结束了,可以看出来引用dynamic 注解的方式来加入多数据源会方便很多,不需要额外加配置。
如果觉得对你有帮助的话欢迎点赞关注哦!