Use SODA for Java

SODA for Java is a Java library providing an implementation of SODA for use with Java. You can use it to perform create, remove, update, and delete (CRUD) operations on documents of any kind, and you can use it to query JSON documents.

For further details, see Oracle Database SODA for Java Developer's Guide.

SODA for Java Pre-Requisites

Before you can use SODA for Java with Oracle Database Exadata Express Cloud Service, you need to configure your Java environment.

To use SODA for Java with Exadata Express: To run and compile an example SODA Java program to test your configuration, see Get Started with SODA for Java.

Get Started with SODA for Java

Follow these steps to compile and run a simple SODA for Java program that creates a JSON document collection, inserts documents into the collection, and retrieves documents using unique document keys or queries-by-example (QBEs).

For further details, see Database SODA for Java Developer's Guide.

To compile and run the sample program:

  1. Enable Oracle Net Services (SQL*Net) access for your service. See Enable Oracle Net Services (SQL*Net) Access for Client Applications.

  2. Configure JDBC Thin Driver for your service. See Connect with JDBC Thin Driver and UCP.

  3. Enable SODA for your service. See Enable SODA for Your Service.

  4. Ensure you have the appropriate JAR files. See SODA for Java Pre-Requisites.

  5. Figure out the correct flags to use with the java command for your environment. See Property Settings for JDBC Thin Driver and UCP.

    You are now ready to compile and run SODA Java programs.

  6. Copy and paste the following simple SODA Java program into a file called testSODA.java. This program performs the following operations:

    • Create a new collection

    • Insert documents into the collection

    • Retrieve the first inserted document by its auto-generated key

    • Retrieve documents matching a query-by-example, or QBE

    import java.sql.Connection
    import java.sql.DriverManager
    
    import oracle.soda.rdbms.OracleRDBMSClient
    
    import oracle.soda.OracleDatabase
    import oracle.soda.OracleCursor
    import oracle.soda.OracleCollection
    import oracle.soda.OracleDocument
    import oracle.soda.OracleException
    
    import java.util.Properties
    
    import oracle.jdbc.OracleConnection
    
    public class testSODA{
      public static void main(String[] args) {
    
        // SODA works on top of a regular JDBC connection.
        // Set up the connection string: replace hostName, port, and serviceName
        // with the info for your Oracle RDBMS instance
        String url = "jdbc:oracle:thin:user/password@service_name"
    
        OracleConnection conn = null
        try{
           // Get a JDBC connection to an Oracle instance
           conn = (OracleConnection) DriverManager.getConnection(url);
    
           // Enable JDBC implicit statement caching
           conn.setImplicitCachingEnabled(true);
           conn.setStatementCacheSize(50);
    
           // Get an OracleRDBMSClient - starting point of SODA for 
           // Java application
           OracleRDBMSClient cl = new OracleRDBMSClient();
    
          // Get a databaseOracleDatabase db = cl.getDatabase(conn);
          // Create a collection with the name "MyFirstJSONCollection".
          // Note: Collection names are case-sensitive.
          // A table with the name "MyFirstJSONCollection" will be
          // created in the RDBMS to store the collection 
          OracleCollection col = db.admin().createCollection("MyFirstJSONCollection");
    
          // Create a few JSON documents, representing
          // users and the number of friends they have
          OracleDocument doc1 =
              db.createDocumentFromString(
                "{ \"name\" : \"Alex\", \"friends\" : \"50\" }");
    
          OracleDocument doc2 =
              db.createDocumentFromString(
                "{ \"name\" : \"Mia\", \"friends\" : \"300\" }");
    
          OracleDocument doc3 =
              db.createDocumentFromString(
                "{ \"name\" : \"Gloria\", \"friends\" : \"399\" }");
    
         // Insert the documents into a collection, one-by-one.
         // The result documents contain auto-generated 
         // keys, among other documents components (version, etc).
         // Note: SODA provides the more efficient bulk insert as well
         OracleDocument resultDoc1 = col.insertAndGet(doc1);
         OracleDocument resultDoc2 = col.insertAndGet(doc2);
         OracleDocument resultDoc3 = col.insertAndGet(doc3);
    
         // Retrieve the first document using its auto-generated
         // unique ID (aka key)
         System.out.println ("* Retrieving the first document by its key *\n");
         OracleDocument fetchedDoc = col.find().key(resultDoc1.getKey()).getOne();
         System.out.println (fetchedDoc.getContentAsString());
    
         // Retrieve all documents representing users that have
         // 300 or more friends. Use the following query-by-example:
         // {friends : {$gte : 300}}.
         System.out.println ("\n* Retrieving documents representing users with" +
                             " at least 300 friends *\n");
    
         OracleDocument f = db.createDocumentFromString(
          "{ \"friends\" : { \"$gte\" : 300 }}");
    
         OracleCursor c = null
    
         try{
           // Get a cursor over all documents in the collection
           // that match our query-by-example
           c = col.find().filter(f).getCursor();
    
           while (c.hasNext()) {
             // Get the next document
             fetchedDoc = c.next();
    
             System.out.println (fetchedDoc.getContentAsString());
           }
          }
          finally{
            // Important: you must close the cursor to release resources!
            if (c != null) {
                c.close();
            }
          }
    
          // Drop the collection, deleting the table backing
          // it and collection metadata
          if (args.length  0 && args[0].equals("drop")) {
              col.admin().drop();System.out.println ("\n* Collection dropped *");
          }
        }
        catch (Exception e) {
          e.printStackTrace();
        }
        finally{
          if (conn != null) {
              try {
                conn.close();
              }
            catch (Exception e) {
            }
          }
        }
      }
    }
  7. Modify the url String at the beginning of the program with connection information for your service. Change the hostName:port and serviceName in the url String for your service:

    For example, replace user, password and service_name in the following URL:

    String url = "jdbc:oracle:thin:user/password@service_name"

    with:

    • user - Replace user with the schema name.

    • password - Replace password with the password for the schema. To use the default schema, see Find the Default Schema Name and Set the Password.

    • service_name - Replace service_name with the service name listed in tnsnames.ora, which is downloaded as part of a prior step, see Download Client Credentials. For example, the content for tnsnames.ora looks something like this:

      cloud2 = (description=
            (address= ...)
            (connect_data= ...)
            (security=...)
       )

      In the above example, cloud2 is the service name.

  8. Compile and run testSODA.java, making sure the necessary JAR files are in the classpath. For example, assuming you’re in the directory where the JARs are located, do:

    Note:

    The exact -D flags and their values shown in the following example will be different in your actual environment. The flags shown assume that /home/user1/cloud directory contains tnsnames.ora file, that truststore.jks and keystore.jks files are located in the current directory, that password is the trust store and key store password, and that JDK8 is used. See Property Settings for JDBC Thin Driver and UCP, for information on what the right flags are for your environment, and how to set them.
    javac "orajsoda-version.jar:ojdbc7.jar:javax.json-1.0.4.jar" testSODA.java
    java -Doracle.net.tns_admin=/home/user1/cloud   
    -Djavax.net.ssl.trustStore=truststore.jks    
    -Djavax.net.ssl.trustStorePassword=password    
    -Djavax.net.ssl.keyStore=keystore.jks    
    -Djavax.net.ssl.keyStorePassword=password    
    -Doracle.net.ssl_server_dn_match=true    
    -Doracle.net.ssl_version=1.2 "orajsoda-version.jar:ojdbc7.jar:javax.json-1.0.4.jar:." testSODA
  9. You should get the following output:

    * Retrieving the first document by its key *
    
    { "name" : "Alex", "friends" : "50" }
    
    * Retrieving documents representing users with at least 300 friends *
    
    { "name" : "Mia", "friends" : "300" }
    { "name" : "Gloria", "friends" : "399" }