SQLiteQueryBuilder.java revision 54b6cfa9a9e5b861a9930af873580d6dc20f773c
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 if (mWhereClause.length() > 0) { 355 mWhereClause.append(')'); 356 } 357 358 // Tack on the user's selection, if present. 359 if (selection != null && selection.length() > 0) { 360 if (mWhereClause.length() > 0) { 361 mWhereClause.append(" AND "); 362 } 363 364 mWhereClause.append('('); 365 mWhereClause.append(selection); 366 mWhereClause.append(')'); 367 } 368 369 return buildQueryString( 370 mDistinct, mTables, projection, mWhereClause.toString(), 371 groupBy, having, sortOrder, limit); 372 } 373 374 /** 375 * Construct a SELECT statement suitable for use in a group of 376 * SELECT statements that will be joined through UNION operators 377 * in buildUnionQuery. 378 * 379 * @param typeDiscriminatorColumn the name of the result column 380 * whose cells will contain the name of the table from which 381 * each row was drawn. 382 * @param unionColumns the names of the columns to appear in the 383 * result. This may include columns that do not appear in the 384 * table this SELECT is querying (i.e. mTables), but that do 385 * appear in one of the other tables in the UNION query that we 386 * are constructing. 387 * @param columnsPresentInTable a Set of the names of the columns 388 * that appear in this table (i.e. in the table whose name is 389 * mTables). Since columns in unionColumns include columns that 390 * appear only in other tables, we use this array to distinguish 391 * which ones actually are present. Other columns will have 392 * NULL values for results from this subquery. 393 * @param computedColumnsOffset all columns in unionColumns before 394 * this index are included under the assumption that they're 395 * computed and therefore won't appear in columnsPresentInTable, 396 * e.g. "date * 1000 as normalized_date" 397 * @param typeDiscriminatorValue the value used for the 398 * type-discriminator column in this subquery 399 * @param selection A filter declaring which rows to return, 400 * formatted as an SQL WHERE clause (excluding the WHERE 401 * itself). Passing null will return all rows for the given 402 * URL. 403 * @param selectionArgs You may include ?s in selection, which 404 * will be replaced by the values from selectionArgs, in order 405 * that they appear in the selection. The values will be bound 406 * as Strings. 407 * @param groupBy A filter declaring how to group rows, formatted 408 * as an SQL GROUP BY clause (excluding the GROUP BY itself). 409 * Passing null will cause the rows to not be grouped. 410 * @param having A filter declare which row groups to include in 411 * the cursor, if row grouping is being used, formatted as an 412 * SQL HAVING clause (excluding the HAVING itself). Passing 413 * null will cause all row groups to be included, and is 414 * required when row grouping is not being used. 415 * @return the resulting SQL SELECT statement 416 */ 417 public String buildUnionSubQuery( 418 String typeDiscriminatorColumn, 419 String[] unionColumns, 420 Set<String> columnsPresentInTable, 421 int computedColumnsOffset, 422 String typeDiscriminatorValue, 423 String selection, 424 String[] selectionArgs, 425 String groupBy, 426 String having) { 427 int unionColumnsCount = unionColumns.length; 428 String[] projectionIn = new String[unionColumnsCount]; 429 430 for (int i = 0; i < unionColumnsCount; i++) { 431 String unionColumn = unionColumns[i]; 432 433 if (unionColumn.equals(typeDiscriminatorColumn)) { 434 projectionIn[i] = "'" + typeDiscriminatorValue + "' AS " 435 + typeDiscriminatorColumn; 436 } else if (i <= computedColumnsOffset 437 || columnsPresentInTable.contains(unionColumn)) { 438 projectionIn[i] = unionColumn; 439 } else { 440 projectionIn[i] = "NULL AS " + unionColumn; 441 } 442 } 443 return buildQuery( 444 projectionIn, selection, selectionArgs, groupBy, having, 445 null /* sortOrder */, 446 null /* limit */); 447 } 448 449 /** 450 * Given a set of subqueries, all of which are SELECT statements, 451 * construct a query that returns the union of what those 452 * subqueries return. 453 * @param subQueries an array of SQL SELECT statements, all of 454 * which must have the same columns as the same positions in 455 * their results 456 * @param sortOrder How to order the rows, formatted as an SQL 457 * ORDER BY clause (excluding the ORDER BY itself). Passing 458 * null will use the default sort order, which may be unordered. 459 * @param limit The limit clause, which applies to the entire union result set 460 * 461 * @return the resulting SQL SELECT statement 462 */ 463 public String buildUnionQuery(String[] subQueries, String sortOrder, String limit) { 464 StringBuilder query = new StringBuilder(128); 465 int subQueryCount = subQueries.length; 466 String unionOperator = mDistinct ? " UNION " : " UNION ALL "; 467 468 for (int i = 0; i < subQueryCount; i++) { 469 if (i > 0) { 470 query.append(unionOperator); 471 } 472 query.append(subQueries[i]); 473 } 474 appendClause(query, " ORDER BY ", sortOrder); 475 appendClause(query, " LIMIT ", limit); 476 return query.toString(); 477 } 478 479 private String[] computeProjection(String[] projectionIn) { 480 if (projectionIn != null && projectionIn.length > 0) { 481 if (mProjectionMap != null) { 482 String[] projection = new String[projectionIn.length]; 483 int length = projectionIn.length; 484 485 for (int i = 0; i < length; i++) { 486 String userColumn = projectionIn[i]; 487 String column = mProjectionMap.get(userColumn); 488 489 if (column == null) { 490 throw new IllegalArgumentException( 491 "Invalid column " + projectionIn[i]); 492 } else { 493 projection[i] = column; 494 } 495 } 496 return projection; 497 } else { 498 return projectionIn; 499 } 500 } else if (mProjectionMap != null) { 501 // Return all columns in projection map. 502 Set<Entry<String, String>> entrySet = mProjectionMap.entrySet(); 503 String[] projection = new String[entrySet.size()]; 504 Iterator<Entry<String, String>> entryIter = entrySet.iterator(); 505 int i = 0; 506 507 while (entryIter.hasNext()) { 508 Entry<String, String> entry = entryIter.next(); 509 510 // Don't include the _count column when people ask for no projection. 511 if (entry.getKey().equals(BaseColumns._COUNT)) { 512 continue; 513 } 514 projection[i++] = entry.getValue(); 515 } 516 return projection; 517 } 518 return null; 519 } 520} 521