SelectionBuilder.java (13248B)
1 package de.nordgedanken.rpicms.common.db; 2 3 /** 4 * Created by frank on 14.10.14. 5 */ 6 import android.content.ContentValues; 7 import android.database.Cursor; 8 import android.database.sqlite.SQLiteDatabase; 9 import android.text.TextUtils; 10 import android.util.Log; 11 12 import java.util.ArrayList; 13 import java.util.Arrays; 14 import java.util.Collections; 15 import java.util.HashMap; 16 import java.util.Map; 17 18 public class SelectionBuilder { 19 20 /** 21 * Helper for building selection clauses for {@link SQLiteDatabase}. 22 * 23 * <p>This class provides a convenient frontend for working with SQL. Instead of composing statements 24 * manually using string concatenation, method calls are used to construct the statement one 25 * clause at a time. These methods can be chained together. 26 * 27 * <p>If multiple where() statements are provided, they're combined using {@code AND}. 28 * 29 * <p>Example: 30 * 31 * <pre> 32 * SelectionBuilder builder = new SelectionBuilder(); 33 * Cursor c = builder.table(FeedContract.Entry.TABLE_NAME) // String TABLE_NAME = "entry" 34 * .where(FeedContract.Entry._ID + "=?", id); // String _ID = "_ID" 35 * .query(db, projection, sortOrder) 36 * 37 * </pre> 38 * 39 * <p>In this example, the table name and filters ({@code WHERE} clauses) are both explicitly 40 * specified via method call. SelectionBuilder takes care of issuing a "query" command to the 41 * database, and returns the resulting {@link Cursor} object. 42 * 43 * <p>Inner {@code JOIN}s can be accomplished using the mapToTable() function. The map() function 44 * can be used to create new columns based on arbitrary (SQL-based) criteria. In advanced usage, 45 * entire subqueries can be passed into the map() function. 46 * 47 * <p>Advanced example: 48 * 49 * <pre> 50 * // String SESSIONS_JOIN_BLOCKS_ROOMS = "sessions " 51 * // + "LEFT OUTER JOIN blocks ON sessions.block_id=blocks.block_id " 52 * // + "LEFT OUTER JOIN rooms ON sessions.room_id=rooms.room_id"; 53 * 54 * // String Subquery.BLOCK_NUM_STARRED_SESSIONS = 55 * // "(SELECT COUNT(1) FROM " 56 * // + Tables.SESSIONS + " WHERE " + Qualified.SESSIONS_BLOCK_ID + "=" 57 * // + Qualified.BLOCKS_BLOCK_ID + " AND " + Qualified.SESSIONS_STARRED + "=1)"; 58 * 59 * String Subqery.BLOCK_SESSIONS_COUNT = 60 * Cursor c = builder.table(Tables.SESSIONS_JOIN_BLOCKS_ROOMS) 61 * .map(Blocks.NUM_STARRED_SESSIONS, Subquery.BLOCK_NUM_STARRED_SESSIONS) 62 * .mapToTable(Sessions._ID, Tables.SESSIONS) 63 * .mapToTable(Sessions.SESSION_ID, Tables.SESSIONS) 64 * .mapToTable(Sessions.BLOCK_ID, Tables.SESSIONS) 65 * .mapToTable(Sessions.ROOM_ID, Tables.SESSIONS) 66 * .where(Qualified.SESSIONS_BLOCK_ID + "=?", blockId); 67 * </pre> 68 * 69 * <p>In this example, we have two different types of {@code JOIN}s: a left outer join using a 70 * modified table name (since this class doesn't directly support these), and an inner join using 71 * the mapToTable() function. The map() function is used to insert a count based on specific 72 * criteria, executed as a sub-query. 73 * 74 * This class is <em>not</em> thread safe. 75 */ 76 private static final String TAG = "basicsyncadapter"; 77 78 private String mTable = null; 79 private Map<String, String> mProjectionMap = new HashMap<String, String>(); 80 private StringBuilder mSelection = new StringBuilder(); 81 private ArrayList<String> mSelectionArgs = new ArrayList<String>(); 82 83 /** 84 * Reset any internal state, allowing this builder to be recycled. 85 * 86 * <p>Calling this method is more efficient than creating a new SelectionBuilder object. 87 * 88 * @return Fluent interface 89 */ 90 public SelectionBuilder reset() { 91 mTable = null; 92 mSelection.setLength(0); 93 mSelectionArgs.clear(); 94 return this; 95 } 96 97 /** 98 * Append the given selection clause to the internal state. Each clause is 99 * surrounded with parenthesis and combined using {@code AND}. 100 * 101 * <p>In the most basic usage, simply provide a selection in SQL {@code WHERE} statement format. 102 * 103 * <p>Example: 104 * 105 * <pre> 106 * .where("blog_posts.category = 'PROGRAMMING'); 107 * </pre> 108 * 109 * <p>User input should never be directly supplied as as part of the selection statement. 110 * Instead, use positional parameters in your selection statement, then pass the user input 111 * in via the selectionArgs parameter. This prevents SQL escape characters in user input from 112 * causing unwanted side effects. (Failure to follow this convention may have security 113 * implications.) 114 * 115 * <p>Positional parameters are specified using the '?' character. 116 * 117 * <p>Example: 118 * <pre> 119 * .where("blog_posts.title contains ?, userSearchString); 120 * </pre> 121 * 122 * @param selection SQL where statement 123 * @param selectionArgs Values to substitute for positional parameters ('?' characters in 124 * {@code selection} statement. Will be automatically escaped. 125 * @return Fluent interface 126 */ 127 public SelectionBuilder where(String selection, String... selectionArgs) { 128 if (TextUtils.isEmpty(selection)) { 129 if (selectionArgs != null && selectionArgs.length > 0) { 130 throw new IllegalArgumentException( 131 "Valid selection required when including arguments="); 132 } 133 134 // Shortcut when clause is empty 135 return this; 136 } 137 138 if (mSelection.length() > 0) { 139 mSelection.append(" AND "); 140 } 141 142 mSelection.append("(").append(selection).append(")"); 143 if (selectionArgs != null) { 144 Collections.addAll(mSelectionArgs, selectionArgs); 145 } 146 147 return this; 148 } 149 150 /** 151 * Table name to use for SQL {@code FROM} statement. 152 * 153 * <p>This method may only be called once. If multiple tables are required, concatenate them 154 * in SQL-format (typically comma-separated). 155 * 156 * <p>If you need to do advanced {@code JOIN}s, they can also be specified here. 157 * 158 * See also: mapToTable() 159 * 160 * @param table Table name 161 * @return Fluent interface 162 */ 163 public SelectionBuilder table(String table) { 164 mTable = table; 165 return this; 166 } 167 168 /** 169 * Verify that a table name has been supplied using table(). 170 * 171 * @throws IllegalStateException if table not set 172 */ 173 private void assertTable() { 174 if (mTable == null) { 175 throw new IllegalStateException("Table not specified"); 176 } 177 } 178 179 /** 180 * Perform an inner join. 181 * 182 * <p>Map columns from a secondary table onto the current result set. References to the column 183 * specified in {@code column} will be replaced with {@code table.column} in the SQL {@code 184 * SELECT} clause. 185 * 186 * @param column Column name to join on. Must be the same in both tables. 187 * @param table Secondary table to join. 188 * @return Fluent interface 189 */ 190 public SelectionBuilder mapToTable(String column, String table) { 191 mProjectionMap.put(column, table + "." + column); 192 return this; 193 } 194 195 /** 196 * Create a new column based on custom criteria (such as aggregate functions). 197 * 198 * <p>This adds a new column to the result set, based upon custom criteria in SQL format. This 199 * is equivalent to the SQL statement: {@code SELECT toClause AS fromColumn} 200 * 201 * <p>This method is useful for executing SQL sub-queries. 202 * 203 * @param fromColumn Name of column for mapping 204 * @param toClause SQL string representing data to be mapped 205 * @return Fluent interface 206 */ 207 public SelectionBuilder map(String fromColumn, String toClause) { 208 mProjectionMap.put(fromColumn, toClause + " AS " + fromColumn); 209 return this; 210 } 211 212 /** 213 * Return selection string based on current internal state. 214 * 215 * @return Current selection as a SQL statement 216 * @see #getSelectionArgs() 217 */ 218 public String getSelection() { 219 return mSelection.toString(); 220 221 } 222 223 /** 224 * Return selection arguments based on current internal state. 225 * 226 * @see #getSelection() 227 */ 228 public String[] getSelectionArgs() { 229 return mSelectionArgs.toArray(new String[mSelectionArgs.size()]); 230 } 231 232 /** 233 * Process user-supplied projection (column list). 234 * 235 * <p>In cases where a column is mapped to another data source (either another table, or an 236 * SQL sub-query), the column name will be replaced with a more specific, SQL-compatible 237 * representation. 238 * 239 * Assumes that incoming columns are non-null. 240 * 241 * <p>See also: map(), mapToTable() 242 * 243 * @param columns User supplied projection (column list). 244 */ 245 private void mapColumns(String[] columns) { 246 for (int i = 0; i < columns.length; i++) { 247 final String target = mProjectionMap.get(columns[i]); 248 if (target != null) { 249 columns[i] = target; 250 } 251 } 252 } 253 254 /** 255 * Return a description of this builder's state. Does NOT output SQL. 256 * 257 * @return Human-readable internal state 258 */ 259 @Override 260 public String toString() { 261 return "SelectionBuilder[table=" + mTable + ", selection=" + getSelection() 262 + ", selectionArgs=" + Arrays.toString(getSelectionArgs()) + "]"; 263 } 264 265 /** 266 * Execute query (SQL {@code SELECT}) against specified database. 267 * 268 * <p>Using a null projection (column list) is not supported. 269 * 270 * @param db Database to query. 271 * @param columns Database projection (column list) to return, must be non-NULL. 272 * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause (excluding the 273 * ORDER BY itself). Passing null will use the default sort order, which may be 274 * unordered. 275 * @return A {@link Cursor} object, which is positioned before the first entry. Note that 276 * {@link Cursor}s are not synchronized, see the documentation for more details. 277 */ 278 public Cursor query(SQLiteDatabase db, String[] columns, String orderBy) { 279 return query(db, columns, null, null, orderBy, null); 280 } 281 282 /** 283 * Execute query ({@code SELECT}) against database. 284 * 285 * <p>Using a null projection (column list) is not supported. 286 * 287 * @param db Database to query. 288 * @param columns Database projection (column list) to return, must be non-null. 289 * @param groupBy A filter declaring how to group rows, formatted as an SQL GROUP BY clause 290 * (excluding the GROUP BY itself). Passing null will cause the rows to not be 291 * grouped. 292 * @param having A filter declare which row groups to include in the cursor, if row grouping is 293 * being used, formatted as an SQL HAVING clause (excluding the HAVING itself). 294 * Passing null will cause all row groups to be included, and is required when 295 * row grouping is not being used. 296 * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause (excluding the 297 * ORDER BY itself). Passing null will use the default sort order, which may be 298 * unordered. 299 * @param limit Limits the number of rows returned by the query, formatted as LIMIT clause. 300 * Passing null denotes no LIMIT clause. 301 * @return A {@link Cursor} object, which is positioned before the first entry. Note that 302 * {@link Cursor}s are not synchronized, see the documentation for more details. 303 */ 304 public Cursor query(SQLiteDatabase db, String[] columns, String groupBy, 305 String having, String orderBy, String limit) { 306 assertTable(); 307 if (columns != null) mapColumns(columns); 308 Log.v(TAG, "query(columns=" + Arrays.toString(columns) + ") " + this); 309 return db.query(mTable, columns, getSelection(), getSelectionArgs(), groupBy, having, 310 orderBy, limit); 311 } 312 313 /** 314 * Execute an {@code UPDATE} against database. 315 * 316 * @param db Database to query. 317 * @param values A map from column names to new column values. null is a valid value that will 318 * be translated to NULL 319 * @return The number of rows affected. 320 */ 321 public int update(SQLiteDatabase db, ContentValues values) { 322 assertTable(); 323 Log.v(TAG, "update() " + this); 324 return db.update(mTable, values, getSelection(), getSelectionArgs()); 325 } 326 327 /** 328 * Execute {@code DELETE} against database. 329 * 330 * @param db Database to query. 331 * @return The number of rows affected. 332 */ 333 public int delete(SQLiteDatabase db) { 334 assertTable(); 335 Log.v(TAG, "delete() " + this); 336 return db.delete(mTable, getSelection(), getSelectionArgs()); 337 } 338 339 }