首发地址,欢迎查看:https://www.pengtao.vip/archives/javadaimashixianliquibasedejibenyongfa
前不久公司要求使用Liquibase
替代Flyway
对数据库版本进行管理,要求项目启动时进行数据库升级,但在网上搜索了一大圈,也不知道为啥,几乎没有关于使用Java代码操作Liquibase
的教程,都是通过命令行进行管理。费了些劲,整理了Java操作 Liquibase 的几种常用方法,在此记录一下。
update
update
通常用于将更改日志文件中指定的数据库更改应用到您的数据库。
/**
* 更新数据库
*/
public void update(String tag) throws SQLException {
Connection connection = null;
try {
connection = DriverManager.getConnection((String) yaml().get("url"), (String) yaml().get("username"), (String) yaml().get("password"));
Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
Liquibase liquibase = new Liquibase(changeLogFile, new ClassLoaderResourceAccessor(), database);
//命令行输出日志
/*StringWriter writer = new StringWriter();
liquibase.update("", writer);
logger.info('\n'+writer.toString());*/
//将即将要执行的执行日志写入文件,不更新数据库
/*String migrationSqlOutputFile = (String) yaml().get("migrationSqlOutputFile");
PrintWriter printWriter = new PrintWriter(migrationSqlOutputFile);
liquibase.update("", printWriter);
logger.info("记录已写入" + migrationSqlOutputFile);*/
liquibase.update(""); //更新数据库
liquibase.tag(tag); //打标签
logger.info("<<<<<<<<<<数据库已更新,新标签为 " + tag + " >>>>>>>>>>");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.rollback();
connection.close();
}
}
}
rollback
rollback
通常在您想要恢复数据库中的更改时使用。共有三种rollback
模式:
liquibase.rollbackCount
-将恢复指定数量的changesets顺序,从最近的变化,直到达到指定的值liquibase.rollbackTag
- 恢复在指定标签之后对数据库所做的所有更改liquibase.rollbackDate
- 将对数据库所做的所有更改从今天的日期恢复到您指定的日期和时间
/**
* 回滚
*/
public void rollback() throws SQLException {
Connection connection = null;
try {
connection = DriverManager.getConnection((String) yaml().get("url"), (String) yaml().get("username"), (String) yaml().get("password"));
Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
Liquibase liquibase = new Liquibase(changeLogFile, new ClassLoaderResourceAccessor(), database);
liquibase.rollback("V2.0",""); //回滚
//PrintStream printStream = new PrintStream("src/main/resources/db_changelog/test.xml");
//liquibase.rollback(2,"");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.rollback();
connection.close();
}
}
}
diff
diff
允许您将相同类型或不同类型的两个数据库相互比较,并生成相应的差异文件。
/**
* 生成两数据库对比差异
*/
public void diffChangeLog(String diffChangeLogFile, String referenceUrl, String referenceUsername, String referencePassword,
String targetUrl, String targetUsername, String targetPassword) throws SQLException {
String outputFile = (String) yaml().get("outputFile");
Connection refConnection = DriverManager.getConnection(referenceUrl,referenceUsername,referencePassword);
Connection tarConnection = DriverManager.getConnection(targetUrl,targetUsername,targetPassword);
try {
Database targetDatabase = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(tarConnection));
Database referenceDatabase = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(refConnection));
Liquibase liquibase = new Liquibase("", new ClassLoaderResourceAccessor(), referenceDatabase);
//详细对比文档
CommandLineUtils.doDiff(referenceDatabase, targetDatabase, StringUtils.trimToNull((String) yaml().get("diffTypes")), null, new PrintStream(outputFile));
//生成差异文档数据
CommandLineUtils.doDiffToChangeLog(diffChangeLogFile, referenceDatabase, targetDatabase,
new DiffOutputControl().setIncludeCatalog(false).setIncludeSchema(false), null,
StringUtils.trimToNull((String) yaml().get("diffTypes")));
logger.info("<<<<<<<<<<数据库差异已写入 " + diffChangeLogFile + " >>>>>>>>>>");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (refConnection != null) {
refConnection.close();
tarConnection.close();
}
}
}
其中:
reference 代表源数据库
target 代表目标数据库
changeLogSync
changelogSync
更新DATABASECHANGELOG表以将所有未应用于数据库的更改标记为已执行,通常在您想要为新的数据库环境建立基线时使用。
/**
* 同步数据库版本号 changeLogSync
*/
public void changeLogSync(String tag) throws SQLException {
Connection connection = null;
try {
connection = DriverManager.getConnection((String) yaml().get("targetUrl"), (String) yaml().get("targetUsername"), (String) yaml().get("targetPassword"));
Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
Liquibase liquibase = new Liquibase(changeLogFile, new ClassLoaderResourceAccessor(), database);
liquibase.changeLogSync("");
logger.info("<<<<<<<<<< 已同步 >>>>>>>>>>");
liquibase.tag(tag);
logger.info("<<<<<<<<<<标签为 " + tag + " >>>>>>>>>>");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
generateChangeLog
generateChangeLog
通常用于捕获数据库的当前状态,然后将这些更改应用于任意数量的数据库。这通常仅在项目具有现有数据库但之前未使用过Liquibase 时才进行。
/**
* 自动生成数据库执行脚本,全量导出
*/
public void GenerateChangeLog(String outputChangeLogFile) throws SQLException {
final Set<Class<? extends DatabaseObject>> compareTypes = new HashSet<>();
compareTypes.add(Column.class);
compareTypes.add(Data.class);
compareTypes.add(ForeignKey.class);
compareTypes.add(Index.class);
compareTypes.add(PrimaryKey.class);
compareTypes.add(Sequence.class);
compareTypes.add(Table.class);
compareTypes.add(UniqueConstraint.class);
compareTypes.add(View.class);
Connection connection = null;
try {
connection = DriverManager.getConnection((String) yaml().get("targetUrl"), (String) yaml().get("referenceUsername"), (String) yaml().get("referencePassword"));
Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
Liquibase liquibase = new Liquibase(outputChangeLogFile, new ClassLoaderResourceAccessor(), database);
DiffToChangeLog diffToChangeLog = new DiffToChangeLog(new DiffOutputControl());
//ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
PrintStream printStream = new PrintStream(outputChangeLogFile);
liquibase.generateChangeLog(database.getDefaultSchema(), diffToChangeLog, printStream, compareTypes.toArray(new Class[compareTypes.size()]));
logger.info("<<<<<<<<<< 开始获取 >>>>>>>>>>");
diffToChangeLog.print(printStream);
logger.info("<<<<<<<<<< 获取完成 >>>>>>>>>>");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.rollback();
connection.close();
}
}
}
关于代码中的获取yaml数据的方法,请移步这篇博客