java是为不同数据库生成脚本的最佳选择

Question

我想知道如何从特定模板生成脚本（DDL、DML）到不同的数据库，比如Oracle、MSSQL、Sybase

Liquibase似乎很合适，但有什么框架或方法可以做到这一点吗

Answer 1

下面的示例为H2数据库生成SQL。XML变更集使liquibase能够生成特定于数据库的SQL

范例

安装罐子

mkdir lib
curl http://search.maven.org/remotecontent?filepath=org/liquibase/liquibase-core/3.0.8/liquibase-core-3.0.8.jar -o lib/liquibase.jar
curl http://search.maven.org/remotecontent?filepath=com/h2database/h2/1.3.174/h2-1.3.174.jar -o lib/h2.jar

这是个骗局。您还可以下载并安装zip包

示例变更日志文件

这个变更日志创建一个表。以下格式是XML，其他支持的格式是YAML和SQL

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet author="mark" id="1">
        <createTable tableName="EMPLOYEE">
            <column name="EMPNO" type="INT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="EMP_PK"/>
            </column>
            <column name="NAME" type="VARCHAR(10)"/>
            <column name="JOB" type="VARCHAR(9)"/>
            <column name="BOSS" type="INT"/>
            <column name="HIREDATE" type="VARCHAR(12)"/>
            <column name="SALARY" type="DECIMAL(7,2)"/>
            <column name="COMM" type="DECIMAL(7,2)"/>
            <column name="DEPTNO" type="INT"/>
        </createTable>
    </changeSet>

</databaseChangeLog>

奔跑

Liquibase可以按如下方式运行以生成SQL

java -jar ./lib/liquibase.jar \
      classpath=lib/h2.jar \
      url=jdbc:h2:db/scottTiger \
      driver=org.h2.Driver \
      username=user \
      password=pass \
      changeLogFile=sample.xml \
     updateSQL

“update”命令将对数据库应用SQL

产生的产出

  *********************************************************************
  Update Database Script
  *********************************************************************
  Change Log: sample.xml
  Ran at: 22/12/13 22:30
  Against: USER@jdbc:h2:db/scottTiger
  Liquibase version: 3.0.8
  *********************************************************************

  Create Database Lock Table
CREATE TABLE PUBLIC.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

  Initialize Database Lock Table
DELETE FROM PUBLIC.DATABASECHANGELOGLOCK;

INSERT INTO PUBLIC.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, FALSE);

  Lock Database
  Create Database Change Log Table
CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20));

  Changeset sample.xml::1::mark
CREATE TABLE PUBLIC.EMPLOYEE (EMPNO INT NOT NULL, NAME VARCHAR(10), JOB VARCHAR(9), BOSS INT, HIREDATE VARCHAR(12), SALARY DECIMAL(7, 2), COMM DECIMAL(7, 2), DEPTNO INT, CONSTRAINT EMP_PK PRIMARY KEY (EMPNO));

INSERT INTO PUBLIC.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, LIQUIBASE) VALUES ('1', 'mark', 'sample.xml', NOW(), 1, '7:4700326f252366e9cfe598fded5037c8', 'createTable', '', 'EXECUTED', '3.0.8');

Liquibase创建一个名为“DATABASECHANGELOG”的特殊表来跟踪所有更改。仔细看，您会发现“EMPLOYEE”表的table create语句

这个例子适用于H2。支持其他数据库