Practice: Building an SQL Query Step by Step

from typing import Optional


class QueryBuilder:
    def __init__(self) -> None:
        self._columns: list[str] = []
        self._table: Optional[str] = None
        self._conditions: list[str] = []
        self._order: Optional[str] = None
        self._limit: Optional[int] = None

    def select(self, *columns: str) -> "QueryBuilder":
        self._columns.extend(columns)
        return self

    def from_table(self, table: str) -> "QueryBuilder":
        self._table = table
        return self

    def where(self, condition: str) -> "QueryBuilder":
        self._conditions.append(condition)
        return self

    def order_by(self, column: str) -> "QueryBuilder":
        self._order = column
        return self

    def limit(self, count: int) -> "QueryBuilder":
        self._limit = count
        return self

    def build(self) -> str:
        if not self._columns or self._table is None:
            raise ValueError("a query needs SELECT columns and a FROM table")
        sql = f"SELECT {', '.join(self._columns)} FROM {self._table}"
        if self._conditions:
            sql += " WHERE " + " AND ".join(self._conditions)
        if self._order:
            sql += f" ORDER BY {self._order}"
        if self._limit is not None:
            sql += f" LIMIT {self._limit}"
        return sql


def search_orders(status: Optional[str], min_total: Optional[int], newest_first: bool) -> str:
    query = QueryBuilder().select("id", "total").from_table("orders")
    if status:
        query.where(f"status = '{status}'")
    if min_total is not None:
        query.where(f"total >= {min_total}")
    if newest_first:
        query.order_by("created_at DESC").limit(20)
    return query.build()


print(search_orders(None, None, False))
print(search_orders("shipped", 50, True))
try:
    QueryBuilder().where("total > 0").build()
except ValueError as error:
    print("refused:", error)

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

public class BdQuery {
    static String searchOrders(String status, Integer minTotal, boolean newestFirst) {
        QueryBuilder query = new QueryBuilder().select("id", "total").from("orders");
        if (status != null) query.where("status = '" + status + "'");
        if (minTotal != null) query.where("total >= " + minTotal);
        if (newestFirst) query.orderBy("created_at DESC").limit(20);
        return query.build();
    }

    public static void main(String[] args) {
        System.out.println(searchOrders(null, null, false));
        System.out.println(searchOrders("shipped", 50, true));
        try {
            new QueryBuilder().where("total > 0").build();
        } catch (IllegalStateException error) {
            System.out.println("refused: " + error.getMessage());
        }
    }
}

class QueryBuilder {
    private final List<String> columns = new ArrayList<>();
    private String table;
    private final List<String> conditions = new ArrayList<>();
    private String order;
    private Integer limit;

    QueryBuilder select(String... names) { columns.addAll(List.of(names)); return this; }
    QueryBuilder from(String name) { table = name; return this; }
    QueryBuilder where(String condition) { conditions.add(condition); return this; }
    QueryBuilder orderBy(String column) { order = column; return this; }
    QueryBuilder limit(int count) { limit = count; return this; }

    String build() {
        if (columns.isEmpty() || table == null) {
            throw new IllegalStateException("a query needs SELECT columns and a FROM table");
        }
        StringBuilder sql = new StringBuilder("SELECT " + String.join(", ", columns) + " FROM " + table);
        if (!conditions.isEmpty()) sql.append(" WHERE ").append(String.join(" AND ", conditions));
        if (order != null) sql.append(" ORDER BY ").append(order);
        if (limit != null) sql.append(" LIMIT ").append(limit);
        return sql.toString();
    }
}