- All Implemented Interfaces:
- AutoCloseable,- ResultSet,- Wrapper,- AwareVTI
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 SummaryNested classes/interfaces inherited from class org.apache.derby.vti.VTITemplateVTITemplate.ColumnDescriptor
- 
Field SummaryFields inherited from interface java.sql.ResultSetCLOSE_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 SummaryModifier and TypeMethodDescriptionvoidvoidclose()getBigDecimal(int columnIndex) booleangetBoolean(int columnIndex) bytegetByte(int columnIndex) doublegetDouble(int columnIndex) floatgetFloat(int columnIndex) intgetInt(int columnIndex) longgetLong(int columnIndex) getObject(int columnIndex) shortgetShort(int columnIndex) getString(int columnIndex) booleannext()static SimpleJsonVTIreadArray(org.json.simple.JSONArray array) Create a SimpleJsonVTI from a JSONArray object.booleanwasNull()Methods inherited from class org.apache.derby.vti.VTITemplateabsolute, 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, updateTimestampMethods inherited from class java.lang.Objectequals, getClass, hashCode, notify, notifyAll, toString, wait, wait, waitMethods inherited from interface java.sql.ResultSetupdateObject, updateObject, updateObject, updateObject
- 
Method Details- 
readArrayCreate a SimpleJsonVTI from a JSONArray object. - Parameters:
- array- a json array
- Returns:
- a VTI for reading the json array
- Throws:
- SQLException- on error
 
- 
close- Throws:
- SQLException
 
- 
next- Throws:
- SQLException
 
- 
wasNullpublic boolean wasNull()- Specified by:
- wasNullin interface- ResultSet
- Overrides:
- wasNullin class- VTITemplate
 
- 
getWarnings- Specified by:
- getWarningsin interface- ResultSet
- Overrides:
- getWarningsin class- VTITemplate
- Throws:
- SQLException
 
- 
clearWarnings- Specified by:
- clearWarningsin interface- ResultSet
- Overrides:
- clearWarningsin class- VTITemplate
- Throws:
- SQLException
 
- 
getString- Specified by:
- getStringin interface- ResultSet
- Overrides:
- getStringin class- VTITemplate
- Throws:
- SQLException
 
- 
getBoolean- Specified by:
- getBooleanin interface- ResultSet
- Overrides:
- getBooleanin class- VTITemplate
- Throws:
- SQLException
 
- 
getByte- Specified by:
- getBytein interface- ResultSet
- Overrides:
- getBytein class- VTITemplate
- Throws:
- SQLException
 
- 
getShort- Specified by:
- getShortin interface- ResultSet
- Overrides:
- getShortin class- VTITemplate
- Throws:
- SQLException
 
- 
getInt- Specified by:
- getIntin interface- ResultSet
- Overrides:
- getIntin class- VTITemplate
- Throws:
- SQLException
 
- 
getLong- Specified by:
- getLongin interface- ResultSet
- Overrides:
- getLongin class- VTITemplate
- Throws:
- SQLException
 
- 
getFloat- Specified by:
- getFloatin interface- ResultSet
- Overrides:
- getFloatin class- VTITemplate
- Throws:
- SQLException
 
- 
getDouble- Specified by:
- getDoublein interface- ResultSet
- Overrides:
- getDoublein class- VTITemplate
- Throws:
- SQLException
 
- 
getObject- Specified by:
- getObjectin interface- ResultSet
- Overrides:
- getObjectin class- VTITemplate
- Throws:
- SQLException
 
- 
getBigDecimal- Specified by:
- getBigDecimalin interface- ResultSet
- Overrides:
- getBigDecimalin class- VTITemplate
- Throws:
- SQLException
 
 
-