[大数据]项目怎么同时连接mysql和oracle

之前遇到了一个需求，我们本地的项目（SpringBoot）用的是mysql,application.properties文件中也只有mysql的配置；
但现在有个需求，我们需要每周跑一下客户给的oracle数据库的用户表信息：
怎么做呢？总结如下：

server.port=8021
server.session-timeout=60
spring.application.name=picc-server
spring.http.encoding.force=true
spring.http.encoding.charset=UTF-8
spring.http.encoding.enabled=true
server.tomcat.uri-encoding=UTF-8

logging.level.org.apache.http=info

spring.servlet.multipart.max-file-size=50MB
spring.servlet.multipart.max-request-size=50MB


# Redis���?�������?��?0��
spring.redis.database=4
# Redis��������?velocity
spring.redis.host=127.0.0.1
#spring.redis.host=192.168.1.17
# Redis���������??�
#spring.redis.port=6380
spring.redis.port=6379
# Redis�������������??��?�?�
spring.redis.password=
# ���?������������?�?�?��??�����?�
spring.redis.pool.max-active=8
# ���?���������?�?�??�?�?��??�����?�
spring.redis.pool.max-wait=-1
# ���?������������
spring.redis.pool.max-idle=8
# ���?������������
spring.redis.pool.min-idle=0
# ���?�??�?���?
spring.redis.timeout=6000

email.mail-host=smtp.cntaiping.com
email.mail-port=25
email.mail-username=crm@tpa.cntaiping.com
email.mail-password=Tpa123456
email.mail-timeout=25000
email.mail-from=crm@tpa.cntaiping.com

spring.datasource.primary.url=jdbc:mysql://19678.14468.3.231:3306/picc_dev?useOldAliasMetadataBehavior=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.primary.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.primary.dbcp2.initial-size=10
spring.datasource.primary.dbcp2.min-idle=10
spring.datasource.primary.dbcp2.max-total=20
spring.datasource.primary.dbcp2.max-conn-lifetime-millis=60000

spring.datasource.jndi-name.masterDB=java:comp/env/picccrm,java:comp/env/piccread

jndi.name=picccrm
jndi.driverClassName=com.mysql.cj.jdbc.Driver
jndi.url=jdbc:mysql://14492.16844.3.2314:3306/picc_dev?useOldAliasMetadataBehavior=true&serverTimezone=Asia/Shanghai
jndi.username=root
jndi.password=root
jndi.factory=com.alibaba.druid.pool.DruidDataSourceFactory
jndi.maxActive=50
jndi.filters=stat

read.name=piccread
read.driverClassName=com.mysql.cj.jdbc.Driver
read.url=jdbc:mysql://192.168444.443.23144:3306/picc_dev?useOldAliasMetadataBehavior=true&serverTimezone=Asia/Shanghai
read.username=root
read.password=root
read.factory=com.alibaba.druid.pool.DruidDataSourceFactory
read.maxActive=50
read.filters=stat

es.host=192.168.3.231
es.port=9200
es.connectTimeout=50000
es.socketTimeout=60000

db.name = picc_dev
db.user = root
db.password = root
db.driver = com.mysql.cj.jdbc.Driver
db.url = jdbc:mysql://194442.168444.3444.231:3306/picc_dev?useOldAliasMetadataBehavior=true&serverTimezone=Asia/Shanghai
db.usePool = true
db.validateSQL = select 1 from dual
db.jdbcFetchSize = 10000
db.showsql = true

#启信宝接口链接
qixin.appkey=2376e5e9-be45-49aa-b6ac-a2b5ac3fb4b1
qixin.secret_key=44669c17-1458-4b0b-ba4f-11b0af840f1f
qixin.url=http://api.qixin.com/APIService

uaa.staticpwdauth=http://10444.3044.14441.177:8080/uaa-server/authentication/authUser/userStaticAuthentication
uaa.userdata=http://14440.3440.18881.174447:8080/uaa-server/query/userquery/query
uaa.userauth=http://14880.38880.11888.1444477:8080/uaa-server/groupRest/userGroup/userGroupAuth

#oracle
spring.datasource.tripdb.url=jdbc:oracle:thin:@144440.124449.144444.30:1521/sirm
spring.datasource.tripdb.username=ai44m3444
spring.datasource.tripdb.password=ai44m344444
spring.datasource.tripdb.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.tripdb.type=com.alibaba.druid.pool.DruidDataSource
#初始化时建立物理连接的个数
spring.datasource.tripdb.dbcp2.initial-size=5
#最小连接池数量
spring.datasource.tripdb.dbcp2.min-idle=5
#最大连接池数量
spring.datasource.tripdb.dbcp2.max-total=20
#获取连接时最大等待时间
spring.datasource.tripdb.dbcp2.max-wait-millis=60000

我这oracle文件配置的内容这一段摘出来：

spring.datasource.tripdb.url=jdbc:oracle:thin:@144440.124449.144444.30:1521/sirm
spring.datasource.tripdb.username=ai44m3444
spring.datasource.tripdb.password=ai44m344444
spring.datasource.tripdb.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.tripdb.type=com.alibaba.druid.pool.DruidDataSource
#初始化时建立物理连接的个数
spring.datasource.tripdb.dbcp2.initial-size=5
#最小连接池数量
spring.datasource.tripdb.dbcp2.min-idle=5
#最大连接池数量
spring.datasource.tripdb.dbcp2.max-total=20
#获取连接时最大等待时间
spring.datasource.tripdb.dbcp2.max-wait-millis=60000

文件配置好了，接下来需要自定义一个oracle相关的自定义Bean

package com.sinitek.common.crm.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * @Author: py.sun
 * @Date: 2022/1/19 18:34
 */

@Configuration
@MapperScan(basePackages = "com.sinitek.common.crm.contact.syncdata.mapper", sqlSessionFactoryRef = "activitySqlSessionFactory")
@MapperScan(basePackages = "com.sinitek.common.crm.account.mapper", sqlSessionFactoryRef = "activitySqlSessionFactory")
public class DruidOracleConfig {


    @Bean(name = "tripdbDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.tripdb")
    public DataSource primaryDataSource() {
        return new DruidDataSource();
    }


    @Bean(name = "tripdbTransactionManager")
    public DataSourceTransactionManager primaryTransactionManager() throws SQLException {
        return new DataSourceTransactionManager(primaryDataSource());
    }


    @Bean(name = "activitySqlSessionFactory")
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("tripdbDataSource") DataSource primaryDataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(primaryDataSource);

        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setCacheEnabled(true);
        configuration.setLazyLoadingEnabled(true);
        configuration.setMultipleResultSetsEnabled(true);
        configuration.setUseColumnLabel(true);
        configuration.setUseGeneratedKeys(true);
        configuration.setDefaultExecutorType(ExecutorType.SIMPLE);
        configuration.setDefaultStatementTimeout(25000);
        configuration.setCallSettersOnNulls(true);
        return sessionFactory.getObject();
    }

}

下面这段代码，是你扫描的mapper接口：

@MapperScan(basePackages = “com.sinitek.common.crm.contact.syncdata.mapper”, sqlSessionFactoryRef = “activitySqlSessionFactory”)
@MapperScan(basePackages = “com.sinitek.common.crm.account.mapper”, sqlSessionFactoryRef = “activitySqlSessionFactory”)

相关Mapper接口我也给大家摘出来，给大家看看：

package com.sinitek.common.crm.account.mapper;

import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;


@Mapper
public interface AccountOracleMapper {

    /**
     * 获取oracle数据库的客户信息
     */
    @Select("select a.OBJID,a.CREATETIMESTAMP,a.COMPANYCODE,(select d.name from SDC_DICT_AREA d where a.ADDRESS=d.CODE) as ADDRESS,(select d.name from SDC_DICT_AREA d where a.AREA=d.CODE) as AREA,a.BUSINESSLICENSE,a.BUSINESSSCOPE,a.ENTERPRISETYPE," +
            "a.FINHIERARCHY,a.GROUPCUSTOMER,a.ISFINPLATFORM,a.ISQUOTEDCOMPANY,a.LEGALPERSON,a.NAME,a.NETWORTH,a.ORGCODE,a.OUTCODE,a.PURCHASETYPE,a.REGISTRATIONADDRESS,a.REGISTRATIONCAPTIAL,a.REGISTRATIONDATE,a.SHORTNAME,a.TOTALASSETS,a.ZIPCODE," +
            "b.INSTVALIDITYDATE,b.REPRESENTATIVENUMBER,b.REPRESENTATIVETYPE,b.VALIDITYDATE,c.INDUSTRYCODE from (PM_INSTITUTION a left join PM_INSTITUTIONEXT b on a.objid=b.INSTITUTIONID) left join PM_INSTINDUSTRY c on a.OBJID=c.INSTID")
    List<Map<String,Object>> listAccount();

}

List<Map<String,Object>> listAccount();
当我们调用方法后，我们其实就能读取到oracle的方法了

再来继续看看“DruidOracleConfig”配置类：

到这儿已经给大家介绍完毕，有什么不懂的、有疑问的、有错的的请大家评论区留言！！！！！！！！