org.fluentjdbc.DatabaseSqlBuilder Maven / Gradle / Ivy

Go to download
package org.fluentjdbc;

import javax.annotation.Nonnull;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Optional;
import java.util.stream.Stream;

/**
 * Used to construct SQL SELECT statements in a flexible way with {@link #where(String, Object)}
 * clauses, {@link #select(String...)} column names, {@link #from(String)} table statement,
 * {@link #groupBy(String...)}, {@link #orderBy(String)} and {@link #skipAndLimit(int, int)}.
 *
 * Example:
 *
 *  * new DatabaseSqlBuilder()
 *      .select("city, avg(age) as average_age")
 *      .from("person")
 *      .groupBy("city")
 *      .order("avg(age) desc")
 *      .limit(1)
 *      .singleString(connection, "city");
 * 
 *
 */
public class DatabaseSqlBuilder implements DatabaseQueryBuilder, DatabaseListableQueryBuilder {

    private final ArrayList columns = new ArrayList<>();
    private final DatabaseStatementFactory factory;
    private String fromStatement;
    private final ArrayList groupByClauses = new ArrayList<>();
    private final ArrayList orderByClauses = new ArrayList<>();
    private Integer offset;
    private Integer rowCount;
    private final DatabaseWhereBuilder whereBuilder = new DatabaseWhereBuilder();

    public DatabaseSqlBuilder(DatabaseStatementFactory factory) {
        this.factory = factory;
    }

    /**
     * Add the arguments to the column list for the SELECT column, column... statement
     */
    public DatabaseSqlBuilder select(String... columns) {
        this.columns.addAll(Arrays.asList(columns));
        return this;
    }

    /**
     * Replace the from part of the SELECT ... FROM fromStatement in the select statement
     */
    public DatabaseSqlBuilder from(String fromStatement) {
        this.fromStatement = fromStatement;
        return this;
    }

    /**
     * Adds the expression to the WHERE-clause and all the values to the parameter list.
     * E.g. whereExpressionWithParameterList("created_at between ? and ?", List.of(earliestDate, latestDate))
     */
    @Override
    public DatabaseSqlBuilder whereExpressionWithParameterList(String expression, Collection parameters) {
        //noinspection ResultOfMethodCallIgnored
        whereBuilder.whereExpressionWithParameterList(expression, parameters);
        return this;
    }

    /**
     * Add the arguments to the column list for the SELECT column, column... statement
     */
    public DatabaseSqlBuilder groupBy(String... groupByStatement) {
        groupByClauses.addAll(Arrays.asList(groupByStatement));
        return this;
    }

    /**
     * If you haven't called {@link #orderBy}, the results of {@link DatabaseListableQueryBuilder#list}
     * will be unpredictable. Call unordered() if you are okay with this.
     */
    @Override
    public DatabaseSqlBuilder unordered() {
        return this;
    }

    /**
     * Adds ORDER BY ... clause to the SELECT statement
     */
    @Override
    public DatabaseSqlBuilder orderBy(String orderByClause) {
        orderByClauses.add(orderByClause);
        return this;
    }

    /**
     * Adds OFFSET ... ROWS FETCH ... ROWS ONLY clause to the SELECT
     * statement. FETCH FIRST was introduced in
     * SQL:2008
     * and is supported by Postgresql 8.4, Oracle 12c, IBM DB2, HSQLDB, H2, and SQL Server 2012.
     */
    @Override
    public DatabaseSqlBuilder skipAndLimit(int offset, int rowCount) {
        this.offset = offset;
        this.rowCount = rowCount;
        return this;
    }

    /**
     * If the query returns no rows, returns {@link Optional#empty()}, if exactly one row is returned, maps it and return it,
     * if more than one is returned, throws `IllegalStateException`
     *
     * @param mapper Function object to map a single returned row to a object
     * @return the mapped row if one row is returned, Optional.empty otherwise
     * @throws IllegalStateException if more than one row was matched the the query
     */
    @Nonnull
    @Override
    public