Oracle对Java查询的几种方式

Oracle11g对Json的处理很不方便，如果json中带有一些难以匹配的字符，如时间等，就非常麻烦。
但是Oracle支持导入Java函数，用面向对象的方法可以简化Json的查询使用过程。

一、使用Java类(外部依赖包Org.Json或者FastJson或者Gson)

“Org.Json”

1、导入java包(从此处为org.Json的示例，Org.Json下载地址为：http://download.csdn.net/download/loveyou388i/10013186本人自己上传的，可以在百度其他地方找到)
用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处理函数，以下为作者原创，转载请注明出处：http://blog.csdn.net/loveyou388i/article/details/78176431

1、导入需要用到的jar包（Gson.2.8.2.jar可以在Gson官网下载：百度Gson官网）
再cmd中像上面一样导入这个jar包，如果你用的是Oracle11g且导入出现问题，请参照我的下一篇博文:导入Jar包出错
http://blog.csdn.net/loveyou388i/article/details/78206524

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类。

需要注意的是，查出来的值有可能是空的，而且大小写、空格有很严格的限制，在实际使用中需要开发者再去精确地检验一次。