Java Embedded in OracleDB - 2

After the first posting of Java Embedded in Oracle DB, here is little advanced one with IN/OUT BLOB.

Note: 

1. Make sure java source code compiles without warnings, when you do create & compile java source

2. Sometimes you may have to run fetching query second time to avoid java class conflicts

--Java code

--drop java source OraHashMap;

create or replace and compile java source named OraHashMap as

import java.lang.Exception;

import java.io.ObjectInputStream;

import java.io.ByteArrayInputStream;

import java.util.HashMap;

import java.util.Set;

import oracle.sql.BLOB;

import java.io.OutputStream;

import java.io.ByteArrayOutputStream;

import java.io.ObjectOutputStream;

import oracle.jdbc.OracleConnection;

import oracle.jdbc.driver.OracleDriver;

import java.lang.StringBuffer;

public class OraHashMap

{ 

  private static HashMap blobToHashMap(BLOB in_blob) throws Exception {

    int i_length = (int) in_blob.length();

    byte[] blobAsBytes = in_blob.getBytes(1, i_length);

    

    ObjectInputStream ois = new ObjectInputStream(new ByteArrayInputStream(blobAsBytes));

    HashMap hmap = (HashMap) ois.readObject(); 

    return hmap;

  }

  private static BLOB hashMapToBlob(HashMap hmap) throws Exception {

    ByteArrayOutputStream baos = new ByteArrayOutputStream();

    ObjectOutputStream oout = new ObjectOutputStream(baos);

    oout.writeObject(hmap);

    oout.close();

    OracleConnection conn = (OracleConnection) new OracleDriver().defaultConnection();

    BLOB out_blob = BLOB.createTemporary(conn, true, BLOB.DURATION_SESSION);

    OutputStream ostrm = out_blob.setBinaryStream(0);

    ostrm.write(baos.toByteArray());

    ostrm.flush();  

    return out_blob;

  }

  public static long getSize(BLOB in_blob) throws Exception {

    HashMap hmap = blobToHashMap(in_blob);

    return hmap.size();

  }

  public static String getValue(BLOB in_blob, String str_key) throws Exception {

    HashMap hmap = blobToHashMap(in_blob);

    return (String)hmap.get(str_key);

  }

  /*

   * Next 3 methods written in an assumption that

   * Both key and value are string

   */

  public static BLOB remove(BLOB in_blob, String key_str) throws Exception {

    HashMap hmap = blobToHashMap(in_blob);

    String removed = (String) hmap.remove(key_str);

    System.out.println("Removed:\nKey: " + key_str + "\nValue: " + removed + "\n");

    BLOB out_blob = hashMapToBlob(hmap);

    return out_blob;

  }

  public static BLOB put(BLOB in_blob, String key_str, String val) throws Exception {

    HashMap hmap = blobToHashMap(in_blob);

    String added = (String) hmap.put(key_str, val);

    System.out.println("Removed:\nKey: " + key_str + "\nValue: " + added + "\n");

    

    BLOB out_blob = hashMapToBlob(hmap);

    return out_blob;

  }

  

  public static String toVarchar(BLOB in_blob) throws Exception {

    HashMap hMap = blobToHashMap(in_blob);

    Set hKeys = hMap.keySet();

    StringBuffer sf = new StringBuffer();

    for(Object hKey: hKeys) {

      sf.append(hKey + ":" + hMap.get(hKey) + ",\n");

    }

    System.out.println("Java Print:\n" + sf.toString() + "\n");

    return sf.toString();

  }

};

--sql wrappers

--drop function hashmap_size;

create or replace function hashmap_size(hmap blob)

return number

as language java

name 'OraHashMap.getSize(oracle.sql.BLOB) return long';

--drop function hashmap_value;

create or replace function hashmap_value(hmap blob, var_key varchar2)

return varchar2

as language java

name 'OraHashMap.getValue(oracle.sql.BLOB, java.lang.String) return java.lang.String';

--drop function hashmap_remove;

create or replace function hashmap_remove(hmap blob, var_key varchar2)

return Blob

as language java

name 'OraHashMap.remove(oracle.sql.BLOB, java.lang.String) return oracle.sql.BLOB';

--drop function hashmap_put;

create or replace function hashmap_put(hmap blob, var_key varchar2, var_val varchar2)

return Blob

as language java

name 'OraHashMap.put(oracle.sql.BLOB, java.lang.String, java.lang.String) return oracle.sql.BLOB';

--drop function hashmap_varchar;

create or replace function hashmap_varchar(hmap blob)

return varchar2

as language java

name 'OraHashMap.toVarchar(oracle.sql.BLOB) return java.lang.String';

--Queries

select id, hashmap_size(<blob_fld>) from <table_name>; --where id = ### ;

select id, hashmap_value(<blob_fld>, '<key>') from <table_name>;

--To read the hashmap

select hashmap_varchar(<blob_fld>) from <table_name>; --where id = ###;

select id, hashmap_size(<blob_fld>) as num, hashmap_value(record, '<key>') as first_name from <table_name> 

--Following steps prints Java System.out.println

set serveroutput on;

CALL dbms_java.set_output (10);

Declare

  out_str varchar2(500);

BEGIN

select hashmap_varchar(<blob_fld>) into out_str from <table_name> where id = ###;

dbms_output.put_line(out_str);

end;

--To modify and save hashmap

--Edit commented lines to perform the tasks

set serveroutput on;

CALL dbms_java.set_output (10);

DECLARE

  record_1 BLOB;

  record_2 BLOB;

  

  record_id number := <xxx>;

  key_str_remove varchar2(32) := '<key>';

  key_str_add varchar2(32) := 'USR_TEST';

  add_value varchar2(32) := 'CHECK THIS';

BEGIN

  SELECT tn.<blob_fld> into record_1 from <table_name> tn where id = record_id;

  dbms_output.put_line(hashmap_size(record_1));

  dbms_output.put_line(hashmap_value(record_1, key_str_remove));

  record_2 := hashmap_remove(record_1, key_str_remove);

  record_2 := hashmap_put(record_1, key_str_add, add_value);

  update <table_name> set <blob_fld> = record_2 where id = record_id;

  dbms_output.put_line(hashmap_size(record_2));

  dbms_output.put_line(hashmap_value(record_2, key_str_add));

END;

Other Helps:

select utl_raw.cast_to_varchar2(<blob_fld>) from <table_name> where id = ###;

http://przemyslawkruglej.com/archive/2013/11/returning-blob-from-embedded-java/

https://docs.oracle.com/database/121/JJDEV/toc.htm

Next Level: Prepared statements inside java

Questions: 
 Can Java access the table fields directly? - Have to try with OracleConnector!
REF: http://docs.oracle.com/cd/B28359_01/java.111/b31225/chsix.htm#BABJJFJC