Groovy连接2个数据库进行数据操作
import groovy.sql.Sqlclass CardLossMSSQL { static { } static void main(args){ def oradb = [url:"jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = bs)))", user:"scott", password:"tiger", driver:"oracle.jdbc.driver.OracleDriver"] def orasql = Sql.newInstance(oradb.url, oradb.user, oradb.password, oradb.driver)def msdb = [url:"jdbc:sqlserver://xx.xx.xx.xx:1433;DataBaseName=bs", user:"sa", password:"sa", driver:"com.microsoft.sqlserver.jdbc.SQLServerDriver"] def mssql = Sql.newInstance(msdb.url, msdb.user, msdb.password, msdb.driver)try{orasql.eachRow("SELECT tct.ID, tct.CardID, tct.Tpye from TCM_CARDLOST_TEMP tct ORDER BY tct.ID"){r ->if(r.Tpye == '1'){mssql.execute("delete ComUnLost where scCardID=${r.CardID}")mssql.execute("delete ComLost where scCardID=${r.CardID}")mssql.execute("insert into ComLost(scCardID) values(${r.CardID})")mssql.execute("Insert into ComCardOperate(scID, coType, coCount, coMoney, slID, slStationNo, ClientNo, coUseSystem, coDT, opName, coRemark) (select scID, 3 as coType,crdcount,crdmoney,0 as slID,0 as slStationNo, 1 as ClientNo, 65535 as coUseSystem,GetDate(),39, 0 as coRemark from comsendcard where sccardid = ${r.CardID})")mssql.execute("update ComSendCard set scCardStatus=scCardStatus | 2 where scCardID=${r.CardID}")orasql.execute("DELETE FROM TCM_CARDLOST_TEMP WHERE ID = ${r.ID}")} else {mssql.execute("insert into ComUnLost(scCardID,LostID) select scCardID,LostID FROM ComLost WHERE scCardID=${r.CardID}")mssql.execute("delete ComLost where scCardID=${r.CardID}")mssql.execute("Insert into ComCardOperate(scID, coType, coCount, coMoney, slID, slStationNo, ClientNo, coUseSystem, coDT, opName, coRemark) (select scID, 4 as coType,crdcount,crdmoney,0 as slID,0 as slStationNo, 1 as ClientNo, 65535 as coUseSystem,GetDate(),39, 0 as coRemark from comsendcard where sccardid = ${r.CardID})")mssql.execute("update comsendcard set scCardStatus=scCardStatus & 0xfffffffd where scCardID=${r.CardID}")orasql.execute("DELETE FROM TCM_CARDLOST_TEMP WHERE ID = ${r.ID}")}}} catch (java.sql.SQLException e){ e.printStackTrace() } finally {orasql.close() mssql.close() } }}