Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for count/exists queries. #214

Merged
merged 5 commits into from
Apr 18, 2014
Merged

Add support for count/exists queries. #214

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
4a8af85
Add support for count/exists queries.
mpfeiffermway Apr 13, 2014
a980772
Added several test cases for count and exists.
mpfeiffermway Apr 17, 2014
25ea077
Added additional tests for where-chaining.
mpfeiffermway Apr 17, 2014
79c80ad
Must honor group by, having, limit and offset for exists.
mpfeiffermway Apr 17, 2014
1c8e7d3
Added additional tests for count and exists.
mpfeiffermway Apr 17, 2014
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
245 changes: 170 additions & 75 deletions src/com/activeandroid/query/From.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -34,7 +34,7 @@ public final class From implements Sqlable {
private Class<? extends Model> mType;
private String mAlias;
private List<Join> mJoins;
private String mWhere;
private final StringBuilder mWhere = new StringBuilder();
private String mGroupBy;
private String mHaving;
private String mOrderBy;
Expand Down Expand Up @@ -87,29 +87,41 @@ public Join crossJoin(Class<? extends Model> table) {
return join;
}

public From where(String where) {
if (mWhere != null) { // Chain conditions if a previous
mWhere = mWhere + " AND " + where; // condition exists.
} else {
mWhere = where;
public From where(String clause) {
// Chain conditions if a previous condition exists.
if (mWhere.length() > 0) {
mWhere.append(" AND ");
}
mWhere.append(clause);
return this;
}

public From where(String clause, Object... args) {
where(clause).addArguments(args);
return this;
}

public From where(String where, Object... args) {
if (mWhere != null) { // Chain conditions if a previous
mWhere = mWhere + " AND " + where; // condition exists.
}
else {
mWhere = where;
}
public From and(String clause) {
return where(clause);
}

addArguments(args);
public From and(String clause, Object... args) {
return where(clause, args);
}

public From or(String clause) {
if (mWhere.length() > 0) {
mWhere.append(" OR ");
}
mWhere.append(clause);
return this;
}

public From or(String clause, Object... args) {
or(clause).addArguments(args);
return this;
}

public From groupBy(String groupBy) {
mGroupBy = groupBy;
return this;
Expand Down Expand Up @@ -144,99 +156,182 @@ public From offset(String offset) {
}

void addArguments(Object[] args) {
for( Object arg : args ) {
if (arg.getClass() == boolean.class || arg.getClass() == Boolean.class)
arg = ( arg.equals(true) ? 1 : 0 );

for(Object arg : args) {
if (arg.getClass() == boolean.class || arg.getClass() == Boolean.class) {
arg = (arg.equals(true) ? 1 : 0);
}
mArguments.add(arg);
}
}

@Override
public String toSql() {
StringBuilder sql = new StringBuilder();
sql.append(mQueryBase.toSql());
sql.append("FROM ");
sql.append(Cache.getTableName(mType)).append(" ");
private void addFrom(final StringBuilder sql) {
sql.append("FROM ");
sql.append(Cache.getTableName(mType)).append(" ");

if (mAlias != null) {
sql.append("AS ");
sql.append(mAlias);
sql.append(" ");
}
if (mAlias != null) {
sql.append("AS ");
sql.append(mAlias);
sql.append(" ");
}
}

for (Join join : mJoins) {
sql.append(join.toSql());
}
private void addJoins(final StringBuilder sql) {
for (final Join join : mJoins) {
sql.append(join.toSql());
}
}

if (mWhere != null) {
sql.append("WHERE ");
sql.append(mWhere);
sql.append(" ");
}
private void addWhere(final StringBuilder sql) {
if (mWhere.length() > 0) {
sql.append("WHERE ");
sql.append(mWhere);
sql.append(" ");
}
}

if (mGroupBy != null) {
sql.append("GROUP BY ");
sql.append(mGroupBy);
sql.append(" ");
}
private void addGroupBy(final StringBuilder sql) {
if (mGroupBy != null) {
sql.append("GROUP BY ");
sql.append(mGroupBy);
sql.append(" ");
}
}

if (mHaving != null) {
sql.append("HAVING ");
sql.append(mHaving);
sql.append(" ");
}
private void addHaving(final StringBuilder sql) {
if (mHaving != null) {
sql.append("HAVING ");
sql.append(mHaving);
sql.append(" ");
}
}

if (mOrderBy != null) {
sql.append("ORDER BY ");
sql.append(mOrderBy);
sql.append(" ");
}
private void addOrderBy(final StringBuilder sql) {
if (mOrderBy != null) {
sql.append("ORDER BY ");
sql.append(mOrderBy);
sql.append(" ");
}
}

if (mLimit != null) {
sql.append("LIMIT ");
sql.append(mLimit);
sql.append(" ");
}
private void addLimit(final StringBuilder sql) {
if (mLimit != null) {
sql.append("LIMIT ");
sql.append(mLimit);
sql.append(" ");
}
}

if (mOffset != null) {
sql.append("OFFSET ");
sql.append(mOffset);
sql.append(" ");
}
private void addOffset(final StringBuilder sql) {
if (mOffset != null) {
sql.append("OFFSET ");
sql.append(mOffset);
sql.append(" ");
}
}

// Don't wast time building the string
// unless we're going to log it.
if (Log.isEnabled()) {
Log.v(sql.toString() + " " + TextUtils.join(",", getArguments()));
}
private String sqlString(final StringBuilder sql) {

return sql.toString().trim();
}
final String sqlString = sql.toString().trim();

// Don't waste time building the string
// unless we're going to log it.
if (Log.isEnabled()) {
Log.v(sqlString + " " + TextUtils.join(",", getArguments()));
}

return sqlString;
}

@Override
public String toSql() {
final StringBuilder sql = new StringBuilder();
sql.append(mQueryBase.toSql());

addFrom(sql);
addJoins(sql);
addWhere(sql);
addGroupBy(sql);
addHaving(sql);
addOrderBy(sql);
addLimit(sql);
addOffset(sql);

return sqlString(sql);
}

public String toExistsSql() {

final StringBuilder sql = new StringBuilder();
sql.append("SELECT EXISTS(SELECT 1 ");

addFrom(sql);
addJoins(sql);
addWhere(sql);
addGroupBy(sql);
addHaving(sql);
addLimit(sql);
addOffset(sql);

sql.append(")");

return sqlString(sql);
}

public String toCountSql() {

final StringBuilder sql = new StringBuilder();
sql.append("SELECT COUNT(*) ");

addFrom(sql);
addJoins(sql);
addWhere(sql);
addGroupBy(sql);
addHaving(sql);
addLimit(sql);
addOffset(sql);

return sqlString(sql);
}

public <T extends Model> List<T> execute() {
if (mQueryBase instanceof Select) {
return SQLiteUtils.rawQuery(mType, toSql(), getArguments());
}
else {
} else {
SQLiteUtils.execSql(toSql(), getArguments());
Cache.getContext().getContentResolver().notifyChange(ContentProvider
.createUri(mType, null), null);
Cache.getContext().getContentResolver().notifyChange(ContentProvider.createUri(mType, null), null);
return null;

}
}

public <T extends Model> T executeSingle() {
if (mQueryBase instanceof Select) {
limit(1);
return (T) SQLiteUtils.rawQuerySingle(mType, toSql(), getArguments());
}
else {
} else {
limit(1);
SQLiteUtils.rawQuerySingle(mType, toSql(), getArguments()).delete();
return null;

}
}

/**
* Gets a value indicating whether the query returns any rows.
* @return <code>true</code> if the query returns at least one row; otherwise, <code>false</code>.
*/
public boolean exists() {
return SQLiteUtils.intQuery(toExistsSql(), getArguments()) != 0;
}

/**
* Gets the number of rows returned by the query.
*/
public int count() {
return SQLiteUtils.intQuery(toCountSql(), getArguments());
}

public String[] getArguments() {
final int size = mArguments.size();
Expand Down
15 changes: 15 additions & 0 deletions src/com/activeandroid/util/SQLiteUtils.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -106,6 +106,14 @@ public static <T extends Model> List<T> rawQuery(Class<? extends Model> type, St

return entities;
}

public static int intQuery(final String sql, final String[] selectionArgs) {
final Cursor cursor = Cache.openDatabase().rawQuery(sql, selectionArgs);
final int number = processIntCursor(cursor);
cursor.close();

return number;
}

public static <T extends Model> T rawQuerySingle(Class<? extends Model> type, String sql, String[] selectionArgs) {
List<T> entities = rawQuery(type, sql, selectionArgs);
Expand Down Expand Up @@ -349,6 +357,13 @@ public static <T extends Model> List<T> processCursor(Class<? extends Model> typ
return entities;
}

private static int processIntCursor(final Cursor cursor) {
if (cursor.moveToFirst()) {
return cursor.getInt(0);
}
return 0;
}

public static List<String> lexSqlScript(String sqlScript) {
ArrayList<String> sl = new ArrayList<String>();
boolean inString = false, quoteNext = false;
Expand Down
Loading