環(huán)境搭建
1.基本環(huán)境
Java、Maven、數(shù)據(jù)庫
2.初始化控制表
創(chuàng)建CHANGELOG表
create table CHANGELOG
(
CHANGE_NUMBER NUMBER(22) not null,
COMPLETE_DT TIMESTAMP(6) not null,
APPLIED_BY VARCHAR2(100) not null,
DESCRIPTION VARCHAR2(500) not null
)
/
alter table CHANGELOG add constraint PK_CHANGELOG primary key (CHANGE_NUMBER)
/
3.sql文件創(chuàng)建腳本(與src平級)
dbdeploy1.png
createNewDDLChangeFile.bat
mvn dbdeploy:change-script -Ddbchangefile.name=DDL
createNewDMLChangeFile.bat
mvn dbdeploy:change-script -Ddbchangefile.name=DML
生成的文件在sql目錄中
dbdeploy2.png
4.執(zhí)行腳本 update.bat (與src平級)
mvn dbdeploy:update -Ddb.url=jdbc:oracle:thin:@ip:port:dis -Ddb.usr=xxx -Ddb.pwd=xxx > dblog.log
5. pom文件
<?xml version="1.0" encoding="GBK"?>
<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>xxx</groupId>
<artifactId>yyyy</artifactId>
<version>x.y.z</version>
</parent>
<groupId>xxx.db</groupId>
<artifactId>db</artifactId>
<properties>
<db.url>jdbc:oracle:thin:@127.0.0.1:TEST</db.url>
<db.usr>${env.DBDEPLOY_USR}</db.usr>
<db.pwd>${env.DBDEPLOY_PWD}</db.pwd>
</properties>
<build>
<plugins>
<plugin>
<groupId>com.dbdeploy</groupId>
<artifactId>maven-dbdeploy-plugin</artifactId>
<version>3.0M3</version>
<configuration>
<scriptdirectory>./src/main/sql</scriptdirectory>
<name>${dbchangefile.name}</name>
<encoding>GBK</encoding>
<lineEnding>lf</lineEnding>
<outputfile>./target/apply.sql</outputfile>
<undoOutputfile>./target/undo.sql</undoOutputfile>
<driver>oracle.jdbc.OracleDriver</driver>
<url>${db.url}</url>
<userid>${db.usr}</userid>
<password>${db.pwd}</password>
<dbms>ora</dbms>
<delimiter>/</delimiter>
<delimiterType>row</delimiterType>
</configuration>
<dependencies>
<!-- 根據(jù)需要進行依賴修改 -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.1.0</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>orai18n</artifactId>
<version>11.2.0.1.0</version>
</dependency>
</dependencies>
<executions>
<execution>
<id>update-db</id>
<phase>verify</phase>
<goals>
<goal>update</goal>
</goals>
<configuration>
<scriptdirectory>${basedir}/target/sql-all</scriptdirectory>
</configuration>
</execution>
</executions>
</plugin>
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>2.5</version>
<executions>
<execution>
<id>copy-sql-to-source-dir</id>
<phase>process-resources</phase>
<goals>
<goal>copy-resources</goal>
</goals>
<configuration>
<outputDirectory>${basedir}/target/sql-all</outputDirectory>
<resources>
<resource>
<directory>src/main/sql</directory>
</resource>
</resources>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
<profiles>
<profile>
<id>script</id>
<build>
<plugins>
<plugin>
<groupId>com.dbdeploy</groupId>
<artifactId>maven-dbdeploy-plugin</artifactId>
<executions>
<execution>
<id>create-script-file</id>
<phase>verify</phase>
<goals>
<goal>db-scripts</goal>
</goals>
<configuration>
<scriptdirectory>${basedir}/target/sql-all</scriptdirectory>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
</profile>
</profiles>
</project>
腳本規(guī)范
1.創(chuàng)建腳本文件
執(zhí)行createNewDDLChangeFile.bat創(chuàng)建DDL（數(shù)據(jù)定義語言，用來維護數(shù)據(jù)庫對象）腳本文件，
在src/main/sql目錄下會創(chuàng)建對應(yīng)的DDL的sql文件，每個DDL語句一個腳本文件。
執(zhí)行createNewDMLChangeFile.bat創(chuàng)建DML（用于增刪改表中數(shù)據(jù)，DML是伴隨事務(wù)控制的）腳本文件，
在src/main/sql目錄下會創(chuàng)建對應(yīng)的DML的sql文件
2.編寫腳本
用正斜杠/分割DML語句，形如：
insert into fw_permission (PERMISSION_ID, NAME, DESCRIPTION)
values ('function_financeManagement_liqsettle', '扣款明細導(dǎo)出', '')
/
insert into fw_permission_management (NODE_ID, PARENT_NODE_ID, PERMISSION_ID, NODE_NAME, DISPLAY_ORDER, NOTES)
values ('110062', '100004', 'function_financeManagement_liqsettle', '扣款明細導(dǎo)出', 350, '')
/
用正斜杠/分割DDL語句，形如：
create table CO_SEND
(
SEND_ID VARCHAR2(20) not null,
MOBILE VARCHAR2(24)
)
/
comment on column CO_SEND.SEND_ID
is '發(fā)送ID'
/
comment on column CO_SEND.MOBILE
is '手機號碼'
/
替換;為/的方法：查找正則表達式;\r\n，替換為\r\n/\r\n
3.UNDO語句
UNDO語句編寫方法如下：
CREATE TABLE FOO (
FOO_ID INTEGER NOT NULL
,FOO_VALUE VARCHAR(30)
)
/
ALTER TABLE FOO ADD CONSTRAINT PK_FOO PRIMARY KEY (FOO_ID)
/
--//@UNDO
DROP TABLE FOO
/
4.創(chuàng)建方法
創(chuàng)建方法腳本時，語句中的分號保留，在語句結(jié)尾的下一行增加/
create or replace function F_DICT(DICTIONARY_ID_IN in VARCHAR2,ITEM_ID_IN in VARCHAR2)
return VARCHAR2 DETERMINISTIC is ITEM_NAME VARCHAR2(50);
i NUMBER;
BEGIN
select
COUNT(1) INTO i
from CO_DICTIONARY_ITEM t
where t.DICTIONARY_ID = DICTIONARY_ID_IN
and t.ITEM_ID = ITEM_ID_IN;
CASE
WHEN i = 0
THEN ITEM_NAME:=ITEM_ID_IN;
ELSE
SELECT t.ITEM_NAME INTO ITEM_NAME
FROM CO_DICTIONARY_ITEM t
WHERE t.DICTIONARY_ID = DICTIONARY_ID_IN
AND t.ITEM_ID = ITEM_ID_IN;
END CASE;
return(ITEM_NAME);
END F_DICT;
/
執(zhí)行
修改update.bat中的相關(guān)參數(shù)為自己需要的數(shù)據(jù)，執(zhí)行update.bat，執(zhí)行之后變會執(zhí)行src/sql下未執(zhí)行過的sql，同時改變changelog表，輸入執(zhí)行日志在指定文件中
