iT邦幫忙

第 12 屆 iT 邦幫忙鐵人賽

DAY 29
0
Modern Web

30天從零撰寫 Kotlin 語言並應用於 Spring Boot 開發系列 第 29

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

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

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

    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
    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
    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作為示範,若大家需要使用其他資料庫,請記得要先設定資料庫配置,本篇資料庫配置設定如下:

    implementation("com.microsoft.sqlserver:mssql-jdbc")
    implementation("mysql:mysql-connector-java")
    
  4. 再來設定 application.yml YAML檔案,內容主要是設定要連接的兩個資料庫,命名利用 primarysecondary 進行區分,此命名會關係到待會設定的 Config 檔案,內容如下:

    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. 建立兩個資料庫關聯的 EntityRepository 檔案,內容如下:

    • entity / mssql / user.kt

      @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

      @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

      @Repository
      interface UserRepository : JpaRepository<User, Int>
      
    • repository / mysql / ArticleRepository

      @Repository
      interface ArticleRepository : JpaRepository<Article, Int>
      
  6. 當我們建立完成與資料庫相關的 EntityRepository 檔案後,就可以來設定多資料庫連線的配置檔案,內容如下:

    • PrimaryDBConfig

      @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

      @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
    @RestController
    @RequestMapping("/users")
    class MssqlUserController (
            val userRepository: UserRepository
    ){
        @GetMapping("/")
        @ResponseBody
        fun getAllUser(): ResponseEntity<Any>{
            return ResponseEntity.ok(userRepository.findAll())
        }
    }
    
    • controller / MysqlArticleController
    @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 供大家參考


上一篇
[Day 28] 遠征 Kotlin × Spring Boot 介紹 WebSocket 實作
下一篇
[Day 30] 遠征 Kotlin × Spring Boot 完賽心得分享
系列文
30天從零撰寫 Kotlin 語言並應用於 Spring Boot 開發30

尚未有邦友留言

立即登入留言