//*********************************************************************
//
//                  Copyright (c) 2012 by Teradata Corporation
//                            All Rights Reserved
//
//*********************************************************************
//
//      File:     T21503J6.java
//      Header:   None
//      Purpose:  This sample program demonstrates the use of the
//                Teradata Database 14.10 XML data types.
//                This program will:
//                - Connect to the Teradata Database as guest user.
//                - Drop SampleSchools tables if
//                  already existing in the database.
//                - Create SampleSchools tables with XML data types.
//                - Insert XML values into the table using DOMResult &
//                  StAXResult
//                - Select XML columns from SampleSchools using StAXSource
//                - Display the results.
//
//      JDBC API: java.sql.Connection, java.sql.PreparedStatement,
//                java.sql.PreparedStatement.setSQLXML,
//                java.sql.PreparedStatement.addBatch,
//                java.sql.PreparedStatement.executeBatch,
//                java.sql.SQLXML, java.sql.SQLXML.setString,
//                java.sql.SQLXML.getString, java.sql.SQLXML.setResult,
//                java.sql.SQLXML.getSource, java.sql.SQLXML.free
//
//      Version: Created for Teradata Database 14.10
//
//      Requires JDK 6.0 or later
//
//*********************************************************************

import java.sql.* ;
import java.io.UnsupportedEncodingException ;
import java.io.IOException ;

import javax.xml.transform.stax.StAXResult ;
import javax.xml.transform.stax.StAXSource ;
import javax.xml.stream.XMLStreamReader ;
import javax.xml.stream.XMLStreamWriter ;
import javax.xml.stream.XMLStreamException ;

import javax.xml.parsers.DocumentBuilder ;
import javax.xml.parsers.DocumentBuilderFactory ;
import javax.xml.parsers.ParserConfigurationException ;
import javax.xml.transform.dom.DOMResult ;
import javax.xml.transform.stream.StreamSource ;

import org.w3c.dom.Element ;
import org.w3c.dom.Document ;
import org.w3c.dom.Attr ;
import org.w3c.dom.NodeList ;
import org.w3c.dom.Text ;


public class T21503J6
{
    // Name of the user able to create, drop, and manipulate tables
    public static String sUser = "guest" ;
    public static String sPassword = "please" ;

    public static void main (String [] args)
    throws ClassNotFoundException
    , XMLStreamException
    , ParserConfigurationException
    {
        // Creation of URL to be passed to the JDBC driver
        String sUrl = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8" ;

        String sCreateSchools =
            " CREATE TABLE SampleSchools (id INTEGER,"
            + " SchoolName VARCHAR (50), Teachers XML, Students XML)" ;

        String sDropSchools = " DROP TABLE SampleSchools " ;

        String sInsertSchools =
            " INSERT INTO SampleSchools (Id, SchoolName, Teachers, Students)"
            + " VALUES (?, ?, ?, ?)" ;

        String sSelectSchools =
            " SELECT * FROM SampleSchools ORDER BY id " ;

        try
        {
            System.out.println ("\n Sample T21503J6: \n") ;
            System.out.println (" Looking for the Teradata JDBC driver... ") ;
            // Loading the Teradata JDBC driver
            Class.forName ("com.teradata.jdbc.TeraDriver") ;
            System.out.println (" JDBC driver loaded. \n") ;

            System.out.println (
            " Attempting to connect to Teradata via the JDBC driver...") ;

            // Creating a connection object
            Connection con = DriverManager.getConnection (sUrl, sUser, sPassword) ;
            System.out.println (" User " + sUser + " connected.") ;
            System.out.println (" Connection to Teradata established. \n") ;
            try
            {
                // Creating a Statement, drop the table and ignore any exception
                // thrown from the drop since the table may not exist yet.
                Statement stmt = con.createStatement () ;
                try
                {
                    try
                    {
                        // Dropping table incase it has already been created
                        stmt.executeUpdate (sDropSchools) ;
                        System.out.println (" SampleSchools Table has been dropped ") ;
                    }
                    catch (SQLException ex)
                    {
                        // Ignore exception since table may not have already been created
                        System.out.println (" Ignoring exception " + ex.getMessage ()) ;
                    }

                    // Creating the table
                    System.out.println (" \n Creating SampleSchools table .... ") ;
                    stmt.executeUpdate (sCreateSchools) ;
                    System.out.println (" SampleSchools table has been created. ") ;

                    // Creating a PreparedStatement object from an active
                    // connection. A PreparedStatement is an object that represents
                    // a precompiled SQL statement and allows for efficient
                    // execution of the precompiled SQL statement multiple times.
                    System.out.println (" Preparing this Insert statement for execution:\n "
                                        + sInsertSchools) ;
                    PreparedStatement pstmt = con.prepareStatement (sInsertSchools) ;
                    System.out.println (" Prepared statement object created. \n") ;

                    try
                    {
                        // Set parameter values indicated by ? (dynamic update)
                        // PreparedStatement.setInt, PreparedStatement.setString and
                        // PreparedStatement.setSQLXML methods will be demonstrated.
                        System.out.println (" Using setInt (), setString () and setSQLXML () to " +
                                             "assign values to parameter markers:\n") ;

                        // Binding integer to parameter one, "Id"
                        pstmt.setInt (1, 203) ;

                        // Binding String parameter two, "SchoolName"
                        pstmt.setString (2, "Rivergate High School") ;

                        // Creating an SQLXML object and using setResult to set
                        // the XML value.
                        System.out.println (" Creating SQLXML object and setting the value. ") ;
                        SQLXML xmlTeachers = con.createSQLXML () ;

                        // Obtaining DOMResult object from SQLXML.
                        DOMResult domResult = xmlTeachers.setResult (DOMResult.class) ;

                        // Obtaining document and creating & setting elements.
                        DocumentBuilder dBldr = DocumentBuilderFactory.newInstance ().newDocumentBuilder () ;
                        Document doc = dBldr.newDocument () ;
                        Element rootElement = doc.createElement ("Teachers") ;
                        doc.appendChild (rootElement) ;

                        Element elTeacher = doc.createElement ("teacher") ;

                        Element elName = doc.createElement ("name") ;
                        elName.appendChild (doc.createTextNode ("Jim Smith")) ;
                        elTeacher.appendChild (elName) ;

                        Element elClasses = doc.createElement ("classes") ;

                        Element elClass1 = doc.createElement ("class") ;
                        Attr atClassScience3Id = doc.createAttribute ("id") ;
                        atClassScience3Id.setValue (String.valueOf ("S03")) ;
                        elClass1.setAttributeNode (atClassScience3Id) ;
                        elClass1.appendChild (doc.createTextNode ("Science III")) ;
                        elClasses.appendChild (elClass1) ;

                        Element elClass2 = doc.createElement ("class") ;
                        Attr atClassMath1Id = doc.createAttribute ("id") ;
                        atClassMath1Id.setValue (String.valueOf ("M01")) ;
                        elClass2.setAttributeNode (atClassMath1Id) ;
                        elClass2.appendChild (doc.createTextNode ("Math I")) ;
                        elClasses.appendChild (elClass2) ;

                        Element elClass3 = doc.createElement ("class") ;
                        Attr atClassMath2Id = doc.createAttribute ("id") ;
                        atClassMath2Id.setValue (String.valueOf ("M02")) ;
                        elClass3.setAttributeNode (atClassMath2Id) ;
                        elClass3.appendChild (doc.createTextNode ("Math II")) ;
                        elClasses.appendChild (elClass3) ;

                        elTeacher.appendChild (elClasses) ;
                        rootElement.appendChild (elTeacher) ;


                        elTeacher = doc.createElement ("teacher") ;

                        elName = doc.createElement ("name") ;
                        elName.appendChild (doc.createTextNode ("Mike Jones")) ;
                        elTeacher.appendChild (elName) ;

                        elClasses = doc.createElement ("classes") ;

                        elClass1 = doc.createElement ("class") ;
                        Attr atClassEng1Id = doc.createAttribute ("id") ;
                        atClassEng1Id.setValue (String.valueOf ("E01")) ;
                        elClass1.setAttributeNode (atClassEng1Id) ;
                        elClass1.appendChild (doc.createTextNode ("English I")) ;
                        elClasses.appendChild (elClass1) ;

                        elClass2 = doc.createElement ("class") ;
                        Attr atClassEng2Id = doc.createAttribute ("id") ;
                        atClassEng2Id.setValue (String.valueOf ("E02")) ;
                        elClass2.setAttributeNode (atClassEng2Id) ;
                        elClass2.appendChild (doc.createTextNode ("English II")) ;
                        elClasses.appendChild (elClass2) ;

                        elClass3 = doc.createElement ("class") ;
                        Attr atClassEng3Id = doc.createAttribute ("id") ;
                        atClassEng3Id.setValue (String.valueOf ("E03")) ;
                        elClass3.setAttributeNode (atClassEng3Id) ;
                        elClass3.appendChild (doc.createTextNode ("English III")) ;
                        elClasses.appendChild (elClass3) ;

                        elTeacher.appendChild (elClasses) ;
                        rootElement.appendChild (elTeacher) ;
                        domResult.setNode (doc) ;

                        // Binding the SQLXML object to parameter 3
                        pstmt.setSQLXML (3, xmlTeachers) ;

                        // Calling free to release any resources used by SQLXML
                        xmlTeachers.free () ;

                        // Creating an SQLXML object and using setString to set
                        // the XML value.
                        System.out.println (" Creating SQLXML object and setting the value. ") ;
                        SQLXML xmlStudents = con.createSQLXML () ;
                        StAXResult staxResult = xmlStudents.setResult (StAXResult.class) ;

                        XMLStreamWriter xsw = staxResult.getXMLStreamWriter () ;

                        xsw.writeStartElement ("Students") ;

                        xsw.writeStartElement ("student") ;

                        xsw.writeStartElement ("name") ;
                        xsw.writeCharacters ("Sam Young") ;
                        xsw.writeEndElement () ;

                        xsw.writeStartElement ("classes") ;

                        xsw.writeStartElement ("class") ;
                        xsw.writeAttribute ("id", "E01") ;
                        xsw.writeCharacters ("English I") ;
                        xsw.writeEndElement () ;

                        xsw.writeStartElement ("class") ;
                        xsw.writeAttribute ("id", "S03") ;
                        xsw.writeCharacters ("Science III") ;
                        xsw.writeEndElement () ;

                        xsw.writeStartElement ("class") ;
                        xsw.writeAttribute ("id", "M01") ;
                        xsw.writeCharacters ("Math I") ;
                        xsw.writeEndElement () ;

                        xsw.writeEndElement () ;

                        xsw.writeEndElement () ;

                        xsw.writeStartElement ("student") ;

                        xsw.writeStartElement ("name") ;
                        xsw.writeCharacters ("Jane White") ;
                        xsw.writeEndElement () ;

                        xsw.writeStartElement ("classes") ;

                        xsw.writeStartElement ("class") ;
                        xsw.writeAttribute ("id", "E02") ;
                        xsw.writeCharacters ("English II") ;
                        xsw.writeEndElement () ;

                        xsw.writeStartElement ("class") ;
                        xsw.writeAttribute ("id", "S03") ;
                        xsw.writeCharacters  ("Science III") ;
                        xsw.writeEndElement () ;

                        xsw.writeStartElement ("class") ;
                        xsw.writeAttribute ("id", "M02") ;
                        xsw.writeCharacters ("Math II") ;
                        xsw.writeEndElement () ;

                        xsw.writeEndElement () ;

                        xsw.writeEndElement () ;

                        xsw.writeEndElement () ;

                        xsw.writeEndDocument () ;

                        xsw.flush () ;
                        xsw.close () ;

                        // Binding the SQLXML object to parameter 4
                        pstmt.setSQLXML (4, xmlStudents) ;

                        // Calling SQLXML.free to free up any resources
                        xmlStudents.free () ;

                        System.out.println (
                            " Inserting first row into SampleSchools. ") ;

                        // Calling executeUpdate to execute the SQL command after
                        // all of the parameter values have been set.
                        pstmt.executeUpdate () ;
                    }
                    finally
                    {
                        // Closing the PreparedStatement
                        pstmt.close () ;
                    }

                    // The following methods will perform a SELECT query on the table
                    // and will display the results of the query

                    // Selecting all data in the table
                    executeAndDispResults (con, sSelectSchools) ;

                }
                finally
                {
                    // Closing the Statement
                    System.out.println ("\n Closing Statement object ...") ;
                    stmt.close () ;
                    System.out.println (" Connection has been closed.") ;
                }
            }
            finally
            {
                // Closing the connection
                System.out.println ("\n Closing Connection ... ") ;
                con.close () ;
                System.out.println (" Connection has been closed ") ;
            }

            System.out.println ( " Sample T21503J6 finished. \n") ;
        }
        catch (SQLException ex)
        {
            // A SQLException was generated.  Catch it and display
            // the error information.
            // Note that there could be multiple error objects chained
            // together.
            System.out.println () ;
            System.out.println ("*** SQLException caught ***") ;

            while (ex != null)
            {
                System.out.println (" Error code: " + ex.getErrorCode ()) ;
                System.out.println (" SQL State: " + ex.getSQLState ()) ;
                System.out.println (" Message: " + ex.getMessage ())  ;
                ex.printStackTrace () ;
                System.out.println () ;
                ex = ex.getNextException () ;
            }

            throw new IllegalStateException (" Sample failed.") ;
        }
    } // end main

    private static void executeAndDispResults (Connection con, String sQuery)
    throws SQLException
    , XMLStreamException
    {
        // Creating a Statement
        Statement stmt = con.createStatement () ;
        try
        {
            // Selecting data from the table.
            System.out.println ("\n Executing Select query ... ") ;
            ResultSet rs = stmt.executeQuery (sQuery) ;

            try
            {
                // Displaying the results of the SELECT query
                System.out.println () ;
                System.out.println (" Results from " + sQuery ) ;
                ResultSetMetaData rsmd = rs.getMetaData () ;
                int numCols = rsmd.getColumnCount () ;
                System.out.println () ;
                while (rs.next ())
                {
                    int nSchoolId = rs.getInt ("Id") ;
                    String sSchoolName = rs.getString ("SchoolName") ;
                    SQLXML xmlTeachers = rs.getSQLXML ("Teachers") ;
                    SQLXML xmlStudents = rs.getSQLXML ("Students") ;

                    // Print the results
                    System.out.println ("   SchoolId     " + nSchoolId) ;
                    System.out.println ("   SchoolName   " + sSchoolName) ;

                    // Retrieve the StAXSource from each XML column and display the results
                    printXMLReaderFromStAXSource (xmlTeachers.getSource (StAXSource.class)) ;
                    printXMLReaderFromStAXSource (xmlStudents.getSource (StAXSource.class)) ;

                    // Freeing up resources held by SQLXML
                    xmlTeachers.free () ;
                    xmlStudents.free () ;
                    System.out.println () ;
                    System.out.println () ;
               }
               System.out.println ("**********************************************************************") ;
            }
            finally
            {
                // Closing the ResultSet
                rs.close () ;
            }
        }
        finally
        {
            // Closing the Statement
            stmt.close () ;
        }
    } // end executeAndDispResultSet

    private static void printXMLReaderFromStAXSource (StAXSource staxSource)
    throws XMLStreamException
    {
        // Retrieve the XMLStreamReader and loop through the events
        XMLStreamReader streamReader = staxSource.getXMLStreamReader () ;
        System.out.println ("   ****") ;
        while (streamReader.hasNext ())
        {
            streamReader.next () ;
            switch (streamReader.getEventType ())
            {
                case XMLStreamReader.CHARACTERS :
                    // print the value of the element
                    System.out.println ("      " + streamReader.getText ()) ;
                    break ;
                case XMLStreamReader.START_ELEMENT :
                    // print the element's local name and its attributes
                    // In this sample there will only be one attribute
                    System.out.println ("   " + streamReader.getLocalName ()) ;
                    for (int nIndex = 0 ; nIndex < streamReader.getAttributeCount () ; nIndex ++ )
                    {
                        System.out.print ("      "+streamReader.getAttributeLocalName (nIndex) +"  "+streamReader.getAttributeValue (nIndex)) ;
                    }
                    break ;
            }
        }
        System.out.println () ;

    }  // end printXMLReaderFromStAXSource

} // end class T21503J6