[大数据]spring boot使用双数据源mysql和oracle获取数据

spring boot使用双数据源mysql和oracle获取数据

pom.xml配置

       <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.4.0</version>
        </dependency>
       <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>5.1.42</scope>
        </dependency>
            <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.1.41</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.14</version>
        </dependency>

其他基础配置自行配置

application.properties:

spring.datasource.primary.url=jdbc:mysql://localhost:3306/sfes_yueyang?useUincode=true&characterEncoding=UTF-8&useSSL=false&zeroDateTimeBehavior=convertToNull
spring.datasource.primary.username=root
spring.datasource.primary.password=test
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.second.url=jdbc:oracle:thin:@127.0.0.1:1521:oraracb
spring.datasource.second.username=test
spring.datasource.second.password=password
spring.datasource.second.driver-class-name=oracle.jdbc.driver.OracleDriver

项目结构：

创建MasterDataSourceConfig

@Configuration
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionTemplateRef  = "masterSqlSessionTemplate")
public class MasterDataSourceConfig{
    // 主数据库对应的mapper文件夹
    static final String PACKAGE = "com.test_kafka.demo.mapper.mysql";

    @Value("${spring.datasource.primary.url}")
    private String url;

    @Value("${spring.datasource.primary.username}")
    private String user;

    @Value("${spring.datasource.primary.password}")
    private String password;

    @Value("${spring.datasource.primary.driver-class-name}")
    private String driverClass;

    @Primary
    @Bean(name = "masterDataSource")
    @ConfigurationProperties("spring.datasource.primary")
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }

    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        //  sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    @Bean(name = "masterSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sessionFactory) throws  Exception{
        return  new SqlSessionTemplate(sessionFactory);
    }
}

创建SecondDataSourceConfig

@Configuration
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionTemplateRef  = "secondSqlSessionTemplate")
public class SecondDataSourceConfig {
    static final String PACKAGE = "com.test_kafka.demo.mapper.orcle";

    @Value("${spring.datasource.second.url}")
    private String url;

    @Value("${spring.datasource.second.username}")
    private String user;

    @Value("${spring.datasource.second.password}")
    private String password;

    @Value("${spring.datasource.second.driver-class-name}")
    private String driverClass;

    @Bean(name = "secondDataSource")
    @ConfigurationProperties("spring.datasource.second")
    public DataSource masterDataSource() {
        DruidDataSource dataSource2 = new DruidDataSource();
        dataSource2.setDriverClassName(driverClass);
        dataSource2.setUrl(url);
        dataSource2.setUsername(user);
        dataSource2.setPassword(password);
        return dataSource2;
    }

    @Bean(name = "secondTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }

    @Bean(name = "secondSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("secondDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        //  sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    @Bean(name = "secondSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sessionFactory) throws  Exception{
        return  new SqlSessionTemplate(sessionFactory);
    }
}

在entity下创建数据库对应的实体类：
Tj_atmosphere_real_time_data

@Repository
//@Proxy(lazy = false)
@Entity
@Data
public class Tj_atmosphere_real_time_data {
    @Id
    private int ID;
    private String ATMOSPHERE_STATION_NUM;
    private String ATMOSPHERE_NAME;
    private String GAS_ID;
    private String GAS_NAME;
    }

在orcle包下新建接口Orcle_Mapper编写查询代码

@Mapper
@Repository
public interface Orcle_Mapper {
    //orcle查询

    @Select("select * from TJ_ATMOSPHERE_REAL_TIME_DATA WHERE ATMOSPHERE_STATION_NUM=100")
    List<Tj_atmosphere_real_time_data> daqi_look();
}

在controller下新建Orcle_dataController测试代码

@Controller
public class Orcle_dataController {
    @Autowired
    Sfes_yueyangMapper sfes_yueyangMapper;

    @Autowired
    Orcle_Mapper orcle_mapper;

    @Autowired
    Tj_atmosphere_real_time_data tj_atmosphere_real_time_data;

    @RequestMapping("/orcle_test")
    public @ResponseBody
    Map orcle_test(){
        Map<String,Object> map=new HashMap<>();
        List<Tj_atmosphere_real_time_data> daqi_datas=orcle_mapper.daqi_look();
        map.put("data",daqi_datas);
        return map;
    }
    @RequestMapping("/mysql_test")
    public @ResponseBody
    Map mysql_test(){
        Map<String,Object> map=new HashMap<>();
        List<BaseCompany> baseCompanies=sfes_yueyangMapper.company_select();
        map.put("data",baseCompanies);
        return map;
    }
}

出现的异常：java.sql.SQLException: oracle.jdbc.driver.OracleDriver

原因：造成的原因是ojdbc是收费的，不能通过idea maven直接下载。需要手动下载ojdbc14.jar，把他手动添加到maven仓库
oracle14驱动10.2.0.4.0

mvn 安装命令：

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=10.2.0.4.0 -Dpackaging=jar -Dfile=D:\ojdbc14-10.2.0.4.0.jar

成功提示：

oracle和mysql数据能正常显示出来表示查询成功