Skip to content

Conditional Expressions

Jump to bottom
Siim Kinks edited this page Mar 21, 2017 · 3 revisions

In SqliteMagic, conditional expressions are built from the column expressions, calling various methods on them:

SQL SqliteMagic 
BOOK.TITLE = 'Foo'
BOOK.TITLE != 'Foo'
 
BOOK.TITLE.is("Foo")
BOOK.TITLE.isNot("Foo")

Boolean Operators

Conditional expressions can be connected using the and and or binary operators, as well as their not counterparts, to form new conditional expressions.

SQL SqliteMagic 
BOOK.TITLE = 'Foo' OR BOOK.PAGES > 200

((BOOK.TITLE = 'Foo' OR BOOK.PAGES > 200)
    AND NOT AUTHOR.LAST_NAME = 'Foo')
 
BOOK.TITLE.is("Foo").or(BOOK.PAGES.greaterThan(200))

BOOK.TITLE.is("Foo").or(BOOK.PAGES.greaterThan(200))
    .andNot(AUTHOR.LAST_NAME.is("Foo"))

The example above shows that in Java the number of parenthesis can quickly explode. Therefore, proper indentation may become crucial in making such code readable.

SqliteMagic does not implement any operator precedence. All operators are evaluated from left to right, as expected in an object-oriented API. This is important to understand when combining boolean operators, such as AND, OR, and NOT.

SqliteMagic wraps all conditional expressions in parenthesis:

Expr a = BOOK.TITLE.is("Foo");
Expr b = BOOK.PAGES.greaterThan(200);
Expr c = AUTHOR.LAST_NAME.is("Foo");

// These OR connected expressions
// form a new expression, wrapped in parenthesis
Expr d = a.or(b); // (a OR b)

// Same is applied when applying more operators
Expr e = d.andNot(c); // ((a OR b) AND NOT c)

Expr f = d.orNot(e); // ((a OR b) OR NOT ((a OR b) AND NOT c))

List of all the boolean operators

and(Expr)    // Combine expressions with `AND`.
andNot(Expr) // Combine expressions with `AND NOT`.
or(Expr)     // Combine expressions with `OR`.
orNot(Expr)  // Combine expressions with `OR NOT`.

Comparison Predicates

SqliteMagic supports the following comparisons:

// For Columns
is(T)                        // = (some value)
is(Column)                   // = (some column)
is(SelectNode<?, Select1>)   // = (some scalar SELECT statement)
isNot(T)                     // != (some value)
isNot(Column)                // != (some column)
isNot(SelectNode<?, Select1) // != (some scalar SELECT statement)

// For NumericColumns
greaterThan(T)                         // >  (some value)
greaterThan(NumericColumn)             // >  (some column)
greaterThan(SelectNode<?, Select1>)    // >  (some scalar SELECT statement)
greaterOrEqual(T)                      // >= (some value)
greaterOrEqual(NumericColumn)          // >= (some column)
greaterOrEqual(SelectNode<?, Select1>) // >= (some scalar SELECT statement)
lessThan(T)                            // <  (some value)
lessThan(NumericColumn)                // <  (some column)
lessThan(SelectNode<?, Select1>)       // <  (some scalar SELECT statement)
lessOrEqual(T)                         // <= (some value)
lessOrEqual(NumericColumn)             // <= (some column)
lessOrEqual(SelectNode<?, Select1>)    // <= (some scalar SELECT statement)

Null Predicate

In order to test a column expression for NULL, use the NULL predicate as such:

SQL SqliteMagic 
BOOK.TITLE IS NULL
BOOK.TITLE IS NOT NULL
 
BOOK.TITLE.isNull()
BOOK.TITLE.isNotNull()

BETWEEN Predicate

The BETWEEN predicate can be seen as syntactic sugar for a pair of comparison predicates. According to the SQL standard, the following two predicates are equivalent:

[A] BETWEEN [B] AND [C]
 
[A] >= [B] AND [A] <= [C]

Note the inclusiveness of range boundaries in the definition of the BETWEEN predicate.

The BETWEEN predicate is supported in SqliteMagic as follows:

SQL SqliteMagic 
BOOK.PAGES     BETWEEN 100 AND 1000
BOOK.PAGES NOT BETWEEN 200 AND 400
 
BOOK.PAGES.between(100).and(1000);
BOOK.PAGES.notBetween(200).and(400);

LIKE Predicate

LIKE predicates are popular for simple wildcard-enabled pattern matching. Supported wildcards in SQLite are:

  • _ single-character wildcard
  • % multi-character wildcard

The LIKE predicate can be created from any column as such:

SQL SqliteMagic 
BOOK.TITLE     LIKE '%Foo%'
BOOK.TITLE NOT LIKE '%Foo%'
 
BOOK.TITLE.like("%Foo%");
BOOK.TITLE.notLike("%Foo%");

GLOB Predicate

The GLOB predicate is similar to LIKE except it uses case sensitive comparisons. It also uses the Unix file globbing syntax for its wildcards.

SQL SqliteMagic 
BOOK.TITLE     GLOB '*Foo*'
BOOK.TITLE NOT GLOB '*Foo*'
 
BOOK.TITLE.glob("*Foo*");
BOOK.TITLE.notGlob("*Foo*");

IN Predicate

SqliteMagic supports the following IN predicates:

in(Collection<T>)             // Construct an IN predicate from a collection of values
in(T...)                      // Construct an IN predicate from values
in(SelectNode<?, Select1>)    // Construct an IN predicate from a subselect
notIn(Collection<T>)          // Construct a NOT IN predicate from a collection of values
notIn(T...)                   // Construct a NOT IN predicate from values
notIn(SelectNode<?, Select1>) // Construct a NOT IN predicate from a subselect

Example:

SQL SqliteMagic 
BOOK.TITLE     IN ('FOO', 'BAR')
BOOK.TITLE NOT IN ('FOO', 'BAR')
 
BOOK.TITLE.in("Foo", "Bar");
BOOK.TITLE.notIn("Foo", "Bar");

See Next

Clone this wiki locally