项目怎么同时连接mysql和oracle

同步oracle库的数据到mysql库的数据

东方巴黎～Sunsiny

10455人浏览 · 2022-08-16 16:30:26

东方巴黎～Sunsiny · 2022-08-16 16:30:26 发布

之前遇到了一个需求，我们本地的项目（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=1292.16228.1.1723
# 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
email.mail-port=25
email.mail-username=crm@tpa
email.mail-password=Tpa123456
email.mail-timeout=25000
email.mail-from=crm@tpa.cntaiping.com

spring.datasource.primary.url=jdbc:mysql://19678.14468.3.2631: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=2376e5e669-b66e45-49aa-b622ac-a2b5ac33333fb4b1
qixin.secret_key=44669222c17-1422258-4b0b-222b2a4f-11b0af84022221f
qixin.url=http://api.qixin.com/APIService

uaa.staticpwdauth=http://1044224.3044.14222441.172227:8080/uaa-server/authentication/authUser/userStaticAuthentication
uaa.userdata=http://144240.3440.188281.1744427:8080/uaa-server/query/userquery/query
uaa.userauth=http://14880.38880.118288.14244477:8080/uaa-server/groupRest/userGroup/userGroupAuth

#oracle
spring.datasource.tripdb.url=jdbc:oracle:thin:@1444240.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:@1442440.1244429.1442444.320: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”配置类：

在这里插入图片描述

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

魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

【计算机视觉】Pixel逐像素分类&Mask掩码分类理解摘要

魔乐社区

计算机视觉（opencv）实战三十二——CascadeClassifier 人脸微笑检测（摄像头）

本文从原理到实现，详细介绍了基于 OpenCV Haar 分类器的人脸与微笑检测：讲解了 Haar 特征和级联检测原理。对代码逐行拆解并解释参数含义。画出完整流程图，帮助理解执行过程。给出了常见问题和优化建议，甚至扩展到深度学习方法。这种方法简单、轻量、实时性好，非常适合入门和小型应用项目。但如果需要更高准确率和更强鲁棒性，建议使用深度学习检测器替代 Haar 分类器。