@Override
  public String getProducts() throws Exception {
    JAXBContext jaxbContext = JAXBContext.newInstance(Products.class);
    Marshaller jaxbMarshaller = jaxbContext.createMarshaller();
    jaxbMarshaller.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);

    Products products = new Products();
    products.setProductsList(new ArrayList<Product>());
    PreparedStatement query = null;
    Connection conn = null;
    // List<Product> productList = new ArrayList<Product>();

    try {

      conn = MySQLDB.MySQLDBConn().getConnection();
      query =
          conn.prepareStatement(
              "select ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, "
                  + "UnitsOnOrder, ReorderLevel, Discontinued from products");

      ResultSet rs = query.executeQuery();
      while (rs.next()) {
        Product product = new Product();
        product.setProductId(rs.getInt("ProductID"));
        product.setProductName(rs.getString("ProductName"));
        product.setQuantityPerUnit(rs.getString("QuantityPerUnit"));
        product.setUnitPrice(rs.getDouble("UnitPrice"));
        product.setUnitsInStock(rs.getInt("UnitsInStock"));
        product.setRecorderLevel(rs.getInt("ReorderLevel"));
        product.setDiscontinued(rs.getBoolean("Discontinued"));
        products.getProductsList().add(product);
      }
      // jaxbMarshaller.marshal(products, System.out);
      jaxbMarshaller.marshal(products, new File("c:/temp/products.xml"));
      query.close();
    } catch (SQLException sqlError) {
      sqlError.printStackTrace();
      List<Product> emptyProductList = new ArrayList<Product>();
      return "Error occured";
    } finally {
      if (conn != null) conn.close();
    }

    // XStream xstream = new XStream();
    XStream xstream = new XStream(new DomDriver());
    xstream.alias("ListProducts", Products.class);
    xstream.alias("product", Product.class);
    System.out.println(xstream.toXML(products));

    return xstream.toXML(products);
  }
  @Override
  public String getProductsWithIDAttribute() throws Exception {

    PreparedStatement query = null;
    Connection conn = null;
    // Used for the xml
    Document doc = null;
    Element root = null;
    String xmlString = null;

    try {

      // Create a document object
      DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
      DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
      doc = docBuilder.newDocument();

      // create the root element and add it to the document
      root = doc.createElement("products");
      doc.appendChild(root);

      conn = MySQLDB.MySQLDBConn().getConnection();
      query =
          conn.prepareStatement(
              "select ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, "
                  + "UnitsOnOrder, ReorderLevel, Discontinued from products");

      ResultSet rs = query.executeQuery();
      while (rs.next()) {
        Integer i = rs.getRow(); // used to populate the record tag id attribute
        // child 1 - ProductID
        Element record = doc.createElement("record");
        record.setAttribute("id", i.toString());
        root.appendChild(record);
        Element child1 = doc.createElement("child");
        child1.setAttribute("id", rs.getMetaData().getColumnName(1));
        record.appendChild(child1);
        Text text1 = doc.createTextNode(rs.getString("ProductID").trim());
        child1.appendChild(text1);
        // child 2- ProductName
        Element child2 = doc.createElement("child");
        child2.setAttribute("id", rs.getMetaData().getColumnName(2));
        record.appendChild(child2);
        Text text2 = doc.createTextNode(rs.getString("ProductName").trim());
        child2.appendChild(text2);
        // child 3- QuantityPerUnit
        Element child3 = doc.createElement("child");
        child3.setAttribute("id", rs.getMetaData().getColumnName(3));
        record.appendChild(child3);
        Text text3 = doc.createTextNode(rs.getString("QuantityPerUnit").trim());
        child3.appendChild(text3);
        // child 4- UnitPrice
        Element child4 = doc.createElement("child");
        child4.setAttribute("id", rs.getMetaData().getColumnName(4));
        record.appendChild(child4);
        Text text4 = doc.createTextNode(rs.getString("UnitPrice").trim());
        child4.appendChild(text4);
        // child 5- UnitsInStock
        Element child5 = doc.createElement("child");
        child5.setAttribute("id", rs.getMetaData().getColumnName(5));
        record.appendChild(child5);
        Text text5 = doc.createTextNode(rs.getString("UnitsInStock").trim());
        child5.appendChild(text5);
        // child 6- UnitsOnOrder
        Element child6 = doc.createElement("child");
        child6.setAttribute("id", rs.getMetaData().getColumnName(6));
        record.appendChild(child6);
        Text text6 = doc.createTextNode(rs.getString("UnitsOnOrder").trim());
        child6.appendChild(text6);
        // child 7- ReorderLevel
        Element child7 = doc.createElement("child");
        child7.setAttribute("id", rs.getMetaData().getColumnName(7));
        record.appendChild(child7);
        Text text7 = doc.createTextNode(rs.getString("ReorderLevel").trim());
        child7.appendChild(text7);
        // child 8- Discontinued
        Element child8 = doc.createElement("child");
        child8.setAttribute("id", rs.getMetaData().getColumnName(8));
        record.appendChild(child8);
        Text text8 = doc.createTextNode(rs.getString("Discontinued").trim());
        child8.appendChild(text8);
      }
      try {
        // set up a transformer
        TransformerFactory transfac = TransformerFactory.newInstance();
        Transformer trans = transfac.newTransformer();
        trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
        trans.setOutputProperty(OutputKeys.INDENT, "yes");

        // create string from xml tree
        StringWriter sw = new StringWriter();
        StreamResult result = new StreamResult(sw);
        DOMSource source = new DOMSource(doc);
        trans.transform(source, result);
        xmlString = sw.toString();
      } catch (Exception e) {
        e.printStackTrace();
      }

      query.close();
    } catch (SQLException sqlError) {
      sqlError.printStackTrace();
      return "Error occured";
    } finally {
      if (conn != null) conn.close();
    }
    System.out.println(xmlString);
    return xmlString;
  }
  @Override
  public String fetchAllProducts() throws Exception {

    PreparedStatement query = null;
    Connection conn = null;
    // Used for the xml
    Document doc = null;
    Element root = null;
    String xmlString = null;

    try {

      // Create a document object
      DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
      DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
      doc = docBuilder.newDocument();

      // create the root element and add it to the document
      root = doc.createElement("products");
      doc.appendChild(root);

      conn = MySQLDB.MySQLDBConn().getConnection();
      query =
          conn.prepareStatement(
              "select ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, "
                  + "UnitsOnOrder, ReorderLevel, Discontinued from products");

      ResultSet rs = query.executeQuery();
      while (rs.next()) {
        Integer i = rs.getRow(); // used to populate the record tag id attribute
        // child 1 - ProductID
        Element product = doc.createElement("product");
        product.setAttribute("id", i.toString());
        root.appendChild(product);
        Element ProductID = doc.createElement("ProductID");
        product.appendChild(ProductID);
        Text text1 = doc.createTextNode(rs.getString("ProductID").trim());
        ProductID.appendChild(text1);
        // ProductName
        Element ProductName = doc.createElement("ProductName");
        product.appendChild(ProductName);
        Text text2 = doc.createTextNode(rs.getString("ProductName").trim());
        ProductName.appendChild(text2);
        // QuantityPerUnit
        Element QuantityPerUnit = doc.createElement("QuantityPerUnit");
        product.appendChild(QuantityPerUnit);
        Text text3 = doc.createTextNode(rs.getString("QuantityPerUnit").trim());
        QuantityPerUnit.appendChild(text3);
        // UnitPrice
        Element UnitPrice = doc.createElement("UnitPrice");
        product.appendChild(UnitPrice);
        Text text4 = doc.createTextNode(rs.getString("UnitPrice").trim());
        UnitPrice.appendChild(text4);
        // UnitsInStock
        Element UnitsInStock = doc.createElement("UnitsInStock");
        product.appendChild(UnitsInStock);
        Text text5 = doc.createTextNode(rs.getString("UnitsInStock").trim());
        UnitsInStock.appendChild(text5);
        // UnitsOnOrder
        Element UnitsOnOrder = doc.createElement("UnitsOnOrder");
        product.appendChild(UnitsOnOrder);
        Text text6 = doc.createTextNode(rs.getString("UnitsOnOrder").trim());
        UnitsOnOrder.appendChild(text6);
        // ReorderLevel
        Element ReorderLevel = doc.createElement("ReorderLevel");
        product.appendChild(ReorderLevel);
        Text text7 = doc.createTextNode(rs.getString("ReorderLevel").trim());
        ReorderLevel.appendChild(text7);
        // Discontinued
        Element Discontinued = doc.createElement("Discontinued");
        product.appendChild(Discontinued);
        Text text8 = doc.createTextNode(rs.getString("Discontinued").trim());
        Discontinued.appendChild(text8);
      }
      try {
        // set up a transformer
        TransformerFactory transfac = TransformerFactory.newInstance();
        Transformer trans = transfac.newTransformer();
        trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
        trans.setOutputProperty(OutputKeys.INDENT, "yes");

        // create string from xml tree
        StringWriter sw = new StringWriter();
        StreamResult result = new StreamResult(sw);
        DOMSource source = new DOMSource(doc);
        trans.transform(source, result);
        xmlString = sw.toString();
      } catch (Exception e) {
        e.printStackTrace();
      }

      query.close();
    } catch (SQLException sqlError) {
      sqlError.printStackTrace();
      return "Error occured";
    } finally {
      if (conn != null) conn.close();
    }
    System.out.println(xmlString);
    return xmlString;
  }