/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package oraclexmltypeexample;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import oracle.jdbc.*;
import oracle.xdb.XMLType;

/**
 *
 * @author piyush
 */
public class OracleXMLTypeExample {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // TODO code application logic here


              // the following statement loads the Oracle jdbc driver

    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
        System.out.println ("Could not load the driver");
      }

    String user, pass, host, servicename;

    Scanner sc = new Scanner(System.in);
    System.out.println("Type userid, password, hostname or ipaddress: ");
    user = sc.next();
    pass = sc.next();
    host = sc.next();
    servicename = "xe";
    System.out.println(user+" "+pass+" "+host);

    /*  userid, password and hostname are obtained from the console
     *  It is assumed that the service name is XE and the database server
     *  listens on the default port 1521.
     */

    try (                                     //try-with-resources
      Connection conn = DriverManager.getConnection
        ("jdbc:oracle:thin:"+user+"/"+pass+"@"+host+":1521/"+servicename);
      Statement stmt = conn.createStatement();
    )
    {

    /* JDBC default is to commit each SQL statement as it is sent to the database.  Setting autocommmit=false changes the default
       behaviour so that transactions have to be committed explicity.
     */
    conn.setAutoCommit(false);

    stmt.executeUpdate("DROP TABLE Addrbook");

    //  ----------------   CREATING A TABLE WITH AN XMLTYPE ATTRIBUTE --------

        stmt.executeUpdate("CREATE TABLE Addrbook(name VARCHAR(20) " +
                           "CONSTRAINT Addrbook_PK PRIMARY KEY, " +
                           " card SYS.XMLTYPE, creationdate Date)");

    //  --------------  INSERTING XMLType DATA  ----------------------

   // We can insert XMLType data by supplying the corresponding string.

   stmt.executeUpdate("INSERT INTO Addrbook VALUES( " +
                       " 'John', " +
                       "    " +
                       " sys.XMLType.createXML(" +
                        " '<ACARD CREATEDBY=\"Thomas\"> \n " +
                          " <EMAIL> john1234@yahoo.co.uk </EMAIL> \n "  +
                          " <HOMEPHONE>90361111</HOMEPHONE> \n " +
                          " <WORKPHONE>90362222</WORKPHONE> \n " +
                          " <ADDRESS> \n " +
                            " <STREET> 10 Shore Road </STREET> \n " +
                            " <CITY> Newtownabbet </CITY> \n " +
                           " <POSTCODE> BT37 0QB </POSTCODE> \n " +
                         " </ADDRESS> \n " +
                         " <COMMENT> PGD/ M Sc Informatics Student </COMMENT> \n " +
                        "</ACARD>'), " +
                        "           " +
                        " DATE'2007-04-17' " +
                                               " )")   ;

    /*  The following is a somewhat convoluted way of reading XML data from a file.
    We open a FileInputStream and use a constructor for XMLType to read the data
    into an instance of XMLType.  However when we pass it to SQL, we convert it
    to a string.

    Note that sys.XMLType in the SQL statement is a PL/SQL package ! (Not Java!)

    We are converting a Java XMLType into a String and then into a PL/SQL XMLType.
    */

    FileInputStream fis = new FileInputStream("xmltypedata/acard1.xml");
    XMLType xmlv = new XMLType(conn, fis);
    System.out.println(xmlv.getStringVal());

    stmt.executeUpdate("INSERT INTO Addrbook VALUES( 'Tom', " +
                       "sys.XMLType.createXML( ' " +  xmlv.getStringVal() + " ' ), " +
                       "DATE'2007-04-18')" );


    /*  The following code is a lot neater.  Note that we have to use
    OraclePreparedStatement rather than JDBC PreparedStatement because
    the latter knows nothing about XMLType.
    */

    // First read the data from file into an instance of XMLType.

    FileInputStream fis2 = new FileInputStream("xmltypedata/acard2.xml");
    XMLType xmlv2 = new XMLType(conn, fis2);

    String sqltxt = "INSERT INTO Addrbook VALUES(?, ?, ?)";

    OraclePreparedStatement osqlstmt = (OraclePreparedStatement) conn.prepareStatement(sqltxt);
    osqlstmt.setString(1, "Gerry");

    // The 2nd argument  is of type XMLType
    osqlstmt.setObject(2, xmlv2);
    java.sql.Date when; when = java.sql.Date.valueOf("2007-04-20");
    osqlstmt.setDate(3, when);
    osqlstmt.execute();

    // ---------------------  QUERYING  -------------------------------------

    // We have 3 rows in the Addrbook table.  Let's query it.

    // Let's just retrieve John's ACARD as a Clob and print it.
   ResultSet result = stmt.executeQuery("SELECT a.card.getClobval() " +
                                        "FROM Addrbook a WHERE Name='John'");
   System.out.println("\n Results as Clob: \n");

   while(result.next())
      System.out.println(result.getString(1));

   //  We could also have retrieved it as a String
   result = stmt.executeQuery("SELECT a.card.getStringval() FROM Addrbook a WHERE Name='Tom'");
   System.out.println("\n Results as String: \n");

   while(result.next())
      System.out.println(result.getString(1));


   //  ------------  QUERYING WITH XPATH EXPRESSIONS -----------------------

   /*  An alternative method for retrieving everyone's name and ACARD.
       The extract function (of the PL/SQL XMLType) takes an XPATH expression as an argument and
       returns an XMLType value.  We must convert it to String by using the getStringVal function.
   */


   result = stmt.executeQuery("SELECT a.name, a.card.extract('/').getStringval() " +
                              "FROM Addrbook a ");
   System.out.println("\n Name and /: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );

   // The following SQL does exactly the same thing as the previous statement.

   result = stmt.executeQuery("SELECT a.name, a.card.extract('/ACARD').getStringval() " +
                              "FROM Addrbook a ");
   System.out.println("\n Name and /ACARD: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );

   //  Let's get everyone's name and e-mail address

   result = stmt.executeQuery("SELECT a.name, " +
            "a.card.extract('/ACARD/EMAIL').getStringVal() " +
            "FROM Addrbook a ");
   System.out.println("\n Name and E-mail Address: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );

   //Let's get everyone's name and e-mail address WITHOUT the surrounding EMAIL tag

   result = stmt.executeQuery("SELECT a.name, " +
            "a.card.extract('/ACARD/EMAIL/text()').getStringVal() " +
            "FROM Addrbook a ");
   System.out.println("\n Name & E-Mail without the EMAIL tag: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );

   /* The previous 2 queries did not return Gerry's e-mail address because it is not
      a subelement of /ACARD but of /ACARD/CONTACTS.  If we look through the
      grandchindren of /ACARD, we should find  Gerry's E-mail address. Notice the
      use of the wildcard character * .
   */

   result = stmt.executeQuery("SELECT a.name, " +
            "a.card.extract('/ACARD/*/EMAIL/text()').getStringVal() " +
            "FROM Addrbook a ");
   System.out.println("\n Name & E-Mail (only Gerry's): \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );


   /* Alternatively, we can search all descendants of /ACARD (and not just the
      children or grandchildren) by extracting /ACARD//EMAIL.
      Notice the double slash!
   */

   result = stmt.executeQuery("SELECT a.name, " +
            "a.card.extract('/ACARD//EMAIL/text()').getStringVal() " +
            "FROM Addrbook a ");
   System.out.println("\n Name & E-Mail (including Gerry's): \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );

   /* We can use predicates in XPATH expressions.  These are enclosed in [ ]
      The following query retrieves the name and address of the person whose
      e-mail address is john1234@yahoo.co.uk. Watch surrounding spaces though.
   */

   result = stmt.executeQuery("SELECT a.name, " +
          "a.card.extract('/ACARD[EMAIL=\" john1234@yahoo.co.uk \"]/ADDRESS').getStringVal() " +
          "FROM Addrbook a ");
   System.out.println("\n Name & Address of a given E-Mail address: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );


   /* The previous query also returns Tom and Gerry's names, albeit with NULL address.
      We can use the existsNode method to retrieve only those names that match the given
      e-mail address. The existsNode method takes an XPATH expression as an arguments and
      returns 0 or 1.
   */

   result = stmt.executeQuery("SELECT a.name, " +
          "a.card.extract('/ACARD[EMAIL=\" john1234@yahoo.co.uk \"]/ADDRESS').getStringVal() "+
          "FROM Addrbook a " +
          "WHERE a.card.existsNode('/ACARD[EMAIL=\" john1234@yahoo.co.uk \"]')=1");
   System.out.println("\n Name & Address of a given E-Mail address: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );

   /* The [ ] expression is also used to denote an index in a list.  The following
      query retrieves everyone's second WORKPHONE.
   */

   result = stmt.executeQuery("SELECT a.name, " +
            "a.card.extract('/ACARD//WORKPHONE[2]').getStringVal() FROM Addrbook a ");
   System.out.println("\n Name & Second WORKPHONE: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );

   /* We don't really want to retrieve the names of those who don't have a 2nd WORKPHONE.
      Therefore use existsNode.
   */

   result = stmt.executeQuery("SELECT a.name, " +
            "a.card.extract('/ACARD//WORKPHONE[2]').getStringVal() FROM Addrbook a " +
            "WHERE a.card.existsNode('/ACARD//WORKPHONE[2]')=1" );
   System.out.println("\n Name & Second WORKPHONE: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );



   // So far all queries have used XML elements.  Let us use attributes instead.
   // Name and mobile number of everyone whose ACARD was created by Thomas.

   result = stmt.executeQuery("SELECT a.name, " +
            "a.card.extract('/ACARD//MOBILE').getStringVal() FROM Addrbook a " +
            "WHERE a.card.extract('/ACARD/@CREATEDBY').getStringVal() = 'Thomas' ");
   System.out.println("\n Name & Second WORKPHONE: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );


   // -----------------    FUNCTIONS ----------------------------------------


   result = stmt.executeQuery("SELECT a.name, " +
                  "a.card.extract('/ACARD[count(//WORKPHONE) = 2]//WORKPHONE[2]').getStringVal() FROM Addrbook a " );

   System.out.println("\n Count Function: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );

   System.out.println("\n End of printout from Count Function: \n");

   result = stmt.executeQuery("SELECT a.name, " +
                  "a.card.extract('/ACARD[count(//WORKPHONE)]').getStringVal() FROM Addrbook a " );

   System.out.println("\n Count Function: \n");

   while(result.next())
      System.out.println(result.getString(1) + "  " + result.getString(2) );

   System.out.println("\n End of printout from Count Function: \n");



   // ------------------   UPDATING THE VALUE OF AN XMLType ATTRIBUTE  -------------------

   /*  Standard SQL, e.g.

       UPDATE  Addrbook SET card = sys.XMLType.createXML('string-containing-xml-data')
       WHERE name = 'john';

       would do this.
   */


   //  ------------------  DELETING ROWS -------------------------------------------------

   //  Let us get rid of everyone whose ACARD was created by Thomas.

   stmt.executeUpdate("DELETE Addrbook a " +
                      "WHERE a.card.extract('/ACARD/@CREATEDBY').getStringVal() = 'Thomas' " );

   // There should be nothing left. CHECK.

   result = stmt.executeQuery("SELECT a.name FROM Addrbook a ");
   System.out.println("\n After deleting all created by Thomas: \n");
   while(result.next())
       System.out.println(result.getString(1));

   stmt.close();
   conn.close();






    }
        catch (SQLException | FileNotFoundException exc) {

        exc.printStackTrace(System.err);

    }

}
}