All Downloads are FREE. Search and download functionalities are using the official Maven repository.

com.heliorm.sql.QueryHelper Maven / Gradle / Ivy

The newest version!
package com.heliorm.sql;

import com.heliorm.Field;
import com.heliorm.Field.FieldType;
import com.heliorm.OrmException;
import com.heliorm.Table;
import com.heliorm.def.Where;
import com.heliorm.impl.ExecutablePart;
import com.heliorm.impl.ExpressionContinuationPart;
import com.heliorm.impl.ExpressionPart;
import com.heliorm.impl.IsExpressionPart;
import com.heliorm.impl.JoinPart;
import com.heliorm.impl.LimitPart;
import com.heliorm.impl.ListExpressionPart;
import com.heliorm.impl.OrderPart;
import com.heliorm.impl.SelectPart;
import com.heliorm.impl.ValueExpressionPart;
import com.heliorm.impl.WherePart;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
import java.util.Set;
import java.util.StringJoiner;
import java.util.function.Function;
import java.util.stream.Collectors;

import static java.lang.String.format;

/**
 * Helper class that builds SQL queries.
 *
 * @author gideon
 */
final class QueryHelper {

    static final String POJO_NAME_FIELD = "pojo_field_name";
    private static final DateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    private final SqlDriver driver;
    private final Function, String> getFieldId;
    private final FullTableName fullTableName;

    QueryHelper(SqlDriver driver, Function, String> getFieldId, FullTableName fullTableName) {
        this.driver = driver;
        this.getFieldId = getFieldId;
        this.fullTableName = fullTableName;
    }

    String buildInsertQuery(Table table) throws OrmException {
        StringBuilder query = new StringBuilder();
        query.append(format("INSERT INTO %s(", fullTableName.apply(table)));
        StringJoiner fields = new StringJoiner(",");
        StringJoiner values = new StringJoiner(",");
        for (Field field : table.getFields()) {
            if (field.isPrimaryKey()) {
                if (field.isAutoNumber()) {
                    if (field.getFieldType() != Field.FieldType.STRING) {
                        continue;
                    }
                }
            }
            fields.add(format("%s", driver.fieldName(table, field)));
            values.add("?");
        }
        query.append(fields);
        query.append(") VALUES(");
        query.append(values);
        query.append(")");
        return query.toString();
    }

     String buildUpdateQuery(Table table) throws OrmException {
        if (table.getPrimaryKey().isEmpty()) {
            throw new OrmException("A table needs primary key for objects to be updated");
        }
        StringBuilder query = new StringBuilder();
        query.append(format("UPDATE %s SET ", fullTableName.apply(table)));
        StringJoiner fields = new StringJoiner(",");
        for (Field field : table.getFields()) {
            if (!field.isPrimaryKey()) {
                fields.add(format("%s=?", driver.fieldName(table, field)));
            }
        }
        query.append(fields);
        query.append(format(" WHERE %s=?", driver.fieldName(table, table.getPrimaryKey().get())));
        return query.toString();
    }

     String buildDeleteQuery(Table table) throws OrmException {
        if (table.getPrimaryKey().isPresent()) {
            return format("DELETE FROM %s WHERE %s=?", fullTableName.apply(table), driver.fieldName(table, table.getPrimaryKey().get()));
        } else {
            throw new OrmException("A table needs primary key for objects to be deleted");
        }
    }

     String buildSelectQuery(ExecutablePart exec) throws OrmException {
        SelectPart root = exec.getSelect();
        StringBuilder tablesQuery = new StringBuilder();
        tablesQuery.append("SELECT DISTINCT  ");
        StringJoiner fieldList = new StringJoiner(",");
        for (Field field : new ArrayList>(root.getTable().getFields())) {
            fieldList.add(format("%s AS %s", driver.fullFieldName(root.getTable(), field), driver.virtualFieldName(getFieldId(field))));
        }
        tablesQuery.append(fieldList);
        tablesQuery.append(format(" FROM %s", fullTableName.apply(root.getTable())));
        StringBuilder whereQuery = new StringBuilder();
        Optional> where = root.getSelect().getWhere();
        if (where.isPresent()) {
            whereQuery.append(expandCriteria(root.getSelect().getTable(), (WherePart) where.get()));
        }
        for (JoinPart join : root.getJoins()) {
            tablesQuery.append(expandLinkTables(root.getTable(), join));
            String joinWhere = expandLinkWheres(join);
            if (!joinWhere.isEmpty()) {
                if (!whereQuery.isEmpty()) {
                    whereQuery.append(" AND ");
                }
                whereQuery.append(expandLinkWheres(join));
            }
        }
        // finalize the query
        StringBuilder query = new StringBuilder();
        query.append(tablesQuery);
        if (!whereQuery.isEmpty()) {
            query.append(" WHERE ");
            query.append(whereQuery);
        }
        // do ordering
        query.append(expandOrder(root.getTable(), exec.getOrder()));
        // do limit
        query.append(expandLimit(exec.getLimit()));
        return query.toString();
    }

     String buildSelectUnionQuery(List> queries) throws OrmException {
        Set> allFields = queries.stream()
                .map(query -> query.getSelect().getTable())
                .flatMap(table -> table.getFields().stream())
                .collect(Collectors.toSet());
        StringJoiner buf = new StringJoiner(" UNION ALL ");
        ExecutablePart root = null;
        for (ExecutablePart query : queries) {
            if (root == null) {
                root = query;
            }
            buf.add(buildPartialUnionQuery(query.getSelect(), allFields));
        }
        if (root == null) {
            throw new OrmException("Could not find any parts in a union query. BUG!");
        }
        return buf.toString();
    }

    private  String buildPartialUnionQuery(SelectPart select, Set> allFields) throws OrmException {
        StringBuilder tablesQuery = new StringBuilder();
        StringJoiner fieldsQuery = new StringJoiner(",");
        var tableFields = select.getTable().getFields();
        for (Field field : allFields) {
            String fieldId = getFieldId(field);
            if (tableFields.contains(field)) {
                fieldsQuery.add(format("%s AS %s", driver.fullFieldName(select.getTable(), field), driver.virtualFieldName(fieldId)));
            } else {
                String empty = driver.castNull(field);
                fieldsQuery.add(format("%s AS %s", empty, driver.virtualFieldName(fieldId)));
            }
        }
        tablesQuery.append(format("SELECT %s", fieldsQuery));
        tablesQuery.append(format(",%s AS %s", driver.virtualValue(select.getTable().getObjectClass().getName()), driver.virtualFieldName(POJO_NAME_FIELD)));
        tablesQuery.append(format(" FROM %s", fullTableName.apply(select.getTable())));
        StringBuilder whereQuery = new StringBuilder();
        Optional> where = select.getWhere();
        if (where.isPresent()) {
            whereQuery.append(expandCriteria(select.getTable(), (WherePart) where.get()));
        }
        for (JoinPart join : select.getJoins()) {
            tablesQuery.append(expandLinkTables(select.getTable(), join));
            if (!whereQuery.isEmpty()) {
                whereQuery.append(" AND ");
            }
            whereQuery.append(expandLinkWheres(join));
        }
        if (!whereQuery.isEmpty()) {
            tablesQuery.append(" WHERE ");
            tablesQuery.append(whereQuery);
        }
        return tablesQuery.toString();
    }

    private String expandLinkTables(Table table, JoinPart right) throws OrmException {
        StringBuilder query = new StringBuilder();
        query.append(format(" JOIN %s ON %s=%s ",
                fullTableName.apply(right.getTable()),
                driver.fullFieldName(table, right.getOn().getLeftField()),
                driver.fullFieldName(right.getTable(), right.getOn().getRightField())));
        for (JoinPart join : right.getJoins()) {
            query.append(expandLinkTables(right.getTable(), join));
        }
        return query.toString();
    }

    private String expandLinkWheres(JoinPart join) throws OrmException {
        StringBuilder query = new StringBuilder();
        Optional> where = join.getWhere();
        if (where.isPresent()) {
            query = new StringBuilder(expandCriteria(join.getTable(), where.get()));
        }
        for (JoinPart next : join.getJoins()) {
            String linkWheres = expandLinkWheres(next);
            if (!linkWheres.isEmpty()) {
                if (!query.isEmpty()) {
                    query.append(" AND ");
                }
                query.append(linkWheres);
            }
        }
        return query.toString();
    }

    private String expandCriteria(Table table, WherePart where) throws OrmException {
        ExpressionPart expr = where.getExpression();
        StringBuilder query = new StringBuilder();
        query.append(expandExpression(table, expr));
        for (ExpressionContinuationPart ec : where.getContinuations()) {
            query.append(expandContinuation(table, ec));
        }
        return query.toString();
    }

    private String expandExpression(Table table, ExpressionPart expr) throws OrmException {
        StringBuilder query = new StringBuilder();
        List> conts = expr.getContinuations();
        if (!conts.isEmpty()) {
            query.append("(");
        }
        query.append(
                switch (expr.getType()) {
                    case LIST_EXPRESSION -> expandListFieldCriteria(table, ((ListExpressionPart) expr));
                    case VALUE_EXPRESSION -> expandValueFieldCriteria(table, (ValueExpressionPart) expr);
                    case IS_EXPRESSION -> expandIsFieldCriteria(table, (IsExpressionPart) expr);
                });
        if (!conts.isEmpty()) {
            for (ExpressionContinuationPart cont : conts) {
                query.append(" ");
                query.append(expandContinuation(table, cont));
            }
            query.append(")");
        }
        return query.toString();
    }

    private String expandContinuation(Table table, ExpressionContinuationPart ec) throws OrmException {
        return switch (ec.getType()) {
            case AND -> " AND ";
            case OR -> " OR ";
        } + expandExpression(table, ec.getExpression());
    }

    private String expandListFieldCriteria(Table table, ListExpressionPart crit) throws OrmException {
        StringJoiner list = new StringJoiner(",");
        if (crit.getValues().isEmpty()) {
            throw new OrmException(format("Empty %s list for field %s in table %s", crit.getOperator(),
                    crit.getField().getJavaName(), table.getObjectClass().getSimpleName()));
        }
        for (Object val : crit.getValues()) {
            list.add(format("'%s'", sqlValue(crit.getField(), val)));
        }
        return format("%s %s (%s)", driver.fullFieldName(table, crit.getField()), listOperator(crit), list);
    }

    private String expandValueFieldCriteria(Table table, ValueExpressionPart crit) throws OrmException {
        StringBuilder query = new StringBuilder();
        if (crit.getValue() == null) {
            throw new OrmException(format("Null %s value for field %s in table %s", crit.getOperator(),
                    crit.getField().getJavaName(), table.getObjectClass().getSimpleName()));
        }
        query.append(format("%s%s'%s'", driver.fullFieldName(table, crit.getField()), valueOperator(crit), sqlValue(crit.getField(), crit.getValue())));
        return query.toString();
    }

    private String expandIsFieldCriteria(Table table, IsExpressionPart crit) throws OrmException {
        return format("%s%s", driver.fullFieldName(table, crit.getField()), isOperator(crit));
    }

    /**
     * Expand the given order part into the fields of a SQL order clause.
     *
     * @param table The table spec to which the ordering applies
     * @return The partial SQL query string
     */
    private String expandOrder(Table table, List> orders) throws OrmException {
        StringBuilder query = new StringBuilder();
        if (!orders.isEmpty()) {
            query.append(" ORDER BY ");
        }
        StringBuilder body = new StringBuilder();
        for (OrderPart order : orders) {
            if (!body.isEmpty()) {
                body.append(", ");
            }
            body.append(format("%s", driver.fieldName(table, order.getField())));
            if (order.getDirection() == OrderPart.Direction.DESCENDING) {
                body.append(" DESC");
            }
        }
        query.append(body);
        return query.toString();
    }

    private String expandLimit(LimitPart limit) {
        var query = new StringBuilder();
        if (limit.getNumber() > -1) {
            query.append(" LIMIT ");
            if (limit.getFrom() > 0) {
                query.append(limit.getFrom());
                query.append(",");
            }
            query.append(limit.getNumber());
        }
        return query.toString();
    }

    private String sqlValue(Field field, Object object) {
        if (Objects.requireNonNull(field.getFieldType()) == FieldType.DATE) {
            return dateTimeFormat.format(object);
        }
        return object.toString();
    }

    /**
     * Return the SQL operator for the given list criteria part's operator.
     *
     * @param part The part
     * @return The operator
     */
    private String listOperator(ListExpressionPart part) {
        return switch (part.getOperator()) {
            case IN -> " IN";
            case NOT_IN -> " NOT IN";
        };
    }

    /**
     * Return the SQL operator for the given value criteria part's operator.
     *
     * @param part The part
     * @return The operator
     */
    private String valueOperator(ValueExpressionPart part) {
        return switch (part.getOperator()) {
            case EQ -> "=";
            case NOT_EQ -> "<>";
            case GE -> ">=";
            case LE -> "<=";
            case GT -> ">";
            case LT -> "<";
            case LIKE -> " LIKE ";
            case NOT_LIKE -> " NOT LIKE ";
        };
    }

    private String isOperator(IsExpressionPart part) {
        return switch (part.getOperator()) {
            case IS_NULL -> " IS NULL";
            case IS_NOT_NULL -> " IS NOT NULL";
        };
    }

    private String getFieldId(Field field) {
        return getFieldId.apply(field);
    }

}




© 2015 - 2024 Weber Informatics LLC | Privacy Policy