SQLiteQueryBuilder.java revision accbadeb52eda2d972fa6d9f41ebaf9168dc343a
1/* 2 * Copyright (C) 2006 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17package android.database.sqlite; 18 19import android.database.Cursor; 20import android.database.DatabaseUtils; 21import android.database.sqlite.SQLiteDatabase; 22import android.provider.BaseColumns; 23import android.text.TextUtils; 24import android.util.Config; 25import android.util.Log; 26 27import java.util.Iterator; 28import java.util.Map; 29import java.util.Set; 30import java.util.Map.Entry; 31 32/** 33 * This is a convience class that helps build SQL queries to be sent to 34 * {@link SQLiteDatabase} objects. 35 */ 36public class SQLiteQueryBuilder 37{ 38 private static final String TAG = "SQLiteQueryBuilder"; 39 40 private Map<String, String> mProjectionMap = null; 41 private String mTables = ""; 42 private StringBuilder mWhereClause = new StringBuilder(64); 43 private boolean mDistinct; 44 private SQLiteDatabase.CursorFactory mFactory; 45 46 public SQLiteQueryBuilder() { 47 mDistinct = false; 48 mFactory = null; 49 } 50 51 /** 52 * Mark the query as DISTINCT. 53 * 54 * @param distinct if true the query is DISTINCT, otherwise it isn't 55 */ 56 public void setDistinct(boolean distinct) { 57 mDistinct = distinct; 58 } 59 60 /** 61 * Returns the list of tables being queried 62 * 63 * @return the list of tables being queried 64 */ 65 public String getTables() { 66 return mTables; 67 } 68 69 /** 70 * Sets the list of tables to query. Multiple tables can be specified to perform a join. 71 * For example: 72 * setTables("foo, bar") 73 * setTables("foo LEFT OUTER JOIN bar ON (foo.id = bar.foo_id)") 74 * 75 * @param inTables the list of tables to query on 76 */ 77 public void setTables(String inTables) { 78 mTables = inTables; 79 } 80 81 /** 82 * Append a chunk to the WHERE clause of the query. All chunks appended are surrounded 83 * by parenthesis and ANDed with the selection passed to {@link #query}. The final 84 * WHERE clause looks like: 85 * 86 * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) 87 * 88 * @param inWhere the chunk of text to append to the WHERE clause. 89 */ 90 public void appendWhere(CharSequence inWhere) { 91 if (mWhereClause.length() == 0) { 92 mWhereClause.append('('); 93 } 94 mWhereClause.append(inWhere); 95 } 96 97 /** 98 * Append a chunk to the WHERE clause of the query. All chunks appended are surrounded 99 * by parenthesis and ANDed with the selection passed to {@link #query}. The final 100 * WHERE clause looks like: 101 * 102 * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) 103 * 104 * @param inWhere the chunk of text to append to the WHERE clause. it will be escaped 105 * to avoid SQL injection attacks 106 */ 107 public void appendWhereEscapeString(String inWhere) { 108 if (mWhereClause.length() == 0) { 109 mWhereClause.append('('); 110 } 111 DatabaseUtils.appendEscapedSQLString(mWhereClause, inWhere); 112 } 113 114 /** 115 * Sets the projection map for the query. The projection map maps 116 * from column names that the caller passes into query to database 117 * column names. This is useful for renaming columns as well as 118 * disambiguating column names when doing joins. For example you 119 * could map "name" to "people.name". If a projection map is set 120 * it must contain all column names the user may request, even if 121 * the key and value are the same. 122 * 123 * @param columnMap maps from the user column names to the database column names 124 */ 125 public void setProjectionMap(Map<String, String> columnMap) { 126 mProjectionMap = columnMap; 127 } 128 129 /** 130 * Sets the cursor factory to be used for the query. You can use 131 * one factory for all queries on a database but it is normally 132 * easier to specify the factory when doing this query. @param 133 * factory the factor to use 134 */ 135 public void setCursorFactory(SQLiteDatabase.CursorFactory factory) { 136 mFactory = factory; 137 } 138 139 /** 140 * Build an SQL query string from the given clauses. 141 * 142 * @param distinct true if you want each row to be unique, false otherwise. 143 * @param tables The table names to compile the query against. 144 * @param columns A list of which columns to return. Passing null will 145 * return all columns, which is discouraged to prevent reading 146 * data from storage that isn't going to be used. 147 * @param where A filter declaring which rows to return, formatted as an SQL 148 * WHERE clause (excluding the WHERE itself). Passing null will 149 * return all rows for the given URL. 150 * @param groupBy A filter declaring how to group rows, formatted as an SQL 151 * GROUP BY clause (excluding the GROUP BY itself). Passing null 152 * will cause the rows to not be grouped. 153 * @param having A filter declare which row groups to include in the cursor, 154 * if row grouping is being used, formatted as an SQL HAVING 155 * clause (excluding the HAVING itself). Passing null will cause 156 * all row groups to be included, and is required when row 157 * grouping is not being used. 158 * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause 159 * (excluding the ORDER BY itself). Passing null will use the 160 * default sort order, which may be unordered. 161 * @param limit Limits the number of rows returned by the query, 162 * formatted as LIMIT clause. Passing null denotes no LIMIT clause. 163 * @return the SQL query string 164 */ 165 public static String buildQueryString( 166 boolean distinct, String tables, String[] columns, String where, 167 String groupBy, String having, String orderBy, String limit) { 168 if (TextUtils.isEmpty(groupBy) && !TextUtils.isEmpty(having)) { 169 throw new IllegalArgumentException( 170 "HAVING clauses are only permitted when using a groupBy clause"); 171 } 172 173 StringBuilder query = new StringBuilder(120); 174 175 query.append("SELECT "); 176 if (distinct) { 177 query.append("DISTINCT "); 178 } 179 if (columns != null && columns.length != 0) { 180 appendColumns(query, columns); 181 } else { 182 query.append("* "); 183 } 184 query.append("FROM "); 185 query.append(tables); 186 appendClause(query, " WHERE ", where); 187 appendClause(query, " GROUP BY ", groupBy); 188 appendClause(query, " HAVING ", having); 189 appendClause(query, " ORDER BY ", orderBy); 190 appendClauseEscapeClause(query, " LIMIT ", limit); 191 192 return query.toString(); 193 } 194 195 private static void appendClause(StringBuilder s, String name, String clause) { 196 if (!TextUtils.isEmpty(clause)) { 197 s.append(name); 198 s.append(clause); 199 } 200 } 201 202 private static void appendClauseEscapeClause(StringBuilder s, String name, String clause) { 203 if (!TextUtils.isEmpty(clause)) { 204 s.append(name); 205 DatabaseUtils.appendEscapedSQLString(s, clause); 206 } 207 } 208 209 /** 210 * Add the names that are non-null in columns to s, separating 211 * them with commas. 212 */ 213 public static void appendColumns(StringBuilder s, String[] columns) { 214 int n = columns.length; 215 216 for (int i = 0; i < n; i++) { 217 String column = columns[i]; 218 219 if (column != null) { 220 if (i > 0) { 221 s.append(", "); 222 } 223 s.append(column); 224 } 225 } 226 s.append(' '); 227 } 228 229 /** 230 * Perform a query by combining all current settings and the 231 * information passed into this method. 232 * 233 * @param db the database to query on 234 * @param projectionIn A list of which columns to return. Passing 235 * null will return all columns, which is discouraged to prevent 236 * reading data from storage that isn't going to be used. 237 * @param selection A filter declaring which rows to return, 238 * formatted as an SQL WHERE clause (excluding the WHERE 239 * itself). Passing null will return all rows for the given URL. 240 * @param selectionArgs You may include ?s in selection, which 241 * will be replaced by the values from selectionArgs, in order 242 * that they appear in the selection. The values will be bound 243 * as Strings. 244 * @param groupBy A filter declaring how to group rows, formatted 245 * as an SQL GROUP BY clause (excluding the GROUP BY 246 * itself). Passing null will cause the rows to not be grouped. 247 * @param having A filter declare which row groups to include in 248 * the cursor, if row grouping is being used, formatted as an 249 * SQL HAVING clause (excluding the HAVING itself). Passing 250 * null will cause all row groups to be included, and is 251 * required when row grouping is not being used. 252 * @param sortOrder How to order the rows, formatted as an SQL 253 * ORDER BY clause (excluding the ORDER BY itself). Passing null 254 * will use the default sort order, which may be unordered. 255 * @return a cursor over the result set 256 * @see android.content.ContentResolver#query(android.net.Uri, String[], 257 * String, String[], String) 258 */ 259 public Cursor query(SQLiteDatabase db, String[] projectionIn, 260 String selection, String[] selectionArgs, String groupBy, 261 String having, String sortOrder) { 262 return query(db, projectionIn, selection, selectionArgs, groupBy, having, sortOrder, 263 null /* limit */); 264 } 265 266 /** 267 * Perform a query by combining all current settings and the 268 * information passed into this method. 269 * 270 * @param db the database to query on 271 * @param projectionIn A list of which columns to return. Passing 272 * null will return all columns, which is discouraged to prevent 273 * reading data from storage that isn't going to be used. 274 * @param selection A filter declaring which rows to return, 275 * formatted as an SQL WHERE clause (excluding the WHERE 276 * itself). Passing null will return all rows for the given URL. 277 * @param selectionArgs You may include ?s in selection, which 278 * will be replaced by the values from selectionArgs, in order 279 * that they appear in the selection. The values will be bound 280 * as Strings. 281 * @param groupBy A filter declaring how to group rows, formatted 282 * as an SQL GROUP BY clause (excluding the GROUP BY 283 * itself). Passing null will cause the rows to not be grouped. 284 * @param having A filter declare which row groups to include in 285 * the cursor, if row grouping is being used, formatted as an 286 * SQL HAVING clause (excluding the HAVING itself). Passing 287 * null will cause all row groups to be included, and is 288 * required when row grouping is not being used. 289 * @param sortOrder How to order the rows, formatted as an SQL 290 * ORDER BY clause (excluding the ORDER BY itself). Passing null 291 * will use the default sort order, which may be unordered. 292 * @param limit Limits the number of rows returned by the query, 293 * formatted as LIMIT clause. Passing null denotes no LIMIT clause. 294 * @return a cursor over the result set 295 * @see android.content.ContentResolver#query(android.net.Uri, String[], 296 * String, String[], String) 297 */ 298 public Cursor query(SQLiteDatabase db, String[] projectionIn, 299 String selection, String[] selectionArgs, String groupBy, 300 String having, String sortOrder, String limit) { 301 if (mTables == null) { 302 return null; 303 } 304 305 String sql = buildQuery( 306 projectionIn, selection, selectionArgs, groupBy, having, 307 sortOrder, limit); 308 309 if (Log.isLoggable(TAG, Log.DEBUG)) { 310 Log.d(TAG, "Performing query: " + sql); 311 } 312 return db.rawQueryWithFactory( 313 mFactory, sql, selectionArgs, 314 SQLiteDatabase.findEditTable(mTables)); 315 } 316 317 /** 318 * Construct a SELECT statement suitable for use in a group of 319 * SELECT statements that will be joined through UNION operators 320 * in buildUnionQuery. 321 * 322 * @param projectionIn A list of which columns to return. Passing 323 * null will return all columns, which is discouraged to 324 * prevent reading data from storage that isn't going to be 325 * used. 326 * @param selection A filter declaring which rows to return, 327 * formatted as an SQL WHERE clause (excluding the WHERE 328 * itself). Passing null will return all rows for the given 329 * URL. 330 * @param selectionArgs You may include ?s in selection, which 331 * will be replaced by the values from selectionArgs, in order 332 * that they appear in the selection. The values will be bound 333 * as Strings. 334 * @param groupBy A filter declaring how to group rows, formatted 335 * as an SQL GROUP BY clause (excluding the GROUP BY itself). 336 * Passing null will cause the rows to not be grouped. 337 * @param having A filter declare which row groups to include in 338 * the cursor, if row grouping is being used, formatted as an 339 * SQL HAVING clause (excluding the HAVING itself). Passing 340 * null will cause all row groups to be included, and is 341 * required when row grouping is not being used. 342 * @param sortOrder How to order the rows, formatted as an SQL 343 * ORDER BY clause (excluding the ORDER BY itself). Passing null 344 * will use the default sort order, which may be unordered. 345 * @param limit Limits the number of rows returned by the query, 346 * formatted as LIMIT clause. Passing null denotes no LIMIT clause. 347 * @return the resulting SQL SELECT statement 348 */ 349 public String buildQuery( 350 String[] projectionIn, String selection, String[] selectionArgs, 351 String groupBy, String having, String sortOrder, String limit) { 352 String[] projection = computeProjection(projectionIn); 353 354 StringBuilder where = new StringBuilder(); 355 356 if (mWhereClause.length() > 0) { 357 where.append(mWhereClause.toString()); 358 where.append(')'); 359 } 360 361 // Tack on the user's selection, if present. 362 if (selection != null && selection.length() > 0) { 363 if (mWhereClause.length() > 0) { 364 where.append(" AND "); 365 } 366 367 where.append('('); 368 where.append(selection); 369 where.append(')'); 370 } 371 372 return buildQueryString( 373 mDistinct, mTables, projection, where.toString(), 374 groupBy, having, sortOrder, limit); 375 } 376 377 /** 378 * Construct a SELECT statement suitable for use in a group of 379 * SELECT statements that will be joined through UNION operators 380 * in buildUnionQuery. 381 * 382 * @param typeDiscriminatorColumn the name of the result column 383 * whose cells will contain the name of the table from which 384 * each row was drawn. 385 * @param unionColumns the names of the columns to appear in the 386 * result. This may include columns that do not appear in the 387 * table this SELECT is querying (i.e. mTables), but that do 388 * appear in one of the other tables in the UNION query that we 389 * are constructing. 390 * @param columnsPresentInTable a Set of the names of the columns 391 * that appear in this table (i.e. in the table whose name is 392 * mTables). Since columns in unionColumns include columns that 393 * appear only in other tables, we use this array to distinguish 394 * which ones actually are present. Other columns will have 395 * NULL values for results from this subquery. 396 * @param computedColumnsOffset all columns in unionColumns before 397 * this index are included under the assumption that they're 398 * computed and therefore won't appear in columnsPresentInTable, 399 * e.g. "date * 1000 as normalized_date" 400 * @param typeDiscriminatorValue the value used for the 401 * type-discriminator column in this subquery 402 * @param selection A filter declaring which rows to return, 403 * formatted as an SQL WHERE clause (excluding the WHERE 404 * itself). Passing null will return all rows for the given 405 * URL. 406 * @param selectionArgs You may include ?s in selection, which 407 * will be replaced by the values from selectionArgs, in order 408 * that they appear in the selection. The values will be bound 409 * as Strings. 410 * @param groupBy A filter declaring how to group rows, formatted 411 * as an SQL GROUP BY clause (excluding the GROUP BY itself). 412 * Passing null will cause the rows to not be grouped. 413 * @param having A filter declare which row groups to include in 414 * the cursor, if row grouping is being used, formatted as an 415 * SQL HAVING clause (excluding the HAVING itself). Passing 416 * null will cause all row groups to be included, and is 417 * required when row grouping is not being used. 418 * @return the resulting SQL SELECT statement 419 */ 420 public String buildUnionSubQuery( 421 String typeDiscriminatorColumn, 422 String[] unionColumns, 423 Set<String> columnsPresentInTable, 424 int computedColumnsOffset, 425 String typeDiscriminatorValue, 426 String selection, 427 String[] selectionArgs, 428 String groupBy, 429 String having) { 430 int unionColumnsCount = unionColumns.length; 431 String[] projectionIn = new String[unionColumnsCount]; 432 433 for (int i = 0; i < unionColumnsCount; i++) { 434 String unionColumn = unionColumns[i]; 435 436 if (unionColumn.equals(typeDiscriminatorColumn)) { 437 projectionIn[i] = "'" + typeDiscriminatorValue + "' AS " 438 + typeDiscriminatorColumn; 439 } else if (i <= computedColumnsOffset 440 || columnsPresentInTable.contains(unionColumn)) { 441 projectionIn[i] = unionColumn; 442 } else { 443 projectionIn[i] = "NULL AS " + unionColumn; 444 } 445 } 446 return buildQuery( 447 projectionIn, selection, selectionArgs, groupBy, having, 448 null /* sortOrder */, 449 null /* limit */); 450 } 451 452 /** 453 * Given a set of subqueries, all of which are SELECT statements, 454 * construct a query that returns the union of what those 455 * subqueries return. 456 * @param subQueries an array of SQL SELECT statements, all of 457 * which must have the same columns as the same positions in 458 * their results 459 * @param sortOrder How to order the rows, formatted as an SQL 460 * ORDER BY clause (excluding the ORDER BY itself). Passing 461 * null will use the default sort order, which may be unordered. 462 * @param limit The limit clause, which applies to the entire union result set 463 * 464 * @return the resulting SQL SELECT statement 465 */ 466 public String buildUnionQuery(String[] subQueries, String sortOrder, String limit) { 467 StringBuilder query = new StringBuilder(128); 468 int subQueryCount = subQueries.length; 469 String unionOperator = mDistinct ? " UNION " : " UNION ALL "; 470 471 for (int i = 0; i < subQueryCount; i++) { 472 if (i > 0) { 473 query.append(unionOperator); 474 } 475 query.append(subQueries[i]); 476 } 477 appendClause(query, " ORDER BY ", sortOrder); 478 appendClause(query, " LIMIT ", limit); 479 return query.toString(); 480 } 481 482 private String[] computeProjection(String[] projectionIn) { 483 if (projectionIn != null && projectionIn.length > 0) { 484 if (mProjectionMap != null) { 485 String[] projection = new String[projectionIn.length]; 486 int length = projectionIn.length; 487 488 for (int i = 0; i < length; i++) { 489 String userColumn = projectionIn[i]; 490 String column = mProjectionMap.get(userColumn); 491 492 if (column == null) { 493 throw new IllegalArgumentException( 494 "Invalid column " + projectionIn[i]); 495 } else { 496 projection[i] = column; 497 } 498 } 499 return projection; 500 } else { 501 return projectionIn; 502 } 503 } else if (mProjectionMap != null) { 504 // Return all columns in projection map. 505 Set<Entry<String, String>> entrySet = mProjectionMap.entrySet(); 506 String[] projection = new String[entrySet.size()]; 507 Iterator<Entry<String, String>> entryIter = entrySet.iterator(); 508 int i = 0; 509 510 while (entryIter.hasNext()) { 511 Entry<String, String> entry = entryIter.next(); 512 513 // Don't include the _count column when people ask for no projection. 514 if (entry.getKey().equals(BaseColumns._COUNT)) { 515 continue; 516 } 517 projection[i++] = entry.getValue(); 518 } 519 return projection; 520 } 521 return null; 522 } 523} 524