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