本文是小小的總結一下本人開發時要在sybase數據庫上創建特定的觸發器
創建的觸發器完成的功能如下 :
在數據庫中指定的表上創建插入,更新,刪除觸發器,當指定的表發生插入、更新或者刪除操作時,將觸發觸發器相應的動作,觸發器的作用就是,將發生上述操作的表的表名、主鍵名、對應的主鍵值、相應的操作存到另一張記錄表中。本總結中除了創建該類型觸發器外還包括刪除觸發器,刪除記錄表,判斷是否已有記錄表。
需要導入的架包:activejdbc.jar,jconn3.jar:
import java.util.List; import java.util.Map; import org.javalite.activejdbc.Base; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * 模板類 * @author ywnwa * */ public abstract class AbstractTriggerManager implements TriggerManager { protected static final String TRIGGER_EVENT_TABLE_NAME = "TRIGGER_EVENTS" ; private final Logger logger = LoggerFactory .getLogger(getClass()); private final String user; private final String password; public AbstractTriggerManager(String user, String password) { this .user = user; this .password = password; } public void openDatabase() { Base.open(getDriverClass(), getUrl(), this .user, this .password); logger.debug( "open database sucessfuly!" ); } public void closeDatabase() { Base.close(); logger.debug( "close database sucessfuly!" ); } @SuppressWarnings( "rawtypes" ) public boolean hasEventTable() { // NOTE (Tan Bingjian) // the values of table_name must be given in capital List<Map> result = Base.findAll(getSelectTriggerEventTableSql()); return ! result.isEmpty(); } public void createEventTable() { if (! hasEventTable()) { logger.debug( "TRIGGER_EVENTS is not exist! It will be created ." ); Base.exec(getCreateEventTableSql()); logger.debug( "TRIGGER_EVENTS create successfuly!" ); } logger.debug( "TRIGGER_EVENTS is get ready !" ); } public void dropEventTable() { if (hasEventTable()) { Base.exec(getDropEventTableSql()); logger.debug( "TRIGGER_EVENTS drop successfuly!" ); } } @Override public void create(String name) { // trigger for insert Base.exec(sqlForTrigger(name, "insert" )); // trigger for delete Base.exec(sqlForTrigger(name, "delete" )); // trigger for update Base.exec(sqlForTrigger(name, "update" )); logger.debug( "The triggers on table " + name + " create successfuly ! !" ); } @Override public void remove(String tableName) { String[] actions = getActionsType(); // { "INSERT", "UPDATE", "DELETE" }; for (String i : actions) { String triggerName = String.format("TR_%s_%s" , tableName, i); logger.debug( "look for " + triggerName + " on table :" + tableName); if (triggerExists(tableName, triggerName)) { dropTrigger(triggerName); logger.debug( "the trigger has been deleted !" ); } else { logger.debug( "the trigger no existe !" ); } } } @Override public void removeAll() { List <String> tableNames = getAllTables(); for (String name : tableNames) { remove(name); } } // @Override public void removeAll( boolean removeEventTable) { removeAll(); if (removeEventTable && hasEventTable()) { dropEventTable(); } } @SuppressWarnings( "rawtypes" ) public boolean triggerExists(String tableName, String triggerName) { List <Map> result = Base.findAll(getSelectTriggersSql(tableName, triggerName)); return ! result.isEmpty(); } protected void dropTrigger(String triggerName) { Base.exec(getDropTriggerSql(triggerName)); } protected String getEventTableName() { return TRIGGER_EVENT_TABLE_NAME; } protected abstract List<String> getAllTables(); protected abstract String getDriverClass(); protected abstract String getUrl(); protected abstract String getSelectTriggerEventTableSql(); protected abstract String sqlForTrigger(String tableName, String opType); protected abstract String getSelectTriggersSql(String tableName, String triggerName); protected abstract String getDropTriggerSql(String triggerName); protected abstract String getCreateEventTableSql(); protected abstract String getDropEventTableSql(); protected abstract String[] getActionsType(); }
這個只是模板類不做過多介紹,下面是具體的實現類:
import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import org.javalite.activejdbc.Base; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class SyBaseTriggerManager extends AbstractTriggerManager { private final Logger logger = LoggerFactory .getLogger(getClass()); private static final String URL_TEMPLATE = "jdbc:sybase:Tds:%s:%s/%s" ; private static final String SELECT_TABLES_SQL_TEMPLATE = "select name from sysobjects where type='U'" ; private static final String DRIVER_CLASS = "com.sybase.jdbc3.jdbc.SybDriver" ; private static final String TRIGGER_EVENT_TABLE_NAME = "TRIGGER_EVENTS" ; private static final String SELECT_TRIGGER_EVENT_TABLE_SQL = "select name from sysobjects where name='" + TRIGGER_EVENT_TABLE_NAME + "'" ; private static final String CREATE_TRIGGER_EVENT_TABLE_SQL = "CREATE TABLE " + TRIGGER_EVENT_TABLE_NAME + "(TRIGGER_EVENTS_ID INT IDENTITY NOT NULL PRIMARY KEY ," + " dbName varchar(30)," + " tableName varchar(30)," + " pkNames varchar(2000)," + " pkValues varchar(2000)," + " action varchar(20))" ; private static final String SELECT_PK_NAMES_SQL_TEMPLATE = "select columnname from (SELECT object_name(id) tabname, index_col( object_name(id) ,indid,1) columnname FROM sysindexes WHERE status & 2048=2048 " + "union " + "SELECT object_name(id), index_col( object_name(id) ,indid,2) FROM sysindexes WHERE status & 2048=2048 " + "union " + "SELECT object_name(id), index_col( object_name(id) ,indid,3) FROM sysindexes WHERE status & 2048=2048 " + "union " + "SELECT object_name(id), index_col( object_name(id) ,indid,4) FROM sysindexes WHERE status & 2048=2048 " + "union " + "SELECT object_name(id), index_col( object_name(id) ,indid,5) FROM sysindexes WHERE status & 2048=2048 " + "union " + "SELECT object_name(id), index_col( object_name(id) ,indid,6) FROM sysindexes WHERE status & 2048=2048 " + "union " + "SELECT object_name(id), index_col( object_name(id) ,indid,7) FROM sysindexes WHERE status & 2048=2048 " + "union " + "SELECT object_name(id), index_col( object_name(id) ,indid,8) FROM sysindexes WHERE status & 2048=2048 " + "union " + "SELECT object_name(id), index_col( object_name(id) ,indid,9) FROM sysindexes WHERE status & 2048=2048 " + "union " + "SELECT object_name(id), index_col( object_name(id) ,indid,10) FROM sysindexes WHERE status & 2048=2048 " + ")pk where columnname is not null and tabname='%s'" ; private static final String SELECT_TRIGGERS_SQL_TEMPLATE = "select name from sysobjects where type = 'TR'and name='%s'" ; private static final String DROP_TRIGGER_SQL_TEMPLATE = "drop trigger %s" ; private static final String DROP_TRIGGER_EVENT_TABLE_SQL = "drop table " + TRIGGER_EVENT_TABLE_NAME; private final String url; private final String host; private final int port; private final String db; private final String user; private final String password; public SyBaseTriggerManager(String host, int port, String db, String user, String password) { super (user, password); this .db = db; this .user = user; this .port = port; this .host = host; this .url = String.format(URL_TEMPLATE, host, port, db); this .password = password; } @SuppressWarnings( "rawtypes" ) @Override protected List<String> getAllTables() { List <Map> result = Base.findAll(SELECT_TABLES_SQL_TEMPLATE); List <String> names = new ArrayList<String> (); for (Map row : result) { names.add(row.get( "name" ).toString()); } return names; } protected String getHost() { return host; } protected int getPort() { return port; } protected String getDb() { return db; } protected String getUser() { return user; } protected String getPassword() { return password; } @Override protected String getDriverClass() { return DRIVER_CLASS; } @Override protected String getUrl() { return url; } @Override protected String getSelectTriggerEventTableSql() { return SELECT_TRIGGER_EVENT_TABLE_SQL; } @Override protected String sqlForTrigger(String tableName, String opType) { String actionTo; String[] pkeys = getPks(tableName); String tigger; String va = "" ; String declare = "" ; String values = "" ; String pk = "" ; if (opType.equals("insert") || opType.equals("update" )) { actionTo = "inserted " ; } else { actionTo = "deleted " ; } for ( int i = 0; i < pkeys.length; i++ ) { pk += pkeys[i] + "," ; va += "@" + pkeys[i] + "+" + "','" + "+" ; declare += "@" + pkeys[i] + " varchar(20)" + "," ; values += "select " + "@" + pkeys[i] + "=convert(char(200)," + pkeys[i] + ")" + " from " + actionTo; } declare = declare.substring(0, declare.lastIndexOf("," )); va = va.substring(0, va.lastIndexOf("+" )); tigger = "create trigger TR_%s_%s on %s" + " for %s as declare %s begin" + " %s insert into %s" + "(dbName,tableName,pkNames,pkValues,action)" + "values('%s','%s','%s',%s,'%s') end" ; return String.format(tigger, tableName, opType, tableName, opType, declare, values, TRIGGER_EVENT_TABLE_NAME, db, tableName, pk, va, opType); } @Override protected String getSelectTriggersSql(String tableName, String triggerName) { return String.format(SELECT_TRIGGERS_SQL_TEMPLATE, triggerName); } @Override protected String getDropTriggerSql(String triggerName) { return String.format(DROP_TRIGGER_SQL_TEMPLATE, triggerName); } @Override protected String getCreateEventTableSql() { return CREATE_TRIGGER_EVENT_TABLE_SQL; } @Override protected String getDropEventTableSql() { return DROP_TRIGGER_EVENT_TABLE_SQL; } @SuppressWarnings({ "rawtypes" }) public String[] getPks(String tableName) { ArrayList <Map> list3 = new ArrayList<Map> (); list3 = (ArrayList<Map> ) Base.findAll(String.format( SELECT_PK_NAMES_SQL_TEMPLATE, tableName)); String[] pkNames = new String[list3.size()]; for ( int i = 0; i < list3.size(); i++ ) { Map map = list3.get(i); Set set = map.keySet(); Iterator it = set.iterator(); while (it.hasNext()) { pkNames[i] = (String) map.get(it.next()); System.out.println(pkNames[i]); } } return pkNames; } public void create(String tableName) { String[] actions = { "insert", "update", "delete" }; for (String i : actions) { String triggerName = String.format("TR_%s_%s" , tableName, i); if (triggerExists(tableName, triggerName)) { dropTrigger(triggerName); } Base.exec(sqlForTrigger(tableName, i)); } logger.debug( "The triggers on table " + tableName + " create successfuly ! !" ); } @Override protected String[] getActionsType() { String[] actions = { "insert", "update", "delete" }; return actions; } }
關鍵語句介紹:
private static final String SELECT_PK_NAMES_SQL_TEMPLATE
獲取對應表的主鍵名語句,即對應表的主鍵名,不過這條語句有個局限性是最多只能獲取表中只有10個主鍵的表,超過十個的話第十一個主鍵將不再獲取。如果大家有更加自由的方法歡迎交流。
?protected String sqlForTrigger(String tableName, String opType)
該方法返回創建觸發器語句
創建觸發器語句:return返回的是完整的語句
tigger = "create trigger TR_%s_%s on %s" + " for %s as declare %s begin" + " %s insert into %s" + "(dbName,tableName,pkNames,pkValues,action)" + "values('%s','%s','%s',%s,'%s') end" ; return String.format(tigger, tableName, opType, tableName, opType, declare, values, TRIGGER_EVENT_TABLE_NAME, db, tableName, pk, va, opType);
對了,sybase數據庫對大小寫敏感,還有就是sybase似乎不支持中文創表,和記錄中有中午,也許是我還不夠了解,懂的朋友歡迎指導
最后,這是第一次寫技術博客,就大概貼出了代碼,還有很多不足的地方,請大家多多指教,歡迎一起探討共同進步。
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
