一.多数据源的典型使用场景
在实际开发中,经常可能遇到在一个应用中可能要访问多个数据库多的情况,以下是两种典型场景
1.业务复杂
数据分布在不同的数据库中,数据库拆了,应用没拆.一个公司多个子项目,各用各的数据库,设计数据共享
2.读写分离
为了解决数据库的性能瓶颈(读比写性能更高,写锁会影响读阻塞,从而影响读的性能)
很多数据库主从架构,也就是,一台主数据库服务器,是对外提供增删改多的生产服务器;另一台从数据库服务器,主要进行读的操作,可以通过中间件(ShardingSphere .mycat .mysql-proxxy .TDDL …),但是有一些规模较小的公司,没有专门的中间件团队搭建读写分离基础设施,因此需要业务开发人员自行实现读写分离.
二.如何实现多数据源
1、修改application.yml,添加一个数据库连接配置
#配置视图解析器
spring:
mvc:
view:
prefix: /
suffix: .jsp
#配置html
thymeleaf:
mode: LEGACYHTML5
prefix: classpath:/templates/
suffix: .html
encoding: UTF-8
#配置连接数据库
datasource:
beltcon:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc-url: jdbc:sqlserver://localhost:1433;DatabaseName=BeltconveyorMonitor
username: sa
password: 123456
kjdbf:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc-url: jdbc:sqlserver://localhost:1433;DatabaseName=KJ968DBF
username: sa
password: 123
特别注意:
(1)使用beltcon、kjdbf区分两个数据库连接
(2)url改为:jdbc-url
2、使用代码进行数据源注入,和扫描dao层路径(以前是在yml文件里配置mybatis扫描dao的路径)
新建config包,包含数据库1和数据库2的配置文件
(1)第一个数据库作为主数据库,项目启动默认连接此数据库
DataSource1Config.java
package com.example.springboot.config;
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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;
import javax.sql.DataSource;
//表示这是一个配置类
@Configuration
@MapperScan(basePackages = "com.example.springboot.Dao.pidai", sqlSessionTemplateRef = "beltconSqlSessionTemplate")
public class DataSource1Config {
@Bean(name = "beltconDataSource")
@ConfigurationProperties(prefix = "spring.datasource.beltcon")
public DataSource beltconDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "beltconSqlSessionFactory")
public SqlSessionFactory beltconSqlSessionFactory(@Qualifier("beltconDataSource") DataSource beltconDataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
//设置数据源
sqlSessionFactoryBean.setDataSource(beltconDataSource);
org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration();
config.setMapUnderscoreToCamelCase(true);
sqlSessionFactoryBean.setConfiguration(config);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:Dao/*.xml"));
return sqlSessionFactoryBean.getObject();
}
@Bean(name = "beltconSqlSessionTemplate")
public SqlSessionTemplate beltconSqlSessionTemplate(@Qualifier("beltconSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
特别注意:
(1)主数据库DataSource1Config都有 @Primary注解,从数据库DataSource2Config都没有
(2)第二个数据库DataSource2Config作为从数据库
DataSource2Config.java
package com.example.springboot.config;
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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.example.springboot.Dao.dingwei", sqlSessionTemplateRef = "kjdbfSqlSessionTemplate")
public class DataSource2Config {
@Bean(name = "kjdbfDataSource")
@ConfigurationProperties(prefix = "spring.datasource.kjdbf")
public DataSource kjdbfDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "kjdbfSqlSessionFactory")
public SqlSessionFactory beltconSqlSessionFactory(@Qualifier("kjdbfDataSource") DataSource kjdbfDataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
//设置数据源
sqlSessionFactoryBean.setDataSource(kjdbfDataSource);
org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration();
config.setMapUnderscoreToCamelCase(true);
sqlSessionFactoryBean.setConfiguration(config);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:Dao/dingwei/*.xml"));
return sqlSessionFactoryBean.getObject();
}
@Bean(name = "kjdbfSqlSessionTemplate")
public SqlSessionTemplate kjdbfSqlSessionTemplate(@Qualifier("kjdbfSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
三.测试
3、 在dao文件夹下,新建test1和test2两个包,分别放两个不同数据库的dao层文件
(1)IUserDao .java
@Repository /*解决SpringBoot中@Autowired爆红*/
@Mapper
public interface IUserDao {
@Select("select * from CDWInfo")
List<CDWInfo> findAll();
}
(2)IRollerDao .java
@Component
@Repository /*解决SpringBoot中@Autowired爆红*/
@Mapper
public interface IRollerDao {
/*托辊故障检测*/
@Select("select * from Vw_yi")
List<Vw_yi> findAll();
}
4、 在resource下新建test1和test2两个文件夹,分别放入对应dao层的xml文件
(我原来项目的dao的xml文件在resource目录下,你们在自己的项目对应目录下即可)
注意dao的java文件和dao的xml文件名字要一致
(1)IUserDao .xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.springboot.Dao.dingwei">
</mapper>
(2)IRollerDao .xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.springboot.Dao.dingwei">
</mapper>
5、测试
在controller文件里,注入两个数据库的dao,分别查询数据
@RestController
public class TestController{
@Autowired
private IUserService iUserService;
@Autowired
private IRollerService iRollerService;
@RequestMapping("/demo")
public ModelAndView say2(){
ModelAndView mv = new ModelAndView();
//人员定位的
List<CDWInfo> all = iUserService.findAll();
System.out.println(all);
mv.addObject("message","hello world");//将数据放到message中
mv.setViewName("demo");//调到 say页面
return mv;
} }
@RequestMapping("/say2")
public ModelAndView say(){
ModelAndView mv = new ModelAndView();
mv.addObject("message","hello world");//将数据放到message中
List<Vw_ro> roAll = iRollerService.findROAll();
System.out.println(roAll);
mv.setViewName("say02");//调到 say页面
return mv;
}
}