Module org.apache.derby.optionaltools
Package org.apache.derby.optional.api

## Class SimpleJsonVTI

- java.lang.Object
- - org.apache.derby.vti.VTITemplate
  - - org.apache.derby.optional.api.SimpleJsonVTI

- All Implemented Interfaces:
    `AutoCloseable`, `ResultSet`, `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;

- - ### Nested Class Summary

    - ### Nested classes/interfaces inherited from class org.apache.derby.vti.VTITemplate

      `VTITemplate.ColumnDescriptor`

  - ### Field Summary

    - ### Fields inherited from interface java.sql.ResultSet

      `CLOSE_CURSORS_AT_COMMIT, CONCUR_READ_ONLY, CONCUR_UPDATABLE, FETCH_FORWARD, FETCH_REVERSE, FETCH_UNKNOWN, HOLD_CURSORS_OVER_COMMIT, TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE`

  - ### Method Summary

    All Methods Static Methods Instance Methods Concrete Methods
    Modifier and Type | Method | Description
    --- | --- | ---
    `void` | `clearWarnings()` |
    `void` | `close()` |
    `BigDecimal` | `getBigDecimal​(int columnIndex)` |
    `boolean` | `getBoolean​(int columnIndex)` |
    `byte` | `getByte​(int columnIndex)` |
    `double` | `getDouble​(int columnIndex)` |
    `float` | `getFloat​(int columnIndex)` |
    `int` | `getInt​(int columnIndex)` |
    `long` | `getLong​(int columnIndex)` |
    `Object` | `getObject​(int columnIndex)` |
    `short` | `getShort​(int columnIndex)` |
    `String` | `getString​(int columnIndex)` |
    `SQLWarning` | `getWarnings()` |
    `boolean` | `next()` |
    `static SimpleJsonVTI` | `readArray​(org.json.simple.JSONArray array)` | Create a SimpleJsonVTI from a JSONArray object.
    `boolean` | `wasNull()` |

    - ### Methods inherited from class org.apache.derby.vti.VTITemplate

      `absolute, afterLast, beforeFirst, cancelRowUpdates, deleteRow, findColumn, first, getArray, getArray, getAsciiStream, getAsciiStream, getBigDecimal, getBigDecimal, getBigDecimal, getBinaryStream, getBinaryStream, getBlob, getBlob, getBoolean, getByte, getBytes, getBytes, getCharacterStream, getCharacterStream, getClob, getClob, getConcurrency, getContext, getCursorName, getDate, getDate, getDate, getDate, getDouble, getFetchDirection, getFetchSize, getFloat, getHoldability, getInt, getLong, getMetaData, getNCharacterStream, getNCharacterStream, getNClob, getNClob, getNString, getNString, getObject, getObject, getObject, getObject, getObject, getRef, getRef, getReturnTableSignature, getRow, getRowId, getRowId, getShort, getSQLXML, getSQLXML, getStatement, getString, getTime, getTime, getTime, getTime, getTimestamp, getTimestamp, getTimestamp, getTimestamp, getType, getUnicodeStream, getUnicodeStream, getURL, getURL, insertRow, isAfterLast, isBeforeFirst, isClosed, isFirst, isLast, isWrapperFor, last, moveToCurrentRow, moveToInsertRow, previous, refreshRow, relative, rowDeleted, rowInserted, rowUpdated, setContext, setFetchDirection, setFetchSize, unwrap, updateArray, updateArray, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateBigDecimal, updateBigDecimal, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBlob, updateBlob, updateBlob, updateBlob, updateBlob, updateBlob, updateBoolean, updateBoolean, updateByte, updateByte, updateBytes, updateBytes, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateClob, updateClob, updateClob, updateClob, updateClob, updateClob, updateDate, updateDate, updateDouble, updateDouble, updateFloat, updateFloat, updateInt, updateInt, updateLong, updateLong, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNString, updateNString, updateNull, updateNull, updateObject, updateObject, updateObject, updateObject, updateRef, updateRef, updateRow, updateRowId, updateRowId, updateShort, updateShort, updateSQLXML, updateSQLXML, updateString, updateString, updateTime, updateTime, updateTimestamp, updateTimestamp`

    - ### Methods inherited from class java.lang.Object

      `equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

    - ### Methods inherited from interface java.sql.ResultSet

      `updateObject, updateObject, updateObject, updateObject`

- - ### Method Detail

    - #### readArray

          public static SimpleJsonVTI readArray​(org.json.simple.JSONArray array)
                                         throws SQLException

      Create a SimpleJsonVTI from a JSONArray object.

      Parameters:
        `array` - a json array
      Returns:
        a VTI for reading the json array
      Throws:
        `SQLException` - on error

    - #### close

          public void close()
                     throws SQLException

      Throws:
        `SQLException`

    - #### next

          public boolean next()
                       throws SQLException

      Throws:
        `SQLException`

    - #### wasNull

          public boolean wasNull()

      Specified by:
        `wasNull` in interface `ResultSet`
      Overrides:
        `wasNull` in class `VTITemplate`

    - #### getWarnings

          public SQLWarning getWarnings()
                                 throws SQLException

      Specified by:
        `getWarnings` in interface `ResultSet`
      Overrides:
        `getWarnings` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### clearWarnings

          public void clearWarnings()
                             throws SQLException

      Specified by:
        `clearWarnings` in interface `ResultSet`
      Overrides:
        `clearWarnings` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### getString

          public String getString​(int columnIndex)
                           throws SQLException

      Specified by:
        `getString` in interface `ResultSet`
      Overrides:
        `getString` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### getBoolean

          public boolean getBoolean​(int columnIndex)
                             throws SQLException

      Specified by:
        `getBoolean` in interface `ResultSet`
      Overrides:
        `getBoolean` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### getByte

          public byte getByte​(int columnIndex)
                       throws SQLException

      Specified by:
        `getByte` in interface `ResultSet`
      Overrides:
        `getByte` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### getShort

          public short getShort​(int columnIndex)
                         throws SQLException

      Specified by:
        `getShort` in interface `ResultSet`
      Overrides:
        `getShort` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### getInt

          public int getInt​(int columnIndex)
                     throws SQLException

      Specified by:
        `getInt` in interface `ResultSet`
      Overrides:
        `getInt` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### getLong

          public long getLong​(int columnIndex)
                       throws SQLException

      Specified by:
        `getLong` in interface `ResultSet`
      Overrides:
        `getLong` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### getFloat

          public float getFloat​(int columnIndex)
                         throws SQLException

      Specified by:
        `getFloat` in interface `ResultSet`
      Overrides:
        `getFloat` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### getDouble

          public double getDouble​(int columnIndex)
                           throws SQLException

      Specified by:
        `getDouble` in interface `ResultSet`
      Overrides:
        `getDouble` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### getObject

          public Object getObject​(int columnIndex)
                           throws SQLException

      Specified by:
        `getObject` in interface `ResultSet`
      Overrides:
        `getObject` in class `VTITemplate`
      Throws:
        `SQLException`

    - #### getBigDecimal

          public BigDecimal getBigDecimal​(int columnIndex)
                                   throws SQLException

      Specified by:
        `getBigDecimal` in interface `ResultSet`
      Overrides:
        `getBigDecimal` in class `VTITemplate`
      Throws:
        `SQLException`