使用Java存储过程将数据从JSON字符串导入和导出到Oracle

Hello EE members,

您好EE成员，

I'm proud to propose to you this article which will demonstrate to you how to import and export data between an Oracle Database and a basic JSON string.

我很自豪地向您推荐这篇文章，它将向您展示如何在Oracle数据库和基本JSON字符串之间导入和导出数据。

As you read, please keep in mind this is not regarding the storing of the JSON object as a CLOB, but rather the using of each key as a column name and the root element as the table name for import.

在阅读时，请记住，这与将JSON对象存储为CLOB无关，而是将每个键用作列名，将根元素用作表名进行导入。

I'm neither a Java nor an Oracle guru, but in my opinion it would be long work to write a full JSON library with PL/SQL able to support import and export of a JSON String; therefore, I choose to use Java Stored Procedures instead.

我既不是Java专家，也不是Oracle专家，但我认为用PL / SQL编写一个完整的JSON库以支持JSON字符串的导入和导出将是一项漫长的工作。 因此，我选择使用Java存储过程代替。

1个 (1)

Prepare the database

准备数据库

What we need :

我们需要的 ：

An Oracle database with Java

使用Java的Oracle数据库

如果您正在使用Oracle XE数据库，则 enabled so goodbye if you're using Oracle XE database because, as you know, it doesn't support 启用了“再见”，因为您知道，它不支持 Java Stored Procedure Java存储过程

You don't know Java Stored Procedure ?

您不知道Java存储过程吗？

Some links :

  - Oracle8i

-Oracle8i

  - Oracle9i

-Oracle9i

  - Oracle 10g

- Oracle10g

  - Oracle 11g

- Oracle11g

A user with sufficient  privilege to create, delete and replace procedures and the java.net.SocketPermission

具有足够权限创建，删除和替换过程以及java.net.SocketPermission的用户

SCOTT/TIGER reactivated (non-production server!)

SCOTT / TIGER重新激活（非生产服务器！）

The table

桌子

从JSON字符串接收或提取数据的 bindings which receive or extract data from JSON String 绑定

Last time I did some programming on Oracle, it was on the 8i : I decided to install Oracle 11g on my favourite OS, windows 2000 which run on a virtual machine.

上一次我在Oracle上进行编程时，它是在8i上进行的：我决定在我最喜欢的操作系统Windows 2000上安装Oracle 11g，该操作系统在虚拟机上运行。

I found the user SCOTT disabled and I needed to enable it by using the SYS account. I needed to change its password too :

我发现用户SCOTT已禁用，我需要使用SYS帐户启用它。 我也需要更改其密码：

ALTER USER SCOTT ACCOUNT UNLOCK;
ALTER USER SCOTT IDENTIFIED BY TIGER; -- NON PRODUCTION DATABASE

Grant permission to Scott to use java.net.SocketPermission :

向Scott授予使用java.net.SocketPermission的权限：

dbms_java.grant_permission('SCOTT','SYS:java.net.SocketPermission','127.0.0.1:1521','resolve');

CREATE TABLE SCOTT.BINDINGS
(
  IRCEVENT  VARCHAR2(24 BYTE),
  METHOD    VARCHAR2(24 BYTE),
  REGEX     VARCHAR2(24 BYTE)
)

2 (2)

Using of Java classes and objects

Java类和对象的使用

Which one ?

哪一个 ？

Java JSON classes : java class to play with java JSON object

Java JSON类：与Java JSON对象一起玩的Java类

JSON Java classes JSON Java类

A JSON String : For testing purpose I propose you to play with the

JSON字符串：出于测试目的，我建议您使用

myJSONObject string myJSONObject字符串

JSON in JavaScript JavaScript中的JSON

A SQL class composed of two functions :

一个由两个功能组成SQL类：

- The first function insertJSON will do the "import job" :

-第一个函数insertJSON将执行“导入工作”：

You pass to it a connexion string to connect to the database, the credentials to login and the JSON string to import. We will see later an example.

您将连接数据库的连接字符串，登录凭据和导入的JSON字符串传递给它。 我们将在后面看到一个示例。

We create a JSONObject from the JSON string passed as argument :

我们根据作为参数传递的JSON字符串创建一个JSONObject：

The JSON object that contains rows data to import in a table

包含要导入表中的行数据的JSON对象

JSONObject strjson = new JSONObject(sJSON);

String root = strjson.names().getString(0);

JSONArray jsonArray = (JSONArray )strjson.get(root);

JSONObject json;

StringBuffer columns = new StringBuffer();

StringBuffer qstmark = new StringBuffer();

for(int j=0;j<jsonArray.length();j++) {

columns.delete(0, columns.length());
qstmark.delete(0, qstmark.length());

json = (JSONObject )jsonArray.get(j);

String key[] = new String[json.length()];

String val[] = new String[json.length()];

for(Iterator i = json.keys();i.hasNext();k++) {
   key[k] = (String )i.next();
   val[k] = json.getString(key[k]);

if(k>0) {
    columns.append("," + key[k]);
    qstmark.append(",?");
}
else {
    columns.append(key[k]);
    qstmark.append("?");
}

String insertString = "INSERT INTO " + root + " (" + columns.toString() + ")";
insertString += " VALUES (" + qstmark.toString() + ")";
pstmt = conn.prepareStatement(insertString);

int parameterNumber;
for(int p=0;p<val.length;p++) {
    parameterNumber = p + 1; 
    pstmt.setString( parameterNumber, val[p] ); 
}

pstmt.executeUpdate();

- The second function, selectJSON, allows us to get a JSON Object string from an SQL Query, the "export job" :

-第二个函数selectJSON允许我们从SQL查询“导出作业”中获取JSON对象字符串：

You pass to it a connexion string to connect to the database, the credentials to login, the name of the root key of the JSON Object and a SQL query. We will see later an example.

您将连接数据库的连接字符串，登录凭据，JSON对象的根密钥名称和SQL查询传递给它。 我们将在后面看到一个示例。

We need to know the number of column : It will be the number of peer {key:value} for each json element

我们需要知道column的数目：它将是每个json元素的对等{key：value}的数目

ResultSetMetaData rsmd = rset.getMetaData();
int numColumns  = rsmd.getColumnCount();

String key[] = new String[numColumns];
int columnNumber;
for(int i=0;i<numColumns+1;i++) { 
    columnNumber = i + 1; 
    key[i] = rsmd.getColumnName(columnNumber); 
}

JSONObject json = new JSONObject();

JSONArray ajson = new JSONArray();

while(rset.next()) {

For each column, we create a JSON entry {key:value} :

对于每一列，我们创建一个JSON条目{key：value}：

for(int i=0;i<numColumns;i++) { 
    json.putOpt(key[i], 
    rset.getString(key[i])); 
}

ajson.put(json);

JSONObject fJSON = new JSONObject();

fJSON = fJSON.put(root,ajson);

sJSON = fJSON.toString();

return sJSON;

You can found the full source code of the class org.json.oracle.SQL (SQL.java) at the end of this tutorial as file attachment.

您可以在本教程的结尾找到org.json.oracle.SQL（SQL.java）类的完整源代码，作为文件附件。

3 (3)

将Java类导入Oracle数据库中

For that purpose we use the command line Oracle tools : loadjava (you should find this tools under ORACLE_HOME /bin folder)

为此，我们使用命令行Oracle工具： loadjava （您应该在ORACLE_HOME / bin文件夹下找到此工具）

We import them by using the SCOTT schema :

我们使用SCOTT模式导入它们：

C:\>CD E:\org\json
C:\>E:
E:\>loadjava -user scott/tiger JSONArray.java
E:\>loadjava -user scott/tiger JSONException.java
E:\>loadjava -user scott/tiger JSONObject.java
E:\>loadjava -user scott/tiger JSONString.java
E:\>loadjava -user scott/tiger JSONTokener.java
E:\>loadjava -user scott/tiger JSONWriter.java
E:\oracle\>loadjava -user scott/tiger SQL.java

4 (4)

使用PL / SQL引用Java函数

We have to create (link should be more appropriate for me) the Oracle procedure and function

我们必须创建（链接应该更适合我）Oracle过程和函数

Go to start SQL*Plus and connect with scott/tiger.

转到启动SQL * Plus并与scott / tiger连接。

Create/link the INSERT_JSON PL/SQL procedure with the JAVA Stored Procedure :

创建/链接INSERT_JSON PL / SQL过程和JAVA存储过程：

CREATE OR REPLACE PROCEDURE INSERT_JSON (
    connection IN VARCHAR2, username IN VARCHAR2, passwd IN VARCHAR2, sJSON IN VARCHAR2) 
AS LANGUAGE JAVA NAME 'org.json.oracle.SQL.insertJSON(
    java.lang.String, java.lang.String, java.lang.String, java.lang.String)';

Create/link the SELECT_JSON PL/SQL procedure with the JAVA Stored Procedure :

创建/链接SELECT_JSON PL / SQL过程与JAVA存储过程：

create or replace FUNCTION SELECT_JSON (
connection IN VARCHAR2, username IN VARCHAR2, passwd IN VARCHAR2, root IN VARCHAR2, sJSON IN VARCHAR2) 
RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'org.json.oracle.SQL.selectJSON+(
    java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
return java.lang.String';

5 (5)

Tests

测验

Yes, it's time to test :

是的，该测试了：

Import :

导入：

CALL INSERT_JSON('jdbc:oracle:thin:@localhost:1521:orcl', 'scott', 'tiger', 
'{"bindings":[{"ircEvent":"PRIVMSG","method":"newURI","regex":"^http://.*"},
{"ircEvent":"PRIVMSG","method":"deleteURI","regex":"^delete.*"},
{"ircEvent":"PRIVMSG","method":"randomURI","regex":"^random.*"}]}');

With export we'll able to check the import test ran fine.

通过导出，我们可以检查导入测试是否运行良好。

Export :

出口 ：

SELECT
   SELECT_JSON('jdbc:oracle:thin:@localhost:1521:orcl','scott','tiger','bindings', 'SELECT * FROM BINDINGS')
FROM DUAL;

SQL.java SQL.java

翻译自: https://www.experts-exchange.com/articles/2715/Import-and-Export-datas-from-JSON-string-to-Oracle-with-Java-Stored-Procedure.html