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

de.greenrobot.dao.query.QueryBuilder Maven / Gradle / Ivy

Go to download

greenDAO is a light and fast ORM for Android

There is a newer version: 3.0.0-beta3
Show newest version
/*
 * Copyright (C) 2011-2015 Markus Junginger, greenrobot (http://greenrobot.de)
 *
 * 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.
 */
package de.greenrobot.dao.query;

import de.greenrobot.dao.AbstractDao;
import de.greenrobot.dao.AbstractDaoSession;
import de.greenrobot.dao.DaoException;
import de.greenrobot.dao.DaoLog;
import de.greenrobot.dao.Property;
import de.greenrobot.dao.internal.SqlUtils;

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

/**
 * Builds custom entity queries using constraints and parameters and without SQL (QueryBuilder creates SQL for you). To
 * acquire an QueryBuilder, use {@link AbstractDao#queryBuilder()} or {@link AbstractDaoSession#queryBuilder(Class)}.
 * Entity properties are referenced by Fields in the "Properties" inner class of the generated DAOs. This approach
 * allows compile time checks and prevents typo errors occuring at build time.

 * 

 * Example: Query for all users with the first name "Joe" ordered by their last name. (The class Properties is an inner
 * class of UserDao and should be imported before.)

 * 
 * List joes = dao.queryBuilder().where(Properties.FirstName.eq("Joe")).orderAsc(Properties.LastName).list();
 * 
 *
 * @param  Entity class to create an query for.
 * @author Markus
 */
public class QueryBuilder {

    /** Set to true to debug the SQL. */
    public static boolean LOG_SQL;

    /** Set to see the given values. */
    public static boolean LOG_VALUES;
    private final WhereCollector whereCollector;

    private StringBuilder orderBuilder;

    private final List values;
    private final List> joins;
    private final AbstractDao dao;
    private final String tablePrefix;

    private Integer limit;
    private Integer offset;
    private boolean distinct;

    /** For internal use by greenDAO only. */
    public static  QueryBuilder internalCreate(AbstractDao dao) {
        return new QueryBuilder(dao);
    }

    protected QueryBuilder(AbstractDao dao) {
        this(dao, "T");
    }

    protected QueryBuilder(AbstractDao dao, String tablePrefix) {
        this.dao = dao;
        this.tablePrefix = tablePrefix;
        values = new ArrayList();
        joins = new ArrayList>();
        whereCollector = new WhereCollector(dao, tablePrefix);
    }

    private void checkOrderBuilder() {
        if (orderBuilder == null) {
            orderBuilder = new StringBuilder();
        } else if (orderBuilder.length() > 0) {
            orderBuilder.append(",");
        }
    }

    /** Use a SELECT DISTINCT to avoid duplicate entities returned, e.g. when doing joins. */
    public QueryBuilder distinct() {
        distinct = true;
        return this;
    }

    /**
     * Adds the given conditions to the where clause using an logical AND. To create new conditions, use the properties
     * given in the generated dao classes.
     */
    public QueryBuilder where(WhereCondition cond, WhereCondition... condMore) {
        whereCollector.add(cond, condMore);
        return this;
    }

    /**
     * Adds the given conditions to the where clause using an logical OR. To create new conditions, use the properties
     * given in the generated dao classes.
     */
    public QueryBuilder whereOr(WhereCondition cond1, WhereCondition cond2, WhereCondition... condMore) {
        whereCollector.add(or(cond1, cond2, condMore));
        return this;
    }

    /**
     * Creates a WhereCondition by combining the given conditions using OR. The returned WhereCondition must be used
     * inside {@link #where(WhereCondition, WhereCondition...)} or
     * {@link #whereOr(WhereCondition, WhereCondition, WhereCondition...)}.
     */
    public WhereCondition or(WhereCondition cond1, WhereCondition cond2, WhereCondition... condMore) {
        return whereCollector.combineWhereConditions(" OR ", cond1, cond2, condMore);
    }

    /**
     * Creates a WhereCondition by combining the given conditions using AND. The returned WhereCondition must be used
     * inside {@link #where(WhereCondition, WhereCondition...)} or
     * {@link #whereOr(WhereCondition, WhereCondition, WhereCondition...)}.
     */
    public WhereCondition and(WhereCondition cond1, WhereCondition cond2, WhereCondition... condMore) {
        return whereCollector.combineWhereConditions(" AND ", cond1, cond2, condMore);
    }

    /**
     * Expands the query to another entity type by using a JOIN. The primary key property of the primary entity for
     * this QueryBuilder is used to match the given destinationProperty.
     */
    public  Join join(Class destinationEntityClass, Property destinationProperty) {
        return join(dao.getPkProperty(), destinationEntityClass, destinationProperty);
    }

    /**
     * Expands the query to another entity type by using a JOIN. The given sourceProperty is used to match the primary
     * key property of the given destinationEntity.
     */
    public  Join join(Property sourceProperty, Class destinationEntityClass) {
        AbstractDao destinationDao = (AbstractDao) dao.getSession().getDao(destinationEntityClass);
        Property destinationProperty = destinationDao.getPkProperty();
        return addJoin(tablePrefix, sourceProperty, destinationDao, destinationProperty);
    }

    /**
     * Expands the query to another entity type by using a JOIN. The given sourceProperty is used to match the given
     * destinationProperty of the given destinationEntity.
     */
    public  Join join(Property sourceProperty, Class destinationEntityClass, Property destinationProperty) {
        AbstractDao destinationDao = (AbstractDao) dao.getSession().getDao(destinationEntityClass);
        return addJoin(tablePrefix, sourceProperty, destinationDao, destinationProperty);
    }

    /**
     * Expands the query to another entity type by using a JOIN. The given sourceJoin's property is used to match the
     * given destinationProperty of the given destinationEntity. Note that destination entity of the given join is used
     * as the source for the new join to add. In this way, it is possible to compose complex "join of joins" across
     * several entities if required.
     */
    public  Join join(Join sourceJoin, Property sourceProperty, Class destinationEntityClass,
                               Property destinationProperty) {
        AbstractDao destinationDao = (AbstractDao) dao.getSession().getDao(destinationEntityClass);
        return addJoin(sourceJoin.tablePrefix, sourceProperty, destinationDao, destinationProperty);
    }

    private  Join addJoin(String sourceTablePrefix, Property sourceProperty, AbstractDao destinationDao,
                                   Property destinationProperty) {
        String joinTablePrefix = "J" + (joins.size() + 1);
        Join join = new Join(sourceTablePrefix, sourceProperty, destinationDao, destinationProperty,
                joinTablePrefix);
        joins.add(join);
        return join;
    }

    /** Adds the given properties to the ORDER BY section using ascending order. */
    public QueryBuilder orderAsc(Property... properties) {
        orderAscOrDesc(" ASC", properties);
        return this;
    }

    /** Adds the given properties to the ORDER BY section using descending order. */
    public QueryBuilder orderDesc(Property... properties) {
        orderAscOrDesc(" DESC", properties);
        return this;
    }

    private void orderAscOrDesc(String ascOrDescWithLeadingSpace, Property... properties) {
        for (Property property : properties) {
            checkOrderBuilder();
            append(orderBuilder, property);
            if (String.class.equals(property.type)) {
                orderBuilder.append(" COLLATE LOCALIZED");
            }
            orderBuilder.append(ascOrDescWithLeadingSpace);
        }
    }

    /** Adds the given properties to the ORDER BY section using the given custom order. */
    public QueryBuilder orderCustom(Property property, String customOrderForProperty) {
        checkOrderBuilder();
        append(orderBuilder, property).append(' ');
        orderBuilder.append(customOrderForProperty);
        return this;
    }

    /**
     * Adds the given raw SQL string to the ORDER BY section. Do not use this for standard properties: orderAsc and
     * orderDesc are preferred.
     */
    public QueryBuilder orderRaw(String rawOrder) {
        checkOrderBuilder();
        orderBuilder.append(rawOrder);
        return this;
    }

    protected StringBuilder append(StringBuilder builder, Property property) {
        whereCollector.checkProperty(property);
        builder.append(tablePrefix).append('.').append('\'').append(property.columnName).append('\'');
        return builder;
    }


    /** Limits the number of results returned by queries. */
    public QueryBuilder limit(int limit) {
        this.limit = limit;
        return this;
    }

    /**
     * Sets the offset for query results in combination with {@link #limit(int)}. The first {@code limit} results are
     * skipped and the total number of results will be limited by {@code limit}. You cannot use offset without limit.
     */
    public QueryBuilder offset(int offset) {
        this.offset = offset;
        return this;
    }

    /**
     * Builds a reusable query object (Query objects can be executed more efficiently than creating a QueryBuilder for
     * each execution.
     */
    public Query build() {
        StringBuilder builder = createSelectBuilder();
        int limitPosition = checkAddLimit(builder);
        int offsetPosition = checkAddOffset(builder);

        String sql = builder.toString();
        checkLog(sql);

        return Query.create(dao, sql, values.toArray(), limitPosition, offsetPosition);
    }

    /**
     * Builds a reusable query object for low level android.database.Cursor access.
     * (Query objects can be executed more efficiently than creating a QueryBuilder for each execution.
     */
    public CursorQuery buildCursor() {
        StringBuilder builder = createSelectBuilder();
        int limitPosition = checkAddLimit(builder);
        int offsetPosition = checkAddOffset(builder);

        String sql = builder.toString();
        checkLog(sql);

        return CursorQuery.create(dao, sql, values.toArray(), limitPosition, offsetPosition);
    }

    private StringBuilder createSelectBuilder() {
        String select = SqlUtils.createSqlSelect(dao.getTablename(), tablePrefix, dao.getAllColumns(), distinct);
        StringBuilder builder = new StringBuilder(select);

        appendJoinsAndWheres(builder, tablePrefix);

        if (orderBuilder != null && orderBuilder.length() > 0) {
            builder.append(" ORDER BY ").append(orderBuilder);
        }
        return builder;
    }

    private int checkAddLimit(StringBuilder builder) {
        int limitPosition = -1;
        if (limit != null) {
            builder.append(" LIMIT ?");
            values.add(limit);
            limitPosition = values.size() - 1;
        }
        return limitPosition;
    }

    private int checkAddOffset(StringBuilder builder) {
        int offsetPosition = -1;
        if (offset != null) {
            if (limit == null) {
                throw new IllegalStateException("Offset cannot be set without limit");
            }
            builder.append(" OFFSET ?");
            values.add(offset);
            offsetPosition = values.size() - 1;
        }
        return offsetPosition;
    }

    /**
     * Builds a reusable query object for deletion (Query objects can be executed more efficiently than creating a
     * QueryBuilder for each execution.
     */
    public DeleteQuery buildDelete() {
        if (!joins.isEmpty()) {
            throw new DaoException("JOINs are not supported for DELETE queries");
        }
        String tablename = dao.getTablename();
        String baseSql = SqlUtils.createSqlDelete(tablename, null);
        StringBuilder builder = new StringBuilder(baseSql);

        // tablePrefix gets replaced by table name below. Don't use tableName here because it causes trouble when
        // table name ends with tablePrefix.
        appendJoinsAndWheres(builder, tablePrefix);

        String sql = builder.toString();
        // Remove table aliases, not supported for DELETE queries.
        // TODO(?): don't create table aliases in the first place.
        sql = sql.replace(tablePrefix + ".\"", '"' + tablename + "\".\"");
        checkLog(sql);

        return DeleteQuery.create(dao, sql, values.toArray());
    }

    /**
     * Builds a reusable query object for counting rows (Query objects can be executed more efficiently than creating a
     * QueryBuilder for each execution.
     */
    public CountQuery buildCount() {
        String tablename = dao.getTablename();
        String baseSql = SqlUtils.createSqlSelectCountStar(tablename, tablePrefix);
        StringBuilder builder = new StringBuilder(baseSql);
        appendJoinsAndWheres(builder, tablePrefix);

        String sql = builder.toString();
        checkLog(sql);

        return CountQuery.create(dao, sql, values.toArray());
    }

    private void checkLog(String sql) {
        if (LOG_SQL) {
            DaoLog.d("Built SQL for query: " + sql);
        }
        if (LOG_VALUES) {
            DaoLog.d("Values for query: " + values);
        }
    }

    private void appendJoinsAndWheres(StringBuilder builder, String tablePrefixOrNull) {
        values.clear();
        for (Join join : joins) {
            builder.append(" JOIN ").append(join.daoDestination.getTablename()).append(' ');
            builder.append(join.tablePrefix).append(" ON ");
            SqlUtils.appendProperty(builder, join.sourceTablePrefix, join.joinPropertySource).append('=');
            SqlUtils.appendProperty(builder, join.tablePrefix, join.joinPropertyDestination);
        }
        boolean whereAppended = !whereCollector.isEmpty();
        if (whereAppended) {
            builder.append(" WHERE ");
            whereCollector.appendWhereClause(builder, tablePrefixOrNull, values);
        }
        for (Join join : joins) {
            if (!join.whereCollector.isEmpty()) {
                if (!whereAppended) {
                    builder.append(" WHERE ");
                    whereAppended = true;
                } else {
                    builder.append(" AND ");
                }
                join.whereCollector.appendWhereClause(builder, join.tablePrefix, values);
            }
        }
    }

    /**
     * Shorthand for {@link QueryBuilder#build() build()}.{@link Query#list() list()}; see {@link Query#list()} for
     * details. To execute a query more than once, you should build the query and keep the {@link Query} object for
     * efficiency reasons.
     */
    public List list() {
        return build().list();
    }

    /**
     * Shorthand for {@link QueryBuilder#build() build()}.{@link Query#listLazy() listLazy()}; see
     * {@link Query#listLazy()} for details. To execute a query more than once, you should build the query and keep the
     * {@link Query} object for efficiency reasons.
     */
    public LazyList listLazy() {
        return build().listLazy();
    }

    /**
     * Shorthand for {@link QueryBuilder#build() build()}.{@link Query#listLazyUncached() listLazyUncached()}; see
     * {@link Query#listLazyUncached()} for details. To execute a query more than once, you should build the query and
     * keep the {@link Query} object for efficiency reasons.
     */
    public LazyList listLazyUncached() {
        return build().listLazyUncached();
    }

    /**
     * Shorthand for {@link QueryBuilder#build() build()}.{@link Query#listIterator() listIterator()}; see
     * {@link Query#listIterator()} for details. To execute a query more than once, you should build the query and keep
     * the {@link Query} object for efficiency reasons.
     */
    public CloseableListIterator listIterator() {
        return build().listIterator();
    }

    /**
     * Shorthand for {@link QueryBuilder#build() build()}.{@link Query#unique() unique()}; see {@link Query#unique()}
     * for details. To execute a query more than once, you should build the query and keep the {@link Query} object for
     * efficiency reasons.
     */
    public T unique() {
        return build().unique();
    }

    /**
     * Shorthand for {@link QueryBuilder#build() build()}.{@link Query#uniqueOrThrow() uniqueOrThrow()}; see
     * {@link Query#uniqueOrThrow()} for details. To execute a query more than once, you should build the query and
     * keep
     * the {@link Query} object for efficiency reasons.
     */
    public T uniqueOrThrow() {
        return build().uniqueOrThrow();
    }

    /**
     * Shorthand for {@link QueryBuilder#buildCount() buildCount()}.{@link CountQuery#count() count()}; see
     * {@link CountQuery#count()} for details. To execute a query more than once, you should build the query and keep
     * the {@link CountQuery} object for efficiency reasons.
     */
    public long count() {
        return buildCount().count();
    }

}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy