Liquibase与程序化变更：能否将JDBC操作记录至变更日志？

Great question—this is a super common pain point when mixing custom JDBC code with Liquibase's structured change management. Let’s break down your options to get those programmatic updates into your Liquibase changelog properly:

1. Swap Raw JDBC Calls for Liquibase’s Java API #

Instead of using bare Statement or PreparedStatement calls, use Liquibase’s native Java API to execute your changes. This way, Liquibase automatically logs every change to its databasechangelog table, just like any other changelog entry you’d define in XML/YAML/JSON.

Here’s a quick example of updating a table using the API:

import liquibase.Liquibase;
import liquibase.database.DatabaseFactory;
import liquibase.database.jvm.JdbcConnection;
import liquibase.resource.ClassLoaderResourceAccessor;
import liquibase.change.core.UpdateDataChange;
import liquibase.changelog.ChangeSet;

// Grab your existing JDBC connection
Connection jdbcConn = ...; 

// Wrap it in a Liquibase-compatible database object
JdbcConnection liquibaseJdbcConn = new JdbcConnection(jdbcConn);
Database db = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(liquibaseJdbcConn);

// Define your update change
UpdateDataChange updateChange = new UpdateDataChange();
updateChange.setTableName("customer");
updateChange.addColumn(new ColumnConfig().setName("status").setValue("active"));
updateChange.setWhereClause("id = 456");

// Attach the change to a ChangeSet and execute via Liquibase
ChangeSet changeSet = new ChangeSet("update-customer-status-456", "your-name", false, false, null, null, null, updateChange);
Liquibase liquibase = new Liquibase("path/to/your/master-changelog.xml", new ClassLoaderResourceAccessor(), db);
liquibase.getChangeLogService().getChangeLog().addChangeSet(changeSet);
liquibase.update("");

This approach handles all the heavy lifting: unique change IDs, checksums, execution timestamps, and status tracking are all managed automatically.

2. Retroactively Capture Changes with Liquibase Diff #

If you’ve already run JDBC changes and need to backfill them into your changelog, use Liquibase’s diffChangeLog tool. This compares your current database state against a baseline (like your last fully Liquibase-managed state) and generates a changelog with all the differences.

Step-by-step: #

1. First, get a baseline of your database before the JDBC changes ran. If you don’t have one, generate it with:

   liquibase generateChangeLog --url=jdbc:yourdb://baseline-db-url --username=user --password=pass --outputFile=baseline-changelog.xml
   

2. Run the diff to generate a changelog for the JDBC-made changes:

   liquibase diffChangeLog --referenceUrl=jdbc:yourdb://baseline-db-url --username=baseline-user --password=baseline-pass --url=jdbc:yourdb://current-db-url --username=current-user --password=current-pass --outputFile=jdbc-changes-changelog.xml
   

3. Review carefully: The generated changelog will include all differences between the baseline and current DB, so you’ll need to filter out unrelated changes, add meaningful id/author values, and clean up descriptions.
4. Add this cleaned-up changelog to your Liquibase setup and run liquibase update—this marks the changes as executed so they won’t run again.

3. Custom Manual Tracking (Last Resort) #

If you absolutely can’t replace your JDBC code, you can manually insert entries into Liquibase’s databasechangelog table. Warning: This is error-prone because you have to manage checksums, unique IDs, and execution status yourself.

A simplified example SQL insert:

INSERT INTO databasechangelog (
    ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, LIQUIBASE
) VALUES (
    'manual-jdbc-update-789', 'roland', 'custom-jdbc-change', NOW(), 10, 'MD5:abc123...', 'Update user email via JDBC', 'Manual tracking of programmatic change', 'EXECUTED', '4.23.0'
);

You’ll need to calculate the MD5 checksum manually (based on the exact SQL you ran) to match what Liquibase would generate. This bypasses Liquibase’s validation, so use it only if you have no other option.

Quick Best Practices #

• Prioritize the Liquibase API: It’s the most reliable way to keep your changelog in sync with actual database changes.
• Avoid drift: Make sure your team aligns on how programmatic changes are tracked—consistency prevents gaps in your changelog.
• Test retroactively: If using diffChangeLog, always test the generated changelog in a staging environment before applying it to production.

内容的提问来源于stack exchange，提问作者Roland