Oracle11g对Json的处理很不方便,如果json中带有一些难以匹配的字符,如时间等,就非常麻烦。
但是Oracle支持导入Java函数,用面向对象的方法可以简化Json的查询使用过程。
一、使用Java类(外部依赖包Org.Json或者FastJson或者Gson)
“Org.Json”
1、导入java包(从此处为org.Json的示例,Org.Json下载地址为:本人自己上传的,可以在百度其他地方找到)
用java写好处理json的函数之后,测试后存在oracle中。
在cmd中进行操作
--向数据库导入json相关jar包
loadjava -r -f -u user/password@xxx.xxx.xxx.xxx:1521/orcl json.jar
--删除指定jar
#dropjava -u user/password@xxx.xxx.xxx.xxx:1521/orcl json.jar
导入成功后是没有明显提示的,至少会退出那一条命令,大约几秒钟就可以了
2、登录所要操作的数据库中,创建具体的java包和函数
可以用sqlPlus 或者 plSQL、Navicat等一切可以执行sql的工具。
执行以下sql语句
create or replace and compile java source named "JsonUtil" as
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import java.lang.Integer;
public class JsonUtil {
//取json串单个节点值
public static String getValue(String jsonStr,String nodeName){
String nodeValue="";
try {
if(jsonStr==null||!jsonStr.startsWith("{")||!jsonStr.endsWith("}")){
nodeValue="";
}else{
JSONObject obj =new JSONObject(jsonStr);
nodeValue = obj.getString(nodeName);
}
} catch (JSONException e) {
nodeValue="";
}
return nodeValue;
}
}
/
create or replace package jsonpkg
as
function getval(jsonstr varchar2,nodename varchar2) return varchar2;
end jsonpkg;
/
create or replace package body jsonpkg
as
function getval(jsonstr varchar2,nodename varchar2) return varchar2
as language java name 'JsonUtil.getValue(java.lang.String,java.lang.String) return java.lang.String';
end jsonpkg;
/
下面就是查询具体键值对中值的sql语句
//jsonpkg.getval(JSON所在列名,'需要查询的json中的键')
select distinct jsonpkg.getval(t.JSONSTR,'Name') from TableTest t;
“Gson”
对于Gson 一样的需要自己写jar处理函数,以下为作者原创,转载请注明出处:
1、导入需要用到的jar包(Gson.2.8.2.jar可以在Gson官网下载:百度Gson官网)
再cmd中像上面一样导入这个jar包,如果你用的是Oracle11g且导入出现问题,请参照我的下一篇博文:导入Jar包出错
cmd-> loadjava -f -r -u user/pass@orcl ****.jar
2、导入后在sqlplus中执行以下sql:
create or replace and compile java source named "GsonUtil" as
import com.google.gson.*;
public class GsonUtil {
public static String TryGetJSON(String JSONstr,String sKey)
{
JsonElement resEle = new JsonParser().parse(JSONstr).getAsJsonObject().get(sKey);
if (resEle == null || resEle == JsonNull.INSTANCE)
return null;
String value = resEle.getAsString();
if(value!=null)
return value;
return null;
}
public static int GetJSONValue(int bsm,String JSONstr,String sKey,String sValue)
{
JsonElement resEle = new JsonParser().parse(JSONstr).getAsJsonObject().get(sKey);
JsonNull NullJson = JsonNull.INSTANCE;
if (resEle == null || resEle == JsonNull.INSTANCE)
return -1;
String tValue = resEle.getAsString();
if(tValue.equals(sValue))
return bsm;
return -1;
}
}
/
create or replace package gsonpkg
as
function getval(jsonstr varchar2,nodename varchar2) return varchar2;
function getvalue(bsm number,jsonstr varchar2,nodeKey varchar2,nodeValue varchar2) return number;
end gsonpkg;
/
create or replace package body gsonpkg
as
function getval(jsonstr varchar2,nodename varchar2) return varchar2
as language java name 'GsonUtil.TryGetJSON(java.lang.String,java.lang.String) return java.lang.String';
function getvalue(bsm number,jsonstr varchar2,nodeKey varchar2,nodeValue varchar2) return number
as language java name 'GsonUtil.GetJSONValue(int,java.lang.String,java.lang.String,java.lang.String) return int';
end gsonpkg;
/
创建完检查包无误后,查询Json的sql语句为:
select distinct gsonpkg.getVal(t.bqxx,'PC') from t_markssource t;
select * from t_markssource t1 where t1.ID in (select distinct gsonpkg.getvalue(t.id,t.bqxx,'PC','02') from t_markssource t) and t1.ID in(select distinct gsonpkg.getvalue(t.id,t.bqxx,'PC','03') from t_markssource t);
FastJson(貌似有过多的外部依赖jar包),此处不作介绍了。
二、安装PL/JSON
pljson的好处是容易安装,函数都已经写好了,但是学习很难。本人琢磨一下午才弄了一点皮毛来用。
pl/json的下载地址为https://github.com/pljson/pljson
解压后 用sqlplus 运行@install.sql,
方法:cmd->输入:
sqlplus user/password@orcl as sysdba @…/../install.sql
【如果安装显示什么无法找到路径src/**.typ,我也不知道什么原因,你可以打开install.sql,将路径全部改成根目录下面,然后把对于目录下的文件移动到根目录下再执行。】
安装成功后,可以在package中看到以下几个包,概念层的东西我就不说了,直接说怎么用。
在查询语句中,可以如下方法使用:
select distinct json_ext.get_string(json(t.bqxx),'JSR') from t_markssource t ;
其中,t_markssource是表名,json(t.bqxx)是指将t表的bqxx这一列当成json类,这个用法真是叼叼的,我琢磨了一下午才猜出来,破文档根本没有说明,’JSR’是json里需要找的key,获得的是json里的value,前面加distinct可以区分唯一值。
至于其他函数,也是同样的道理,只是需要在传入参数处确保参数类型为json类,或者json_list类,或者json_value类。
需要注意的是,查出来的值有可能是空的,而且大小写、空格有很严格的限制,在实际使用中需要开发者再去精确地检验一次。