[Day 29] 遠征 Kotlin × Spring Boot 介紹多資料庫連線配置

2020-IThome鐵人賽 Comments

在實務開發上，我們有可能會遇到專案的業務需求越來越複雜，會使用的資料庫相對變得比較分散，這時就可以採用多資料來源方式取得資料，而這篇文章將介紹如何在 Spring Boot 使用多資料庫連線配置，我們一樣直接使用實作來體驗如何完成功能：

  1. 由於這篇要介紹多資料庫範例，我們選擇常見資料庫（SQL Server、MySQL）進行示範，而這邊為了實作方便，會直接利用 Docker 進行示範，大家可以在電腦內安裝 Docker 與 Docker-Compose，若朋友電腦裡面本身就有 SQL Server 與 MySQL的話，也可以直接修改為自己電腦的資料庫，不需要使用 Docker，而Docker-Compose 配置如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    version: '3'
    services:
      # MySQL 配置
      ironman_mysql:
        container_name: ironman_mysql
        image: mysql
        ports:
          - 3333:3306
        command:
          --port 3306
        environment:
          - MYSQL_ROOT_PASSWORD=root

      # SQL Server 配置
      ironman_mssql:
        container_name: ironman_mssql
        image: microsoft/mssql-server-linux:2017-latest
        ports:
          - 3334:1433
        environment:
          - ACCEPT_EULA=Y
          - SA_PASSWORD=SqlServer123!@#
          - MSSQL_PID=Developer

  2. 資料庫設定完成後，我們可以先連到資料庫建立資料表與資料，SQL 範例如下：

    • MySQL

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      CREATE DATABASE IF NOT EXISTS ironman DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
      USE ironman;
      CREATE TABLE article
      (
          id      INT NOT NULL AUTO_INCREMENT,
          title   VARCHAR(200),
          author  VARCHAR(30),
          PRIMARY KEY (id)
      );

      INSERT INTO article (title, author) VALUES ('[Day 29] 遠征 Kotlin × Spring Boot 介紹多資料庫連線配置', 'Devin');
      INSERT INTO article (title, author) VALUES ('[Day 30] 遠征 Kotlin × Spring Boot', 'Devin');

    • SQL Server

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'ironman')
      BEGIN
          CREATE DATABASE ironman
      END

      IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'userData')
      BEGIN
          CREATE TABLE userData (
              id int primary key identity (1, 1),
              name varchar(100)
          )
      END

      INSERT INTO userData (name) VALUES ('Devin')
      INSERT INTO userData (name) VALUES ('Eric')

  3. 接下來要進入實際專案開發，首先在專案中引入資料庫套件配置，這篇文章將選擇 SQL Server、MySQL作為示範，若大家需要使用其他資料庫，請記得要先設定資料庫配置，本篇資料庫配置設定如下：

    1
    2
    implementation("com.microsoft.sqlserver:mssql-jdbc")
    implementation("mysql:mysql-connector-java")

  4. 再來設定 application.yml YAML檔案，內容主要是設定要連接的兩個資料庫，命名利用 primary 與 secondary 進行區分，此命名會關係到待會設定的 Config 檔案，內容如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    spring:
      datasource:
        primary:
          url: jdbc:mysql://localhost:3333/ironman
          username: root
          password: root
          driver-class-name: com.mysql.cj.jdbc.Driver
        secondary:
          url: jdbc:sqlserver://localhost:3334
          databaseName: ironman
          username: sa
          password: SqlServer123!@#
          driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

  5. 建立兩個資料庫關聯的 Entity 與 Repository 檔案，內容如下：

    • entity / mssql / user.kt

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      @Entity
      @Table(name = "userData")
      data class User(
              @Id
              @GeneratedValue(strategy = GenerationType.IDENTITY)
              @Column(name = "id")
              var id: Int = 0,

              @Column(name = "name")
              var name: String = ""
      )

    • entity / mysql / article.kt

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      @Entity
      @Table(name = "article")
      data class Article(

              @Id
              @GeneratedValue(strategy = GenerationType.IDENTITY)
              @Column(name = "id")
              var id: Int = 0,

              @Column(name = "title")
              var title: String = "",

              @Column(name = "author")
              var author: String = ""
      )

    • repository / mssql / UserRepository

      1
      2
      @Repository
      interface UserRepository : JpaRepository<User, Int>

    • repository / mysql / ArticleRepository

      1
      2
      @Repository
      interface ArticleRepository : JpaRepository<Article, Int>

  6. 當我們建立完成與資料庫相關的 Entity 與 Repository 檔案後，就可以來設定多資料庫連線的配置檔案，內容如下：

    • PrimaryDBConfig

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      @Configuration
      @EnableJpaRepositories(
              basePackages = ["com.ironman.multipledatabase.repository.mysql"],
              entityManagerFactoryRef = "primaryDBEntityManager",
              transactionManagerRef = "primaryDBTransactionManager"
      )
      class PrimaryDBConfig {
          @Bean
          @Primary
          @ConfigurationProperties(prefix = "spring.datasource.primary")
          fun primaryDBProperties(): DataSourceProperties {
              return DataSourceProperties()
          }

          @Bean
          @Primary
          @Autowired
          fun primaryDBDataSource(
                  @Qualifier("primaryDBProperties") properties: DataSourceProperties
          ): DataSource {
              return properties.initializeDataSourceBuilder().build()
          }

          @Bean
          @Primary
          @Autowired
          fun primaryDBEntityManager(
                  builder: EntityManagerFactoryBuilder,
                  @Qualifier("primaryDBDataSource") dataSource: DataSource
          ): LocalContainerEntityManagerFactoryBean {
              return builder.dataSource(dataSource)
                      .packages("com.ironman.multipledatabase.entity.mysql")
                      .properties(mapOf("hibernate.hbm2ddl.auto" to "update"))
                      .persistenceUnit("primary")
                      .build()
          }

          @Bean
          @Primary
          @Autowired
          fun primaryDBTransactionManager(
                  @Qualifier("primaryDBEntityManager") primaryDBEntityManager: EntityManagerFactory
          ): JpaTransactionManager {
              return JpaTransactionManager(primaryDBEntityManager)
          }
      }

    • SecondaryDBConfig

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      @Configuration
      @EnableJpaRepositories(
              basePackages = ["com.ironman.multipledatabase.repository.mssql"],
              entityManagerFactoryRef = "secondaryDBEntityManager",
              transactionManagerRef = "secondaryDBTransactionManager"
      )
      class SecondaryDBConfig {
          @Bean
          @ConfigurationProperties(prefix = "spring.datasource.secondary")
          fun secondaryDBProperties(): DataSourceProperties {
              return DataSourceProperties()
          }

          @Bean
          @Autowired
          fun secondaryDBDataSource(
                  @Qualifier("secondaryDBProperties") properties: DataSourceProperties
          ): DataSource {
              return properties.initializeDataSourceBuilder().build()
          }

          @Bean
          @Autowired
          fun secondaryDBEntityManager(
                  builder: EntityManagerFactoryBuilder,
                  @Qualifier("secondaryDBDataSource") dataSource: DataSource
          ): LocalContainerEntityManagerFactoryBean {
              return builder.dataSource(dataSource)
                      .packages("com.ironman.multipledatabase.entity.mssql")
                      .properties(mapOf("hibernate.hbm2ddl.auto" to "update"))
                      .persistenceUnit("secondary")
                      .build()
          }

          @Bean
          @Autowired
          fun secondaryDBTransactionManager(
                  @Qualifier("secondaryDBEntityManager") primaryDBEntityManager: EntityManagerFactory
          ): JpaTransactionManager {
              return JpaTransactionManager(primaryDBEntityManager)
          }
      }

  7. 當我們設定完資料庫部份後，我們再利用 Controller 建立 API 取得資料庫資料，內容如下：

    • controller / MssqlUserController

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      @RestController
      @RequestMapping("/users")
      class MssqlUserController (
              val userRepository: UserRepository
      ){
          @GetMapping("/")
          @ResponseBody
          fun getAllUser(): ResponseEntity<Any>{
              return ResponseEntity.ok(userRepository.findAll())
          }
      }

    • controller / MysqlArticleController

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      @RestController
      @RequestMapping("/articles")
      class MysqlArticleController (
              val storeRepository: ArticleRepository
      ){
          @GetMapping("/")
          @ResponseBody
          fun getAllStore(): ResponseEntity<Any>{
              return ResponseEntity.ok(storeRepository.findAll())
          }
      }

  8. 執行結果如下：
    https://ithelp.ithome.com.tw/upload/images/20201008/20121179W6w4KybFG8.png
    https://ithelp.ithome.com.tw/upload/images/20201008/2012117985RMHU753h.png

此文章有提供範例程式碼在 Github 供大家參考