"Strategies for Storing Java Objects in Relational Databases"
Vol. 6, Issue 8, p. 70

	


Listing 1: Embedded SQL Code in the Order Class

    /**
     * Save an order and its aggregate order Items
     */
    private void save(Connection connection) throws SQLException {


        PreparedStatement orderStatement = null;
        PreparedStatement orderItemStatement = null;
        Vector items;


        //  Build statements to either insert or update
        if (isPersistent() ) {
            orderStatement = connection.prepareStatement( "Update INTO Order VALUES(?,?,?,?,?,?,?)");
            orderItemStatement = connection.prepareStatement(OrderItem.getUpdateSQL());
        } else {
            orderStatement = connection.prepareStatement( "INSERT INTO Order VALUES(?,?,?,?,?,?,?)");
            orderItemStatement = connection.prepareStatement(OrderItem.getInsertSQL());
        }


   //  Add the order information from this object
        orderStatement.setInt(1, getOrderID());
        orderStatement.setInt(2, getCustomerNumber());
        orderStatement.setDate(3, getOrderDate());
        orderStatement.setDouble(4, getFederalTax().getNumber());
        orderStatement.setDouble(5, getStateTax().getNumber());
        orderStatement.setDouble(6, getLocalTax().getNumber());
        orderStatement.setDouble(7, getSubtotal().getNumber());


        //  Save the order and order contact information
        orderStatement.executeUpdate();
        orderStatement.close();


        //  Save the order items
        items = getOrderItems();
        for ( int i = 1; i <= items.size(); i++ ) {
            OrderItem item = (OrderItem) items.elementAt(i);
            item.save(orderItemStatement, getOrderID(), i);
        }
        orderItemStatement.close();


    }
    /**
     * Refreshes this object with the data for
     * the order with the given id.
     */
    public void retrieve(Connection connection)
        throws SQLException
    {
        OrderContact ship, bill;
        int shipToID, billToID;


        // Retrieve the record from the Order table


        PreparedStatement statement =
        connection.prepareStatement("SELECT * FROM Order WHERE OrderID = ?");
        statement.setInt(getOrderID());
        ResultSet rs = statement.executeUpdate();
        rs.next();


        setCustomerNumber(rs.getInt(2));
        setOrderDate(rs.getDate(3));
        setFederalTax(rs.getDouble(4));
        setStateTax(rs.getDouble(5));
        setLocalTax(rs.getDouble(6));
        setSubtotal(rs.getDouble(7));
        setIsPersistent(true);


        // Read the records from the OrderItem table
        try {
            Vector items = OrderItem.retrieveOrderItems(orderID);
            setOrderItems(items);
        } catch ( Exception ex ) {
            //  Handle the exception appropriately...
        }
    }


    /**
     * Delete the order and Its order Items
     */
    private void delete(Connection connection)
    throws SQLException {


        PreparedStatement ps = null;
        Vector items;


   ps = connection.prepareStatement( "DELETE Order WHERE OrderID = ?");


        ps.setInt(1, getOrderID());
        ps.executeUpdate();
        ps.close();


        //  Add deletion of the order items to the transaction
        items = getOrderItems();


        for ( int i = 1; i <= items.size(); i++ ) {
            OrderItem item = (OrderItem) items.elementAt(i);
            ps = item.delete(connection);
            ps.executeUpdate();
        }
    }



  Listing 2: Delegating Persistence of Order to OrderData
 
  /**
     * Save an order and its aggregate order Items
     */
    private void save(Connection connection) throws SQLException {
        OrderData.save(this, connection);
    }


    /**
     * Refreshes this object with the data for
     * the order with the given id.
     */
    public void retrieve(Connection connection)
        throws SQLException
    {
        OrderData.retrieve(this, connection);
    }


    /**
     * Delete the order Its order Items
     */
    private void delete(Connection connection)
    throws SQLException {
        OrderData.delete(this, connection);
    }