Skip navigation links
Oracle® Database JDBC Java API Reference
Release 21c
F16896-01

Package oracle.sql.json

The API for JSON type in Oracle Database.

See: Description

Package oracle.sql.json Description

The API for JSON type in Oracle Database.

This package contains classes and interfaces for working with SQL JSON type values. Use this package to:

The package contains three components:

Description Classes/interfaces
JSON type object-model OracleJsonValue, OracleJsonObject, OracleJsonArray, OracleJsonString, OracleJsonDecimal, OracleJsonDouble, OracleJsonFloat, OracleJsonTimestamp, OracleJsonTimestampTZ, 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: 
  stmt.setObject(1, "{\"hello\":\"world\"}", OracleType.JSON);
java.io.InputStream
byte[]
 Either a JSON text value (UTF8, UTF16, etc) or Oracle binary JSON (see below).
oracle.sql.json.OracleJsonValue
jakarta.json.JsonValue
javax.json.JsonValue (deprecated) 		A JSON object-model value. This includes any value derived from OracleJsonValue such as OracleJsonObject and OracleJsonArray. For example: 
  OracleJsonFactory factory = new OracleJsonFactory(); 
  OracleJsonArray arr = factory.createArray();
  arr.add("hello");
  arr.add("world");
  stmt.setObject(1, arr, OracleType.JSON);
JSON-P interfaces in javax.json and jakarta.json are also supported.
oracle.sql.json.OracleJsonParser
jakarta.json.stream.JsonParser
javax.json.stream.JsonParser (deprecated)
 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: 
  String json = resultSet.getObject(1, String.class);
java.io.InputStream The JSON type value is returned as UTF8 JSON text.
oracle.sql.json.OracleJsonValue
jakarta.json.JsonValue
javax.json.JsonValue (deprecated)		 The JSON type value is returned as OracleJsonValue. Any derived interface, such as OracleJsonObject and OracleJsonArray, may also be used. 
  OracleJsonArray arr = resultSet.getObject(1, OracleJsonArray.class);
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 and jakarta.json) may also be used - see OracleJsonValue#wrap(Class).
oracle.sql.json.OracleJsonParser
jakarta.json.stream.JsonParser
javax.json.stream.JsonParser (deprecated) 		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: For more information, see 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:

In some cases it may be to desirable read and write JSON type values using JSON-P 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).

JSON-P deprecation notice: Starting with version 2.0, JSON-P is part of Jakarta EE 9 and it has been repackaged from javax.json to jakarta.json. Since Oracle Database JDBC Release 23c both JSON-P 1.0 (javax.json) and JSON-P 2.0 (jakarta.json) are supported. However, support for javax.json is deprecated and may be removed in a future release.

Dependencies

JDBC depends on the JSON-P 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)
Skip navigation links
Oracle® Database JDBC Java API Reference
Release 21c
F16896-01

Copyright © 2008, 2022, Oracle and/or its affiliates.