<

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

Published on
30,099 Points
21,999 Views
1 Endorsement
Last Modified:
Approved
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 :

An Oracle database with Java enabled so goodbye if you're using Oracle XE database because, as you know, it doesn't support Java Stored Procedure

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

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

SCOTT/TIGER reactivated (non-production server!)

The table 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.
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

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

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

Open in new window


2

Using of Java classes and objects
Which one ?

Java JSON classes : java class to play with java JSON object
You can download them here : JSON Java classes

A JSON String : For testing purpose I propose you to play with the myJSONObject string
People who like JSON have already seen this string at this address : JSON in JavaScript

A SQL class composed of two functions :

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

Open in new window

We get the root, IT MUST BE the name of the table
String root = strjson.names().getString(0);

Open in new window

To read all rows of data, we use a JSON array
JSONArray jsonArray = (JSONArray )strjson.get(root);

Open in new window

We create a new JSON object to receive each "row"
JSONObject json;

Open in new window

We use a mutable string to store each column name
StringBuffer columns = new StringBuffer();

Open in new window

We use a mutable string to set associated the parameter of each column
StringBuffer qstmark = new StringBuffer();

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++) {

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

Open in new window

We get the current row
json = (JSONObject )jsonArray.get(j);

Open in new window

We create an array to store the name of the columns (keys)
String key[] = new String[json.length()];

Open in new window

We create an array to store the values for each column
String val[] = new String[json.length()];

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

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("?");
}

Open in new window

We build our query :
String insertString = "INSERT INTO " + root + " (" + columns.toString() + ")";
insertString += " VALUES (" + qstmark.toString() + ")";
pstmt = conn.prepareStatement(insertString);

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] ); 
}

Open in new window

We run the query
pstmt.executeUpdate();

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

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

Open in new window

We create a JSON Object. It will receive each row :
JSONObject json = new JSONObject();

Open in new window

We create a JSON Array to receive all JSON element :
JSONArray ajson = new JSONArray();

Open in new window

We read all row returned by the query :
while(rset.next()) {

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])); 
}

Open in new window

We add the entry (at the origin, a result row) to the JSON Array
ajson.put(json);

Open in new window

We create an empty JSON object and convert it to
JSONObject fJSON = new JSONObject();

Open in new window

We associate the JSON elements with the root key :
fJSON = fJSON.put(root,ajson);

Open in new window

We convert our JSON Object to a JSON String :
sJSON = fJSON.toString();

Open in new window

We return the JSON String :
return sJSON;

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

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

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

Open in new window


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

Open in new window


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;

Open in new window

result
SQL.java
1
Author:leakim971
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free