Simple Conditions

Simple conditions are the most common - they render the basic SQL operators.

Subqueries

Many conditions can be rendered with subqueries.

Column Comparison Conditions

Column comparison conditions can be used to write where clauses comparing the values of columns in a table.

Value Transformation

All conditions (except isNull and isNotNull) support a map function that allows you to transform the value(s) associated with the condition before the statement is rendered. The map function functions similarly to the JDK standard map functions on Streams and Optionals - it allows you to transform a value and change the data type.

For example, suppose you want to code a wild card search with the SQL like operator. To make this work, you will need to append SQL wildcards to the search value. This can be accomplished directly in the condition with a map method as follows:

List<Animal> search(String searchName) {
        SelectStatementProvider selectStatement=select(id,animalName,bodyWeight,brainWeight)
        .from(animalData)
        .where(animalName,isLike(searchName).map(s -> "%"+s+"%"))
        .orderBy(id)
        .build()
        .render(RenderingStrategies.MYBATIS3);

        ...
}

You can see the map method accepts a lambda that adds SQL wildcards to the searchName field. This is more succinct if you use a method reference:

List<Animal> search(String searchName){
        SelectStatementProvider selectStatement=select(id,animalName,bodyWeight,brainWeight)
        .from(animalData)
        .where(animalName,isLike(searchName).map(this::appendWildCards))
        .orderBy(id)
        .build()
        .render(RenderingStrategies.MYBATIS3);
}

String appendWildCards(String in) {
    return "%" + in + "%";
}

The map on each condition accepts a lambda expression that can be used to transform the value(s) associated with the condition. The lambda is the standard JDK type Function<T,R> where T is the type of the condition and R is the output type. For most conditions this should be fairly simple to understand. The unusual cases are detailed below:

1. The Between and NotBetween conditions have map methods that accept one or two map functions. If you pass one function, it will be applied to both values in the condition. If you supply two functions, then they will be applied to the first and second values respectively.
2. The In and NotIn conditions accept a single mapping function, and it will be applied to all values in the collection of values in the condition.

Optional Conditions

All conditions support optionality - meaning they can be configured to render into the final SQL if a configured test passes. Optionality is implemented via standard “filter” and “map” methods - which behave very similarly to the “filter” and “map” methods in java.util.Optional. In general, if a condition's “filter” method is not satisfied, then the condition will not be rendered. The “map” method can be used the alter the value in a condition before the condition is rendered.

For example, you could code a search like this:

    public List<AnimalData> searchPeople(String animalName_, Double bodyWeight_, Double brainWeight_) {
        ...
        SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
                .from(animalData)
                .where(animalName, isEqualTo(animalName_).filter(Objects::nonNull))
                .and(bodyWeight, isEqualTo(bodyWeight_).filter(Objects::nonNull))
                .and(brainWeight, isEqualTo(brainWeight_).filter(Objects::nonNull))
                .build()
                .render(RenderingStrategies.MYBATIS3);
        ...
    }

In this example, the three conditions will only be rendered if the values passed to them are not null. If all three values are null, then no where clause will be generated.

Each of the conditions accepts a lambda expression that can be used to determine if the condition should render or not. The lambdas will all be of standard JDK types (either java.util.function.BooleanSupplier, java.util.function.Predicate, or java.util.function.BiPredicate depending on the type of condition). The following table lists the optional conditions and shows how to use them:

// the following two lines are functionally equivalent
... where (id, isEqualToWhenPresent(x)) ...
... where (id, isEqualTo(x).filter(Objects::nonNull)) ...

   delete.from(foo)
     .where(status, isTrue())
     .and(id, isIn(Collections.emptyList()));

   delete from foo where status = ? and id in ()

    SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
            .from(animalData)
            .where(animalName, isIn("  Mouse", "  ", null, "", "Musk shrew  ")
                    .filter(Objects::nonNull)
                    .map(String::trim)
                    .filter(st -> !st.isEmpty()))
            .orderBy(id)
            .build()
            .render(RenderingStrategies.MYBATIS3);

import org.mybatis.dynamic.sql.SqlBuilder;

public class MyInCondition {
    public static IsIn<String> isIn(String... values) {
        return SqlBuilder.isIn(values)
               .filter(Objects::nonNull)
               .map(String::trim)
               .filter(st -> !st.isEmpty());
    }
}

    SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
            .from(animalData)
            .where(animalName, MyInCondition.isIn("  Mouse", "  ", null, "", "Musk shrew  "))
            .orderBy(id)
            .build()
            .render(RenderingStrategies.MYBATIS3);

Potential for Non Rendering Where Clauses

An “inWhenPresent” condition will be dropped from rendering if the list of values is empty. Other conditions could be dropped from a where clause due to filtering. If all conditions fail to render, then the entire where clause will be dropped from the rendered SQL. In general, we think it is a good thing that the library will not render invalid SQL. But this stance does present a danger - if a where clause is dropped from the rendered SQL, then the statement could end up impacting all rows in a table. For example, a delete statement could inadvertently delete all rows in a table.

By default, and out of an abundance of caution, the library will not allow a statement to render if the entire where clause will be dropped. If a where clause is coded, but fails to render, then the library will throw a NonRenderingWhereClauseException by default.

If no where clause is coded in a statement, then we assume the statement is intended to affect all rows in a table. In that case no exception will be thrown.

This behavior can be modified through configuration - either globally for the entire library, or for each statement individually. See the “Configuration of the Library” page for details on configuration options.