E-Lin通用微服务平台-Oracle SQL脚本注册Restful API服务

       通过注册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转换以字符串类型返回值
 

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

AlanChen

您的鼓励是我创作的源泉

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值