springboot同时连接oracle和mysql数据库

1、项目目录结构

image.png

2、pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.gysoft</groupId>
    <artifactId>emqdemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>emqdemo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.dmsoft.things</groupId>
            <artifactId>things-rbac</artifactId>
            <version>2.0.8-SNAPSHOT</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- Spring Data JPA 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <!-- 数据库驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4</version>
        </dependency>

        <!-- 数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.18</version>
        </dependency>
        <!-- json工具-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.32</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

3、DataSourceConfig

package com.dmsoft.wavetide.service.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import javax.sql.DataSource;
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;

@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.location")
    DataSource dataSourceLocation() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.foreign")
    DataSource dataSourceForeign() {
        return DruidDataSourceBuilder.create().build();
    }

}

4、DataSource1Config

package com.dmsoft.wavetide.service.config;

import javax.annotation.Resource;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import java.util.HashMap;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = {"com.dmsoft.wavetide.service.repository"},
        entityManagerFactoryRef = "entityManagerFactoryLocation",
        transactionManagerRef = "transactionManagerLocation")
public class DataSource1Config {
    @Resource(name = "dataSourceLocation")
    DataSource dataSourceLocation;
 
    @Autowired
    JpaProperties jpaProperties;

    @Primary
    @Bean
    LocalContainerEntityManagerFactoryBean entityManagerFactoryLocation(
            EntityManagerFactoryBuilder builder) {

        Map<String,String> map = new HashMap<>();
        map.put("hibernate.dialect","org.hibernate.dialect.Oracle12cDialect");// 设置对应的数据库方言
        jpaProperties.setProperties(map);

        return builder.dataSource(dataSourceLocation)
                .properties(jpaProperties.getProperties())
                .packages("com.dmsoft.wavetide.service.entity")
                .persistenceUnit("pu1")
                .build();
    }

//    private Map<String, String> getVendorProperties(DataSource dataSource) {
//        Map<String,String> map = new HashMap<>();
//        map.put("hibernate.dialect",localDialect);// 设置对应的数据库方言
//        jpaProperties.setProperties(map);
//        return jpaProperties.getHibernateProperties(dataSource);
//    }


    @Bean
    PlatformTransactionManager transactionManagerLocation(
            EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean factoryOne
                = entityManagerFactoryLocation(builder);
        return new JpaTransactionManager(factoryOne.getObject());
    }
}

5、DataSource2Config

package com.dmsoft.wavetide.service.config;

import javax.annotation.Resource;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.beans.factory.support.AbstractBeanFactory;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import java.util.HashMap;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.dmsoft.wavetide.service.feirepository",
        entityManagerFactoryRef = "entityManagerFactoryForeign",
        transactionManagerRef = "transactionManagerForeign")
public class DataSource2Config {
    @Resource(name = "dataSourceForeign")
    DataSource dataSourceForeign;
 
    @Autowired
    JpaProperties jpaProperties;
 
    @Bean
    LocalContainerEntityManagerFactoryBean entityManagerFactoryForeign(
            EntityManagerFactoryBuilder builder) {
        Map<String,String> map = new HashMap<>();
        map.put("hibernate.dialect","org.hibernate.dialect.MySQL5Dialect");
        jpaProperties.setProperties(map);
        return builder.dataSource(dataSourceForeign)
                .properties(jpaProperties.getProperties())
                .packages("com.dmsoft.wavetide.service.entity")
                .persistenceUnit("pu2")
                .build();
    }
 
    @Bean
    PlatformTransactionManager transactionManagerForeign(
            EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean factoryTwo
                = entityManagerFactoryForeign(builder);
        return new JpaTransactionManager(factoryTwo.getObject());
    }
}

6 Controller

package com.dmsoft.wavetide.service.controller;

import com.dmsoft.wavetide.service.repository.impl.BookQueryDao2Impl;
import com.dmsoft.wavetide.service.entity.User;
import com.dmsoft.wavetide.service.entity.User1;
import com.dmsoft.wavetide.service.service.Book1Service;
import com.dmsoft.wavetide.service.feirepository.BookDao1;
import com.dmsoft.wavetide.service.repository.BookDao2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/api")
public class HelloController {

    @Autowired
    BookDao1 bookDao1;

    @Autowired
    BookDao2 bookDao2;

    @Autowired
    BookQueryDao2Impl bookQueryDao2;

    @Autowired
    Book1Service book1Service;

    @RequestMapping("/test")
    public void test(String asda1) {
        // 往第一个数据库中插入数据
        User book1 = new User();
        book1.setPassword("asd");
        book1.setUsername("asda");
        bookDao1.save(book1); //使用save方法将数据保存到数据库

        // 往第二个数据库中插入数据
        User1 book2 = new User1();
        book2.setPassword("asd1");
        book2.setUsername(asda1);
        bookDao2.save(book2); //使用save方法将数据保存到数据库
    }

    @GetMapping(value = "/findAll")
    public void findAll() {
        List<String> list = bookQueryDao2.findAll();
        System.out.println(list.size());

    }

    @GetMapping(value = "/findById")
    public void findById() {
        Integer id = 1;
        User1 list = bookDao2.findById(id).orElse(null);
        System.out.println(list.getUsername());

    }
}

7 entity

package com.dmsoft.wavetide.service.entity;

import javax.persistence.*;

import lombok.Data;

@Table(name = "q_user")
@Entity
@Data
public class User {

  @GeneratedValue(strategy= GenerationType.IDENTITY)
  @Id
  @Column(name = "id")
  private Integer id;

  @Column(name = "username")
  private String username;

  @Column(name = "password")
  private String password;

}


package com.dmsoft.wavetide.service.entity;

import lombok.Data;

import javax.persistence.*;

@Table(name = "q_user")
@Entity
@Data
public class User1 {
    @GeneratedValue(strategy= GenerationType.SEQUENCE)
    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "username")
    private String username;

    @Column(name = "password")
    private String password;
}

8 BookDao1

package com.dmsoft.wavetide.service.feirepository;

import com.dmsoft.wavetide.service.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

@Repository
public interface BookDao1 extends JpaRepository<User, Integer>, JpaSpecificationExecutor {
}

9 BookDao2

package com.dmsoft.wavetide.service.repository;

import com.dmsoft.wavetide.service.entity.User1;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
public interface BookDao2 extends JpaRepository<User1, Integer>, JpaSpecificationExecutor {

    Optional<User1> findById(Integer id);
}

package com.dmsoft.wavetide.service.repository;

import java.util.List;


public interface BookQueryDao2 {

    List<String> findAll();
}

package com.dmsoft.wavetide.service.repository.impl;

import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;


@Transactional(value = "transactionManagerLocation")
@Repository
public class BookQueryDao2Impl{

    @Autowired
    @PersistenceContext(unitName = "entityManagerFactoryLocation")
    private EntityManager entityManager;

    public List<String> findAll(){
        String querySql = "SELECT Q_USER.USERNAME FROM Q_USER";
        Query query = this.entityManager.createNativeQuery(querySql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List resultList = query.getResultList();
        System.out.println(resultList);

        return resultList;
    }


}

10 Book1Service

package com.dmsoft.wavetide.service.service;


import com.dmsoft.wavetide.service.entity.User;

public interface Book1Service {

    void create(User user);
}


package com.dmsoft.wavetide.service.service.impl;

import com.dmsoft.wavetide.service.service.Book1Service;
import com.dmsoft.wavetide.service.feirepository.BookDao1;
import com.dmsoft.wavetide.service.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class Book1ServiceImpl implements Book1Service {

    @Autowired
    private BookDao1 bookDao1;
    @Override
    public void create(User user) {
        bookDao1.save(user);

    }
}

11 启动类

package com.dmsoft.wavetide.service;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication(scanBasePackages = {"com"})
@RestController
public class EmqdemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(EmqdemoApplication.class, args);
    }

}

12 配置文件

debug: true

spring:
   datasource:
      location:
         type: com.alibaba.druid.pool.DruidDataSource
         driver-class-name: oracle.jdbc.driver.OracleDriver
         url: jdbc:oracle:thin:@localhost:1521:orcl
         username: admin
         password: admin
      foreign:
         type: com.alibaba.druid.pool.DruidDataSource
         driver-class-name: com.mysql.cj.jdbc.Driver
         url: jdbc:mysql://localhost:3306/blueway?serverTimezone=UTC
         username: root
         password: root


   jpa:
      show-sql: true
   mvc:
      static-path-pattern: /api/static/**
   resources:
      static-locations: classpath:/static,classpath:/public,classpath:/resources,classpath:/META-INF/resources
   jackson:
      date-format: yyyy-MM-dd HH:mm:ss
      time-zone: GMT+8
   main:
      allow-bean-definition-overriding: true