# Example Class Usage Notes

The example class specifies how to build a dynamic where clause. Each non-BLOB column in the table can optionally be included in the where clause. Examples are the best way to demonstrate the usage of this class.

The example class can be used to generate a virtually unlimited where clauses.

The example classes contain an inner static class called `Criteria` that holds a list of conditions that will be `anded` together in the where clause. The example class holds a list of `Criteria` objects and all the clauses from the inner classes will be `ored` together. Using different sets of `Criteria` classes allows you to generate virtually unlimited types of where clauses.

`Criteria` objects can be created with the either the `createCriteria` method or the `or` method in the example class. When the first `Criteria` object is created with the `createCriteria` method it is automatically added to the list of `Criteria` objects - this makes it easy to write a simple where clause if you don't need to `or` several other clauses together. When using the `or` method, the `Criteria` class is added to the list in all instances.

Important We recommend that you only use the `or` method for creating `Criteria` classes. We believe that this method makes for more readable code.

## Simple Queries

This example shows how to generate a simple WHERE clause using the generated example class:

      TestTableExample example = new TestTableExample();

      example.createCriteria().andField1EqualTo(5);

Alternatively, the following syntax also works:

      TestTableExample example = new TestTableExample();

      example.or().andField1EqualTo(5);

In either above example, the dynamically generated where clause will effectively be:

      where field1 = 5

## Complex Queries

The next example shows how to generate a complex WHERE clause using the generated example class (using JSE 5.0 parameterized types):

      TestTableExample example = new TestTableExample();

      example.or()
        .andField1EqualTo(5)
        .andField2IsNull();

      example.or()
        .andField3NotEqualTo(9)
        .andField4IsNotNull();

      List<Integer> field5Values = new ArrayList<Integer>();
      field5Values.add(8);
      field5Values.add(11);
      field5Values.add(14);
      field5Values.add(22);

      example.or()
        .andField5In(field5Values);

      example.or()
        .andField6Between(3, 7);

In the above example, the dynamically generated where clause will effectively be:

      where (field1 = 5 and field2 is null)
         or (field3 <> 9 and field4 is not null)
         or (field5 in (8, 11, 14, 22))
         or (field6 between 3 and 7)

Returned records will meet these criteria.

## Distinct Queries

You can force queries to be DISTINCT by calling the `setDistinct(true)` method on any example class.

## Criteria Classes

The `Criteria` inner class includes `andXXX` methods for each field, and each standard SQL predicate including:

- IS NULL - meaning the related column must be NULL
- IS NOT NULL - meaning the related column must not be NULL
- = (equal) - meaning the related column must be equal to the value passed in on the method call
- <> (not equal) - meaning the related column must not be equal to the value passed in on the method call
- > (greater than) - meaning the related column must be greater than the value passed in on the method call
- >= (greater than or equal) - meaning the related column must be greater than or equal to the value passed in on the method call
- < (less than) - meaning the related column must be less than the value passed in on the method call
- <= (less than or equal) - meaning the related column must be less than or equal to the value passed in on the method call
- LIKE - meaning the related column must be "like" the value passed in on the method call. The code does not add the required '%', you must set that value yourself in the value you pass in on the method call.
- NOT LIKE - meaning the related column must be "not like" the value passed in on the method call. The code does not add the required '%', you must set that value yourself in the value you pass in on the method call.
- BETWEEN - meaning the related column must be "between" the two values passed in on the method call.
- NOT BETWEEN - meaning the related column must be "not between" the two values passed in on the method call.
- IN - meaning the related column must be one of the list of values passed in on the method call.
- NOT IN - meaning the related column must not be one of the list of values passed in on the method call.