- 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 voidclearWarnings()voidclose()BigDecimalgetBigDecimal(int columnIndex)booleangetBoolean(int columnIndex)bytegetByte(int columnIndex)doublegetDouble(int columnIndex)floatgetFloat(int columnIndex)intgetInt(int columnIndex)longgetLong(int columnIndex)ObjectgetObject(int columnIndex)shortgetShort(int columnIndex)StringgetString(int columnIndex)SQLWarninggetWarnings()booleannext()static SimpleJsonVTIreadArray(org.json.simple.JSONArray array)Create a SimpleJsonVTI from a JSONArray object.booleanwasNull()-
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:
wasNullin interfaceResultSet- Overrides:
wasNullin classVTITemplate
-
getWarnings
public SQLWarning getWarnings() throws SQLException
- Specified by:
getWarningsin interfaceResultSet- Overrides:
getWarningsin classVTITemplate- Throws:
SQLException
-
clearWarnings
public void clearWarnings() throws SQLException- Specified by:
clearWarningsin interfaceResultSet- Overrides:
clearWarningsin classVTITemplate- Throws:
SQLException
-
getString
public String getString(int columnIndex) throws SQLException
- Specified by:
getStringin interfaceResultSet- Overrides:
getStringin classVTITemplate- Throws:
SQLException
-
getBoolean
public boolean getBoolean(int columnIndex) throws SQLException- Specified by:
getBooleanin interfaceResultSet- Overrides:
getBooleanin classVTITemplate- Throws:
SQLException
-
getByte
public byte getByte(int columnIndex) throws SQLException- Specified by:
getBytein interfaceResultSet- Overrides:
getBytein classVTITemplate- Throws:
SQLException
-
getShort
public short getShort(int columnIndex) throws SQLException- Specified by:
getShortin interfaceResultSet- Overrides:
getShortin classVTITemplate- Throws:
SQLException
-
getInt
public int getInt(int columnIndex) throws SQLException- Specified by:
getIntin interfaceResultSet- Overrides:
getIntin classVTITemplate- Throws:
SQLException
-
getLong
public long getLong(int columnIndex) throws SQLException- Specified by:
getLongin interfaceResultSet- Overrides:
getLongin classVTITemplate- Throws:
SQLException
-
getFloat
public float getFloat(int columnIndex) throws SQLException- Specified by:
getFloatin interfaceResultSet- Overrides:
getFloatin classVTITemplate- Throws:
SQLException
-
getDouble
public double getDouble(int columnIndex) throws SQLException- Specified by:
getDoublein interfaceResultSet- Overrides:
getDoublein classVTITemplate- Throws:
SQLException
-
getObject
public Object getObject(int columnIndex) throws SQLException
- Specified by:
getObjectin interfaceResultSet- Overrides:
getObjectin classVTITemplate- Throws:
SQLException
-
getBigDecimal
public BigDecimal getBigDecimal(int columnIndex) throws SQLException
- Specified by:
getBigDecimalin interfaceResultSet- Overrides:
getBigDecimalin classVTITemplate- Throws:
SQLException
-
-