/*
 * This program illustrates the use of Oracle XML-SQL Utility (XSU) Java API
 *
 */

package oraclexsuexample;

import java.sql.*;
import java.net.URL;
import java.util.Scanner;
import oracle.xml.sql.query.OracleXMLQuery;
import oracle.xml.sql.dml.OracleXMLSave;

/**
 *
 * @author Piyush Ojha
 */
public class OracleXSUExample {


  public static void main (String args []) {
      // throws SQLException, IOException {

      // 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)
    )
    {

    /* 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);

    //RETRIEVALS

    //  Create an OracleXMLQuery instance by supplying an SQL query string
    // (One can also supply a JDBC ResultSet object)

    OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from actor");
    qry.keepObjectOpen(true); // so that we can getXMLString more than once

    // Obtain the result as an XML string.
    // (Alternatively, obtain the result as a DOM tree.)
    // One can also set several OracleXMLQuery options.

    String str = qry.getXMLString();
    System.out.println(str);


    //INSERTIONS


    // For inserting data, first create an OracleXMLSave object by passing table name
    OracleXMLSave sav = new OracleXMLSave(conn, "Actor");

    // Set rowTag to ACTOR  because that is what we have done in the XML document
    sav.setRowTag("ACTOR");

    // obtain the URL object for the data file
    URL u = OracleXMLSave.getURL("xmldata/min_filmdb_xml_insert.txt");

    //  insert data
    sav.insertXML(u);

    /* This effectively executes

    *  INSERT INTO Actor VALUES('Amitabh Bachchan', 'Male', 'Indian', '10-Mar-1950', 'Hum Hindustano');
    *  INSERT INTO Actor VALUES('Ian McKellen', 'Male', 'British', '25-Apr-1960', 'Julius Caesar');

    *  The default behaviour is to insert all data in the XML document.
    *  Missing attributes are set to NULL
    */

    // Let's check
    OracleXMLQuery qry2 = new OracleXMLQuery(conn, "select * from actor");
    System.out.println(qry2.getXMLString());

    /*  To insert data only into the specified columns from an XML document
     *  (and ignore the remainder), we need to set a list of columns to be updated
     */

    String [] colnames = new String [2];
    colnames[0] = "ACTORNAME";
    colnames[1] = "NATIONALITY";
    sav.setUpdateColumnList(colnames);

    u = OracleXMLSave.getURL("xmldata/min_filmdb_xml_inssomecols.txt");

    sav.insertXML(u);

    /* This effectively executes

    *  INSERT INTO Actor(ActorName, Nationality) VALUES('Shahrukh Khan', 'Indian');
    *  INSERT INTO Actor(ActorName, Nationality) VALUES('Peter Sellers', 'British');
    *  INSERT INTO Actor(ActorName, Nationality) VALUES('Emma Thompson', 'British');

    */

    // Let's check
    OracleXMLQuery qry3 = new OracleXMLQuery(conn, "select * from actor");
    System.out.println(qry3.getXMLString());

    sav.close();


    //UPDATES


    // Now change some rows according to the data in another XML file

    //  Having set update column list for sav, we are not sure how to 'unset' it.
    //  Therefore create a new OracleXMLSave object.

    OracleXMLSave sav2 = new OracleXMLSave(conn, "Actor");
    sav2.setRowTag("ACTOR");
    u = OracleXMLSave.getURL("xmldata/min_filmdb_xml_update.txt");

    // Need to specify the  key columns for making updates
    String[] keyColNames = new String [1];
    keyColNames[0]="ACTORNAME";
    sav2.setKeyColumnList(keyColNames);

    // Now update
    sav2.updateXML(u);

    /* This effectively executes

    *  UPDATE Actor SET DOB='11-Sep-1946'. DebutFilmTitle='Saat Hindustani' WHERE ActorName = 'Amitabh Bachchan';
    *  UPDATE Actor SET DebutFilmTitle='Anthony and Cleopatra' WHERE ActorName = 'Ian McKellen';

    */

    // Let's check
    OracleXMLQuery qry4 = new OracleXMLQuery(conn, "select * from actor");
    System.out.println(qry4.getXMLString());

    // To update only some of the columns (and ignore rest of the data in the XML document), we again need to specify
    // an update column list.

    String [] updateColNames = new String [1];
    updateColNames[0] = "DEBUTFILMTITLE";
    sav2.setUpdateColumnList(updateColNames);
    u = OracleXMLSave.getURL("xmldata/min_filmdb_xml_updsomecols.txt");

    sav2.updateXML(u);

    /* This effectively executes

    *  UPDATE Actor SET DebutFilmTitle='Amar, Akbar, Anthony' WHERE ActorName = 'Amitabh Bachchan';
    *  UPDATE Actor SET DebutFilmTitle='King Lear' WHERE ActorName = 'Ian McKellen';

    */


    // Let's check
    OracleXMLQuery qry5 = new OracleXMLQuery(conn, "select * from actor");
    System.out.println(qry5.getXMLString());

    sav2.close();


    //DELETIONS

    OracleXMLSave sav3 = new OracleXMLSave(conn, "Actor");
    sav3.setRowTag("ACTOR");

    URL u3 = OracleXMLSave.getURL("xmldata/min_filmdb_xml_delete.txt");

    sav3.deleteXML(u3);

    /* This effectively executes

    *  DELETE FROM  Actor WHERE ActorName = 'Amitabh Bachchan';
    *  DELETE FROM  Actor WHERE ActorName = 'Judi Dench' AND DebutFilmTitle = 'Anthony and Cleopatra';

    *  Note that there is no row to match the 2nd deletetion criterion
    */

    // Let's check
    OracleXMLQuery qry6 = new OracleXMLQuery(conn, "select * from actor");
    System.out.println(qry6.getXMLString());

    String[] keyColNames3 = new String [1];
    keyColNames3[0]="ACTORNAME";
    sav3.setKeyColumnList(keyColNames3);

    sav3.deleteXML(u3);

    /* Now

    *  DELETE FROM  Actor WHERE ActorName = 'Amitabh Bachchan';
    *  DELETE FROM  Actor WHERE ActorName = 'Judi Dench';

    *  No match for the 1st deletion.  Dame Judi Dench now disappears
    */

    // Let's check
    OracleXMLQuery qry7 = new OracleXMLQuery(conn, "select * from actor");
    System.out.println(qry7.getXMLString());

    conn.commit();

    qry.close();

    conn.close();

    }
    catch (SQLException se) {

        se.printStackTrace(System.err);

    }
  }
}