/*
 * @Author : Abhijeet Kulkarni
 * @Version 1.0
 * Copyright (C) 2003 Oracle Corporation
 *
 * Development Environment : Oracle9i JDeveloper
 * Creation / Modification History
 * Abhijeet Kulkarni            1-July-2003       Created
 *
 */

//Package definition
package oracle.otnsamples.orajaxb;


//Importing the Record object generated by the JAXB Class Generator
import jaxbderived.profile.Record;

//Importing OraclePreparedStatement
import oracle.jdbc.OraclePreparedStatement;

//Importing the required IO classes
import java.io.ByteArrayInputStream;
import java.io.StringWriter;

import java.sql.CallableStatement;

//Importing the required SQL classes
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

//Importing the required utility classes
import java.util.ArrayList;
import java.util.Collections;

//Importing the required Swing classes
import javax.swing.JOptionPane;
import javax.swing.JTextArea;

//Importing the required XML classes
import javax.xml.bind.JAXBContext;
import javax.xml.bind.Marshaller;
import javax.xml.bind.Unmarshaller;
import javax.xml.transform.stream.StreamSource;


/**
 * This class is used to store and retrieve profile records to and from a
 * database. This class is derived from the RecordHolder class that provides
 * basic functionality to navigate through the record list.
 */
public class DBRecordHolder extends RecordHolder {
    public String hostname = null;
    public String password = null;
    public String sid = null;
    public String username = null;
    public int portno = 0;
    public Connection con = null;

    /**
     * Creates a new DBRecordHolder object.
     *
     * @param frame Reference to the  frame object
     * @param area Reference to the  status area
     */
    public DBRecordHolder(MainFrame frame, JTextArea area) {
        super(frame, area);
    }

    /**
     * This method connects to the database and initializes the database so
     * that it can be used by the Insurance Profie System. It drops the
     * profile_tab table and then recreates the same with an XMLType column.
     * This newly created table is then populated with sample records and
     * these records are fetched back in the list managed by this object.
     */
    public void connect() {
        try {
            //Register the database driver
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            //Generate the connect string
            String connString = "jdbc:oracle:thin:@" + hostname + ":" + portno +
                ":" + sid;

            textArea.append("Connecting to " + connString + "\n");

            //Get the database connection
            con = DriverManager.getConnection(connString, username, password);
        } catch (Exception ex) {
            //Unable to connect go to  Panel2
            textArea.append(ex.toString() + "\n");
            mainFrame.setCurrentPanel(1);
            mainFrame.show();
            mainFrame.repaint();
        } finally {
            //Connection object is null display message to the user
            if (con == null) {
                JOptionPane.showMessageDialog(mainFrame,
                    "Unable to connect to the database please check \n  the connection parameters.",
                    "Warning", JOptionPane.WARNING_MESSAGE
                );
                mainFrame.setCurrentPanel(1);
                mainFrame.show();
                mainFrame.repaint();

                return;
            }
        }

        textArea.append(
            "Now, the application will drop and recreate database table \n"
        );
        textArea.append(
            "and insert sample data in this table. After that, it will  \n"
        );
        textArea.append(
            "populate the record list from the database. This will take \n"
        );
        textArea.append("some time. Please wait... \n");

        //Drop and create required table
        this.dropCreateTable();

        //Populate newly created table
        this.populateTable();

        //Generate the record list that will be managed by this object
        this.createListFromDatabase();

        //Display Panel4
        mainFrame.setCurrentPanel(3);
        mainFrame.show();
        mainFrame.repaint();

        //Show the first record in the Panel4
        Panel4 p4 = (Panel4) mainFrame.getPanelPane(3);

        p4.initialize();
    }

    /**
     * This method drops and recreates profile_tab.
     */
    private void dropCreateTable() {
        Statement st = null;

        //Drop the table
        try {
            //Create statement object
            st = con.createStatement();
            textArea.append("Dropping profile_tab \n");

            //Drop the table
            st.execute("DROP TABLE profile_tab");
            textArea.append("profile_tab dropped\n");
            con.commit();
        } catch (Exception ex) {
            textArea.append(
                "Warning: There was some problem while  dropping profile_tab \n"
            );
            textArea.append(ex.toString());
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception ex1) {
            }
        }

        CallableStatement cstmt = null;

        //Drop the schema
        try {
            //Create statement object
            cstmt = con.prepareCall(
                    "BEGIN DBMS_XMLSCHEMA.DELETESCHEMA('http://jaxbderived.profile/profile.xsd',DBMS_XMLSCHEMA.DELETE_CASCADE); END;"
                );
            textArea.append("Dropping Record schema\n");

            //Drop the schema
            cstmt.execute();

            textArea.append("Record Schema dropped\n");
            con.commit();
        } catch (Exception ex) {
            textArea.append(ex.toString());
            textArea.append(
                "Warning: There was some problem while deleting the schema \n"
            );
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception ex1) {
            }
        }

        // Load the Schema and Create the table again
        try {
            st = con.createStatement();
            loadSchema();
            textArea.append("Creating profile_tab\n");

            String sql = "CREATE TABLE profile_tab (c1 XMLTYPE) XMLTYPE COLUMN c1 XMLSCHEMA \"http://jaxbderived.profile/profile.xsd\" ELEMENT \"Record\"";

            //Create the table
            st.execute(sql);
            con.commit();
            textArea.append("profile_tab created\n");
        } catch (Exception ex) {
            //There was problem while creating the table. Show Panel2
            textArea.append(ex.toString());
            textArea.append(
                "Warning: There was some problem while creating  profile_tab \n"
            );
            JOptionPane.showMessageDialog(mainFrame,
                "Unable to create necessary table.", "Warning",
                JOptionPane.WARNING_MESSAGE
            );
            mainFrame.setCurrentPanel(1);
            mainFrame.show();
            mainFrame.repaint();
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception ex1) {
            }
        }
    }

    /**
     * This method populates the newly created profile_tab with sample data.
     */
    private void populateTable() {
        JAXBContext jc = null;
        Marshaller m = null;

        listiterator = list.listIterator(0);

        try {
            //Create a JAXBContext instance
            jc = JAXBContext.newInstance("jaxbderived.profile");

            //Create a marshaller instance
            m = jc.createMarshaller();

            //Create the insert statement
            OraclePreparedStatement stmt = (OraclePreparedStatement) con.prepareStatement(
                    "INSERT INTO profile_tab VALUES (XMLType.CreateXML(?).CreateSchemaBasedXML('http://jaxbderived.profile/profile.xsd'))"
                );

            textArea.append("Uploading the profile records to the database \n");

            while (listiterator.hasNext()) {
                Record r = (Record) listiterator.next();
                StringWriter swriter = new StringWriter();

                //Marshal record object in a string
                m.marshal(r, swriter);
                String tempstr = this.removeXmlNs(swriter.toString());
                //Create XMLType parameter
                stmt.setString(1, tempstr);

                //Execute insert statement
                stmt.execute();
                con.commit();
            }

            textArea.append(list.size() +
                " records have been uploaded to the database \n"
            );

            if (stmt != null) {
                stmt.close();
            }
        } catch (Exception ex) {
            textArea.append(
                "Warning: There was some problem while uploading records into  profile_tab \n"
            );
            textArea.append(ex.toString());
            JOptionPane.showMessageDialog(mainFrame,
                "Unable to upload records.", "Warning",
                JOptionPane.WARNING_MESSAGE
            );

            //Show Panel2
            mainFrame.setCurrentPanel(1);
            mainFrame.show();
            mainFrame.repaint();
        } finally {
        }
    }

    /**
     * This method creates the list of record objects. These objects are
     * fetched  from the specified database.
     */
    private void createListFromDatabase() {
        // Create a list instance
        list = Collections.synchronizedList(new ArrayList());

        Statement st = null;
        ResultSet rs = null;
        Unmarshaller u = null;
        JAXBContext jc = null;

        try {
            //Create a JAXBContext instance
            jc = JAXBContext.newInstance("jaxbderived.profile");

            //Create unmarshaller object
            u = jc.createUnmarshaller();
            u.setValidating(false);
            textArea.append("Reading records from the database \n");
            st = con.createStatement();

            //Select all records from profile_tab
            rs = st.executeQuery(
                    "SELECT pt.c1.getStringVal() FROM profile_tab pt"
                );

            int i = 0;

            while (rs.next()) {
                //Retrieve the XMLType object as a String
                String s = this.addXmlNs(rs.getString(1));
                //Get byte array from the String object
                byte[] bytes = s.getBytes();

                //Create ByteArrayInputStream object
                ByteArrayInputStream bais = new ByteArrayInputStream(bytes);

                try {
                    //Unmarshal the record
                    Record rec = (Record) u.unmarshal(new StreamSource(bais));
                    //Add the record to the list
                    list.add(rec);
                } catch (Exception e1) {
                    textArea.append("There was an error while parsing " + i +
                        " th record from the result set \n" +
                        " This record has been skipped"
                    );
                }

                i++;
            }

            textArea.append("Number of records retrieved from the database :  " +
                list.size() + "\n"
            );
            listiterator = list.listIterator(0);

            try {
                listiterator.next();
            } catch (Exception ex) {
            }

            if (st != null) {
                st.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception ex1) {
            }
        }
    }

    /**
     * This method saves the profile record in the database. This method
     * replaces the entire XMLType entry. It is possible though to update the
     * XMLType entry using piece wise upates. Piece wise update is an advanced
     * XML DB feature beyond the scope of this sample and hence it is not
     * used.
     *
     * @param rec Reference to the Record object
     */
    public void saveRecord(Record rec) {
        Statement st = null;
        JAXBContext jc = null;
        Marshaller m = null;
        OraclePreparedStatement stmt = null;

        try {
            st = con.createStatement();
            textArea.append("Saving record with customer id " +
                rec.getCustomerID() + " \n"
            );

            //Delete previous record if any
            st.execute(
                "DELETE profile_tab pt WHERE  pt.c1.extract('//Record/@CustomerID').getStringVal() ='" +
                rec.getCustomerID() + "'"
            );

            if (st != null) {
                st.close();
            }

            //Insert updated record
            stmt = (OraclePreparedStatement) con.prepareStatement(
                    "INSERT INTO profile_tab VALUES (XMLType.CreateXML(?).CreateSchemaBasedXML('http://jaxbderived.profile/profile.xsd'))"
                );

            //Create JAXBContext instance
            jc = JAXBContext.newInstance("jaxbderived.profile");

            //Create Marshaller instance
            m = jc.createMarshaller();

            StringWriter swriter = new StringWriter();

            //Marshal record object to a StringWriter
            m.marshal(rec, swriter);
            String strtemp = this.removeXmlNs(swriter.toString());
            //Set the parameter
            stmt.setString(1, strtemp);

            //Execute the insert statement
            stmt.execute();
            con.commit();
            textArea.append("One record has been added / updated \n");
        } catch (Exception e) {
            textArea.append(
                "There was problem while adding / updating profile record in the database\n"
            );
            textArea.append("Error message is \n");
            textArea.append(e.toString() + "\n");
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e1) {
            }
        }
    }

    /**
     * This method loads the Profile Record Schema in the database
     */
    private void loadSchema() {
        String schema =
            "<xs:schema xmlns:xs=\"http://www.w3.org/2001/XMLSchema\">" +
            "  <xs:element name=\"Record\" type=\"RecordType\"/>" +
            "  <xs:complexType name=\"RecordType\">" + "    <xs:sequence>" +
            "      <xs:element name=\"CustomerRec\" type=\"Customer\"/>" +
            "      <xs:element name=\"AddressRec\" type=\"Address\"/>" +
            "      <xs:element name=\"ProfileRec\" type=\"Profile\"/>" +
            "      <xs:element name=\"ClaimsRec\" type=\"Claims\"/>" +
            "    </xs:sequence>" +
            "    <xs:attribute name=\"CustomerID\" use=\"required\">" +
            "      <xs:simpleType>" +
            "        <xs:restriction base=\"xs:string\">" +
            "          <xs:whiteSpace value=\"preserve\"/>" +
            "        </xs:restriction>" + "      </xs:simpleType>" +
            "    </xs:attribute>" + "  </xs:complexType>" +
            "  <xs:complexType name=\"Customer\">" + "    <xs:sequence>" +
            "      <xs:element name=\"FirstName\" type=\"xs:string\"/>" +
            "      <xs:element name=\"LastName\" type=\"xs:string\"/>" +
            "      <xs:element name=\"Gender\">" + "        <xs:simpleType>" +
            "          <xs:restriction base=\"xs:string\">" +
            "            <xs:whiteSpace value=\"preserve\"/>" +
            "            <xs:enumeration value=\"Male\"/>" +
            "            <xs:enumeration value=\"Female\"/>" +
            "          </xs:restriction>" + "        </xs:simpleType>" +
            "      </xs:element>" +
            "      <xs:element name=\"BirthDate\" type=\"xs:string\"/>" +
            "    </xs:sequence>" + "  </xs:complexType>" +
            "  <xs:complexType name=\"Address\">" + "    <xs:sequence>" +
            "      <xs:element name=\"Line1\" type=\"xs:string\"/>" +
            "      <xs:element name=\"Line2\" type=\"xs:string\"/>" +
            "      <xs:element name=\"City\" type=\"xs:string\"/>" +
            "      <xs:element name=\"Zip\" type=\"xs:int\"/>" +
            "      <xs:element name=\"Country\" type=\"xs:string\"/>" +
            "    </xs:sequence>" + "  </xs:complexType>" +
            "  <xs:complexType name=\"Profile\">" + "    <xs:sequence>" +
            "      <xs:element name=\"Rating\">" + "        <xs:simpleType>" +
            "          <xs:restriction base=\"xs:string\">" +
            "            <xs:enumeration value=\"Average\"/>" +
            "            <xs:enumeration value=\"Burden\"/>" +
            "            <xs:enumeration value=\"Contributor\"/>" +
            "            <xs:enumeration value=\"Revenue\"/>" +
            "          </xs:restriction>" + "        </xs:simpleType>" +
            "      </xs:element>" + "      <xs:element name=\"PremiumMode\">" +
            "        <xs:simpleType>" +
            "          <xs:restriction base=\"xs:string\">" +
            "            <xs:enumeration value=\"Annual\"/>" +
            "            <xs:enumeration value=\"BiAnnual\"/>" +
            "            <xs:enumeration value=\"Monthly\"/>" +
            "            <xs:enumeration value=\"Quarterly\"/>" +
            "          </xs:restriction>" + "        </xs:simpleType>" +
            "      </xs:element>" +
            "      <xs:element name=\"PremiumAmount\" type=\"xs:float\"/>" +
            "      <xs:element name=\"CustomerType\">" +
            "        <xs:simpleType>" +
            "          <xs:restriction base=\"xs:string\">" +
            "            <xs:enumeration value=\"Platinum\"/>" +
            "            <xs:enumeration value=\"Gold\"/>" +
            "            <xs:enumeration value=\"Silver\"/>" +
            "            <xs:enumeration value=\"Bronze\"/>" +
            "          </xs:restriction>" + "        </xs:simpleType>" +
            "      </xs:element>" + "    </xs:sequence>" +
            "  </xs:complexType>" + "  <xs:complexType name=\"Claims\">" +
            "    <xs:sequence>" +
            "      <xs:element name=\"Claim\" minOccurs=\"0\" maxOccurs=\"unbounded\">" +
            "        <xs:complexType>" + "          <xs:sequence>" +
            "            <xs:element name=\"Amount\" type=\"xs:float\"/>" +
            "            <xs:element name=\"ClaimID\" type=\"xs:string\"/>" +
            "            <xs:element name=\"ClaimDate\" type=\"xs:string\"/>" +
            "            <xs:element name=\"Status\">" +
            "       <xs:simpleType>" +
            "          <xs:restriction base=\"xs:string\">" +
            "            <xs:enumeration value=\"Paid\"/>" +
            "            <xs:enumeration value=\"Unpaid\"/>" +
            "            <xs:enumeration value=\"Rejected\"/>" +
            "            <xs:enumeration value=\"Review\"/>" +
            "          </xs:restriction>" + "        </xs:simpleType>" +
            "       </xs:element>" +
            "            <xs:element name=\"Description\" type=\"xs:string\"/>" +
            "          </xs:sequence>" +
            "        </xs:complexType>" + "      </xs:element>" +
            "    </xs:sequence>" + "  </xs:complexType>" + "</xs:schema>";
        CallableStatement cstmt = null;

        //Register the schema
        try {
            //Create statement object
            textArea.append("Registering the Record Schema \n");

            cstmt = con.prepareCall(
                    "BEGIN DBMS_XMLSCHEMA.REGISTERSCHEMA('http://jaxbderived.profile/profile.xsd',?); END;"
                );
            cstmt.setString(1, schema);
            cstmt.execute();
            textArea.append("Registered the schema\n");
            con.commit();
        } catch (Exception ex) {
            textArea.append(
                "Warning: There was some problem while registering the schema \n"
            );
            JOptionPane.showMessageDialog(mainFrame,
                "Unable to register the Schema (profile.xsd) ", "Warning",
                JOptionPane.WARNING_MESSAGE
            );
            textArea.append(ex.toString());

            //Show Panel2
            mainFrame.setCurrentPanel(1);
            mainFrame.show();
            mainFrame.repaint();
        } finally {
            try {
                if (cstmt != null) {
                    cstmt.close();
                }
            } catch (Exception ex1) {
            }
        }
    }
    
   /**
     * This method removes the xmlns attribute from the root element of JAXB 
     * marshalled XML content. We associate the xml namespace explicitly by using
     * CreateXML(?).CreateSchemaBasedXML(...) function.
     *
     * @param String str JAXB generated XML instance
     * @return String that does not have xml namespace attribute
     */
   private String removeXmlNs(String str) {
     int startidx = str.indexOf("xmlns=");
     int endidx = str.indexOf(">",startidx + 3);
     endidx--;
     StringBuffer sb = new StringBuffer();
     sb.append(str.substring(0,startidx -1));
     sb.append(str.substring(endidx +1 , str.length() -1 ));
     return sb.toString();
   }

   /**
     * This method adds the xmlns attribute to the root element. JAXB unmarshaller
     * requires this attribute to function properly.
     *
     * @param String str String retrieved from the database
     * @return String that has xml namespace attribute
     */
   private String addXmlNs(String str) {
     int startidx = str.indexOf(">");
     StringBuffer sb = new StringBuffer();
     sb.append(str.substring(0,startidx));
     sb.append(" xmlns=\"http://jaxbderived/profile/\">");
     sb.append(str.substring(startidx+1,str.length() -1));  
     return sb.toString();
   }
}