在java 中執行觸發器代碼、創表語句

由于程序的需要，在SQLServer 中創建觸發器及建表，碰到了在java 代碼中執行創建觸發器及表。 /**建立中間表*/ public static final String createMiddleTableSQL = "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] "; /**在告警表上創建觸發器*/ public static final String createMiddleTblTriggerSQL = "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 "; JAVA中執行以上語句過程： view plaincopy to clipboardprint? /** * 創建中間表或者觸發器 * * @param sql String * @return boolean 返回語句執行結果,true 成功，false 失敗 */ private boolean createTableOrTrigger(String sql) { Connection con = null; PreparedStatement st = null; boolean result = false; try { con = dbh.getConnection(); st = con.prepareStatement(sql); st.execute(); result = true; dbh.closeConnections(null, st, con); } catch (SQLException ex) { Log.error("Unable to create :" + sql + " ,ErrorCode :" + ex.getErrorCode() + ",Exception :" + ex.getLocalizedMessage()); dbh.closeConnections(null, st, con); } return result; }