13

I am looking at the Android notepad application sample code in <path_to_SDK>/samples/android-16/NotePad/src/com/example/android/notepad.

I was wondering if anyone could explain to me why the following code is needed in NotepadProvider.java?

// Creates a new projection map instance. The map returns a column name
// given a string. The two are usually equal.
sNotesProjectionMap = new HashMap<String, String>();

// Maps the string "_ID" to the column name "_ID"
sNotesProjectionMap.put(NotePad.Notes._ID, NotePad.Notes._ID);

// Maps "title" to "title"
sNotesProjectionMap.put(NotePad.Notes.COLUMN_NAME_TITLE,NotePad.Notes.COLUMN_NAME_TITLE);

// Maps "note" to "note"
sNotesProjectionMap.put(NotePad.Notes.COLUMN_NAME_NOTE, NotePad.Notes.COLUMN_NAME_NOTE);

// Maps "created" to "created"
sNotesProjectionMap.put(NotePad.Notes.COLUMN_NAME_CREATE_DATE, NotePad.Notes.COLUMN_NAME_CREATE_DATE);

// Maps "modified" to "modified"
sNotesProjectionMap.put(
        NotePad.Notes.COLUMN_NAME_MODIFICATION_DATE,
        NotePad.Notes.COLUMN_NAME_MODIFICATION_DATE)

I notice the projection map is used later in the query() method:

...
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
qb.setTables(NotePad.Notes.TABLE_NAME);

/**
 * Choose the projection and adjust the "where" clause based on URI pattern-matching.
 */
switch (sUriMatcher.match(uri)) {
    // If the incoming URI is for notes, chooses the Notes projection
    case NOTES:
    qb.setProjectionMap(sNotesProjectionMap);
    break;
...

Why is this projection map needed?

Umbungu
  • 945
  • 3
  • 10
  • 30
  • 1
    The documentation for `SQLiteQueryBuilder.setProjectionMap()` has a good explanation. – user Oct 05 '12 at 18:46
  • Thanks. Maybe my question wasn't clear enough - I understand what a projection map is, but I was wondering what the point of having a projection map that only contains mappings that have identical key and values (I would understand if at least one of the mappings had a different key, value pair). – Umbungu Oct 08 '12 at 13:28
  • 1
    That is a sample application, one that should be an example of `API` use and good practices using those `APIs`, that is way they probably use a projection map. For example, if I remember right, the `Shelves` application made by one of the google engineers is using a projection map in its `ContentProvider` and that projection map isn't just a simple mapping with identical key-value pairs. – user Oct 09 '12 at 07:55
  • Thanks - if you make your comment into an answer then I'll accept it. – Umbungu Oct 09 '12 at 09:47

4 Answers4

10

The Notepad application from the SDK demos is a sample application, one that should be an example of API use and good practices using those APIs, that is why they probably use a projection map. Although the Notepad sample doesn't really need a projection map the use of one is a good showcase for more complex cases when one is needed. For example, if I remember right, the Shelves application written by one of the Google engineers is using a projection map in its ContentProvider and that projection map isn't just a simple mapping with identical key-value pairs.

I've also added a link to the documentation of the method SQLiteQueryBuilder.setProjectionMap which has some details on why you would need a projection map.

nickgrim
  • 5,387
  • 1
  • 22
  • 28
user
  • 86,916
  • 18
  • 197
  • 190
  • Would you have an explanation for the why using a projection map? I am not sure to understand the point of using another name between the query and the column names? – Paul Nov 04 '13 at 23:02
  • 3
    @Paul That projection map is required when you used a `SQLiteQueryBuilder` for more advanced scenarios than a very simple query. For example, maybe you want to make an alias between the current column names of a sqlite table and some arbitrary names you want in your app(check the `AS` sqlite keyword http://www.sqlite.org/syntaxdiagrams.html#result-column). Also in join queries you may need different names if your tables have columns with the same name(most commonly, two tables that each have an `_id` column). – user Nov 05 '13 at 12:31
9

Its main purpose is to rename column names found in a cursor produced by a query.

@static declaration

SEARCH_PROJECTION_MAP = new HashMap<String, String>();
SEARCH_PROJECTION_MAP.put( OpenHelper.NAME, OpenHelper.NAME + " as _name" );
SEARCH_PROJECTION_MAP.put( OpenHelper.ID , OpenHelper.ID + " as _id" );

@your query functionality

//if you query using sqliteQueryBuilder then
    sqLiteQueryBuilder.setProjectionMap( SEARCH_PROJECTION_MAP );

//example if you just query
    Cursor cursor = sqLiteQueryBuilder.query( db, projection, selection, selectionArgs, null, null, sortOrder );

the returned columns now are _name and _id in this example.

Juan Mendez
  • 2,658
  • 1
  • 27
  • 23
  • @azizbekian, thanks for the compliment. I found out while working and looked for anyone else who didn't understand it like it happened to me. – Juan Mendez Dec 28 '15 at 21:48
  • This is the only example of actually using the projection map I've ever seen. You're an absolute star. I had such a hard time getting this to work because my assumption was that it would work as " AS " in the query not just replace your column name with "". – seadowg Jun 29 '21 at 13:50
1

Maybe someone needs more thorough explanation. I've gathered important facts about projection map:

  1. If you don’t need a projection map just don’t set it. This way projection names are processed “as is”, exactly as passed in via the projection array.

  2. Even the projection array is optional. If you pass null to the query, it generates a "SELECT *" operation. (actually that’s not recommended in SQL, because of the breakage that can occur if columns are added/removed, or re-ordered).

  3. Supplied projection map applies only to the selection list! So, you could map "store_name" => "bookstore.storename", and it would result in “select bookstore.storename ...”, but if you supplied "store_name" in the "order by" query builder parameter or one of the other qualified parameters, you can potentially end up with bad SQL.

  4. The projection map can be overruled. If the key part of a projection entry contains an "as" clause, the value part is ignored and the key part is inserted into the resulting SQL. For example "parrot as polly" => "cracker" will generate "SELECT parrot as polly ..." and no "cracker". The "as" can be either all upper-case or all lower-case (not mixed case) and must have at least one space before and after the word “as”.

Read more info in this article: http://www.mousetech.com/blog/android-projection-maps-explained/

zkvarz
  • 611
  • 1
  • 8
  • 18
0

In addition to as Juan said, the purpose of projection map is for renaming column names as well as disambiguating column names when doing joins, the important purpose of the projection maps is for verifying the selection against malicious arguments.

When using SQLiteQueryBuilder to create a statement using buildQueryString(boolean, String, String[], String, String, String, String, String), if a projection map is specified, fields not in that map will be ignored.

To get maximum protection against malicious third party apps (for example content provider consumers) you can do the following:

  1. Set SQLiteQueryBuilder#setStrict(true)
  2. Use a projection map.
  3. Use one of the query overloads instead of getting the statement as a sql string
Yogesh Umesh Vaity
  • 41,009
  • 21
  • 145
  • 105