Writes and updates to databases are done with the assistance of a class called ContentValues. ContentValues is a key-value store class, like Java’s HashMap or the Bundles you have been using so far. However, unlike HashMap or Bundle, it is specifically designed to store the kinds of data SQLite can hold.

You will be creating ContentValues instances from Crimes a few times in CrimeLab. Add a private method to take care of shuttling a Crime into a ContentValues. (Remember to use the same two-step trick from above to add an import of CrimeTable: When you get to CrimeTable.Cols.UUID, type Option+Return (Alt+Enter) and choose Add import for 'com.bignerdranch.android.criminalintent.database.CrimeDbSchema.CrimeTable'.)

    public Crime getCrime(UUID id) {
        return null;
    }

    private static ContentValues getContentValues(Crime crime) {
        ContentValues values = new ContentValues();
        values.put(CrimeTable.Cols.UUID, crime.getId().toString());
        values.put(CrimeTable.Cols.TITLE, crime.getTitle());
        values.put(CrimeTable.Cols.DATE, crime.getDate().getTime());
        values.put(CrimeTable.Cols.SOLVED, crime.isSolved() ? 1 : 0);

        return values;
    }
}

For the keys, you use your column names. These are not arbitrary names; they specify the columns that you want to insert or update. If they are misspelled or typo’d compared to what is in the database, your insert or update will fail. Every column is specified here except for _id, which is automatically created for you as a unique row ID.

Now that you have a ContentValues, it is time to add rows to the database. Fill out addCrime(Crime) with a new implementation.

public void addCrime(Crime c) {
    ContentValues values = getContentValues(c);

    mDatabase.insert(CrimeTable.NAME, null, values);
}

The insert(String, String, ContentValues) method has two important arguments and one that is rarely used. The first argument is the table you want to insert into – here, CrimeTable.NAME. The last argument is the data you want to put in.

And the second argument? The second argument is called nullColumnHack. And what does it do?

Well, say that you decided to call insert(…) with an empty ContentValues. SQLite does not allow this, so your insert(…) call would fail.

If you passed in a value of uuid for nullColumnHack, though, it would ignore that empty ContentValues. Instead, it would pass in a ContentValues with uuid set to null. This would allow your insert(…) to succeed and create a new row.

Handy? Perhaps someday. Not today, though. Now you know about it, at least.

Continue applying ContentValues by writing a method to update rows in the database.

public Crime getCrime(UUID id) {
    return null;
}

public void updateCrime(Crime crime) {
    String uuidString = crime.getId().toString();
    ContentValues values = getContentValues(crime);

    mDatabase.update(CrimeTable.NAME, values,
            CrimeTable.Cols.UUID + " = ?",
            new String[] { uuidString });
}

private static ContentValues getContentValues(Crime crime) {

The update(String, ContentValues, String, String[]) method starts off similarly to insert(…) – you pass in the table name you want to update and the ContentValues you want to assign to each row you update. However, the last bit is different, because now you have to specify which rows get updated. You do that by building a where clause (the third argument) and then specifying values for the arguments in the where clause (the final String[] array).

You may be wondering why you are not putting uuidString directly into the where clause. That would be a bit simpler than using ? and passing it in as a String[], after all.

The answer is that in some cases your String might itself contain SQL code. If you put that String directly in your query, that code could change the meaning of your query, or even alter your database. This is called a SQL injection attack, and it is a bad thing indeed.

If you use ?, though, your code will do what you intended: treat it as a String value, not code. So it is best to be safe and use ? as a matter of habit, because it will always do what you intend no matter what the String contains.

Crime instances get modified in CrimeFragment and will need to be written out when CrimeFragment is done. Add an override to CrimeFragment.onPause() that updates CrimeLab’s copy of your Crime.

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    UUID crimeId = (UUID) getArguments().getSerializable(ARG_CRIME_ID);
    mCrime = CrimeLab.get(getActivity()).getCrime(crimeId);
}

@Override
public void onPause() {
    super.onPause();

    CrimeLab.get(getActivity())
            .updateCrime(mCrime);
}

Sadly, you have no way of verifying that this code works. That will need to wait until you can read in the crimes you updated. To make sure that everything compiles correctly, run CriminalIntent one more time before moving on to the next section. You should see a blank list.