在java 中执行触发器代码、创表语句
2008-06-23 20:43:35 来源：WEB开发网 闂備線娼уΛ鎾箯閿燂拷
闂備礁鎲￠崹鐢垫崲閹扮増鍎嶆い鎺戝€甸崑鎾斥槈濞嗗秳娌紓鍌氱▌閹凤拷
濠电姭鎷冮崨顓濈捕闂侀潧娲ゅú銊╁焵椤掍胶鈯曢柕鍥╁仧缁辩偤鏁撻敓锟�闂備線娼уΛ鎾箯閿燂拷 闂備胶枪缁绘鈻嶉弴銏犳瀬闁绘劕鐏氱€氼剟姊洪崹顕呭剰闁逞屽墮缁夊綊寮婚妸褉鍋撻棃娑欏暈闁伙綀浜埀顒傛暬閸嬪﹪宕伴弽褏鏆﹂柨鐕傛嫹
由于程序的需要，在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;
}
中查找“在java 中执行触发器代码、创表语句”更多相关内容
中查找“在java 中执行触发器代码、创表语句”更多相关内容更多精彩
赞助商链接
