NOTE: Oct 2021. Updated to maven repo orajson version 1.1.7.1
Oracle database has native JSON support, including a JSON datatype. Simple Document Access API is provided for several programming languages such as Java, Node, C, REST, Phyton, PL/SQL
In this post we are working with an Autonomous Database in OCI, but same work applies to on-prem databases, Exadata Cloud at Customer and other flavours.
STEP 0: clone repo
git clone this repo for your convenience
Please note that jdbc drivers are located in the root directory of the repo, anyway, you can download them from here
STEP 1: create or grab a db instance
Create an ATP/AWD or Autonomous JSON instance in OCI
STEP 2: Install oracle client
Download and Install OracleInstanClient in client machine in a directory <oracleinstantclientinstalldir>
STEP 3: download connection wallet
Download the autonomous database connection wallet and unzip it to <oracleinstantclientinstalldir>/network/admin
STEP 4: set env variables
Export variable TNS_ADMIN
export TNS_ADMIN=<oracleinstantclientinstalldir>/network/admin
STEP 5: test db connection
(base) mbpj:instantclient_19_3 javiermugueta$ export TNS_ADMIN=/Users/javiermugueta/instantclient_19_3/network/admin (base) mbpj:instantclient_19_3 javiermugueta$ ./sqlplus invictus@dbpre_tp SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 15 03:29:07 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Last Successful login time: Fri Jan 15 2021 03:28:42 +01:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.5.0.0.0 SQL>
STEP 6: mavenize it all
Install maven if you dont have it
STEP 7: create project from archetype
Note: you dont need to do this step beacuse project is already created in the git repo, here is how it was created and pom updated with needed dependencies
mvn archetype:generate -DgroupId=javiermugueta.blog -DartifactId=jsontest -DarchetypeArtifactId=maven-archetype-quickstart -DarchetypeVersion=1.4 -DinteractiveMode=false
Modify pom file with the dependency highlighted in bold down here:
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.oracle.database.soda</groupId>
<artifactId>orajsoda</artifactId>
<version>1.1.7.1</version>
</dependency>
</dependencies>
STEP 8: Locate testSoda.java class source code
Locate source code testSoda.java in cloned git repo
STEP 9: adapt testSoda.java to your environment
(highlighted in bold here below)
package javiermugueta.blog;
import java.util.UUID;
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.soda.rdbms.OracleRDBMSClient;
import oracle.soda.OracleDatabase;
import oracle.soda.OracleCursor;
import oracle.soda.OracleCollection;
import oracle.soda.OracleDocument;
import oracle.soda.OracleException;
import java.util.Properties;
public class testSoda
{
public static void main(String[] arg)
{
String url = "jdbc:oracle:thin:@tnsnamealias from tnsnames.ora in the wallet";
Properties props = new Properties();
props.setProperty("user", "youruser");
props.setProperty("password", "yourpasswd");
Connection conn = null;
try
STEP 10: package code
Locate in project directory and maven package
(base) mbpj:soda4java javiermugueta$ pwd /Users/javiermugueta/Library/Mobile Documents/com~apple~CloudDocs/INTERCHANGE/TUTORIALS/soda4java (base) mbpj:soda4java javiermugueta$ cd jsontest/ (base) mbpj:jsontest javiermugueta$ mvn package [INFO] Scanning for projects… [INFO] [INFO] --------------------< javiermugueta.blog:jsontest >--------------------- [INFO] Building jsontest 1.0-SNAPSHOT ... [INFO] --- maven-jar-plugin:3.0.2:jar (default-jar) @ jsontest --- [INFO] Building jar: /Users/javiermugueta/Library/Mobile Documents/com~apple~CloudDocs/INTERCHANGE/TUTORIALS/soda4java/jsontest/target/jsontest-1.0-SNAPSHOT.jar [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 3.219 s [INFO] Finished at: 2021-01-15T03:46:01+01:00 [INFO] ------------------------------------------------------------------------ (base) mbpj:jsontest javiermugueta$
STEP 11: Test artifact
(base) mbpj:jsontest javiermugueta$ cd .. (base) mbpj:soda4java javiermugueta$ pwd /Users/javiermugueta/Library/Mobile Documents/com~apple~CloudDocs/INTERCHANGE/TUTORIALS/soda4java (base) mbpj:soda4java javiermugueta$ ll total 22360 drwxr-xr-x@ 15 javiermugueta staff 480 Jan 15 03:49 . -rw-r--r--@ 1 javiermugueta staff 6148 Jan 15 03:49 .DS_Store drwxr-xr-x@ 6 javiermugueta staff 192 Jan 15 03:46 jsontest ... -rw-r--r--@ 1 javiermugueta staff 265130 Dec 21 09:43 xdb.jar -rw-r--r--@ 1 javiermugueta staff 1933747 Dec 21 09:43 xmlparserv2.jar drwxr-xr-x 110 javiermugueta staff 3520 Nov 19 2018 .. (base) mbpj:soda4java javiermugueta$ java -cp jsontest/target/jsontest-1.0-SNAPSHOT.jar:*:. javiermugueta.blog.testSoda
If you leave the code as it is, a JSON document with well known ticketId is created, then 1000 JSON docs are created with random ticketID, then all the documents are full scanned (but skipped after first occurrence) and, finally, a search by specific ticketId is performed and result shown in stdout.
...
// a new ticket document with fixed ticketId
OracleDocument doc = db.createDocumentFromString("{\"ticketId\" : \"" + "000001" + "\", \"ref\" : \"0095454353\", \"color\" : \"r023\", \"price\" : \"99.00\"}");
col.insert(doc);
// a bucnh of ticket documents with random ticketId
for (int i = 0; i < 1000; i++){
String ticketId = UUID.randomUUID().toString();
doc = db.createDocumentFromString("{\"ticketId\" : \"" + ticketId + "\", \"ref\" : \"0095454353\", \"color\" : \"r023\", \"price\" : \"99.00\"}");
//OracleDocument doc = db.createDocumentFromString("123", "{ \"ref\" : \"0095454353\", \"color\" : \"r023\", \"price\" : \"99.00\"}");
col.insert(doc);
}
// Find all documents in the collection.
OracleCursor c = null;
try
{
c = col.find().getCursor();
OracleDocument resultDoc;
while (c.hasNext())
{
resultDoc = c.next();
System.out.println ("Key: " + resultDoc.getKey());
...
System.out.println ("\n");
break; // dont wanna lose my time waiting.. 🙂
}
// search by json attribute
OracleDocument filterSpec =db.createDocumentFromString("{ \"ticketId\" : \"000001\"}");
c = col.find().filter(filterSpec).getCursor();
while (c.hasNext()) {
resultDoc = c.next();
System.out.println ("Document key: " + resultDoc.getKey() + "\n" +
" document content: " + resultDoc.getContentAsString());
break; // dont wanna lose my time waiting.. 🙂
}
...
More info here and in many other places…
Hope it helps!!! 🙂
approaching off the bow 