customerorder.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 CustomerOrder {

  public static final String JDBCURL="jdbc:cloudscape:c:/Wrox4370.db";
  public static final String JDBCDRIVER="COM.cloudscape.core.JDBCDriver";
  public static final String OUTPUTFILE="c://temp//customerOrder.xml";
  public StringBuffer sql = new StringBuffer();

  /*
     The CustomerOrder() constructor has only one task.  To build the SQL
     statement that will be used to retrieve the Customer Order data from
     the Cloudscape database.
  */
  public CustomerOrder() {
    sql.append("SELECT                                                        ");
    sql.append("  CustomerOrders.orderId,                                     ");
    sql.append("  Customers.customerFirstName,                                ");
    sql.append("  Customers.customerLastName,                                 ");
    sql.append("  CustomerOrderItems.linenumber,                              ");
    sql.append("  CustomerOrderItems.quantity,                                ");
    sql.append("  CustomerOrderItems.pricePerItem,                            ");
    sql.append("  Recordings.recordingtitle                                   ");
    sql.append("FROM                                                          ");
    sql.append("  CustomerOrders,                                             ");
    sql.append("  CustomerOrderItems,                                         ");
    sql.append("  Customers,                                                  ");
    sql.append("  OrderStatuses,                                              ");
    sql.append("  PostalCodes,                                                ");
    sql.append("  Recordings                                                  ");
    sql.append("WHERE                                                         ");
    sql.append("  CustomerOrders.orderId=CustomerOrderItems.orderId           ");
    sql.append("AND                                                           ");
    sql.append("  CustomerOrders.customerId = Customers.customerId            ");
    sql.append("AND                                                           ");
    sql.append("  CustomerOrders.shipToPostalCode = PostalCodes.postalCode    ");
    sql.append("AND                                                           ");
    sql.append("  CustomerOrders.OrderStatusId = OrderStatuses.OrderStatusId  ");
    sql.append("AND                                                           ");
    sql.append("  CustomerOrderItems.productId = Recordings.recordingId       ");
    sql.append("ORDER BY                                                      ");
    sql.append("  CustomerOrders.orderId        asc,                          ");
    sql.append("  CustomerOrderItems.lineNumber asc                           ");
  }

  /*
     The retrieveCustomerOrders() method will retrieve all of the customer
     order data from our music record database.  It will then take the
     returned Resultset and build an XML document out of it and return
     it back to the method who called it.
  */
  public Document retrieveCustomerOrders(){
    Document xmlDoc = null;
    try{
      /*Retrieves the resultset containing all of the customer order data.*/
      ResultSet customerOrderRS = retrieveCustomerOrderRS();

      /*
        Builds the XML document that will be returned to whoever called this
        method.
      */
      xmlDoc = buildCustomerOrderXML(customerOrderRS);

      /*Creates a file that the XML file will be written to*/
      File outputFile = new File(OUTPUTFILE);

      /*Dumps the output to that file*/
      printDOM(xmlDoc, outputFile);

      /*Closes the ResultSet*/
      customerOrderRS.close();
    }
    catch(Exception e){System.out.println(e.toString());}

      return xmlDoc;
  }

  /*
     This method will connect to the cloudscape database and execute the
     SQL query defined in the constructor.
 */
  private ResultSet retrieveCustomerOrderRS(){
    ResultSet customerOrderRS = null;
    try{
      /*Making my JDBC Connection to Cloudscape*/
      Class.forName(JDBCDRIVER).newInstance();
      Connection conn= DriverManager.getConnection(JDBCURL);

      /*Retrieve my customer order data from the database*/
      Statement statement = conn.createStatement();
      customerOrderRS = statement.executeQuery(sql.toString());
    }
    catch(Exception e){
      System.out.println("Error occurred in retrieveCustomerOrderRS: " + e.toString());
    }
    return customerOrderRS;
  }

  /*
     The buildCustomerOrderXML method will build an XML document containing customer order
     information.  This document does not have as much information as the earlier
     CustomerOrderDocuments shown earlier in the chapter.
  */
  private Document buildCustomerOrderXML(ResultSet _customerOrderRS) throws Exception{
    Document xmlDoc= new DocumentImpl();

    /*Creating the document root*/
    Element documentRoot     = xmlDoc.createElement("CUSTOMERORDERS");
    Element customerOrder    = xmlDoc.createElement("CUSTOMERORDER");

    xmlDoc.appendChild(documentRoot);

    /*Declaring the different Elements found within a customer record*/
    Element header                = null;
    Element customerOrderItems    = null;
    Element customerOrderItem     = null;

    /*Initializing customer Order id holders*/
    String currentCustomerOrderId = "";
    String holderCustomerOrderId  = "";


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

      /*Getting the customer id of the current record being looked at*/
      currentCustomerOrderId = _customerOrderRS.getString("orderId");
      if (!(currentCustomerOrderId.equals(holderCustomerOrderId))){

        holderCustomerOrderId = new String(currentCustomerOrderId);

        /*Initialize the different elements found within an customer Order Record*/
        header               = xmlDoc.createElement("HEADER");
        customerOrderItems   = xmlDoc.createElement("CUSTOMERORDERITEMS");
        customerOrder        = xmlDoc.createElement("CUSTOMERORDER");

        /*Setting up my primary key for my customer order*/
        customerOrder.setAttribute("orderid", holderCustomerOrderId);

        /*Adding my different elements to the order header*/
        header.appendChild(buildDBElement(xmlDoc, "CUSTOMERFIRSTNAME",      _customerOrderRS.getString("customerFirstName")));
        header.appendChild(buildDBElement(xmlDoc, "CUSTOMERLASTNAME",       _customerOrderRS.getString("customerLastName")));

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

      customerOrderItem = xmlDoc.createElement("CUSTOMERORDERITEM");
      customerOrderItem.setAttribute("linenumber",                        _customerOrderRS.getString("lineNumber"));
      customerOrderItem.appendChild(buildDBElement(xmlDoc,"PRODUCTDESCR", _customerOrderRS.getString("recordingTitle")));

      Double lineItemQuantity = new Double(_customerOrderRS.getString("quantity"));
      Double pricePerItem     = new Double(_customerOrderRS.getString("pricePerItem"));

      Double lineItemPrice    = new Double(lineItemQuantity.doubleValue()*pricePerItem.doubleValue());

      customerOrderItem.appendChild(buildDBElement(xmlDoc,"LINEITEMPRICE", lineItemPrice.toString()));

      /*Appending everything together*/
      customerOrderItems.appendChild(customerOrderItem);
      customerOrderItem = xmlDoc.createElement("CUSTOMERORDERITEM");

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

    return xmlDoc;
  }

  /*
     The printDOM method will take the XML document and the File object
     passed into the method and dump its contents out to a file.  This is
     only here for debugging purposes and should not be used in production code.
  */
  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;
  }

  /*
     This main function was left in here for testing purposes.   Running this class
     will retrieve all of the customer order data from the music record database
     and write out the XML DOM to the file defined in the OUTPUT file variable.
  */
  public static void main(String args[]){
    CustomerOrder cs = new CustomerOrder();

    cs.retrieveCustomerOrders();
  }
}