您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 程式設計入門

Java 執行 SQL Server DDL：建立資料表與觸發器的完整程式碼範例

編輯：Java 程式設計入門

場景：應用啟動時自動建立中繼表與觸發器

為了即時同步告警資料到中繼表，需在 SQL Server 建立 AlarmsMiddleTbl 並對 Alarms 表掛載 INSERT/UPDATE 觸發器。以下展示在 Java 程式碼中內嵌 DDL 並執行的完整做法。

DDL 陳述式定義 (常數)

public class AlarmSchema {
    // 建立中繼表
    public static final String CREATE_MIDDLE_TABLE_SQL =
        "CREATE TABLE [dbo].[AlarmsMiddleTbl] (" +
        "  [id] [int] NOT NULL," +
        "  [DeviceID] [smallint] NULL," +
        "  [Aid] [char](10) COLLATE Chinese_PRC_BIN NULL," +
        "  [BeginTime] [datetime] NULL," +
        "  [EndTime] [datetime] NULL," +
        "  [Severity] [char](2) COLLATE Chinese_PRC_BIN NULL," +
        "  [CondType] [char](25) COLLATE Chinese_PRC_BIN NULL," +
        "  [DetailID] [smallint] NULL," +
        "  [AckNotes] [char](150) COLLATE Chinese_PRC_BIN NULL," +
        "  [Description] [char](255) COLLATE Chinese_PRC_BIN NULL," +
        "  [Systemtimes] [char](30) COLLATE Chinese_PRC_BIN NULL" +
        ") ON [PRIMARY];";

    // 建立觸發器：同步 INSERT/UPDATE 至中繼表
    public static final String CREATE_TRIGGER_SQL =
        "CREATE TRIGGER t_alarms ON [NTBW].[dbo].[Alarms] " +
        "FOR INSERT, UPDATE AS " +
        "DECLARE @rows int; SELECT @rows = @@rowcount; " +
        "IF @rows = 0 RETURN; " +
        "IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted) " +
        "BEGIN " +
        "  INSERT INTO [NTBW].[dbo].[AlarmsMiddleTbl] " +
        "  SELECT i.[id], i.[DeviceID], i.[Aid], i.[BeginTime], " +
        "         i.[EndTime], i.[Severity], i.[CondType], i.[DetailID], " +
        "         i.[AckNotes], i.[Description], getdate() FROM inserted i " +
        "END " +
        "IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) " +
        "   AND UPDATE(EndTime) " +
        "BEGIN " +
        "  INSERT INTO [NTBW].[dbo].[AlarmsMiddleTbl] " +
        "  SELECT i.[id], i.[DeviceID], i.[Aid], i.[BeginTime], " +
        "         i.[EndTime], i.[Severity], i.[CondType], i.[DetailID], " +
        "         i.[AckNotes], i.[Description], getdate() FROM inserted i " +
        "END " +
        "IF @@error <> 0 " +
        "BEGIN RAISERROR('ERROR',16,1); ROLLBACK TRANSACTION; RETURN END;";
}

通用執行工具方法

public class DdlExecutor {
    private final DataSource dataSource; // 注入或自行建立

    public boolean executeDdl(String sql) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.execute(); // DDL 使用 execute()，非 executeUpdate()
            return true;
        } catch (SQLException ex) {
            log.error("DDL 執行失敗: sql={}, errorCode={}, msg={}", 
                      sql, ex.getErrorCode(), ex.getMessage(), ex);
            return false;
        } finally {
            closeQuietly(stmt);
            closeQuietly(conn);
        }
    }

    private void closeQuietly(AutoCloseable c) {
        if (c != null) try { c.close(); } catch (Exception ignore) {}
    }
}

呼叫範例

DdlExecutor executor = new DdlExecutor(dataSource);
boolean ok1 = executor.executeDdl(AlarmSchema.CREATE_MIDDLE_TABLE_SQL);
boolean ok2 = executor.executeDdl(AlarmSchema.CREATE_TRIGGER_SQL);
if (ok1 && ok2) {
    log.info("告警中繼表與觸發器建立成功");
} else {
    log.warn("部分 DDL 執行失敗，請檢查日誌");
}

關鍵注意事項

DDL 使用 execute()：executeUpdate() 專為 DML，部分驅動對 DDL 回傳 -1 導致誤判。
批次執行：若多條 DDL 需原子性，考慮資料庫支援的 BEGIN TRAN ... COMMIT 或 Flyway/Liquibase 遷移工具管理版本。
權限：連線使用者需具 CREATE TABLE、CREATE TRIGGER、ALTER 等權限。
冪等性：生產環境建議先 IF NOT EXISTS / IF OBJECT_ID(...) IS NOT NULL DROP ... 避免重複執行報錯。
編碼與排序規則：中文環境明確指定 COLLATE Chinese_PRC_BIN 確保一致性。