Ensemble连接oracle数据库的3种方法

1、通过SQLAdapter

这里我们使用ensemble机制，BS访问BO，其中跳过BP的部分，直接贴出代码

Class TestApp.SQLService Extends Ens.BusinessService [ ProcedureBlock ]
{

Parameter ADAPTER = "EnsLib.SQL.InboundAdapter";

Method OnProcessInput(pInput As EnsLib.SQL.Snapshot, Output pOutput As %RegisteredObject) As %Status
{
	s pOutput = ##class(User.SQLResponse).%New()
	 set req=##class(User.SQLRequest).%New()
	 s req.id=10
	 s req.name="organ.2"
	 set sc=..SendRequestSync("User.SQLOperation",req,.response)
	 s pOutput.result = response.result
	Q sc
}

}

这里做简单例子，在BS中创建发送到BO的处理请求消息，采用同步，
SendRequestSync

看看在BO中的处理

Class User.SQLOperation Extends Ens.BusinessOperation [ ProcedureBlock ]
{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Property Adapter As EnsLib.SQL.OutboundAdapter;

Parameter INVOCATION = "Queue";

Method PostIt(pRequest As User.SQLRequest, Output pResponse As User.SQLResponse) As %Status
{
	 set sql="Insert into lihm.Organ"_
      " (organId,organName) "_
      " Values(?,?)"
      s ^t.1 = sql
   set parms=2,parms(1)=pRequest.id,parms(2)=pRequest.name
   set ok=..Adapter.ExecuteUpdateParmArray(.numrows,sql,.parms)
   s ^ok =ok 
   s pResponse = ##class(User.SQLResponse).%New()
   s pResponse.result = ok
   quit ok
}

XData MessageMap
{
<MapItems>
	<MapItem MessageType="User.SQLRequest"> 
		<Method>PostIt</Method>
	</MapItem>
</MapItems>
}

}

在BO中提取传递的参数，并完成sql语句，插入数据到oracle数据库中，那么如何将BS和BO结合起来呢，在Ensemble中有Production,我们将设置的一些条件添加进去

Class TestApp.StudyProduction Extends Ens.Production [ ProcedureBlock ]
{

XData ProductionDefinition
{
<Production Name="TestApp.StudyProduction" LogGeneralTraceEvents="false">
  <Description></Description>
  <ActorPoolSize>1</ActorPoolSize>
  <Item Name="User.SQLOperation" Category="" ClassName="User.SQLOperation" PoolSize="1" Enabled="true" Foreground="false" Comment="" LogTraceEvents="false" Schedule="">
    <Setting Target="Adapter" Name="Credentials">lihm</Setting>
    <Setting Target="Adapter" Name="DSN">CacheOracle</Setting>
  </Item>
  <Item Name="TestApp.SQLService" Category="" ClassName="TestApp.SQLService" PoolSize="1" Enabled="true" Foreground="false" Comment="" LogTraceEvents="false" Schedule="">
    <Setting Target="Adapter" Name="Credentials">lihm</Setting>
    <Setting Target="Adapter" Name="DSN">CacheOracle</Setting>
  </Item>
</Production>
}

}

在production中配置EnsLib.SQL.OutboundAdapter和EnsLib.SQL.InboundAdapter绑定的数据源DSN名称，以及Credentials，已经创建的连接数据库的用户名以及密码的id，

Home->Ensemble->yourNameSpace->Configure->Credentials来配置

 

使用测试方法：

 s tSC = ##class(Ens.Director).CreateBusinessService("TestApp.SQLService",.tService)
 s input = ##class(EnsLib.SQL.Snapshot).%New()
 s tSC = tService.ProcessInput(input,.output)
 w tSC
 w $SYSTEM.Status.GetErrorText(tSC)

这样即可执行BO中的sql语句插入数据

 

2、通过SQLGatewayConnection访问

ClassMethod Test()
{
	s gc = ##class(%SQLGatewayConnection).%New()
	s sc = gc.Connect("CacheOracle","lihm","lihm",6000)
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit
    s useTime = $P($Now(),",",2)
	
    Set res=##class(%ResultSet).%New("%DynamicQueryGW:SQLGW")
    Set sc=res.Prepare("select * from organ where organId<10000",,gc)
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    Set sc=res.Execute()
	
	s useTime = $P($Now(),",",2) - useTime
	w useTime,!
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit
	d res.%Display()
	;w res.Data
    While res.Next() 
    { Write !,res.GetData(1)
    } 
    Set sc=gc.Disconnect() 
    Quit sc
}

此方法也是先设置数据源，在gc.Connect(),中传入DSN名称，用户名，密码，设置等待时间，然后以动态SQL的形式访问
3、通过JavaGateway访问

ClassMethod TestJDBC()
{
	s tool = ##class(DCApp.DAO.Mongo.MongoTool).%New()
	s gateway = ##class(EnsLib.JavaGateway.JavaGateway).%New()
	s sc = tool.GetGateWay("127.0.0.1",55557,.gateway)
	if sc{
		s useTime = $P($Now(),",",2)
		for i=1:1:1000{
			;s i=1
			s str = "insert into organ values("_i_",'huaxi011')"
			d ##class(JavaGate.Comm.DataUtil).InsertData(gateway,str)
			}
		s useTime = $P($Now(),",",2) - useTime
		w useTime,!
		}

    Quit sc
}

JavaGateway即使用JDBC访问oracle数据库，我们在Java中编写好代码，例如这里的InsertData方法，生成jar包后引入到ensemble中即可，在使用是先连接Java网关，之后就可以使用自己定义的方法