/**
* Creates a query in the form of "SELECT COUNT(*) AS alias FROM albumName
*
* @param albumName the album to be counted
* @param alias the alias of the count field
* @return a string of the corresponding SQL query
*/
public static String createCountAsAliasStarWhere(String albumName, String alias) {
return " SELECT COUNT(*) AS "
+ alias
+ " FROM "
+ DatabaseStringUtilities.encloseNameWithQuotes(
DatabaseStringUtilities.generateTableName(albumName));
}
/**
* Creates a simple select * from albumName with a properly formatted albumName and columnName
*
* @param albumName The name of the album to which this query refers to
* @param columnName The name of the column to be queried
* @param whereColumn The name of the column which is referenced in the where clause
* @return The properly formatted select query containing a wildcard as the value for in the where
* clause
*/
public static String createSelectColumnQuery(String albumName, String columnName) {
return " SELECT "
+ DatabaseStringUtilities.transformColumnNameToSelectQueryName(columnName)
+ " FROM "
+ DatabaseStringUtilities.encloseNameWithQuotes(
DatabaseStringUtilities.generateTableName(albumName));
}
/**
* Creates a simple select * from albumName with a properly formatted albumName
*
* @param albumName The album on which the query should be performed.
* @return A string containing the proper SQL string.
*/
public static String createSelectStarQuery(String albumName) {
return "SELECT * FROM "
+ DatabaseStringUtilities.encloseNameWithQuotes(
DatabaseStringUtilities.generateTableName(albumName));
}
/**
* This method builds a SQL query string out of multiple query components
*
* @param queryComponents a list of query components. Escapes all appearing quotes in the album
* fields.
* @param connectByAnd a boolean specifying whether the query components are connected by AND
* (connectedByAnd == true) or by OR (connectedByAnd == false).
* @param albumName the name of the album which should be queried.
* @param sortField the field upon which the results should be sorted. Can be null or empty if not
* needed
* @param sortAscending only if a sortField is specified. In this case, true means that the
* results are sorted ascending, false means descending
* @return a valid SQL query as a string. By default a 'SELECT *' is performed on the field/column
* names.
* @throws QueryBuilderException
*/
public static String buildQuery(
List<QueryComponent> queryComponents,
boolean connectByAnd,
String albumName,
String sortField,
boolean sortAscending,
Context context)
throws QueryBuilderException {
StringBuilder query = new StringBuilder();
query.append(
"SELECT * FROM "
+ DatabaseStringUtilities.encloseNameWithQuotes(
DatabaseStringUtilities.generateTableName(albumName)));
if (!queryComponents.isEmpty()) {
query.append(" WHERE ");
}
Map<String, FieldType> fieldNameToFieldTypeMap =
DatabaseQueryOperation.retrieveFieldnameToFieldTypeMapping(
DatabaseWrapper.getSQLiteDatabaseInstance(context),
context,
DatabaseStringUtilities.generateTableName(albumName));
for (int i = 0; i < queryComponents.size(); i++) {
if (fieldNameToFieldTypeMap.get(queryComponents.get(i).getFieldName()) == null) {
Log.e(AppParams.LOG_TAG, "An error occurred while executing the query");
throw new QueryBuilderException();
}
if (fieldNameToFieldTypeMap
.get(queryComponents.get(i).getFieldName())
.equals(FieldType.OPTION)
|| fieldNameToFieldTypeMap
.get(queryComponents.get(i).getFieldName())
.equals(FieldType.URL)
|| fieldNameToFieldTypeMap
.get(queryComponents.get(i).getFieldName())
.equals(FieldType.TEXT)) {
if (queryComponents.get(i).getOperator() == QueryOperator.CONTAINS) {
query.append(
"("
+ "["
+ queryComponents.get(i).getFieldName()
+ "] "
+ toSQLOperator(queryComponents.get(i).getOperator())
+ " "
+ "'%"
+ DatabaseStringUtilities.sanitizeSingleQuotesInAlbumItemValues(
queryComponents.get(i).getValue())
+ "%')");
} else {
query.append(
"("
+ "["
+ queryComponents.get(i).getFieldName()
+ "] "
+ toSQLOperator(queryComponents.get(i).getOperator())
+ " "
+ "'"
+ DatabaseStringUtilities.sanitizeSingleQuotesInAlbumItemValues(
queryComponents.get(i).getValue())
+ "')");
}
} else {
query.append(
"("
+ "["
+ queryComponents.get(i).getFieldName()
+ "] "
+ toSQLOperator(queryComponents.get(i).getOperator())
+ " "
+ queryComponents.get(i).getValue()
+ ")");
}
if (i + 1 != queryComponents.size()) {
if (connectByAnd) {
query.append(" AND ");
} else {
query.append(" OR ");
}
}
}
if (sortField != null && !sortField.isEmpty()) {
query.append(" ORDER BY [" + sortField + "]");
if (sortAscending) {
query.append(" ASC");
} else {
query.append(" DESC");
}
}
return query.toString();
}
