<

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

Published on
28,035 Points
19,935 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
Comment
Author:leakim971
0 Comments

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Join & Write a Comment

The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month