A small utility to help you construct SQL query where clauses

WhereClause.java

/*
 * Copyright 2014-2015 Marius Volkhart
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * Original at https://gist.github.com/MariusVolkhart/3e2374b5fdbefad17d56
 */

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Representation of a constructed SQL query where clause
 */
public final class WhereClause {

    public final String selection;
    public final String[] selectionArgs;

    private WhereClause(String selection, String[] selectionArgs) {
        this.selection = selection;
        this.selectionArgs = selectionArgs;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        WhereClause that = (WhereClause) o;

        return !(selection != null ? !selection.equals(that.selection) : that.selection != null)
                && Arrays.equals(selectionArgs, that.selectionArgs);

    }

    @Override
    public int hashCode() {
        int result = selection != null ? selection.hashCode() : 0;
        result = 31 * result + (selectionArgs != null ? Arrays.hashCode(selectionArgs) : 0);
        return result;
    }

    @Override
    public String toString() {
        return WhereClause.class.getSimpleName() + "{" +
                "selection='" + selection + '\'' +
                ", selectionArgs=" + Arrays.toString(selectionArgs) +
                '}';
    }

    /**
     * Common SQL operators. For convenience, consider implementing this interface if creating a
     * lot of queries.
     */
    public interface Operator {
        public String EQUALS = "=";
        public String NOT_EQUALS = "!=";
        public String GREATER_THAN = ">";
        public String LESS_THAN = "<";
        public String GREATER_THAN_EQUALS = ">=";
        public String LESS_THAN_EQUALS = "<=";
        public String LIKE = " LIKE ";
        public String IS = " IS ";
        public String IS_NOT = " IS NOT ";
    }

    public static class Builder {

        private static final String AND = " AND ";
        private static final String OR = " OR ";

        private final StringBuilder stringBuilder = new StringBuilder();
        private final List<String> args = new ArrayList<>();
        private String nextOperator;

        public Builder where(String column, String operand, String arg) {
            setNextOperatorIfNeeded();
            stringBuilder.append(column).append(operand).append('?');
            args.add(arg);
            nextOperator = null;

            return this;
        }

        public Builder where(String column, String operand, boolean arg) {
            return where(column, operand, arg ? "1" : "0");
        }

        public Builder where(String column, String operand, int arg) {
            return where(column, operand, Integer.toString(arg));
        }

        public Builder where(String column, String operand, long arg) {
            return where(column, operand, Long.toString(arg));
        }

        public Builder where(String column, String operand, float arg) {
            return where(column, operand, Float.toString(arg));
        }

        public Builder where(String column, String operand, double arg) {
            return where(column, operand, Double.toString(arg));
        }

        public Builder where(WhereClause whereClause) {

            if (whereClause.selectionArgs.length > 0) {
                setNextOperatorIfNeeded();
                stringBuilder.append('(').append(whereClause.selection).append(')');
                args.addAll(Arrays.asList(whereClause.selectionArgs));
            }

            nextOperator = null;
            return this;
        }

        /**
         * Joins two statements with an {@code AND} operator. This is also the implicit behavior.
         */
        public Builder and() {
            nextOperator = AND;
            return this;
        }

        public Builder or() {
            nextOperator = OR;
            return this;
        }

        /**
         * Ensures that multiple {@code where} statements can be joined safely. Defaults to using
         * {@code AND}.
         */
        private void setNextOperatorIfNeeded() {
            if (stringBuilder.length() == 0) {
                return;
            }

            if (nextOperator == null) {
                stringBuilder.append(AND);
            } else {
                stringBuilder.append(nextOperator);
                nextOperator = null;
            }
        }

        public WhereClause build() {
            String[] arguments = args.toArray(new String[args.size()]);
            return new WhereClause(stringBuilder.toString(), arguments);
        }
    }
}

WhereClauseTest.java

/*
 * Copyright 2014-2015 Marius Volkhart
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * Original at https://gist.github.com/MariusVolkhart/3e2374b5fdbefad17d56
 */

import org.junit.Test;

import static org.assertj.core.api.Assertions.assertThat;

public class WhereClauseTest {

    @Test
    public void operator_values_are_correct() {
        assertThat(WhereClause.Operator.EQUALS).isEqualTo("=");
        assertThat(WhereClause.Operator.NOT_EQUALS).isEqualTo("!=");
        assertThat(WhereClause.Operator.GREATER_THAN).isEqualTo(">");
        assertThat(WhereClause.Operator.LESS_THAN).isEqualTo("<");
        assertThat(WhereClause.Operator.GREATER_THAN_EQUALS).isEqualTo(">=");
        assertThat(WhereClause.Operator.LESS_THAN_EQUALS).isEqualTo("<=");
        assertThat(WhereClause.Operator.LIKE).isEqualTo(" LIKE ");
        assertThat(WhereClause.Operator.IS).isEqualTo(" IS ");
        assertThat(WhereClause.Operator.IS_NOT).isEqualTo(" IS NOT ");
    }

    @Test
    public void where_boolean_is_true() {
        WhereClause whereClause = new WhereClause.Builder()
                .where("isDirty", WhereClause.Operator.EQUALS, true)
                .build();

        assertThat(whereClause.selection).isEqualTo("isDirty=?");
        assertThat(whereClause.selectionArgs).containsExactly("1");
    }

    @Test
    public void where_boolean_is_false() {
        WhereClause whereClause = new WhereClause.Builder()
                .where("isDirty", WhereClause.Operator.EQUALS, false)
                .build();

        assertThat(whereClause.selection).isEqualTo("isDirty=?");
        assertThat(whereClause.selectionArgs).containsExactly("0");
    }

    @Test
    public void where_integer() {
        WhereClause whereClause = new WhereClause.Builder()
                .where("age", WhereClause.Operator.EQUALS, 23)
                .build();

        assertThat(whereClause.selection).isEqualTo("age=?");
        assertThat(whereClause.selectionArgs).containsExactly("23");
    }

    @Test
    public void where_long() {
        WhereClause whereClause = new WhereClause.Builder()
                .where("id", WhereClause.Operator.EQUALS, Long.MAX_VALUE)
                .build();

        assertThat(whereClause.selection).isEqualTo("id=?");
        assertThat(whereClause.selectionArgs).containsExactly(Long.toString(Long.MAX_VALUE));
    }

    @Test
    public void where_float() {
        WhereClause whereClause = new WhereClause.Builder()
                .where("price", WhereClause.Operator.EQUALS, 9.99f)
                .build();

        assertThat(whereClause.selection).isEqualTo("price=?");
        assertThat(whereClause.selectionArgs).containsExactly("9.99");
    }

    @Test
    public void where_double() {
        WhereClause whereClause = new WhereClause.Builder()
                .where("price", WhereClause.Operator.EQUALS, 9.99)
                .build();

        assertThat(whereClause.selection).isEqualTo("price=?");
        assertThat(whereClause.selectionArgs).containsExactly("9.99");
    }

    @Test
    public void where_string() {
        WhereClause whereClause = new WhereClause.Builder()
                .where("name", WhereClause.Operator.EQUALS, "John")
                .build();

        assertThat(whereClause.selection).isEqualTo("name=?");
        assertThat(whereClause.selectionArgs).containsExactly("John");
    }

    @Test
    public void and() {
        WhereClause whereClause = new WhereClause.Builder()
                .where("first_name", WhereClause.Operator.EQUALS, "John")
                .and()
                .where("last_name", WhereClause.Operator.EQUALS, "Doe")
                .build();

        assertThat(whereClause.selection).isEqualTo("first_name=? AND last_name=?");
        assertThat(whereClause.selectionArgs).containsExactly("John", "Doe");
    }

    @Test
    public void or() {
        WhereClause whereClause = new WhereClause.Builder()
                .where("first_name", WhereClause.Operator.EQUALS, "John")
                .or()
                .where("first_name", WhereClause.Operator.EQUALS, "Jane")
                .build();

        assertThat(whereClause.selection).isEqualTo("first_name=? OR first_name=?");
        assertThat(whereClause.selectionArgs).containsExactly("John", "Jane");
    }

    @Test
    public void implicit_and_on_multiple_wheres() {
        WhereClause whereClause = new WhereClause.Builder()
                .where("first_name", WhereClause.Operator.EQUALS, "John")
                .where("last_name", WhereClause.Operator.EQUALS, "Doe")
                .build();

        assertThat(whereClause.selection).isEqualTo("first_name=? AND last_name=?");
        assertThat(whereClause.selectionArgs).containsExactly("John", "Doe");
    }

    @Test
    public void nested_whereClause() {
        WhereClause john = new WhereClause.Builder()
                .where("first_name", WhereClause.Operator.EQUALS, "John")
                .where("last_name", WhereClause.Operator.EQUALS, "Doe")
                .build();

        WhereClause jane = new WhereClause.Builder()
                .where("first_name", WhereClause.Operator.EQUALS, "Jane")
                .where("last_name", WhereClause.Operator.EQUALS, "Doe")
                .build();

        WhereClause whereClause = new WhereClause.Builder()
                .where(john)
                .or()
                .where(jane)
                .build();

        assertThat(whereClause.selection).isEqualTo("(first_name=? AND last_name=?) OR (first_name=? AND last_name=?)");
        assertThat(whereClause.selectionArgs).containsExactly("John", "Doe", "Jane", "Doe");
    }

    @Test
    public void toStringUsesCorrectClassName() {
        assertThat(new WhereClause.Builder().build().toString()).startsWith("WhereClause");
    }
}