Class SimpleJsonVTI
- java.lang.Object
-
- org.apache.derby.vti.VTITemplate
-
- org.apache.derby.optional.api.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;
-
-
Nested Class Summary
-
Nested classes/interfaces inherited from class org.apache.derby.vti.VTITemplate
VTITemplate.ColumnDescriptor
-
-
Method Summary
All Methods Static Methods Instance Methods Concrete Methods Modifier and Type Method Description void
clearWarnings()
void
close()
java.math.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)
java.lang.Object
getObject(int columnIndex)
short
getShort(int columnIndex)
java.lang.String
getString(int columnIndex)
java.sql.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
-
-
-
-
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 interfacejava.sql.ResultSet
- Overrides:
wasNull
in classVTITemplate
-
getWarnings
public java.sql.SQLWarning getWarnings() throws java.sql.SQLException
- Specified by:
getWarnings
in interfacejava.sql.ResultSet
- Overrides:
getWarnings
in classVTITemplate
- Throws:
java.sql.SQLException
-
clearWarnings
public void clearWarnings() throws java.sql.SQLException
- Specified by:
clearWarnings
in interfacejava.sql.ResultSet
- Overrides:
clearWarnings
in classVTITemplate
- Throws:
java.sql.SQLException
-
getString
public java.lang.String getString(int columnIndex) throws java.sql.SQLException
- Specified by:
getString
in interfacejava.sql.ResultSet
- Overrides:
getString
in classVTITemplate
- Throws:
java.sql.SQLException
-
getBoolean
public boolean getBoolean(int columnIndex) throws java.sql.SQLException
- Specified by:
getBoolean
in interfacejava.sql.ResultSet
- Overrides:
getBoolean
in classVTITemplate
- Throws:
java.sql.SQLException
-
getByte
public byte getByte(int columnIndex) throws java.sql.SQLException
- Specified by:
getByte
in interfacejava.sql.ResultSet
- Overrides:
getByte
in classVTITemplate
- Throws:
java.sql.SQLException
-
getShort
public short getShort(int columnIndex) throws java.sql.SQLException
- Specified by:
getShort
in interfacejava.sql.ResultSet
- Overrides:
getShort
in classVTITemplate
- Throws:
java.sql.SQLException
-
getInt
public int getInt(int columnIndex) throws java.sql.SQLException
- Specified by:
getInt
in interfacejava.sql.ResultSet
- Overrides:
getInt
in classVTITemplate
- Throws:
java.sql.SQLException
-
getLong
public long getLong(int columnIndex) throws java.sql.SQLException
- Specified by:
getLong
in interfacejava.sql.ResultSet
- Overrides:
getLong
in classVTITemplate
- Throws:
java.sql.SQLException
-
getFloat
public float getFloat(int columnIndex) throws java.sql.SQLException
- Specified by:
getFloat
in interfacejava.sql.ResultSet
- Overrides:
getFloat
in classVTITemplate
- Throws:
java.sql.SQLException
-
getDouble
public double getDouble(int columnIndex) throws java.sql.SQLException
- Specified by:
getDouble
in interfacejava.sql.ResultSet
- Overrides:
getDouble
in classVTITemplate
- Throws:
java.sql.SQLException
-
getObject
public java.lang.Object getObject(int columnIndex) throws java.sql.SQLException
- Specified by:
getObject
in interfacejava.sql.ResultSet
- Overrides:
getObject
in classVTITemplate
- Throws:
java.sql.SQLException
-
getBigDecimal
public java.math.BigDecimal getBigDecimal(int columnIndex) throws java.sql.SQLException
- Specified by:
getBigDecimal
in interfacejava.sql.ResultSet
- Overrides:
getBigDecimal
in classVTITemplate
- Throws:
java.sql.SQLException
-
-