Dynamic Queries

Build queries based on user input.

1. Specify the where condition used in the dynamic query.

   Query query = new Query();
           
   AttributeTest aTest = new AttributeTest();
           
   aTest.setAttribute("state");
   aTest.setTestValue("NY");
   aTest.setTestType(AttributeTest.EQUAL);
   query.setTestCriteria(aTest);
           
   SortCriteria sort = new SortCriteria();
   sort.add("lname", SortOrderType.ASCENDING);
   sort.add("fname", SortOrderType.ASCENDING);
   query.setSortCriteria(sort);

2. Use the findWithQuery method in the MBO to dynamically retrieve a list of MBOs acccording to the specified attributes.

   GenericList<Customer> customers = Customer.findWithQuery(query);
   int n = customers.count();
   for (int i = 0; i < n;   i)
   {
      Customer c = (Customer)customers.get(i);
      System.out.println("Customer " + i + ": "
          + c.getLname() + ", "  + c.getFname());
   }
   

3. Use the generated database’s executeQuery method to query multiple MBOs through the use of joins.

   Query query = new Query();
           
   query.select("c.fname,c.lname,s.order_date,s.id");
   query.from("Customer", "c");
   query.join("Sales_order", "s", "s.cust_id", "c.id");
           
   AttributeTest ts = new AttributeTest();
   ts.setAttribute("lname");
   ts.setTestValue("Smith");
   ts.setOperator(AttributeTest.EQUAL);
   query.setTestCriteria(ts);
   QueryResultSet qrs = SUP101DB.executeQuery(query);
           
   while(qrs.next())
   {
      System.out.println("order: " 
         qrs.getInt(4) +           // 4 is s.id
         qrs.getString(1) +        // 1 is c.fname
         ", " + qrs.getString(2) + // 2 is c.lname
         " "  + qrs.getDate(3));   // 3 is s.order_date
   }