Package org.apache.derby.optional.api

Class SimpleJsonVTI

  • All Implemented Interfaces:
    java.lang.AutoCloseable, java.sql.ResultSet, java.sql.Wrapper, AwareVTI
    public class SimpleJsonVTI
extends VTITemplate

    This is a table function which turns a JSON array into a relational ResultSet. This table function relies on the JSON.simple JsonArray class found at https://code.google.com/p/json-simple/. Each object in the array is turned into a row. The shape of the row is declared by the CREATE FUNCTION ddl and the shape corresponds to the key names found in the row objects. Provided that the values in those objects have the expected type, the following ResultSet accessors can be called:

    • getString()
    • getBoolean()
    • getByte()
    • getShort()
    • getInt()
    • getLong()
    • getFloat()
    • getDouble()
    • getObject()
    • getBigDecimal()

    This table function relies on the JsonArray type loaded by the simpleJson optional tool. This table function can be combined with other JsonArray-creating functions provided by that tool.

    Here's an example of how to use this VTI on a JSON document read across the network using the readArrayFromURL function provided by the simpleJson tool: 

     call syscs_util.syscs_register_tool( 'simpleJson', true );

 create function thermostatReadings( jsonDocument JsonArray )
 returns table
 (
   "id" int,
   "temperature" float,
   "fanOn" boolean
 )
 language java parameter style derby_jdbc_result_set contains sql
 external name 'org.apache.derby.optional.api.SimpleJsonVTI.readArray';
 
 select * from table
 (
    thermostatReadings
    (
       readArrayFromURL( 'https://thermostat.feed.org', 'UTF-8' )
    )
 ) t;

    That returns a table like this: 

     id         |temperature             |fanOn
 ------------------------------------------
 1          |70.3                    |true 
 2          |65.5                    |false

    Here's an example of how to use this VTI on a JSON document string with the assistance of the readArrayFromString function provided by the simpleJson tool: 

     select * from table
 (
    thermostatReadings
    (
       readArrayFromString
       (
        '[ { "id": 1, "temperature": 70.3, "fanOn": true }, { "id": 2, "temperature": 65.5, "fanOn": false } ]'
       )
    )
 ) t;

    • Method Detail

      • readArray

        public static SimpleJsonVTI readArray​(org.json.simple.JsonArray array)
                               throws java.sql.SQLException

        Create a SimpleJsonVTI from a JsonArray object.

        Throws:
        java.sql.SQLException

      • close

        public void close()
           throws java.sql.SQLException
        Throws:
        java.sql.SQLException

      • next

        public boolean next()
             throws java.sql.SQLException
        Throws:
        java.sql.SQLException

      • wasNull

        public boolean wasNull()
        Specified by:
        wasNull in interface java.sql.ResultSet
        Overrides:
        wasNull in class VTITemplate

      • getWarnings

        public java.sql.SQLWarning getWarnings()
                                throws java.sql.SQLException
        Specified by:
        getWarnings in interface java.sql.ResultSet
        Overrides:
        getWarnings in class VTITemplate
        Throws:
        java.sql.SQLException

      • clearWarnings

        public void clearWarnings()
                   throws java.sql.SQLException
        Specified by:
        clearWarnings in interface java.sql.ResultSet
        Overrides:
        clearWarnings in class VTITemplate
        Throws:
        java.sql.SQLException

      • getString

        public java.lang.String getString​(int columnIndex)
                           throws java.sql.SQLException
        Specified by:
        getString in interface java.sql.ResultSet
        Overrides:
        getString in class VTITemplate
        Throws:
        java.sql.SQLException

      • getBoolean

        public boolean getBoolean​(int columnIndex)
                   throws java.sql.SQLException
        Specified by:
        getBoolean in interface java.sql.ResultSet
        Overrides:
        getBoolean in class VTITemplate
        Throws:
        java.sql.SQLException

      • getByte

        public byte getByte​(int columnIndex)
             throws java.sql.SQLException
        Specified by:
        getByte in interface java.sql.ResultSet
        Overrides:
        getByte in class VTITemplate
        Throws:
        java.sql.SQLException

      • getShort

        public short getShort​(int columnIndex)
               throws java.sql.SQLException
        Specified by:
        getShort in interface java.sql.ResultSet
        Overrides:
        getShort in class VTITemplate
        Throws:
        java.sql.SQLException

      • getInt

        public int getInt​(int columnIndex)
           throws java.sql.SQLException
        Specified by:
        getInt in interface java.sql.ResultSet
        Overrides:
        getInt in class VTITemplate
        Throws:
        java.sql.SQLException

      • getLong

        public long getLong​(int columnIndex)
             throws java.sql.SQLException
        Specified by:
        getLong in interface java.sql.ResultSet
        Overrides:
        getLong in class VTITemplate
        Throws:
        java.sql.SQLException

      • getFloat

        public float getFloat​(int columnIndex)
               throws java.sql.SQLException
        Specified by:
        getFloat in interface java.sql.ResultSet
        Overrides:
        getFloat in class VTITemplate
        Throws:
        java.sql.SQLException

      • getDouble

        public double getDouble​(int columnIndex)
                 throws java.sql.SQLException
        Specified by:
        getDouble in interface java.sql.ResultSet
        Overrides:
        getDouble in class VTITemplate
        Throws:
        java.sql.SQLException

      • getObject

        public java.lang.Object getObject​(int columnIndex)
                           throws java.sql.SQLException
        Specified by:
        getObject in interface java.sql.ResultSet
        Overrides:
        getObject in class VTITemplate
        Throws:
        java.sql.SQLException

      • getBigDecimal

        public java.math.BigDecimal getBigDecimal​(int columnIndex)
                                   throws java.sql.SQLException
        Specified by:
        getBigDecimal in interface java.sql.ResultSet
        Overrides:
        getBigDecimal in class VTITemplate
        Throws:
        java.sql.SQLException