Groovy连接2个数据库进行数据操作

最新推荐文章于 2023-07-25 15:53:24 发布

Leon1509

最新推荐文章于 2023-07-25 15:53:24 发布

阅读量523

点赞数

分类专栏： JAVA Groovy 文章标签： sql java oracle microsoft

Groovy 同时被 2 个专栏收录

28 篇文章 1 订阅

订阅专栏

JAVA

16 篇文章 0 订阅

订阅专栏


import groovy.sql.Sql

class 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()
        }
    }
}