oracle 12c之使用Restful接口访问JSON数据

1、安装补丁20885778
先关闭数据库和监听

SQL> shutdown immediate;
[oracle@plm-db 20885778]$ lsnrctl stop

安装补丁

[oracle@plm-db patches]$ cd 20885778/
[oracle@plm-db patches]$ $ORACLE_HOME/OPatch/opatch apply
SQL> startup
[oracle@plm-db patches]$ cd $ORACLE_HOM/OPatch/
[oracle@plm-db OPatch]$ ./datapatch -verbose

验证补丁是否已经安装

[oracle@plm-db patches]$ $ORACLE_HOME/OPatch/opatch lsinventory

2、安装SODA for REST
首先需要安装JDK 1.7或更高版本
使用RPM包安装完成后,更改环境变量,并切换到新安装的JDK

vi ~/.bash_profile
export JAVA_HOME=/usr/java/jdk1.7.0_79/
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
[root@plm-db]# export JAVA_HOME=/usr/java/jdk1.7.0_79
[root@plm-db]# update-alternatives --install /usr/bin/java java $JAVA_HOME/bin/java 300
[root@plm-db]# update-alternatives --install /usr/bin/javac javac $JAVA_HOME/bin/javac 300
[root@plm-db]# update-alternatives config java
[root@plm-db]# update-alternatives config javac

安装SODA

[oracle@plm-db ords]$ java -jar ords.war install

安装成功后,如果选择start in standalone mode选项,服务就已经启动了。

在数据库中启用ORDS功能

SQL> exec ords.enable_schema;
commit;
PL/SQL procedure successfully completed.

为测试方便,停用ORDS的权限检查功能

SQL> exec ords.delete_privilege_mapping('oracle.soda.privilege.developer','/soda/*');
PL/SQL procedure successfully completed.

3、SODA的Restful接口测试
3.1 查询所有的集合

GET http://10.8.5.212:8080/ords/adam/soda/latest/

返回结果

{"items":[],"more":false}

3.2 增加books的集合

PUT http://10.8.5.212:8080/ords/adam/soda/latest/books

返回结果
无正文内容,状态码为201 Created

3.3 重新查询所有的集合

GET http://10.8.5.212:8080/ords/adam/soda/latest/

返回结果

{
  "items": [
    {
      "name": "books",
      "properties": {
        "schemaName": "ADAM",
        "tableName": "books",
        "keyColumn": {
          "name": "ID",
          "sqlType": "VARCHAR2",
          "maxLength": 255,
          "assignmentMethod": "UUID"
        },
        "contentColumn": {
          "name": "JSON_DOCUMENT",
          "sqlType": "BLOB",
          "compress": "NONE",
          "cache": true,
          "encrypt": "NONE",
          "validation": "STANDARD"
        },
        "versionColumn": {
          "name": "VERSION",
          "type": "String",
          "method": "SHA256"
        },
        "lastModifiedColumn": {
          "name": "LAST_MODIFIED"
        },
        "creationTimeColumn": {
          "name": "CREATED_ON"
        },
        "readOnly": false
      },
      "links": [
        {
          "rel": "canonical",
          "href": "http://10.8.5.212:8080/ords/adam/soda/latest/books"
        }
      ]
    }
  ],
  "more": false
}

3.4 创建单条记录

POST http://10.8.5.212:8080/ords/adam/soda/latest/books

发送内容,POSTMAN中,选择RAW类型,格式为JSON(application/json)
命令行:

curl -X POST --data-binary @book.json -H "Content-Type: application/json" http://10.8.5.212:8080/ords/adam/soda/latest/books
{
    "id": 1,
    "name": "PL/SQL入门",
    "author": "TOM",
    "press": "电子出版社"
}

返回结果

{
  "items": [
    {
      "id": "DAB416F0852447E0B9D387E709638056",
      "etag": "6F2437278889C5C77F213BBA67C56483B09860C288117E209174B662C2E31AD8",
      "lastModified": "2016-04-29T05:34:27.543724Z",
      "created": "2016-04-29T05:34:27.543724Z"
    }
  ],
  "hasMore": false,
  "count": 1
}

3.5 批量创建

POST http://10.8.5.212:8080/ords/adam/soda/latest/books?action=insert

发送内容

[
    {
        "id": 2,
        "name": "精通ORACLE 12c",
        "author": "老盖",
        "press": "机械工业出版社"
    },
    {
        "id": 3,
        "name": "Oracle性能优化",
        "author": "老白",
        "press": "人民邮电出版社"
    }
]

返回结果

{
  "items": [
    {
      "id": "E5FD85A128234E09A336C9247658BD21",
      "etag": "620EBDD8F7CC8730B884D4C6C6B169784B93A7506D0EB14D437FC3135A892EA7",
      "lastModified": "2016-04-29T05:42:29.979498",
      "created": "2016-04-29T05:42:29.979498"
    },
    {
      "id": "C1EC3A300D014B689DA8209ED46BF8D3",
      "etag": "8A6AF6AECD068C7617D08955DE4068B7E80DEE9949B9F9AC8573332312421CE4",
      "lastModified": "2016-04-29T05:42:29.979498",
      "created": "2016-04-29T05:42:29.979498"
    }
  ],
  "hasMore": false,
  "count": 2
}

3.6 根据ID查询单条记录

GET http://10.8.5.212:8080/ords/adam/soda/latest/books/DAB416F0852447E0B9D387E709638056

返回结果

{
  "id": 1,
  "name": "PL/SQL入门",
  "author": "TOM",
  "press": "电子出版社"
}

3.7 更新单条记录

PUT http://10.8.5.212:8080/ords/adam/soda/latest/books/DAB416F0852447E0B9D387E709638056

发送内容

{
  "id": 1,
  "name": "PL/SQL入门",
  "author": "Tom Kyte",
  "press": "电子出版社"
}

返回结果
正文无,状态码为200 OK

重新执行3.6的查询,返回结果为

{
  "id": 1,
  "name": "PL/SQL入门",
  "author": "Tom Kyte",
  "press": "电子出版社"
}

3.8 在数据库中查询上述步骤插入的3条记录
查看表结构

SQL> desc "books"
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(255)
 CREATED_ON                                NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                             NOT NULL TIMESTAMP(6)
 VERSION                                   NOT NULL VARCHAR2(255)
 JSON_DOCUMENT                                      BLOB

注意:因表名称为小写,查询的时候表名称必须加双引号

SELECT
  t.JSON_DOCUMENT.id,
  t.JSON_DOCUMENT.name,
  t.JSON_DOCUMENT.author,
  t.JSON_DOCUMENT.press
FROM "books" t

ID  NAME    AUTHOR  PRESS
1       Tom Kyte    电子出版社
2   精通ORACLE 12c    老盖  机械工业出版社
3   Oracle性能优化  老白  人民邮电出版社

3.9 删除单条记录

DELETE http://10.8.5.212:8080/ords/adam/soda/latest/books/C1EC3A300D014B689DA8209ED46BF8D3

返回结果
正文无,状态码为200 OK

3.10 查询所有记录

GET http://10.8.5.212:8080/ords/adam/soda/latest/books

返回结果

{
  "items": [
    {
      "id": "DAB416F0852447E0B9D387E709638056",
      "etag": "8CC5D855065CA3E33570F464BA506BA9DDB65E05E5F3C6D1EB178D96200BFCA5",
      "lastModified": "2016-04-29T05:47:07.156736Z",
      "created": "2016-04-29T05:34:27.543724Z",
      "links": [
        {
          "rel": "self",
          "href": "http://10.8.5.212:8080/ords/adam/soda/latest/books/DAB416F0852447E0B9D387E709638056"
        }
      ],
      "value": {
        "id": 1,
        "name": "PL/SQL入门",
        "author": "Tom Kyte",
        "press": "电子出版社"
      }
    },
    {
      "id": "E5FD85A128234E09A336C9247658BD21",
      "etag": "620EBDD8F7CC8730B884D4C6C6B169784B93A7506D0EB14D437FC3135A892EA7",
      "lastModified": "2016-04-29T05:42:29.979498Z",
      "created": "2016-04-29T05:42:29.979498Z",
      "links": [
        {
          "rel": "self",
          "href": "http://10.8.5.212:8080/ords/adam/soda/latest/books/E5FD85A128234E09A336C9247658BD21"
        }
      ],
      "value": {
        "id": 2,
        "name": "精通ORACLE 12c",
        "author": "老盖",
        "press": "机械工业出版社"
      }
    }
  ],
  "hasMore": false,
  "count": 2,
  "offset": 0,
  "limit": 100,
  "totalResults": 2,
  "links": []
}

3.11 限制返回条目数和字段

GET http://10.8.5.212:8080/ords/adam/soda/latest/books?fields=value&limit

返回结果:

{
  "items": [
    {
      "value": {
        "id": 1,
        "name": "PL/SQL入门",
        "author": "Tom Kyte",
        "press": "电子出版社"
      }
    }
  ],
  "hasMore": true,
  "count": 1,
  "offset": 0,
  "limit": 1
}

3.12 按条件查询

POST http://10.8.5.212:8080/ords/adam/soda/latest/books?action=query

发送内容

{
    "id": 1
}

返回结果:

{
  "items": [
    {
      "id": "DAB416F0852447E0B9D387E709638056",
      "etag": "8CC5D855065CA3E33570F464BA506BA9DDB65E05E5F3C6D1EB178D96200BFCA5",
      "lastModified": "2016-04-29T05:47:07.156736Z",
      "created": "2016-04-29T05:34:27.543724Z",
      "value": {
        "id": 1,
        "name": "PL/SQL入门",
        "author": "Tom Kyte",
        "press": "电子出版社"
      }
    }
  ],
  "hasMore": false,
  "count": 1
}

组合查询条件

POST http://10.8.5.212:8080/ords/adam/soda/latest/books?action=query

发送内容

{
    "$or": [
        {"id": 1},
        {"id": 2}
    ]
}

返回结果

{
  "items": [
    {
      "id": "DAB416F0852447E0B9D387E709638056",
      "etag": "8CC5D855065CA3E33570F464BA506BA9DDB65E05E5F3C6D1EB178D96200BFCA5",
      "lastModified": "2016-04-29T05:47:07.156736Z",
      "created": "2016-04-29T05:34:27.543724Z",
      "value": {
        "id": 1,
        "name": "PL/SQL入门",
        "author": "Tom Kyte",
        "press": "电子出版社"
      }
    },
    {
      "id": "E5FD85A128234E09A336C9247658BD21",
      "etag": "620EBDD8F7CC8730B884D4C6C6B169784B93A7506D0EB14D437FC3135A892EA7",
      "lastModified": "2016-04-29T05:42:29.979498Z",
      "created": "2016-04-29T05:42:29.979498Z",
      "value": {
        "id": 2,
        "name": "精通ORACLE 12c",
        "author": "老盖",
        "press": "机械工业出版社"
      }
    }
  ],
  "hasMore": false,
  "count": 2
}

参考资料:
SODA for REST Installation
Getting Started with SODA for REST
Using REST to access JSON data in the Oracle Database (SODA for REST)

Comments are closed.