Import and Export datas from JSON string to Oracle with Java Stored Procedure

Hello EE members,

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.

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.

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.

1

Prepare the database
What we need :


You don't know Java Stored Procedure ?
Some links :
  - Oracle8i
  - Oracle9i
  - Oracle 10g
  - Oracle 11g




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.
I found the user SCOTT disabled and I needed to enable it by using the SYS account. I needed to change its password too :

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

Select all Open in new window

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

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

Select all Open in new window

As we need to import and export data from a table, let's create it :

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

Select all Open in new window

2

Using of Java classes and objects
Which one ?

You can download them here : JSON Java classes

People who like JSON have already seen this string at this address : JSON in JavaScript


- The first function insertJSON will do the "import job" :
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.

We create a JSONObject from the JSON string passed as argument :
The JSON object that contains rows data to import in a table

JSONObject strjson = new JSONObject(sJSON);

Select all Open in new window

We get the root, IT MUST BE the name of the table

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

Select all Open in new window

To read all rows of data, we use a JSON array

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

Select all Open in new window

We create a new JSON object to receive each "row"

JSONObject json;

Select all Open in new window

We use a mutable string to store each column name

StringBuffer columns = new StringBuffer();

Select all Open in new window

We use a mutable string to set associated the parameter of each column

StringBuffer qstmark = new StringBuffer();

Select all Open in new window

Our main loop. We read every JSON element {key:value} from the JSON array

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

Select all Open in new window

We delete content of the previous set of columns and parameters

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

Select all Open in new window

We get the current row

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

Select all Open in new window

We create an array to store the name of the columns (keys)

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

Select all Open in new window

We create an array to store the values for each column

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

Select all Open in new window

For each key, we store the column name and its value

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

Select all Open in new window

We don't add a separator (comma) for the first column and the first parameter

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

Select all Open in new window

We build our query :

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

Select all Open in new window

We set the values of each parameters. For this tutorial we assume all value are string

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

Select all Open in new window

We run the query

pstmt.executeUpdate();

Select all Open in new window

- The second function, selectJSON, allows us to get a JSON Object string from an SQL Query, the "export job" :
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.
We need to know the number of column : It will be the number of peer {key:value} for each json element

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

Select all Open in new window

We create an array of String to store all column name. Each column name will be a key :

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

Select all Open in new window

We create a JSON Object. It will receive each row :

JSONObject json = new JSONObject();

Select all Open in new window

We create a JSON Array to receive all JSON element :

JSONArray ajson = new JSONArray();

Select all Open in new window

We read all row returned by the query :

while(rset.next()) {

Select all Open in new window

For this tutorial we assume all value are string
For each column, we create a JSON entry {key:value} :

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

Select all Open in new window

We add the entry (at the origin, a result row) to the JSON Array

ajson.put(json);

Select all Open in new window

We create an empty JSON object and convert it to

JSONObject fJSON = new JSONObject();

Select all Open in new window

We associate the JSON elements with the root key :

fJSON = fJSON.put(root,ajson);

Select all Open in new window

We convert our JSON Object to a JSON String :

sJSON = fJSON.toString();

Select all Open in new window

We return the JSON String :

return sJSON;

Select all Open in new window

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.

3

Import the Java classes in the Oracle database
For that purpose we use the command line Oracle tools : loadjava (you should find this tools under ORACLE_HOME /bin folder)
We import them by using the SCOTT schema :

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

Select all Open in new window

4

Reference the Java function with PL/SQL
We have to create (link should be more appropriate for me) the Oracle procedure and function
Go to start SQL*Plus and connect with scott/tiger.

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

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)';

Select all Open in new window

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

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';

Select all Open in new window

5

Tests
Yes, it's time to test :

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.*"}]}');

Select all Open in new window

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

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

Select all Open in new window

SQL.java