2 months ago

SpringBoot 有整合liquibase 的自動配置,使用上很方便,但是也有些使用上考量的地方,我最後還是沒有放在 SpringBoot 裡面使用,但是先記錄下來也許哪天會用。

先看一下依賴,並不需要額外的套件配置

build.gradle
dependencies {
    compile('org.springframework.boot:spring-boot-starter-data-jpa')
    compile('org.liquibase:liquibase-core')
    testCompile('org.springframework.boot:spring-boot-starter-test')
}

設定檔部分,如果 datasource 已經設定好了 liquibase 就會用你提供的來開啟資料庫,還要記的關閉 ddl-auto 以免衝突,
如果你邊測試邊寫 liquibase 的設定的話, liquibase.drop-first 打開還蠻方便的, 每次重啟就會把所有表格 Drop 是所有表格喔!不是它控管的那幾個而已喔, 這點請特別注意.

application.yml
spring:

  datasource:

    url: ${dburl}

    driver-class-name: com.mysql.jdbc.Driver #com.mysql.cj.jdbc.Driver

    username: ${dbusername}

    password: ${dbpassword}

  jpa:

    hibernate:

      ddl-auto: none


# don't use in production!!!

liquibase:

  drop-first: true

create file src\main\resources\db\changelog\db.changelog-master.yaml
你可以在設定裡面聲明包含哪些檔案

db.changelog-master.yaml
databaseChangeLog:

  - include:

      file: db/changelog/coupon.changelog.yaml

  - include:

      file: db/changelog/coupon-item.changelog.yaml

再來是表格的範例

coupon.changelog.yaml
databaseChangeLog:

  - changeSet:

      id: coupon-1

      author: samchu

      comment: "建立優惠券管理表格"

      changes:

        - createTable:

            tableName: coupon

            remarks: "優惠券管理表格"

            columns:

              - column:

                  name: couponid

                  type: bigint

                  autoIncrement: true

                  constraints:

                    primaryKey: true

                    nullable: false

                  remarks: "流水號"

              - column:

                  name: couponname

                  type: varchar(50)

                  constraints:

                    nullable: false

                  remarks: "優惠券名稱"

              - column:

                  name: denomination

                  type: decimal(8,2)

                  constraints:

                    nullable: false

                  remarks: "優惠券面值"

              - column:

                  name: dispense

                  type: int

                  constraints:

                    nullable: false

                  defaultValueNumeric: 0

                  remarks: "發放總量"

              - column:

                  name: per_person_limit

                  type: int

                  constraints:

                    nullable: false

                  defaultValueNumeric: 0

                  remarks: "每人限額"

              - column:

                  name: order_mon_condition

                  type: int

                  constraints:

                    nullable: false

                  defaultValueNumeric: 0

                  remarks: "訂單金額條件 0:無限制, 1:滿額"

              - column:

                  name: order_mon_min_limit

                  type: int

                  constraints:

                    nullable: false

                  defaultValueNumeric: 0

                  remarks: "訂單金額 滿額 最小限制"

              - column:

                  name: startdate

                  type: datetime

                  constraints:

                    nullable: false

                  remarks: "有效期"

              - column:

                  name: enddate

                  type: datetime

                  constraints:

                    nullable: false

                  remarks: "有效期"

              - column:

                  name: use_item_condition

                  type: int

                  constraints:

                    nullable: false

                  remarks: "使用方式 0:全場, 1:指定商品"

範例2

coupon-item.changelog.yaml
databaseChangeLog:

  - changeSet:

      id: coupon-item-1

      author: samchu

      comment: "建立優惠券可使用商品表格"

      changes:

        - createTable:

            tableName: coupon-item

            remarks: "優惠券可使用商品"

            columns:

              - column:

                  name: couponid

                  type: bigint

                  constraints:

                    nullable: false

                  remarks: "FK-coupon 流水號"

              - column:

                  name: itemid

                  type: bigint

                  constraints:

                    nullable: false

                  remarks: "FK-item 流水號"



        - addForeignKeyConstraint:

            baseColumnNames: couponid

            baseTableName: coupon-item

            constraintName: fk_coupon_couponid

            onDelete: NO ACTION

            onUpdate: NO ACTION

            referencedColumnNames: couponid

            referencedTableName: coupon


        - addUniqueConstraint:

            columnNames: couponid, itemid

            constraintName: uq-coupon-item

            deferrable: true

            disabled: true

            initiallyDeferred: true

            tableName: coupon-item

範例3

databaseChangeLog:

  - changeSet:

      id: pms-user-1

      author: samchu

      comment: "建立帳號管理表格"

      changes:

        - createTable:

            tableName: userinfo

            remarks: "帳號管理表格"

            columns:

              - column:

                  name: userid

                  type: bigint

                  autoIncrement: true

                  constraints:

                    primaryKey: true

                    nullable: false

                  remarks: "流水號"

              - column:

                  name: username

                  type: varchar(20)

                  constraints:

                    nullable: false

                  remarks: "姓名"

              - column:

                  name: email

                  type: varchar(50)

                  constraints:

                    nullable: false

                  remarks: "E-Mail"

        - addUniqueConstraint:

            columnNames: email

            constraintName: userinfo_uq_email

            tableName: userinfo

            tablespace: "限制唯一"

還不難上手,要注意的是寫是 yaml 時候縮排要小心
另外就是這變數 liquibase.drop-first 讓人比較擔心,如果正式跟測試裝在同環境,你就要擔心不知道從 SpringBoot 哪邊接到這變數,然後你資料庫就空了....GG


喔....還有一點 liquibase 遇到 mysql jdbc 驅動6.0.5會掛,5.1.40 就沒問題了

← SpringBoot Data Set Multiple MongoDB SpringBoot + Thymeleaf + Vue 範例 →
 
comments powered by Disqus