通过注册Oracle SQL脚本就可以实现简单返回查询结果的Restful API服务,无需编写Java的Entity、Dao、Services和Controller等等类,也无需编译、部署和发布包和程序,减少60%到80%以上的工作量,注册就可以发布Restful API服务
注意重点:Oracle SQL脚本不能有回车换行符。否则无法调用创建通用API。
一、Oracle SQL无参数脚本
以如下Oracle SQL脚本为例:
SELECT Owner
,Object_Name
,Object_Type
FROM Dba_Objects
WHERE Owner = 'CRM'
1.1 注册通用Restful API对象:CRM_CUSTOM_OBJECT
请求URL:http://localhost:6602/db/api/apiobject/add
请求方法:POST
请求报文为JSON格式参考如下:
{
"apiCode": "CRM_CUSTOM_OBJECT",
"description": "查询E-Lin CRM自定义数据对象&<>'\"",
"objectName": "SELECT Owner,Object_Name,Object_Type FROM Dba_Objects WHERE Owner = 'CRM'",
"dataObjectType": 1,
"dataSourceId": 2,
"enabledFlag": true,
"dataSourceName": "ELIN_CRM"
}
注意:请求报文中的有特殊字符。
提交请求响应报文如下:
{
"success": true,
"message": "New success!",
"code": 200,
"timestamp": 1613486589972,
"result": {
"apiId": 39,
"apiCode": "CRM_CUSTOM_OBJECT",
"description": "查询E-Lin CRM自定义数据对象&<>'\"",
"owner": null,
"packageName": null,
"objectName": "SELECT Owner,Object_Name,Object_Type FROM Dba_Objects WHERE Owner = 'CRM'",
"overLoad": null,
"dataObjectType": 1,
"callScripts": "SELECT Owner,Object_Name,Object_Type FROM Dba_Objects WHERE Owner = 'CRM'",
"dataSourceId": 2,
"serviceId": null,
"enabledFlag": true,
"queryResultFlag": false,
"dataSourceName": "ELIN_CRM",
"dataBaseType": 1,
"argumentCount": 0,
"sequenceName": "CRM_COMMMON_IFACE_S"
}
}
1.2 调用通用查询Restful服务:
请求URL:http://localhost:6602/db/api/callquery?apiCode=CRM_CUSTOM_OBJECT
请求方法:POST
请求报文:{}
请求响应报文件参考下:
二、带参数Oracle SQL脚本
以如下Oracle SQL脚本为例:
SELECT Org_Type
,Org_Name
,Org_No
,Name_Pinyin
FROM Oa_Sys_Org_Element
WHERE Org_No = :1
OR :2 IS NULL
2.1 注册通用Restful API对象:
请求URL:http://localhost:6602/db/api/apiobject/add
请求报文为JSON格式参考如下,其中:1和:2表示二个参数。apiCode表示通用Restful API的代码。
{
"apiCode": "OA_FIND_ORG",
"description": "查询OA用户信息",
"objectName": "SELECT Org_Type ,Org_Name ,Org_No ,Name_Pinyin FROM Oa_Sys_Org_Element WHERE Org_No = :1 Or :2 IS NULL",
"dataObjectType": 1,
"dataSourceId": 2,
"enabledFlag": true,
"dataSourceName": "ELIN_CRM"
}
提交请求响应报文如下:
{
"success": true,
"message": "New success!",
"code": 200,
"timestamp": 1613475770814,
"result": {
"apiId": 37,
"apiCode": "OA_FIND_ORG",
"description": "查询OA用户信息",
"owner": null,
"packageName": null,
"objectName": "SELECT Org_Type ,Org_Name ,Org_No ,Name_Pinyin FROM Oa_Sys_Org_Element WHERE Org_No = :1 Or :2 IS NULL",
"overLoad": null,
"dataObjectType": 1,
"callScripts": "SELECT Org_Type ,Org_Name ,Org_No ,Name_Pinyin FROM Oa_Sys_Org_Element WHERE Org_No = :1 Or :2 IS NULL",
"dataSourceId": 2,
"serviceId": null,
"enabledFlag": true,
"queryResultFlag": false,
"dataSourceName": "ELIN_CRM",
"dataBaseType": 1,
"argumentCount": 0,
"sequenceName": "CRM_COMMMON_IFACE_S"
}
}
2.2 添加通用Restful API的参数:
请求URL:http://localhost:6602/db/api/oraargs/batchadd
请求报文为JSON格式参考如下:
[
{
"apiId": 37,
"argumentKey": "orgNo1",
"argumentName": "ORG_NO1",
"dataType": "VARCHAR2",
"description": "员工工号1",
"inOut": "IN",
"javaType": 12,
"position": 1
},{
"apiId": 37,
"argumentKey": "orgNo2",
"argumentName": "ORG_NO2",
"dataType": "VARCHAR2",
"description": "员工工号2",
"inOut": "IN",
"javaType": 12,
"position": 1
}
]
提交请求响应报文如下:
{
"success": false,
"message": "Batch added successfully[2],failure[0]",
"code": 500,
"timestamp": 1613476146649,
"result": {
"success": [
{
"apiId": 37,
"subprogramId": 0,
"argumentName": "ORG_NO1",
"position": 1,
"sequence": 0,
"dataLevel": 0,
"dataType": "VARCHAR2",
"defaulted": null,
"defaultValue": null,
"defaultLength": 0,
"inOut": "IN",
"dataLength": 0,
"dataPrecision": 0,
"dataScale": 0,
"radix": 0,
"characterSetName": null,
"typeOwner": null,
"typeName": null,
"typeSubname": null,
"typeLink": null,
"plsType": null,
"charLength": 0,
"charUsed": null,
"argumentKey": "orgNo1",
"description": "员工工号2",
"javaType": 12
},
{
"apiId": 37,
"subprogramId": 0,
"argumentName": "ORG_NO2",
"position": 1,
"sequence": 0,
"dataLevel": 0,
"dataType": "VARCHAR2",
"defaulted": null,
"defaultValue": null,
"defaultLength": 0,
"inOut": "IN",
"dataLength": 0,
"dataPrecision": 0,
"dataScale": 0,
"radix": 0,
"characterSetName": null,
"typeOwner": null,
"typeName": null,
"typeSubname": null,
"typeLink": null,
"plsType": null,
"charLength": 0,
"charUsed": null,
"argumentKey": "orgNo2",
"description": "员工工号2",
"javaType": 12
}
]
}
}
2.3 查看通用Restful API的OA_FIND_ORG的实例:
请求URL:http://localhost:6602/db/api/exmaple/OA_FIND_ORG,参考如下:
2.4 调用通用Restful API服务:
请求URL:http://localhost:6602/db/api/callquery?apiCode=OA_FIND_ORG
请求方式:POST
请求报文如下:
{
"orgNo1": "",
"orgNo2": ""
}
请求响应报文如下图:
或者参考如下:
三、Oracle EBS 初始化多组织访问和职责上下文的SQL脚本
以Oracle EBS系统的PO标准采购订单题头视图查询为例:
SELECT Pha.Segment1 Po_Number
,Revision_Num
,Vendor_Name
,Pha.Creation_Date
FROM Po_Headers_v Pha
WHERE Pha.Org_Id = :1
AND (Pha.Creation_Date >= :2)
3.1 注册通用Restful API对象:
请求URL:http://localhost:6602/db/api/apiobject/add
请求方法:POST
请求报文参考如下,其中:1为业务实体ID参数和:2为订单创建日期参数
{
"apiCode": "EBS_PO_HEADERS_QUERY",
"description": "查询Oracle EBS采购订单题头",
"objectName": "SELECT Pha.Segment1 Po_Number,Revision_Num,Vendor_Name,Pha.Creation_Date FROM Po_Headers_v Pha WHERE Pha.Org_Id = :1 AND (Pha.Creation_Date >= :2)",
"dataObjectType": 1,
"enabledFlag": true,
"dataSourceName": "ELIN_EBS_UAT"
}
提交请求响应报文参考如下:
{
"success": true,
"message": "New success!",
"code": 200,
"timestamp": 1613490267483,
"result": {
"apiId": 42,
"apiCode": "EBS_PO_HEADERS_QUERY",
"description": "查询Oracle EBS采购订单题头",
"owner": null,
"packageName": null,
"objectName": "SELECT Pha.Segment1 Po_Number,Revision_Num,Vendor_Name,Pha.Creation_Date FROM Po_Headers_v Pha WHERE Pha.Org_Id = :1 AND (Pha.Creation_Date >= :2)",
"overLoad": null,
"dataObjectType": 1,
"callScripts": "SELECT Pha.Segment1 Po_Number,Revision_Num,Vendor_Name,Pha.Creation_Date FROM Po_Headers_v Pha WHERE Pha.Org_Id = :1 AND (Pha.Creation_Date >= :2)",
"dataSourceId": 3,
"serviceId": null,
"enabledFlag": true,
"queryResultFlag": false,
"dataSourceName": "ELIN_EBS_UAT",
"dataBaseType": 1,
"argumentCount": 0,
"sequenceName": "EBS_COMMMON_IFACE_S"
}
}
3.2 添加通用Restful API对象EBS_PO_HEADERS_QUERY参数:
请求URL:http://localhost:6602/db/api/oraargs/batchadd
请求方法:POST
请求报文参考如下:
[
{
"apiId": 42,
"argumentKey": "orgId",
"argumentName": "ORG_ID",
"dataType": "NUMBER",
"description": "业务实体ID",
"inOut": "IN",
"javaType": 4,
"position": 1
},{
"apiId": 42,
"argumentKey": "creationDate",
"argumentName": "Creation_Date",
"dataType": "DATE",
"description": "创建日期",
"inOut": "IN",
"javaType": 91,
"position": 2
}
]
请求响应报文参考如下:
{
"success": false,
"message": "Batch added successfully[2],failure[0]",
"code": 500,
"timestamp": 1613490363842,
"result": {
"success": [
{
"apiId": 42,
"subprogramId": 0,
"argumentName": "ORG_ID",
"position": 1,
"sequence": 0,
"dataLevel": 0,
"dataType": "NUMBER",
"defaulted": null,
"defaultValue": null,
"defaultLength": 0,
"inOut": "IN",
"dataLength": 0,
"dataPrecision": 0,
"dataScale": 0,
"radix": 0,
"characterSetName": null,
"typeOwner": null,
"typeName": null,
"typeSubname": null,
"typeLink": null,
"plsType": null,
"charLength": 0,
"charUsed": null,
"argumentKey": "orgId",
"description": "业务实体ID",
"javaType": 4
},
{
"apiId": 42,
"subprogramId": 0,
"argumentName": "Creation_Date",
"position": 2,
"sequence": 0,
"dataLevel": 0,
"dataType": "DATE",
"defaulted": null,
"defaultValue": null,
"defaultLength": 0,
"inOut": "IN",
"dataLength": 0,
"dataPrecision": 0,
"dataScale": 0,
"radix": 0,
"characterSetName": null,
"typeOwner": null,
"typeName": null,
"typeSubname": null,
"typeLink": null,
"plsType": null,
"charLength": 0,
"charUsed": null,
"argumentKey": "creationDate",
"description": "创建日期",
"javaType": 91
}
]
}
}
3.3 查看通用Restfull API服务EBS_PO_HEADERS_QUERY实例
请求URL:http://localhost:6602/db/api/exmaple/EBS_PO_HEADERS_QUERY
请求方法:POST
请求响应报文参考如下:
{
"creationDate": "2021-02-16 23:28:50",
"orgId": 0
}
注意:参数日期格式必须为YYYY-MM-DD HH:MM:SS。
3.4 调用通用Restful API服务EBS_PO_HEADERS_QUERY。
请求URL:http://localhost:6602/db/api/callebsquery?apiCode=EBS_PO_HEADERS_QUERY
请求方法:POST
请求报文参考如下:
{
"applInitialize": {
"applShortName": "PO",
"orgId": 81,
"respApplId": 201,
"respId": 50626,
"userId": 1450
},
"arguments": {
"creationDate": "2020-10-01 00:00:00",
"orgId": 81
}
}
提交请求响应报文参考如下:
注意:日期字段默认返回为Timestamp,若想返回年月日时分秒,则需求要通过To_Char转换以字符串类型返回值。