9 months ago
SpringBoot 有整合liquibase 的自動配置，使用上很方便，但是也有些使用上考量的地方，我最後還是沒有放在 SpringBoot 裡面使用，但是先記錄下來也許哪天會用。
先看一下依賴，並不需要額外的套件配置
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 是所有表格喔!不是它控管的那幾個而已喔, 這點請特別注意.
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
你可以在設定裡面聲明包含哪些檔案
databaseChangeLog:
- include:
file: db/changelog/coupon.changelog.yaml
- include:
file: db/changelog/coupon-item.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
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 就沒問題了
