public static void main(String[] args)
  {
    // Set a break point on the following line and then step into the
    // constructor.  It creates the DataContext and initializes Cayenne.
    Main main = new Main();

  public Main()
  {
    super();

    // Create the Cayenne DataContext.  This will also initialize the Cayenne framework.
    dataContext = DataContext.createDataContext();

    // Cayenne supports sending "raw" SQL to the database via SQLTemplates.
    // We prepare the database by deleting any existing data.  NOTE: If you
    // created your database with constraints, this will probably fail.  The
    // QueryChain allows all of these deletes (or other operations) to be done
    // in a single transaction.
    QueryChain  chain = new QueryChain();

    // Add raw SQL templates to the chain.
    chain.addQuery(new SQLTemplate(Customer.class, "DELETE FROM Customers"));
    chain.addQuery(new SQLTemplate(Item.class, "DELETE FROM Items"));
    chain.addQuery(new SQLTemplate(LineItem.class, "DELETE FROM LineItems"));
    chain.addQuery(new SQLTemplate(Order.class, "DELETE FROM Orders"));

    // Execute all of the SQL statements.
    dataContext.performNonSelectingQuery(chain);
  }

    // Step into this method, too, and step through the example.
    main.runExample();
  }

  // Runs the example.  Be sure you step into this or have a breakpoint set.
  private void runExample()
  {
    // Create all the items and customers (and customer orders).  These two
    // initialize our example database with initial data.  Step into both of
    // these!
    createItems();

  // Helper method to create (insert) all of the "static" Items in the database.
  private void createItems()
  {
    Item item;

    // Cayenne needs to have database objects registered with a DataContext.
    // This allows Cayenne to track changes to the objects and to know
    // which objects to consider when it is time to generate SQL and
    // communicate changes with the database.  The easist way to do this
    // is to create your objects in a DataContext right from the start.
    // Do this using createAndRegisterNewObject() and pass the .class
    // you want and Cayenne will create, register, and return the
    // object ready to use.

    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("PowerBook12C");
    item.setDescription("Apple PowerBook 12\" 1.5 GHz G4 with Combo Drive");
    item.setPrice(new BigDecimal(1499.00));

    // Notice that on the following line, we re-assign item.  Don't worry, the
    // item created above won't be lost -- it'll still be in the DataContext
    // instance.
    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("PowerBook12S");
    item.setDescription("Apple PowerBook 12\" 1.5 GHz G4 with Super Drive");
    item.setPrice(new BigDecimal(1699.00));

    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("PowerBook15C");
    item.setDescription("Apple PowerBook 15\" 1.5 GHz G4 with Combo Drive");
    item.setPrice(new BigDecimal(1999.00));

    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("PowerBook15S");
    item.setDescription("Apple PowerBook 15\" 1.67 GHz G4 with Super Drive");
    item.setPrice(new BigDecimal(2299.00));

    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("PowerBook17S");
    item.setDescription("Apple PowerBook 17\" 1.67 GHz G4 with Super Drive");
    item.setPrice(new BigDecimal(2699.00));

    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("iMac17C");
    item.setDescription("Apple iMac 17\" 1.8 GHz G5 with Combo Drive");
    item.setPrice(new BigDecimal(1299.00));

    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("iMac17S");
    item.setDescription("Apple iMac 17\" 2.0 GHz G5 with Super Drive");
    item.setPrice(new BigDecimal(1499.00));

    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("iMac20S");
    item.setDescription("Apple iMac 20\" 2.0 GHz G5 with Super Drive");
    item.setPrice(new BigDecimal(1799.00));

    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("PowerMac20D");
    item.setDescription("Apple PowerMac 2.0 GHz Dual G5 with Super Drive");
    item.setPrice(new BigDecimal(1999.00));

    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("PowerMac23D");
    item.setDescription("Apple PowerMac 2.3 GHz Dual G5 with Super Drive");
    item.setPrice(new BigDecimal(2499.00));

    item = (Item) dataContext.createAndRegisterNewObject(Item.class);
    item.setName("PowerMac27D");
    item.setDescription("Apple PowerMac 2.7 GHz Dual G5 with Super Drive");
    item.setPrice(new BigDecimal(2999.00));

    // Pay particular attention to the SQL logged by commitChanges().  When
    // you tell a Cayenne DataContext to commit it's changes, it looks for
    // all changes and then sends them to the database.  For the following
    // line, it'll allocate primary keys for all the Items above and then
    // insert them into the database.  Prior to commitChanges(), nothing
    // has been saved to the database.  The above createAndRegisterNewObject()
    // method only created the objects in memory, but commitChanges() will
    // persist them.

    dataContext.commitChanges();
  }

    createCustomers();

  // Helper method to create all of the example Customers in the database.
  // This is extremely similar to createItems() above.  Read the comments
  // there if needed.
  private void createCustomers()
  {
    Customer customer;

    customer = (Customer) dataContext.createAndRegisterNewObject(Customer.class);
    customer.setFirstName("Jonathan");
    customer.setLastName("Kent");
    // Step into the createOrder() method.
    createOrder(customer, "iMac17C", 1);

  // Helper method to create a Customer's order.
  private Order createOrder(Customer customer, String product, int quantity)
  {
    // Locate the item for the Customer.  Step into findItem().
    Item item = findItem(product);

  // Helper method to find an Item by name.
  private Item findItem(String name)
  {
    // The following expression was made "final" because it'll never change,
    // which is just a small optimization.  The expression looks for the
    // Item (the root object) of the passed in name.
    final Expression expression = Expression.fromString("name = $name");

    Map parameters = new HashMap(1);
    parameters.put("name", name);

    SelectQuery query = new SelectQuery(Item.class, expression.expWithParameters(parameters));
    List        items = dataContext.performQuery(query);

    // Make sure the item came back and there was only one match.
    if (items.size() == 1)
      return (Item) items.get(0);

    logger.debug("Could not find item!");

    return null;
  }

    // If we found the item ...
    if (item != null)
    {
      // Create and register a new LineItem and Order.
      LineItem lineItem = (LineItem) dataContext.createAndRegisterNewObject(LineItem.class);
      Order    order    = (Order) dataContext.createAndRegisterNewObject(Order.class);

      // Add the order to the Customer's to-many "orders" relationship.  Note
      // that this will automatically set the Order's relationship back to the
      // Customer.  All of the Cayenne relationship methods do that.  For a
      // to-many relationship, you "addTo" the relationship, for a to-one, you
      // use a "set" method.
      customer.addToOrders(order);
      // Add the line item to the Order's to-many "items" relationship.
      order.addToLineItems(lineItem);
      // Set the line item's to-one "item" relationship to the item.
      lineItem.setItem(item);

      // Set initial values for the order and line item.
      order.setIsFilled(false);
      order.setOrderID("Q" + counter++);
      lineItem.setQuantity(quantity);

      // Return the order (uncommitted to the database).
      return order;
    }

    // Problem -- couldn't find the item, return null.
    return null;
  }

    customer = (Customer) dataContext.createAndRegisterNewObject(Customer.class);
    customer.setFirstName("Martha");
    customer.setLastName("Kent");
    // If you stepped into the createOrder() call above, you can probably just
    // step over the following ones.
    createOrder(customer, "PowerBook12C", 1);

    customer = (Customer) dataContext.createAndRegisterNewObject(Customer.class);
    customer.setFirstName("Clark");
    customer.setLastName("Kent");
    createOrder(customer, "PowerBook15S", 1);

    customer = (Customer) dataContext.createAndRegisterNewObject(Customer.class);
    customer.setFirstName("Chloe");
    customer.setLastName("Sullivan");
    createOrder(customer, "PowerMac27D", 2);

    customer = (Customer) dataContext.createAndRegisterNewObject(Customer.class);
    customer.setFirstName("Lex");
    customer.setLastName("Luthor");
    createOrder(customer, "PowerBook17S", 1);

    // Persist everything to the database.  Watch the SQL logged and you'll
    // see primary keys fetched for the Customer, Order, and LineItem objects.
    // You'll also see inserts for all of these objects with the proper
    // primary and foreign keys set for everything.

    dataContext.commitChanges();
  }

    // Perform a variety of queries against the database and modify some of
    // the retrieved records, too.  Step into the method.
    performQueries();
  }

  // This is the workhorse of our example for querying and retrieving data
  // from the database.  Some of the records retrieved will be modified or
  // deleted, too.
  private void performQueries()
  {
    Expression  expression; // Expressions contain WHERE criteria
    Map         parameters; // Map of data to substitute in an expression
    SelectQuery query;      // Query to use when fetching data
    List        values;     // Values returned from query

    // Create an expression (the "WHERE" clause, in SQL parlance) for the data
    // we want.
    expression = Expression.fromString("firstName = $firstName and lastName = $lastName");

    // Expressions usually need to consume parameters (such as data entered
    // into a web application).  We put that data in a HashMap.  Variables
    // are substituted into the expression via the $ placeholders.  The
    // above expression has two variables for substitution: $firstName and
    // $lastName.  The keys in the HashMap need to match the variable names
    // (minus the $).
    parameters = new HashMap(2);
    parameters.put("lastName", "Kent");

    // What about the firstName?  Well, Cayenne will, by default, remove criteria
    // missing in the expression.  Since we don't add a firstName to the HashMap,
    // Cayenne is smart enough to reduce the expression to "lastName = $lastName".
    // This can be very convenient in a search page/etc since the search will
    // automatically broaden when the user enters less information.

    // We can now create an SQL query with the expression and map data.  The
    // expWithParameters() method returns a new expression (SelectQuery needs
    // an expression) built from the initial expression and substitution values.
    // The SelectQuery constructor below (there are other forms available) tells
    // Cayenne to root the query at the Customer object and to retrieve Customers
    // whose criteria matches the expression with the parameters in the HashMap.
    // When you step over the performQuery() call, Cayenne will access the database
    // and retrieve the matching Customer records and return them in a List.
    query  = new SelectQuery(Customer.class, expression.expWithParameters(parameters));
    values = dataContext.performQuery(query);

    logger.info("Customers named Kent:");
    logger.info(values);
{noformat:bgColor=#DADAE2}
INFO Main: Customers named Kent:
INFO Main:
    [{<ObjectId:CustomerprimaryKey=560>; committed; [orders=>(..); firstName=>Jonathan; lastName=>Kent]},
     {<ObjectId:CustomerprimaryKey=562>; committed; [orders=>(..); firstName=>Clark; lastName=>Kent]},
     {<ObjectId:CustomerprimaryKey=563>; committed; [orders=>(..); firstName=>Martha; lastName=>Kent]}]
{noformat}
{code:java|title=performQueries() Method Continued}
    // Qualify the search a little more and add a firstName parameter.
    parameters.put("firstName", "Jonathan");

    // Perform the query again and get new results.
    query  = new SelectQuery(Customer.class, expression.expWithParameters(parameters));
    values = dataContext.performQuery(query);

    logger.info("Customers named Jonathan Kent:");
    logger.info(values);

    // Cayenne supports a dotted path notation for representing relationships.
    // The following expression, which will be rooted at the Customer, tells
    // Cayenne to also join with the Order, LineItem, and Item objects.  The
    // information contained in the model is used to determine how to constuct
    // the joins.
    expression = Expression.fromString("orders.lineItems.item.name = $name");
    parameters = new HashMap(1);
    parameters.put("name", "PowerMac27D");

    // Pay particular attention to the SQL logged from the performQuery() here
    // to see how Cayenne created all the joins to retrieve the data.
    query  = new SelectQuery(Customer.class, expression.expWithParameters(parameters));
    values = dataContext.performQuery(query);

    logger.info("Customers who bought a PowerMac 2.7 GHz Dual G5:");
    logger.info(values);

    // If you already have a database object in memory, Cayenne can handle
    // an expression referring to it, too.  This is the same as the above
    // expression, except an entire complex Cayenne DataObject is the match
    // criteria.  (The findItem() method will return the matching database
    // object -- step into it if you like.)
    expression = Expression.fromString("orders.lineItems.item = $item");
    parameters = new HashMap(1);
    parameters.put("item", findItem("PowerBook17S"));

    // Again, watch the SQL logged from performQuery().  Cayenne creates all the joins automatically.
    query  = new SelectQuery(Customer.class, expression.expWithParameters(parameters));
    values = dataContext.performQuery(query);

    logger.info("Customers who bought a PowerBook 17:");
    logger.info(values);

    // This is another example of a complex query, doing joins in different
    // directions, and rooted at the LineItem instead of Customer.
    expression = Expression.fromString("item = $item and order.customer.firstName = $firstName and order.customer.lastName = $lastName");
    parameters = new HashMap(3);
    parameters.put("item", findItem("PowerMac27D"));

    parameters.put("firstName", "Chloe");
    parameters.put("lastName",  "Sullivan");

    query  = new SelectQuery(LineItem.class, expression.expWithParameters(parameters));
    values = dataContext.performQuery(query);

    // It is usually a good idea to see how many rows you get back, especially
    // if you are only expecting a single row.
    if (values.size() == 1)
    {
      LineItem lineItem = (LineItem) values.get(0);

      // Update the quantity to 1 (instead of 2).  Cayenne tracks changes to
      // your database objects.  It knows not only which database objects have
      // been modified, but it also knows which attributes have been modified.
      lineItem.setQuantity(1);

      // When we commit the changes, since Cayenne is aware of all the changes,
      // it'll only update (or insert or delete) the database objects that have
      // changes.  Also, if you are using optimistic locking (which is advisable
      // and this example is using it), Cayenne includes all of the original
      // values in the database object that you have specified to optimistically
      // lock on as part of the WHERE clause.  Pay attention to the SQL logged
      // from commitChanges() to see how it does locking.  You can also experiment
      // here and change the database contents for this record (say, to a 5)
      // before calling commitChanges() and Cayenne will throw an optimistic
      // locking exception.
      dataContext.commitChanges();
    }

    // Find Jonathan Kent's record -- he called in and wants to cancel his order.
    expression = Expression.fromString("firstName = $firstName and lastName = $lastName");
    parameters = new HashMap(2);
    parameters.put("firstName", "Jonathan");
    parameters.put("lastName",  "Kent");

    query  = new SelectQuery(Customer.class, expression.expWithParameters(parameters));
    values = dataContext.performQuery(query);

    if (values.size() == 1)
    {
      // Note in the logs that the following line must obtain primary keys
      // for the related objects since we have cascading delete turned on.
      // Deleting a customer deletes the associated orders and line items.
      // Yes, deleting one record can actually delete even more if you
      // model it to do that.  This ensures that dependent records are
      // cleaned up automatically.
      dataContext.deleteObject((Customer) values.get(0));

      // The log will show three deletes.
      dataContext.commitChanges();
    }

    // Cayenne allows expressions to be modeled in Cayenne Modeler, too.  This
    // might be a convenient approach when an expression is commonly used in
    // many places in the code.  It reduces the chance of errors and makes the
    // expression easier to maintain.  These expression work just like the
    // above ones, though.  Missing values can automatically be pruned out
    // when needed (although that isn't shown here).

    // As you can see here, a simple expression is pretty easy to call.  The
    // "false" parameter tells Cayenne to return a cached value if possile
    // (that is, if "Result Caching" in the modeler isn't "No Result Caching"
    // and if the "Refresh Results" flag is not checked -- refresh results
    // will trump caching).
    values = dataContext.performQuery("UnfilledOrders", false);

    logger.info("Unfilled Orders = " + values);

    // One way to call with parameters.
    parameters = new HashMap(1);
    parameters.put("orderID", "Q1000003");
    values = dataContext.performQuery("FindOrder", parameters, false);

    logger.info("Order Q1000003 = " + values);

    // Another way to call with parameters.
    SelectQuery prototype = (SelectQuery) dataContext.getEntityResolver().lookupQuery("FindOrder");
    parameters = new HashMap(1);
    parameters.put("orderID", "Q1000004");
    query = prototype.queryWithParameters(parameters);
    values = dataContext.performQuery(query);

    logger.info("Order Q1000004 = " + values);

    // That's all!
  }

lineItem.setQuantity(1);

mrg=> select * from lineitems;
 itemfk | orderfk | primarykey | quantity 
--------+---------+------------+----------
    600 |     604 |        600 |        1
    609 |     603 |        601 |        1
    604 |     600 |        602 |        1
    608 |     602 |        603 |        2
    610 |     601 |        604 |        1
(5 rows)

mrg=> update lineitems set quantity = 5 where primarykey = 603;
UPDATE 1

      // Update the quantity to 1 (instead of 2).  Cayenne tracks changes to
      // your database objects.  It knows not only which database objects have
      // been modified, but it also knows which attributes have been modified.
      lineItem.setQuantity(1);

      // When we commit the changes, since Cayenne is aware of all the changes,
      // it'll only update (or insert or delete) the database objects that have
      // changes.  Also, if you are using optimistic locking (which is advisable
      // and this example is using it), Cayenne includes all of the original
      // values in the database object that you have specified to optimistically
      // lock on as part of the WHERE clause.  Pay attention to the SQL logged
      // from commitChanges() to see how it does locking.  You can also experiment
      // here and change the database contents for this record (say, to a 5)
      // before calling commitChanges() and Cayenne will throw an optimistic
      // locking exception.
      dataContext.commitChanges();

INFO QueryLogger: --- will run 1 query.
INFO QueryLogger: --- transaction started.
INFO QueryLogger: UPDATE LineItems SET quantity = ? WHERE primaryKey = ? AND quantity = ? AND itemFK = ? AND orderFK = ?
INFO QueryLogger: [bind: 1, 603, 2, 608, 602]
INFO QueryLogger: *** error.
org.objectstyle.cayenne.access.OptimisticLockException: [v.1.2RC1 May 31 2006]
    Optimistic Lock Failure, SQL:
      [UPDATE LineItems SET quantity = ? WHERE primaryKey = ? AND quantity = ? AND itemFK = ? AND orderFK = ?],
      WHERE clause bindings: [primaryKey=603, quantity=2, orderFK=602, itemFK=608]
	at org.objectstyle.cayenne.access.jdbc.BatchAction.runAsIndividualQueries(BatchAction.java:235)
	at org.objectstyle.cayenne.access.jdbc.BatchAction.performAction(BatchAction.java:117)
	at org.objectstyle.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:95)
	at org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:309)
	at org.objectstyle.cayenne.access.DataDomainFlushAction.runQueries(DataDomainFlushAction.java:255)
	at org.objectstyle.cayenne.access.DataDomainFlushAction.flush(DataDomainFlushAction.java:177)
	at org.objectstyle.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:829)
	at org.objectstyle.cayenne.access.DataDomain$2.transform(DataDomain.java:800)
	at org.objectstyle.cayenne.access.DataDomain.runInTransaction(DataDomain.java:855)
	at org.objectstyle.cayenne.access.DataDomain.onSync(DataDomain.java:797)
	at org.objectstyle.cayenne.access.DataContext.flushToParent(DataContext.java:1261)
	at org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:1165)
	at com.foo.cayenne.example.Main.performQueries(Main.java:330)
	at com.foo.cayenne.example.Main.runExample(Main.java:75)
	at com.foo.cayenne.example.Main.main(Main.java:35)