onetomanyxmldata.java

此程序都是企业级 的数据库开发程序 全面揭示了JAVA对数据库的操作

import java.sql.*;                   /* JDBC Classes */
// import COM.cloudscape.core.*;        /* Cloudscape JDBC classes */
import org.w3c.dom.*;                /* W3C Interfaces */
import org.apache.xerces.dom.*;      /* Xerces DOM Classes */
import org.apache.xml.serialize.*;   /* Xerces serializer */
import java.io.*;                    /* Java io classes for file reading/writing */

public class OneToManyXmlData {
  public static final String JDBCURL = 
    "jdbc:cloudscape:c:/wrox/database/Wrox4370.db";
  public static final String JDBCDRIVER = "COM.cloudscape.core.JDBCDriver";
  public static StringBuffer SQL = new StringBuffer("");
  public static final String OUTPUTFILE = "c:\\customerOrder.xml";

  /*
   * The intializeSQL method builds the SQL statement using a StringBuffer.
   */
  public static void initializeSQL() {
    SQL.append("SELECT                            ");
    SQL.append("  CustomerOrders.orderid,         ");
    SQL.append("  CustomerOrders.orderdate,       ");
    SQL.append("  CustomerOrders.customerid,      ");
    SQL.append("  CustomerOrderItems.linenumber,  ");
    SQL.append("  CustomerOrderItems.productid,   ");
    SQL.append("  CustomerOrderItems.quantity,    ");
    SQL.append("  CustomerOrderItems.priceperitem ");
    SQL.append("FROM                              ");
    SQL.append("  CustomerOrders,                 ");
    SQL.append("  CustomerOrderItems              ");
    SQL.append("WHERE                             ");
    SQL.append("  CustomerOrders.orderid=         ");
    SQL.append("  CustomerOrderItems.orderid      ");
    SQL.append("ORDER BY                          ");
    SQL.append("  CustomerOrders.orderid        asc,         ");
    SQL.append("  CustomerOrderItems.linenumber asc  ");
  } 

  /*
   * The following is an example of how to capture a one-to-many relationship
   * using XML. The data being pulled is invoice and line item data being pulled
   * from the invoice and line item tables.
   */

  public static void main(String args[]) {
    try {

      /**
       * ****Step 1 - Making my JDBC Connection to Cloudscape****
       */
      Class.forName(JDBCDRIVER).newInstance();
      Connection conn = DriverManager.getConnection(JDBCURL);

      /**
       * ****Step 2 - Retrieve my order data from the database****
       */
      initializeSQL();
      Statement statement = conn.createStatement();
      ResultSet customerOrderRS = statement.executeQuery(SQL.toString());

      /**
       * ****Step 3 - Build CustomerOrder XML DOM.****
       */
      Document xmlDoc = buildCustomerOrderXML(customerOrderRS);

      /**
       * ****Step 4 - Writing the output to a file ****
       */
      File outputFile = new File(OUTPUTFILE);
      printDOM(xmlDoc, outputFile);

      customerOrderRS.close();   /* Closing my result set */
    } catch (Exception e) {
      System.out.println("Really poor exception handling: " + e.toString());
    } 
  } 

  /*
   * The buildCustomerOrderXML method will build an XML document containing
   * CustomerOrder information.
   */
  private static Document buildCustomerOrderXML(ResultSet _customerOrderRS) 
                                                throws Exception {
    Document xmlDoc = new DocumentImpl();

    /* Creating the root element */
    Element rootElement = xmlDoc.createElement("CUSTOMERORDERS");
    xmlDoc.appendChild(rootElement);

    /* Declaring the different Elements found within the invoice */
    Element customerOrder = null;
    Element customerOrderItems = null;
    Element customerOrderItem = null;

    /* Initializing order id holders */
    String currentOrderId = "";   /* Order id of the current resultset record */
    String holderOrderId = "";

    /*
     * Holder for the order id being currently
     * built
     */


    /* Cycling through all of the records within the resultset */
    while (_customerOrderRS.next()) {

      /* Getting the order id of the current record being looked at */
      currentOrderId = _customerOrderRS.getString("orderid");

      /*
       * If the current order id does not match the holder invoice id
       * (which will be an empty string when we process the first record
       * in the resultset) then we are dealing with a new customer order and will
       * have to build the customer order header information. Otherwise,
       * the record is for the same customer order.
       */
      if (!(currentOrderId.equals(holderOrderId))) {

        /* Set the holder order id to be the current invoice id */
        holderOrderId = new String(currentOrderId);

        /* Initialize the different elements found within an invoice record */
        customerOrder = xmlDoc.createElement("CUSTOMERORDER");
        customerOrderItems = xmlDoc.createElement("CUSTOMERORDERITEMS");
        customerOrderItem = xmlDoc.createElement("CUSTOMERORDERITEM");

        /* Setting up my primary key for my invoice */
        customerOrder.setAttribute("orderid", 
                                   _customerOrderRS.getString("orderid"));

        /* Adding my different elements to the invoice header */
        customerOrder.appendChild(buildDBElement(xmlDoc, "CUSTOMERID", 
                                       _customerOrderRS.getString("customerid")));
        customerOrder.appendChild(buildDBElement(xmlDoc, "ORDERDATE", 
                                        _customerOrderRS.getString("orderdate")));

        /* Appending my lines element */
        customerOrder.appendChild(customerOrderItems);
      } 

      /* Building my line item data */
      customerOrderItem.setAttribute("linenumber", 
                                     _customerOrderRS.getString("linenumber"));
      customerOrderItem.appendChild(buildDBElement(xmlDoc, "PRODUCTID", 
              _customerOrderRS.getString("productid")));
      customerOrderItem.appendChild(buildDBElement(xmlDoc, "PRICEPERITEM", 
              _customerOrderRS.getString("priceperitem")));
      customerOrderItem.appendChild(buildDBElement(xmlDoc, "QUANTITY", 
              _customerOrderRS.getString("quantity")));

      /* Appending everything together */
      customerOrderItems.appendChild(customerOrderItem);

      customerOrderItem = xmlDoc.createElement("CUSTOMERORDERITEM");

      customerOrder.appendChild(customerOrderItems);
      rootElement.appendChild(customerOrder);
    } 

    return xmlDoc;
  } 

  /*
   * The printDOM method below will write the contents of the xml document
   * passed into it out to the a file. The method will that the xml document
   * will be written out is defined by the File object passed into it.
   */
  private static void printDOM(Document _xmlDoc, 
                               File _outputFile) throws Exception {
    OutputFormat outputFormat = new OutputFormat("XML", "UTF-8", true);
    FileWriter fileWriter = new FileWriter(_outputFile);

    XMLSerializer xmlSerializer = new XMLSerializer(fileWriter, outputFormat);
    xmlSerializer.asDOMSerializer();

    xmlSerializer.serialize(_xmlDoc.getDocumentElement());
  } 

  /*
   * The buildDBElement will add a text node onto an element and return it back to
   * the method that calls it. This is simply a helper method that makes it easier
   * to add elements to another element.
   */
  private static Element buildDBElement(Document _xmlDoc, String _elementName, 
                                        String _elementValue) throws Exception {

    Element item = _xmlDoc.createElement(_elementName);
    item.appendChild(_xmlDoc.createTextNode(_elementValue));

    return item;
  } 
}