/*
 * @Copyright 2023 Oracle
 */
import java.io.StringReader;
import java.lang.Exception;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Hashtable;
import javax.sql.DataSource;

// Java EE
import javax.naming.Context;
import javax.naming.InitialContext;

// Oracle sql json
import oracle.sql.json.OracleJsonObject;

// Oracle SODA
import oracle.soda.OracleCollection;
import oracle.soda.OracleCursor;
import oracle.soda.OracleDatabase;
import oracle.soda.OracleDocument;
import oracle.soda.rdbms.OracleRDBMSClient;

public class JSONTest {
  public JSONTest() {
  }

  public static void main(String args[]) throws Exception {
    JSONTest t = new JSONTest();
    t.getDataSourceByJndi();
    t.run();
  }

  public void run() throws Exception {
    testJSON();
  }

  public void testJSON() throws Exception {
    Connection con = null;

    try {
      con = currentDS.getConnection();

      // Use SODA
      OracleRDBMSClient client = new OracleRDBMSClient();
      OracleDatabase db = client.getDatabase(con);
      OracleCollection col = db.openCollection("emp");
      
      // get the contents of the collectoin
      OracleCursor cursor = col.find().getCursor();
      System.out.println("Using SODA");
      while (cursor.hasNext()) {
        OracleDocument doc = cursor.next();

        OracleJsonObject obj = null;
        obj = (OracleJsonObject) doc.getContentAs(oracle.sql.json.OracleJsonObject.class);
        System.out.println(" name: " + obj.getString("name") + "\t" +
                           " salary: " + obj.getInt("salary") + "\t" +
                           " job: " + obj.getString("job"));
      }

      // Use SQL
      ResultSet rs = null;
      Statement stmt = con.createStatement();
      rs = stmt.executeQuery("select json_serialize(JSON_document) from emp");
      System.out.println("Using SQL serialized document");
      while (rs.next()) {
        System.out.println(rs.getString(1));
      }
      rs = stmt.executeQuery("select e.JSON_document.name, e.JSON_document.salary, e.JSON_document.job from emp e");
      System.out.println("Using SQL columns");
      while (rs.next()) {
        System.out.println(" name: " + rs.getString(1) + "\t" +
                           " salary: " + rs.getInt(2) + "\t" +
                           " job: " + rs.getString(3));
      }
    } catch (Exception e) {
      e.printStackTrace();
      throw new Exception("testSoda FAILURE");
    } finally {
      try { if (con != null) con.close(); } catch (Exception ignore) {}
    }
  }

  public void getDataSourceByJndi() throws Exception {
    Context ctx = null;
    Hashtable ht = new Hashtable();
    String ip = "localhost";

    ht.put(Context.INITIAL_CONTEXT_FACTORY,
      "weblogic.jndi.WLInitialContextFactory");
    ht.put(Context.PROVIDER_URL, "t3://" + ip + ":7001");
    ht.put(Context.SECURITY_PRINCIPAL, "weblogic");
    ht.put(Context.SECURITY_CREDENTIALS, "welcome1");

    ctx = new InitialContext(ht);
    currentDS = (javax.sql.DataSource)ctx.lookup("jndi.myJDBCDataSource");
  }
  javax.sql.DataSource currentDS;
}