@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; }