Oracle解析複雜json的方法

標籤：oracle

當前在Oracle資料庫(11G之前的版本)解析json沒有可以直接使用的系統方法，網上流傳的PLSQL指令碼大多也只可以解析結構較單一的json串，對於結構複雜的json串還無法解析。如此一來導致即便可以在PL/SQL中調用遠程介面，但返回結果仍需傳給前台js或java等其它代碼進行處理，不太方便。

分析思路:

         1、在PL/SQL中寫json串，無需聲明json對象，只需直接拼接成格式正確的json字串即可，因此資料庫物件間json串的傳遞完全可以用varchar2或clob來代替。

         2、結構複雜的json串節點元素值基本上可以分為兩類：①仍為json串②json數組，因此我們只需對這兩種類型的json對象進行處理即可。

         3、在PL/SQL中處理json數組時，由於json元素是無序且可以重複的，因此我們需要對數群組成員進行索引並能夠擷取數組長度才可以對其迴圈處理。

         4、java中有很多可以方便解析json串的開源jar包，且資料庫支援匯入java類檔案，因此是否可以將java代碼編譯產生的class匯入資料處理json呢？

解決方案:

方法1:loadjava匯入java類進行解析

1、查看當前資料庫已匯入的java類檔案

650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504214047257-914676018.png" style="margin:0px;padding:0px;border:0px;" />

2、執行loadjava命令匯入處理json所需jar檔案，在此選擇org.json而不選擇fastjson或jackson的原因是該jar包沒有外部依賴且滿足功能的同時所需匯入類檔案較少。

--向資料庫匯入json相關jar包loadjava -r   -f  -u  scott/[email protected]:1521/orcl json.jar--刪除指定jar#dropjava -u scott/[email protected]:1521/orcl json.jar

在此，我們執行匯入,如下:

 650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504220213882-1708350568.png" style="margin:0px;padding:0px;border:0px;" />

細節:如果匯入的jar包存在外部依賴，會報如下異常，繼續匯入外部依賴只會使要匯入的jar包越來越多，最終還不一定可以匯入成功,得不償失，如下:

 650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504220333320-1777059659.png" style="margin:0px;padding:0px;border:0px;" />

3、匯入json.jar成功後再次查看當前已匯入的類檔案如下

 650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504214203086-812400083.png" style="margin:0px;padding:0px;border:0px;" />

4、在資料庫SQL視窗執行以下指令碼,建立java源碼對象

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

 1 create or replace and compile java source named "JsonUtil" as 2  3 import org.json.JSONArray; 4 import org.json.JSONException; 5 import org.json.JSONObject; 6 import java.lang.Integer; 7  8 public class JsonUtil { 9   10  //取json串單個節點值11   public static String getValue(String jsonStr,String nodeName){12     String nodeValue="";13     try {14       if(jsonStr==null||!jsonStr.startsWith("{")||!jsonStr.endsWith("}")){15         nodeValue="";16       }else{17         JSONObject obj =new JSONObject(jsonStr);18         nodeValue = obj.getString(nodeName);19       }20     } catch (JSONException e) {21       nodeValue="";22     }23     return nodeValue;24   }25   //取json數組長度便於迴圈處理26   public static Integer getArrayLength(String jsonArrayStr){27     Integer length=0;28     try {29       if(jsonArrayStr==null||!jsonArrayStr.startsWith("[")||!jsonArrayStr.endsWith("]")){30         length=0;31       }else{32         JSONArray jsonArr = new JSONArray(jsonArrayStr);33         length=jsonArr.length();34       }35     } catch (JSONException e) {36       length=0;37     }38     return length;39   }40 41   //取json數組第index個元素42   public static String getArrayValue(String jsonStr,Integer index){43     String nodeValue="";44     try {45       if(jsonStr==null||!jsonStr.startsWith("[")||!jsonStr.endsWith("]")){46         nodeValue="";47       }else{48         JSONArray jsonArr = new JSONArray(jsonStr);49         nodeValue=jsonArr.getString(index);50       }51     } catch (JSONException e) {52       nodeValue="";53     }54     return nodeValue;55   }56 }

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

建立成功後重新查詢可以看到對應的class檔案:

 650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504214526132-953348688.png" style="margin:0px;padding:0px;border:0px;" />

5、利用步驟4建立的class建立function(或procedure),在此為了使其跟目前資料庫已存在的json處理方法區分開，我們建立一個package，如下:

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

 1 create or replace package jsonpkg 2 as 3 function getval(jsonstr varchar2,nodename varchar2) return varchar2; 4 function getarrval(jsonArrayStr varchar2,seqNo number) return varchar2; 5 function getarrlen(jsonArrayStr varchar2) return number; 6 end jsonpkg; 7 / 8 create or replace package body jsonpkg 9 as10 function  getval(jsonstr varchar2,nodename varchar2) return varchar211 as language java name ‘JsonUtil.getValue(java.lang.String,java.lang.String) return java.lang.String‘;12    13 function getarrval(jsonArrayStr varchar2,seqNo number) return varchar214 as language java name ‘JsonUtil.getArrayValue(java.lang.String, java.lang.Integer) return java.lang.String‘;15 16 function getarrlen(jsonArrayStr varchar2) return number17 as language java name ‘JsonUtil.getArrayLength(java.lang.String) return java.lang.Integer‘;18 19 end jsonpkg;20 /

650) this.width=650;" src="/img/fz.gif" alt="複製代碼" style="margin:0px;padding:0px;border:none;" />

建立成功後可以查看包說明和主體:

650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504214922664-864046555.png" style="margin:0px;padding:0px;border:0px;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504214932523-1804702594.png" style="margin:0px;padding:0px;border:0px;" />

6、測試

①簡單json測試

 650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504221401914-448808445.png" style="margin:0px;padding:0px;border:0px;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504221440648-1359482983.png" style="margin:0px;padding:0px;border:0px;" />

650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504221951679-2082169547.png" style="margin:0px;padding:0px;border:0px;" />

②解析複雜json

 650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504221053867-526077869.png" style="margin:0px;padding:0px;border:0px;" />

至此，我們就可以很輕鬆的就取到json串中任意節點的值(如果節點值為數組則可以先計算數組長度再進行loop迴圈處理，或直接用getarrval方法取指定數組元素的值)。

 

方法2:安裝開源組件PL/JSON

: https://github.com/pljson/pljson

 650) this.width=650;" src="http://images2015.cnblogs.com/blog/752160/201705/752160-20170504221214289-2011843698.png" style="margin:0px;padding:0px;border:0px;" />

優點:安裝方便，解析方法較專業;缺點:新增資料庫物件較多，短期學習成本較高。文檔很詳細，在此不再贅述。

Oracle解析複雜json的方法