Spring Boot 3.0.x和Oracle Database 23c(beta)和GraalVM Enterprise 22.3(JDK17)的综合体验(系列4)

Spring Boot 3.0.x和Oracle Database 23c(beta)和GraalVM Enterprise 22.3(JDK17)的综合体验(系列4)

Oracle Database 23c(beta)的JSON RELATIONAL DUALITY示例

推荐之前阅读

• 在AlmaLinux 8上安装Oracle Database 23c
• Spring Boot 3.0.x和Oracle Database 23c(beta)和GraalVM Enterprise 22.3(JDK17)的综合体验(预告篇)
• Spring Boot 3.0.x和Oracle Database 23c(beta)和GraalVM Enterprise 22.3(JDK17)的综合体验(系列1) - Spring Boot 3.0.x和Oracle Database 23c Database API for MongoDB的示例
• Spring Boot 3.0.x和Oracle Database 23c(beta)和GraalVM Enterprise 22.3(JDK17)的综合体验(系列2) - 使用MongoDB Compass管理Oracle Database 23c的Oracle Database API for MongoDB
• Spring Boot 3.0.x和Oracle Database 23c(beta)和GraalVM Enterprise 22.3(JDK17)的综合体验(系列3) - 往Oracle Database 23c(beta)里导入示例数据

创建JSON RELATIONAL DUALITY VIEW

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW EMPLOYEES_DV ASSELECT JSON {'EMPLOYEE_NAME' 	IS e.FIRST_NAME,'EMPLOYEE_ID'      IS e.EMPLOYEE_ID,'DEPARTMENT'       IS(SELECT JSON {'DEPARTMENT_NAME' IS d.DEPARTMENT_NAME,'DEPARTMENT_ID' IS d.DEPARTMENT_ID}FROM DEPARTMENTS d WITH UPDATEWHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID)}FROM EMPLOYEES e WITH INSERT UPDATE DELETE;CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW DEPARTMENTS_DV ASSELECT JSON {'DEPARTMENT_NAME'   IS d.DEPARTMENT_NAME,'DEPARTMENT_ID'     IS d.DEPARTMENT_ID,'EMPLOYEE' IS[ SELECT JSON {'EMPLOYEE_ID' 	IS e.EMPLOYEE_ID,'EMPLOYEE_NAME'  IS e.FIRST_NAME}FROM EMPLOYEES e WITH INSERT UPDATE DELETEWHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID]}FROM DEPARTMENTS d WITH INSERT;

查看JSON RELATIONAL DUALITY VIEW

SET LINESIZE 100
COL VIEW_NAME FORMAT a20
COL JSON_COLUMN_NAME FORMAT a20
COL ROOT_TABLE_NAME FORMAT a20
COL STATUS FORMAT a20SELECT VIEW_NAME,JSON_COLUMN_NAME,ROOT_TABLE_NAME,STATUS FROM USER_JSON_DUALITY_VIEWS;VIEW_NAME            JSON_COLUMN_NAME     ROOT_TABLE_NAME      STATUS
-------------------- -------------------- -------------------- --------------------
DEPARTMENTS_DV       DATA                 DEPARTMENTS          VALID
EMPLOYEES_DV         DATA                 EMPLOYEES            VALID

SELECT json_serialize(DBMS_JSON_SCHEMA.describe('DEPARTMENTS_DV') PRETTY) AS departments_dv_json_schema;DEPARTMENTS_DV_JSON_SCHEMA
----------------------------------------------------------------------------------------------------
{"title" : "DEPARTMENTS_DV","dbObject" : "HR.DEPARTMENTS_DV","dbObjectType" : "dualityView","type" : "object","properties" :{"DEPARTMENT_ID" :{"extendedType" : "number","sqlPrecision" : 4,"sqlScale" : 0},"DEPARTMENT_NAME" :{"extendedType" : "string","maxLength" : 30},"EMPLOYEE" :{"type" : "array","items" :{"type" : "object","properties" :{"EMPLOYEE_NAME" :{"extendedType" : "string","maxLength" : 20},"EMPLOYEE_ID" :{"extendedType" : "number","sqlPrecision" : 6,"sqlScale" : 0}},"required" :["EMPLOYEE_NAME","EMPLOYEE_ID"]}}},"required" :["DEPARTMENT_ID","DEPARTMENT_NAME"]
}

SET PAGES 500
SELECT json_serialize(data PRETTY) FROM DEPARTMENTS_DV;JSON_SERIALIZE(DATAPRETTY)
----------------------------------------------------------------------------------------------------
{"_id" : "FB03C10B00","_etag" : "01981851FBACCE2D0FF4800143A66CC65C8D1352C821B87ABE0EE10CCBB816AD2B010000000001A26F43","DEPARTMENT_NAME" : "Administration","DEPARTMENT_ID" : 10,"EMPLOYEE" :[{"EMPLOYEE_ID" : 200,"EMPLOYEE_NAME" : "Jennifer"}]
}{"_id" : "FB03C11500","_etag" : "010113F34CD32E80D9550D22DBBA8C587F305E94514EA8DB79AC1228630CBB0A59010000000001A26F43","DEPARTMENT_NAME" : "Marketing","DEPARTMENT_ID" : 20,"EMPLOYEE" :[{"EMPLOYEE_ID" : 201,"EMPLOYEE_NAME" : "Michael"},{"EMPLOYEE_ID" : 202,"EMPLOYEE_NAME" : "Pat"}]
}{"_id" : "FB03C11F00","_etag" : "0191F3BD2AA55672E267F2D30B1EE1C5ABC57EBCB2761D21584FFB3547D47A8F2A010000000001A26F43","DEPARTMENT_NAME" : "Purchasing","DEPARTMENT_ID" : 30,"EMPLOYEE" :[{"EMPLOYEE_ID" : 114,"EMPLOYEE_NAME" : "Den"},{"EMPLOYEE_ID" : 115,"EMPLOYEE_NAME" : "Alexander"},{"EMPLOYEE_ID" : 116,"EMPLOYEE_NAME" : "Shelli"},{"EMPLOYEE_ID" : 117,"EMPLOYEE_NAME" : "Sigal"},{"EMPLOYEE_ID" : 118,"EMPLOYEE_NAME" : "Guy"},{"EMPLOYEE_ID" : 119,"EMPLOYEE_NAME" : "Karen"}]
}......

SELECT json_serialize(data PRETTY) FROM EMPLOYEES_DV;{"_id" : "FB03C2022400","_etag" : "0122416C4AAB0F13A37688665862CAFB8ADAE20134CCA2B0EF9B9FA53A4C2801E0010000000001A26F6F","EMPLOYEE_NAME" : "Ki","EMPLOYEE_ID" : 135,"DEPARTMENT" :{"DEPARTMENT_NAME" : "Shipping","DEPARTMENT_ID" : 50}
}{"_id" : "FB03C2022500","_etag" : "018B1F31D1147FD9B35B46235C27E74C18559E9BF309F34C0743797FBEAF763E5A010000000001A26F6F","EMPLOYEE_NAME" : "Hazel","EMPLOYEE_ID" : 136,"DEPARTMENT" :{"DEPARTMENT_NAME" : "Shipping","DEPARTMENT_ID" : 50}
}......

往JSON RELATIONAL DUALITY VIEW插入数据

INSERT INTO DEPARTMENTS_DV VALUES ( '{"DEPARTMENT_NAME" : "Cloud","DEPARTMENT_ID"   : 999}');COMMIT;

SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID = 999;DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------999 Cloud

也可以通过JSON RELATIONAL DUALITY VIEW同时向EMPLOYEES表插入数据。（此处只阐述JSON RELATIONAL DUALITY VIEW的功能。由于此示例中EMPLOYEES表还有其它字段不能为空，所以会失败。）

INSERT INTO DEPARTMENTS_DV VALUES ( '{"DEPARTMENT_NAME" : "Cloud","DEPARTMENT_ID"   : 999,"EMPLOYEE" : [ {"EMPLOYEE_ID" : 1000,"EMPLOYEE_NAME" : "Admin"}]}');COMMIT;

开启SODA

able_schema(true);

declare col soda_collection_t; 
begin col := ate_dv_collection('employees_dv', 'EMPLOYEES_DV'); 
end;
/

SET SERVEROUTPUT ON
DECLAREl_coll_list  SODA_COLLNAME_LIST_T;
BEGINl_coll_list := DBMS_SODA.list_collection_names;IF l_coll_list.COUNT > 0 THENFOR i IN 1 .. l_coll_list.COUNT LOOPDBMS_OUTPUT.put_line(i || '  Nome Collection=' || l_coll_list(i));END LOOP;END IF;
END;
/1  Nome Collection=employees_dvPL/SQL procedure successfully completed.

curl -X GET -u 'hr:oracle' -H "Content-Type: application/json" localhost:8080/ords/hr/soda/latest/employees_dv

完结！