org.fluentjdbc.DatabaseTableQueryBuilder Maven / Gradle / Ivy

Go to download
package org.fluentjdbc;

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

/**
 * Generate SELECT statements by collecting WHERE expressions and parameters.Example:
 *
 *  *  List<Person> result = table
 *     .where("firstName", firstName)
 *     .whereExpression("lastName like ?", "joh%")
 *     .whereIn("status", statuses)
 *     .orderBy("lastName")
 *     .list(connection, row -> new Person(row));
 * 
 */
@ParametersAreNonnullByDefault
public class DatabaseTableQueryBuilder implements
        DatabaseSimpleQueryBuilder,
        DatabaseListableQueryBuilder
{

    protected final DatabaseWhereBuilder whereClause = new DatabaseWhereBuilder();
    protected final DatabaseTable table;
    protected final List orderByClauses = new ArrayList<>();
    protected Integer offset;
    protected Integer rowCount;

    DatabaseTableQueryBuilder(DatabaseTable table) {
        this.table = table;
    }

    /**
     * Executes SELECT count(*) FROM ... on the query and returns the result
     */
    @Override
    public int getCount(Connection connection) {
        String statement = "select count(*) as count " + fromClause() + whereClause.whereClause();
        return table.newStatement("COUNT", statement, whereClause.getParameters())
                .singleObject(connection, row -> row.getInt("count"))
                .orElseThrow(() -> new RuntimeException("Should never happen"));
    }

    /**
     * Execute the query and map each return value over the {@link DatabaseResult.RowMapper} function to return a stream. Example:
     *      *     table.where("status", status).stream(connection, row -> row.getInstant("created_at"))
     * 
     */
    @Override
    public  Stream stream(Connection connection, DatabaseResult.RowMapper mapper) {
        return createSelect().stream(connection, mapper);
    }

    /**
     * Executes the SELECT * FROM ... statement and calls back to
     * {@link DatabaseResult.RowConsumer} for each returned row
     */
    @Override
    public void forEach(Connection connection, DatabaseResult.RowConsumer consumer) {
        createSelect().forEach(connection, consumer);
    }

    public DatabaseStatement createSelect() {
        return table.newStatement("SELECT", createSelectStatement(), whereClause.getParameters());
    }

    /**
     * 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 connection Database connection
     * @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  Optional singleObject(Connection connection, DatabaseResult.RowMapper mapper) {
        return createSelect().singleObject(connection, mapper);
    }

    /**
     * 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))
     */
    public DatabaseTableQueryBuilder whereExpressionWithParameterList(String expression, Collection parameters) {
        //noinspection ResultOfMethodCallIgnored
        whereClause.whereExpressionWithParameterList(expression, parameters);
        return this;
    }

    /**
     * Creates a {@link DatabaseUpdateBuilder} object to fluently generate a UPDATE ... statement
     */
    @Override
    public DatabaseUpdateBuilder update() {
        return table.update().where(whereClause);
    }

    /**
     * Executes DELETE FROM tableName WHERE ....
     */
    @Override
    public int delete(Connection connection) {
        return table.delete().where(whereClause).execute(connection);
    }

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

    /**
     * If you haven't called {@link #orderBy}, the results of {@link #list(Connection, DatabaseResult.RowMapper)}
     * will be unpredictable. Call unordered() if you are okay with this.
     */
    @Override
    public DatabaseTableQueryBuilder unordered() {
        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 DatabaseTableQueryBuilder skipAndLimit(int offset, int rowCount) {
        this.offset = offset;
        this.rowCount = rowCount;
        return this;
    }

    /**
     * Implemented as return this for compatibility purposes
     */
    @Override
    public DatabaseTableQueryBuilder query() {
        return this;
    }

    private String createSelectStatement() {
        return "select *" + fromClause() + whereClause.whereClause() + orderByClause() + fetchClause();
    }

    protected String fromClause() {
        return " from " + table.getTableName();
    }

    protected String orderByClause() {
        return orderByClauses.isEmpty() ? "" : " order by " + String.join(", ", orderByClauses);
    }

    private String fetchClause() {
        return rowCount == null ? "" : " offset " + offset + " rows fetch first " + rowCount + " rows only";
    }

}