Package oracle.sql.json
This package contains classes and interfaces for working with SQL JSON type values. Use this package to:
- Store and retrieve JSON type values in the database
- Read, create, and modify JSON type values
- Encode/decode JSON type values in the same binary JSON storage format used by the database
- Convert JSON type values to and from JSON text
- Access JSON type values using JSON-P interfaces (javax.json)
The package contains three components:
Description | Classes/interfaces |
---|---|
JSON type object-model | OracleJsonValue , OracleJsonObject , OracleJsonArray , OracleJsonString , OracleJsonDecimal , OracleJsonDouble , OracleJsonFloat , OracleJsonTimestamp , OracleJsonDate , OracleJsonDate , OracleJsonBinary , OracleJsonIntervalDS , and OracleJsonIntervalYM . |
JSON type event-stream reader and writer | OracleJsonParser OracleJsonGenerator |
Factory for reading, writing, and creating JSON type values | OracleJsonFactory |
The following example shows how to insert, get, and modify JSON type values.
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.json.OracleJsonFactory;
import oracle.sql.json.OracleJsonObject;
public class JsonExample {
public static void main(String[] args) throws SQLException {
OracleDataSource ds = new OracleDataSource();
ds.setURL("jdbc:oracle:thin:@myhost:1521:orcl");
ds.setUser("SCOTT");
ds.setPassword("tiger");
OracleConnection con = (OracleConnection) ds.getConnection();
// create a table with a JSON column and insert one value
Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE fruit (data JSON)");
stmt.executeUpdate("INSERT INTO fruit VALUES ('{"name":"pear","count":10}')");
// create another JSON object
OracleJsonFactory factory = new OracleJsonFactory();
OracleJsonObject orange = factory.createObject();
orange.put("name", "orange");
orange.put("count", 12);
// insert the orange
PreparedStatement pstmt = con.prepareStatement("INSERT INTO fruit VALUES (:1)");
pstmt.setObject(1, orange, OracleType.JSON);
pstmt.executeUpdate();
pstmt.close();
// select the pear
ResultSet rs = stmt.executeQuery("SELECT data FROM fruit f WHERE f.data.name = 'pear'");
rs.next();
OracleJsonObject pear = rs.getObject(1, OracleJsonObject.class);
int count = pear.getInt("count");
// create a modifiable copy of the pear
pear = factory.createObject(pear);
pear.put("count", count + 1);
pear.put("color", "green");
// update the pear
pstmt = con.prepareStatement("UPDATE fruit f SET data = :1 WHERE f.data.name = 'pear');
pstmt.setObject(1, pear, OracleType.JSON);
pstmt.executeUpdate();
pstmt.close();
rs.close();
stmt.close();
con.close();
}
}
In this example, pstmt.setObject(...)
is called to set a parameter to a JSON value and rs.getObject(...)
is called to get the value of a JSON type column.
Storing and Retrieving JSON
The following methods in JDBC can accept and return JSON values:
Methods that support JSON type | |
---|---|
java.sql.ResultSet |
getObject(int, Class<T>) getObject(String, Class<T>) updateObject(int, Object) updateObject(String, Object) |
java.sql.PreparedStatement |
setObject(String, Object, int) setObject(String, Object, SQLType) |
java.sql.CallableStatement |
getObject(int, Class<T>) getObject(String, Class<T>) setObject(String, Object, int) setObject(String, Object, SQLType) |
javax.sql.RowSet |
setObject(int, Object, int) setObject(String, Object, int) |
Methods that accept JSON values (e.g. setObject(...)
) support instances of the following Java types:
Class | Description |
---|---|
java.lang.String java.lang.CharSequence java.io.Reader |
A JSON text value. For example:
|
java.io.InputStream byte[] |
Either a JSON text value (UTF8, UTF16, etc) or Oracle binary JSON (see below). |
oracle.sql.json.OracleJsonValue javax.json.JsonValue |
A JSON object-model value. This includes any value derived from OracleJsonValue such as OracleJsonObject and OracleJsonArray . For example:
JSON-P interfaces in javax.json are also supported. |
oracle.sql.json.OracleJsonParser javax.json.stream.JsonParser |
A JSON event stream. |
oracle.sql.json.OracleJsonDatum |
Container for Oracle binary JSON. |
Use either OracleType.JSON
or OracleTypes.JSON
to specify the input is JSON, as shown in the above examples.
Methods that return JSON values (e.g. getObject(...)
) support the following Java types:
Class | Description |
---|---|
java.lang.String java.io.Reader |
The JSON type value is returned as JSON text. For example:
|
java.io.InputStream |
The JSON type value is returned as UTF8 JSON text. |
oracle.sql.json.OracleJsonValue javax.json.JsonValue |
The JSON type value is returned as OracleJsonValue . Any derived interface, such as OracleJsonObject and OracleJsonArray , may also be used.
JSON objects and arrays returned will reference the underlying Oracle binary JSON (see below) directly and will be immutable. To make a mutable copy of a returned object or array, use OracleJsonFactory.createObject(OracleJsonObject) and OracleJsonFactory.createArray(OracleJsonArray) respectively. JSON-P interfaces (javax.json ) may also be used - see OracleJsonValue.wrap(Class) . |
oracle.sql.json.OracleJsonParser javax.json.stream.JsonParser |
The JSON type value is returned as an event stream. |
oracle.sql.json.OracleJsonDatum |
Use this to directly access the underlying Oracle binary JSON. |
JSON Extended Types
A JSON text can only contain objects, arrays, strings, numbers, true, false, and null. However, a JSON type value from Oracle Database is extended to support additional SQL types. Specifically:JSON numbers
are broken down into the three SQL number types:NUMBER
,BINARY_DOUBLE
, andBINARY_FLOAT
.- Oracle JSON type values can contain the SQL primitives
TIMESTAMP
,DATE
,RAW
,INTERVALDS
, andINTERVALYM
.
OracleJsonValue
.
Oracle Binary JSON
This package also provides facilities for converting JSON values to and from Oracle binary JSON. Oracle binary JSON is the encoding format used by Oracle Database to store JSON type values. It is an indexed format that supports efficient random access within JSON objects and arrays. This enables JSON type values to be read in-place without need to copy the data to other in-memory structures such as hash tables. It also provides a richer type-system than JSON text, allowing SQL types such as timestamp, date, intervals, and raw binary to be stored within JSON type values.
JSON values obtained from the database are implicitly mapped from Oracle binary JSON and values sent to the database are implicitly encoded to binary JSON. For example, in the previous example, orange
is directly encoded as binary JSON before being sent to the database. Later when pear
is retrieved from the database, the binary JSON value is accessed in-place by the call to pear.getInt("count")
. Direct control over conversions to and from binary JSON is also possible. See OracleJsonFactory
for more information.
JSON type values can also be set and get as JSON text (for example, using methods like ResultSet.getString(int)
and PreparedStatement.setString(int, String)
). The JDBC driver or the database will convert JSON text to and from Oracle binary JSON. However, avoiding JSON text conversions is ideal as parsing and generation of JSON text can be expensive and will also cause some nested SQL types to be rendered as JSON strings. When setting JSON as text, use the setObject()
methods described above, rather than setString()
, to ensure the JSON text can be encoded in the JDBC driver. The JDBC driver can only perform the binary encoding when the type is known to be JSON, as indicated by setting the type parameter of setObject()
to OracleType.JSON
.
Integration with JSON-P (javax.json)
JSON-P is a Java API to parse and generate JSON text and is defined by JSR 374: Java API for JSON Processing 1.1. The interfaces in oracle.sql.json
are similar to ones found in JSON-P but there are two key differences:
- The Oracle JSON type API supports an extended set of
primitive types
that includes SQL timestamp, date, raw binary, etc. JSON-P only supports the standard JSON primitives string, number, true, false, and null. oracle.sql.json.OracleJsonObject
andoracle.sql.json.OracleJsonArray
may be mutable whilejavax.json.JsonObject
andjavax.json.JsonArray
are always immutable.
In some cases it may be to desirable read and write JSON type values using JSON-P (javax.json
) interfaces. For example, if the consuming application is already built on JSON-P or if you want to ensure your application uses standard JSON types. In general, JDBC supports consuming 3rd-party implementations of JSON-P by methods like PreparedStatement.setObject(int, Object)
and also supports wrapping values as javax.json
interfaces. For more information see OracleJsonValue.wrap(Class)
, OracleJsonGenerator.wrap(Class)
and OracleJsonParser.wrap(Class)
.
Dependencies
JDBC depends on the JSON-P 1.1 API. However, typically JSON-P is only required to be in the classpath when an application directly binds or gets JSON type values using JSON-P interfaces. For example: rs.getObject(1, javax.json.JsonObject)
- See Also:
- Java API for JSON Processing (JSON-P)
-
Interface Summary Interface Description OracleJsonArray A JSON array (an ordered sequence of zero or more values).OracleJsonBinary A raw binary value (in any format) stored within JSON data.OracleJsonDate A SQL/JSON DATE value.OracleJsonDecimal A SQL/JSON fixed decimal value.OracleJsonDouble A 64-bit, double-precision floating-point number.OracleJsonFloat A 32-bit, single-precision floating-point number.OracleJsonGenerator Writes a JSON type value to an output source.OracleJsonIntervalDS A SQL/JSON time interval in days, hours, minutes, and seconds.OracleJsonIntervalYM A SQL/JSON time interval in years and months.OracleJsonNumber OracleJsonObject A JSON object (an unordered collection of zero or more key/value pairs).OracleJsonParser Reads a JSON type value from an input source as a stream of events.OracleJsonString A SQL/JSON string value.OracleJsonStructure Super type ofOracleJsonObject
andOracleJsonArray
.OracleJsonTimestamp A SQL/JSON timestamp (without a timezone).OracleJsonValue The interface for JSON type in Oracle Database. -
Class Summary Class Description OracleJsonDatum Represents a JSON type value and holds Oracle binary JSON.OracleJsonFactory A factory for reading, writing, and creating SQL JSON values. -
Enum Summary Enum Description OracleJsonDecimal.TargetType Marker indicating if this value is intended to be mapped to Java int, long, or BigDecimal/BigInteger.OracleJsonParser.Event OracleJsonValue.OracleJsonType -
Exception Summary Exception Description OracleJsonException Indicates that a problem occurred during JSON processing.OracleJsonGenerationException Indicates that a problem occurred during JSON generation.OracleJsonParsingException Indicates that a problem occurred during JSON parsing.