RimuDB Logo
Database Access Framework for Java

WhereLists and OrderByLists

When constructing queries, simple queries can be built with WhereLists and OrderByLists. WhereLists and OrderByLists have the advantage of strictly controlling the where clause of an SQL statement that is created. This means that at compile time you can insure the SQL statement contains valid column names, operators and type safe values.

When to Use WhereLists

  • The SQL only uses a single table. i.e. no joins
  • The only comparison operators used are: =, !=, <, <=, >, >=, IN, NOT IN
  • The boolean operators between conditions are simple. Although both AND and OR are supported, parentheses are not.

If you need to construct a query that is more complex than this then use a GenericQuery.

WhereList Methods

A WhereList is a list of property names, comparison operators and associated values. The property names are converted to column names and an SQL where clause is built. A prepared statement is created and the values bound to it.

There are several variations of the WhereList.add() method. This following parameters can be set:

  • booleanOperator - This enumeration is either BooleanOperator.AND or BooleanOperator.OR
  • propertyName - This should be a static variable from the DO class i.e. OrderTransactionDO.F_LAST_NAME.
  • operator - This enumeration is one of the following:
    • Operator.EQ
    • Operator.NE
    • Operator.GT
    • Operator.GE
    • Operator.LT
    • Operator.LE
    • Operator.IN
    • Operator.NOT_IN
  • value - This is the value to be used for comparison. In the case of the IN and NOT_IN operators, it must be an array.
  • valueIsProperty - Set to true if the value is the name of another property in the DO class. i.e. OrderTransactionDO.F_FIRST_NAME.

There are several variations of the WhereList.add() method:


public WhereList add(BooleanOperator booleanOperator, String propertyName, Operator operator, 
                     Object value, boolean valueIsProperty)

public WhereList add(String propertyName, Operator operator, Object value, boolean valueIsProperty) {

public WhereList add(BooleanOperator booleanOperator, String propertyName, Operator operator, 
                     Object value) {

public WhereList add(String propertyName, Operator operator, Object value) {

There is also a convenience method that uses a BooleanOperator.AND and an Operator.EQ.


public WhereList add_AND_EQ(String propertyName, Object value) {

The following example demonstrates a simple query:


// Create a finder
OrderTransactionFinder finder = new OrderTransactionFinder(compositeDatabase); 

// Reading records that match a Wherelist
WhereList whereList = new WhereList();
whereList.add(OrderTransactionDO.F_LAST_NAME, Operator.EQ, "Smith");
whereList.add(OrderTransactionDO.F_FIRST_NAME, Operator.EQ, "John");
OrderTransactionDO[] orderTransactions = finder.search(whereList, null);

// SQL Generated:
SELECT * FROM order_transactions 
WHERE last_name = ? AND first_name = ?

OrderByLists

The order by clause can be defined with an OrderByList.


// Create a finder
OrderTransactionFinder finder = new OrderTransactionFinder(compositeDatabase); 

// Reading records that match a Wherelist
WhereList whereList = new WhereList();
whereList.add(OrderTransactionDO.F_LAST_NAME, Operator.EQ, "Smith");
whereList.add(OrderTransactionDO.F_FIRST_NAME, Operator.EQ, "John");
OrderByList orderByList = new OrderByList();
orderByList.add(OrderTransactionDO.F_CUSTOMER_NR);
OrderTransactionDO[] orderTransactions = finder.search(whereList, orderByList);

// SQL Generated:
SELECT * FROM order_transactions 
WHERE last_name = ? AND first_name = ?
ORDER BY customer_nr

Using an OR Boolean Operator

By default multiple conditions in a WhereLists use an AND boolean operator, but if needed, an OR operator can be specified.


// Create a finder
OrderTransactionFinder finder = new OrderTransactionFinder(compositeDatabase); 

// Reading records that match a Wherelist
WhereList whereList = new WhereList();
whereList.add(OrderTransactionDO.F_LAST_NAME, Operator.EQ, "Smith");
whereList.add(BooleanOperator.OR, OrderTransactionDO.F_FIRST_NAME, Operator.EQ, "John");
OrderByList orderByList = new OrderByList();
orderByList.add(OrderTransactionDO.F_CUSTOMER_NR);
OrderTransactionDO[] orderTransactions = finder.search(whereList, orderByList);

// SQL Generated:
SELECT * FROM order_transactions 
WHERE last_name = ? OR first_name = ?
ORDER BY customer_nr

Comparison with Another Column

Sometimes a where clause needs to compare two columns with each other.


// Create a finder
OrderTransactionFinder finder = new OrderTransactionFinder(compositeDatabase); 

// Reading records that match a Wherelist
WhereList whereList = new WhereList();
whereList.add(OrderTransactionDO.F_LAST_NAME, Operator.EQ, OrderTransactionDO.F_FIRST_NAME, true);
OrderByList orderByList = new OrderByList();
orderByList.add(OrderTransactionDO.F_CUSTOMER_NR);
OrderTransactionDO[] orderTransactions = finder.search(whereList, orderByList);

// SQL Generated:
SELECT * FROM order_transactions 
WHERE last_name = first_name
ORDER BY customer_nr

Using the IN Comparison Operator

The IN operator allows slightly more complex SQL statements to be created.


// Create a finder
OrderTransactionFinder finder = new OrderTransactionFinder(compositeDatabase); 

String values[] = new String[] {"Alice", "Bob", "Charlie"};
whereList.add(OrderTransactionDO.F_FIRST_NAME, Operator.IN, values);
whereList.add(OrderTransactionDO.F_CUSTOMER_NR, Operator.NE, 55555);
OrderByList orderByList = new OrderByList();
orderByList.add(OrderTransactionDO.F_CUSTOMER_NR);
OrderTransactionDO[] orderTransactions = finder.search(whereList, orderByList);

// SQL Generated:
SELECT * FROM order_transactions 
WHERE first_name IN ('Alice', 'Bob', 'Charlie') AND customer_nr != 55555
ORDER BY customer_nr

Ascending or Descending in an OrderByList

OrderByLists are simple to use. Properties can be specified as either ascending (default) or descending, by passing a boolean on the add() method.


// Create a finder
OrderTransactionFinder finder = new OrderTransactionFinder(compositeDatabase); 

// Reading records that match a Wherelist
WhereList whereList = new WhereList();
whereList.add(OrderTransactionDO.F_LAST_NAME, Operator.EQ, "Smith");
whereList.add(OrderTransactionDO.F_FIRST_NAME, Operator.EQ, "John");
OrderByList orderByList = new OrderByList();
orderByList.add(OrderTransactionDO.F_CUSTOMER_NR, true);
OrderTransactionDO[] orderTransactions = finder.search(whereList, orderByList);

// SQL Generated:
SELECT * FROM order_transactions 
WHERE last_name = ? AND first_name = ?
ORDER BY customer_nr DESC