Back to project page Android-Lib-Database.
The source code is released under:
Apache License
If you think the Android project Android-Lib-Database listed in this page is inappropriate, such as containing malicious code/tools or violating the copyright, please email info at java2s dot com, thanks.
package android.lib.database.query; //from w ww. j a v a2 s .c o m import java.util.ArrayList; import java.util.List; import android.lib.database.predicate.Predicate; import android.text.TextUtils; /** * Provides methods for building SQLite <code>SELECT</code> queries. */ public class Select extends QueryBuilder { private static final String SELECT = "SELECT %1$s FROM %2$s"; //$NON-NLS-1$ private static final String WHERE = " WHERE %s"; //$NON-NLS-1$ private static final String GROUP_BY = " GROUP BY %s"; //$NON-NLS-1$ private static final String HAVING = " HAVING %s"; //$NON-NLS-1$ private static final String ORDER_BY = " ORDER BY %s"; //$NON-NLS-1$ private static final String LIMIT = " LIMIT %s"; //$NON-NLS-1$ private static final String OFFSET = " OFFSET %s"; //$NON-NLS-1$ private static final String COLUMN_ALIAS = "%1$s AS %2$s"; //$NON-NLS-1$ private static final String COLUMN_ALL = "*"; //$NON-NLS-1$ private static final String TABLE_ALIAS = "%1$s AS %2$s"; //$NON-NLS-1$ private static final String JOIN = " JOIN %1$s ON %2$s"; //$NON-NLS-1$ private static final String JOIN_LEFT = " LEFT JOIN %1$s ON %2$s"; //$NON-NLS-1$ private static final String JOIN_RIGHT = " RIGHT JOIN %1$s ON %2$s"; //$NON-NLS-1$ private static final String JOIN_ALIAS = " JOIN %1$s AS %2$s ON %3$s"; //$NON-NLS-1$ private static final String JOIN_LEFT_ALIAS = " LEFT JOIN %1$s AS %2$s ON %3$s"; //$NON-NLS-1$ private static final String JOIN_RIGHT_ALIAS = " RIGHT JOIN %1$s AS %2$s ON %3$s"; //$NON-NLS-1$ private static final String AGGREGATION_AVERAGE = "AVG(%1$s) AS %2$s"; //$NON-NLS-1$ private static final String AGGREGATION_COUNT = "COUNT(%1$s) AS %2$s"; //$NON-NLS-1$ private static final String AGGREGATION_MAXIMUM = "MAX(%1$s) AS %2$s"; //$NON-NLS-1$ private static final String AGGREGATION_MINIMUM = "MIN(%1$s) AS %2$s"; //$NON-NLS-1$ private static final String AGGREGATION_SUMMATION = "SUM(%1$s) AS %2$s"; //$NON-NLS-1$ private static final String DISTINCT = "DISTINCT %s"; //$NON-NLS-1$ private static final String DESCENDING = "%s DESC"; //$NON-NLS-1$ private static final String COMMA = ", "; //$NON-NLS-1$ private final StringBuilder columnBuilder = new StringBuilder(); private final StringBuilder tableBuilder = new StringBuilder(); private final StringBuilder orderBuilder = new StringBuilder(); private final List<Object> parameters = new ArrayList<Object>(); private boolean isDistinct; private String whereClause; private String groupByClause; private String havingClause; private String limitClause; private String offsetClause; protected Select() { } /** * Adds all columns (<code>*</code>) to include in the query results. * @return a {@link Select} object for further query construction. */ public Select columns() { if (this.columnBuilder.length() > 0) { this.columnBuilder.append(Select.COMMA); } this.columnBuilder.append(Select.COLUMN_ALL); return this; } /** * Adds the given <code>columns</code> to include in the query results. * <p>Each item in the list generates one column in the query results.</p> * @param columns the columns to include in the query results. * @return a {@link Select} object for further query construction. */ public Select columns(final String... columns) { for (final String column : columns) { if (this.columnBuilder.length() > 0) { this.columnBuilder.append(Select.COMMA); } this.columnBuilder.append(column); } return this; } /** * Adds the given <code>column</code> to include in the query results and * specifies a name for the column in the query output. * @param column the column to include in the query results. * @param alias the name for the column in the query output. * @return a {@link Select} object for further query construction. */ public Select column(final String column, final String alias) { if (this.columnBuilder.length() > 0) { this.columnBuilder.append(Select.COMMA); } this.columnBuilder.append(String.format(Select.COLUMN_ALIAS, column, alias)); return this; } /** * Adds the count of the number of times the given <code>column</code> * that is not <code>NULL</code> in a group to include in the query results * and specifies a name for it in the query output. * @param column the column to include in the query results. * @param alias the name for the column in the query output. * @return a {@link Select} object for further query construction. */ public Select count(final String column, final String alias) { if (this.columnBuilder.length() > 0) { this.columnBuilder.append(Select.COMMA); } this.columnBuilder.append(String.format(Select.AGGREGATION_COUNT, column, alias)); return this; } /** * Adds the total number of rows to include in the query results and specifies a name * for it in the query output. * @param alias the name for the column in the query output. * @return a {@link Select} object for further query construction. */ public Select count(final String alias) { if (this.columnBuilder.length() > 0) { this.columnBuilder.append(Select.COMMA); } this.columnBuilder.append(String.format(Select.AGGREGATION_COUNT, Select.COLUMN_ALL, alias)); return this; } /** * Adds the average value of the given <code>column</code> to include in the query results * and specifies a name for it in the query output. * <p>The result of SQLite <code>AVG()</code> is always a <code>double</code> value.</p> * @param column the column to include in the query results. * @param alias the name for the column in the query output. * @return a {@link Select} object for further query construction. */ public Select average(final String column, final String alias) { if (this.columnBuilder.length() > 0) { this.columnBuilder.append(Select.COMMA); } this.columnBuilder.append(String.format(Select.AGGREGATION_AVERAGE, column, alias)); return this; } /** * Adds the minimum value of the given <code>column</code> to include in the query results * and specifies a name for it in the query output. * @param column the column to include in the query results. * @param alias the name for the column in the query output. * @return a {@link Select} object for further query construction. */ public Select min(final String column, final String alias) { if (this.columnBuilder.length() > 0) { this.columnBuilder.append(Select.COMMA); } this.columnBuilder.append(String.format(Select.AGGREGATION_MINIMUM, column, alias)); return this; } /** * Adds the maximum value of the given <code>column</code> to include in the query results * and specifies a name for it in the query output. * @param column the column to include in the query results. * @param alias the name for the column in the query output. * @return a {@link Select} object for further query construction. */ public Select max(final String column, final String alias) { if (this.columnBuilder.length() > 0) { this.columnBuilder.append(Select.COMMA); } this.columnBuilder.append(String.format(Select.AGGREGATION_MAXIMUM, column, alias)); return this; } /** * Adds the total value of the given <code>column</code> to include in the query results * and specifies a name for it in the query output. * @param column the column to include in the query results. * @param alias the name for the column in the query output. * @return a {@link Select} object for further query construction. */ public Select sum(final String column, final String alias) { if (this.columnBuilder.length() > 0) { this.columnBuilder.append(Select.COMMA); } this.columnBuilder.append(String.format(Select.AGGREGATION_SUMMATION, column, alias)); return this; } /** * Excludes duplicates of any rows from the query results. * @return a {@link Select} object for further query construction. */ public Select distinct() { this.isDistinct = true; return this; } /** * Specifies one or more tables containing the data that the query retrieves from. * @param tables the names of tables containing the data that the query retrieves from. * @return a {@link Select} object for further query construction. */ public Select from(final Class<?>... tables) { for (final Class<?> table : tables) { if (this.tableBuilder.length() > 0) { this.tableBuilder.append(Select.COMMA); } this.tableBuilder.append(QueryBuilder.getTableName(table)); } return this; } /** * Specifies one or more tables containing the data that the query retrieves from. * @param tables the names of tables containing the data that the query retrieves from. * @return a {@link Select} object for further query construction. */ public Select from(final String... tables) { for (final String table : tables) { if (this.tableBuilder.length() > 0) { this.tableBuilder.append(Select.COMMA); } this.tableBuilder.append(table); } return this; } /** * Specifies one or more tables containing the data that the query retrieves from. * @param table the name of table containing the data that the query retrieves from. * @param alias an alias for the table specified. * @return a {@link Select} object for further query construction. */ public Select from(final Class<?> table, final String alias) { if (this.tableBuilder.length() > 0) { this.tableBuilder.append(Select.COMMA); } this.tableBuilder.append(String.format(Select.TABLE_ALIAS, QueryBuilder.getTableName(table), alias)); return this; } /** * Specifies one or more tables containing the data that the query retrieves from. * @param table the name of table containing the data that the query retrieves from. * @param alias an alias for the table specified. * @return a {@link Select} object for further query construction. */ public Select from(final String table, final String alias) { if (this.tableBuilder.length() > 0) { this.tableBuilder.append(Select.COMMA); } this.tableBuilder.append(String.format(Select.TABLE_ALIAS, table, alias)); return this; } /** * Filters query results that contain only rows from the given <code>table</code> * that match one ore more rows in other tables. * @param table the table to join. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select join(final Class<?> table, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN, QueryBuilder.getTableName(table), predicate.toString())); return this; } /** * Filters query results that contain only rows from the given <code>table</code> * that match one ore more rows in other tables. * @param table the table to join. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select join(final String table, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN, table, predicate.toString())); return this; } /** * Filters query results that contain only rows from the given <code>table</code> * that match one ore more rows in other tables. * @param table the table to join. * @param alias the alias of the given <code>table</code>. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select join(final Class<?> table, final String alias, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN_ALIAS, QueryBuilder.getTableName(table), predicate.toString())); return this; } /** * Filters query results that contain only rows from the given <code>table</code> * that match one ore more rows in other tables. * @param table the table to join. * @param alias the alias of the given <code>table</code>. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select join(final String table, final String alias, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN_ALIAS, table, predicate.toString())); return this; } /** * Filters query results that contain all rows from the tables before and * only matching rows from the given <code>table</code>. * @param table the table to join. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select leftJoin(final Class<?> table, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN_LEFT, QueryBuilder.getTableName(table), predicate.toString())); return this; } /** * Filters query results that contain all rows from the tables before and * only matching rows from the given <code>table</code>. * @param table the table to join. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select leftJoin(final String table, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN_LEFT, table, predicate.toString())); return this; } /** * Filters query results that contain all rows from the tables before and * only matching rows from the given <code>table</code>. * @param table the table to join. * @param alias the alias of the given <code>table</code>. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select leftJoin(final Class<?> table, final String alias, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN_LEFT_ALIAS, QueryBuilder.getTableName(table), predicate.toString())); return this; } /** * Filters query results that contain all rows from the tables before and * only matching rows from the given <code>table</code>. * @param table the table to join. * @param alias the alias of the given <code>table</code>. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select leftJoin(final String table, final String alias, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN_LEFT_ALIAS, table, predicate.toString())); return this; } /** * Filters query results that contain only matching rows from the given <code>table</code> * and all rows from the tables before. * @param table the table to join. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select rightJoin(final Class<?> table, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN_RIGHT, QueryBuilder.getTableName(table), predicate.toString())); return this; } /** * Filters query results that contain only matching rows from the given <code>table</code> * and all rows from the tables before. * @param table the table to join. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select rightJoin(final String table, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN_RIGHT, table, predicate.toString())); return this; } /** * Filters query results that contain only matching rows from the given <code>table</code> * and all rows from the tables before. * @param table the table to join. * @param alias the alias of the given <code>table</code>. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select rightJoin(final Class<?> table, final String alias, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN_RIGHT_ALIAS, QueryBuilder.getTableName(table), predicate.toString())); return this; } /** * Filters query results that contain only matching rows from the given <code>table</code> * and all rows from the tables before. * @param table the table to join. * @param alias the alias of the given <code>table</code>. * @param predicate the condition on which tables are joined. * @return a {@link Select} object for further query construction. */ public Select rightJoin(final String table, final String alias, final Predicate predicate) { this.tableBuilder.append(String.format(Select.JOIN_RIGHT_ALIAS, table, predicate.toString())); return this; } /** * Specifies filter conditions that determine the rows that the query returns. * <p>{@link #where(Predicate)} must not be called after calling {@link #having(Predicate)}.</p> * @param predicate the conditions on which the tables are joined. * @return a {@link Select} object for further query construction. */ public Select where(final Predicate predicate) { this.whereClause = String.format(Select.WHERE, predicate.toString()); this.parameters.addAll(predicate.getParameters()); return this; } /** * Specifies one or more columns used to group rows returned by the * @param columns one or more columns used to group rows returned by the * @return a {@link Select} object for further query construction. */ public Select groupBy(final String... columns) { final StringBuilder builder = new StringBuilder(); for (final String column : columns) { if (builder.length() > 0) { builder.append(Select.COMMA); } builder.append(column); } this.groupByClause = String.format(Select.GROUP_BY, builder.toString()); return this; } /** * Specifies the conditions that determines the groups included in the query result set. * <p>{@link #where(Predicate)} must not be called after calling {@link #having(Predicate)}.</p> * @param predicate the conditions that determines the groups included in the * @return a {@link Select} object for further query construction. */ public Select having(final Predicate predicate) { this.havingClause = String.format(Select.HAVING, predicate.toString()); this.parameters.addAll(predicate.getParameters()); return this; } /** * Specifies one or more items used to sort the final query result set and the order * for sorting the results. * @param columns the items used to sort the final query result set. * @return a {@link Select} object for further query construction. */ public Select orderBy(final String... columns) { for (final String column : columns) { if (this.orderBuilder.length() > 0) { this.orderBuilder.append(Select.COMMA); } this.orderBuilder.append(column); } return this; } /** * Specifies one or more items used to sort the final query result set and the order for sorting the results. * @param column the item used to sort the final query result set. * @param descending <code>true</code> if the item is to be sorted in descending order; * otherwise, <code>false</code>. * @return a {@link Select} object for further query construction. */ public Select orderBy(final String column, final boolean descending) { if (descending) { this.orderBuilder.append(String.format(Select.DESCENDING, column)); return this; } return this.orderBy(column); } /** * Specifies an upper bound on the number of rows returned by the * @param limit an upper bound on the number of rows returned by the * @return a {@link Select} object for further query construction. */ public Select limit(final int limit) { this.limitClause = String.format(Select.LIMIT, String.valueOf(limit)); return this; } /** * Omits the first <code>offset</code> rows from the query result set. * @param offset the number of rows to omit from the start of the query result set. * @return a {@link Select} object for further query construction. */ public Select offset(final int offset) { this.offsetClause = String.format(Select.OFFSET, String.valueOf(offset)); return this; } /** * Builds a SQL statement and abstracts it in a {@link Query} object ready for execution. * @return a {@link Query} object ready for execution. */ @Override public Query build() { final StringBuilder builder = new StringBuilder(String.format(Select.SELECT, this.isDistinct ? String.format(Select.DISTINCT, this.columnBuilder.toString()) : this.columnBuilder.toString(), this.tableBuilder.toString())); if (!TextUtils.isEmpty(this.whereClause)) { builder.append(this.whereClause); } if (!TextUtils.isEmpty(this.groupByClause)) { builder.append(this.groupByClause); } if (!TextUtils.isEmpty(this.havingClause)) { builder.append(this.havingClause); } if (this.orderBuilder.length() > 0) { builder.append(String.format(Select.ORDER_BY, this.orderBuilder.toString())); } if (!TextUtils.isEmpty(this.limitClause)) { builder.append(this.limitClause); } if (!TextUtils.isEmpty(this.offsetClause)) { builder.append(this.offsetClause); } return new Query(builder.toString(), this.parameters); } }