Tutorial :Android SQLite Insert working, Query not working



Question:

this is my first time posting to this site so hopefully it will be a positive experience. I have an Android SQLite/ContentProvider problem that I have been beating my head against the wall for over the past 3 hours. Below is the ContentProvider code:

public class IncidentProvider extends ContentProvider {    private static final UriMatcher sUriMatcher;  private static final HashMap<String, String> projectionMap;  private static final int INCIDENTS = 1;    public static final String AUTHORITY = "com.test.providers.IncidentsProvider";  public static final String TABLE_NAME = "incidents";    private static class DatabaseHelper extends SQLiteOpenHelper {        public DatabaseHelper(Context context) {          super(context, context.getString(R.string.database_name), null, Integer.parseInt(context.getString(R.string.database_version)));      }        @Override      public void onCreate(SQLiteDatabase db) {          db.execSQL("CREATE TABLE " + TABLE_NAME + " ("                   + Incidents.INCIDENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"                   + Incidents.NAME + " VARCHAR(30),"                  + Incidents.CREATE_TIME + " LONG,"                   + Incidents.LAST_UPDATE + " LONG,"                   + Incidents.IS_ACTIVE + " VARCHAR(30)" + ");");      }        @Override      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {          db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME );          onCreate(db);      }    }    private DatabaseHelper dbHelper;    /**   * Delete a row from the database   */  @Override  public int delete(Uri uri, String where, String[] whereArgs) {      SQLiteDatabase db = dbHelper.getWritableDatabase();      int count;      switch(sUriMatcher.match(uri)) {          case INCIDENTS:              count = db.delete(TABLE_NAME, where, whereArgs);              break;          default:              throw new IllegalArgumentException("Unknown URI " + uri);      }      getContext().getContentResolver().notifyChange(uri, null);      db.close();      return count;  }    /**   * Return the content type managed by this ContentProvider   */  @Override  public String getType(Uri uri) {      switch(sUriMatcher.match(uri)) {          case INCIDENTS:              return Incidents.CONTENT_TYPE;          default:              throw new IllegalArgumentException("UNKNOWN URI " + uri);      }  }    /**   * Insert new content into a row in the database   */  @Override  public Uri insert(Uri uri, ContentValues initialValues) {      if(sUriMatcher.match(uri) != INCIDENTS)           throw new IllegalArgumentException("UNKNOWN URI " + uri);        ContentValues values;      if(initialValues != null) {          values = new ContentValues(initialValues);      } else {          values = new ContentValues();      }        SQLiteDatabase db = dbHelper.getWritableDatabase();      long rowId = db.insert(TABLE_NAME, Incidents.NAME, values);        if(rowId > 0) {          Uri incidentUri = ContentUris.withAppendedId(Incidents.CONTENT_URI, rowId);          getContext().getContentResolver().notifyChange(incidentUri, null);          db.close();          return incidentUri;      }      db.close();      throw new SQLException("Failed to insert row into " + uri);  }    /**   * Called when creating this ContentProvider   */  @Override  public boolean onCreate() {      dbHelper = new DatabaseHelper(getContext());      return false;  }    /**   * Called when making a query to this content provider   */  @Override  public Cursor query(Uri uri, String[] projection, String selection,          String[] selectionArgs, String sortOrder) {        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();        switch(sUriMatcher.match(uri)) {          case INCIDENTS:              qb.setTables(TABLE_NAME);              qb.setProjectionMap(projectionMap);              break;          default:              throw new IllegalArgumentException("UNKNOWN URI " + uri);      }        SQLiteDatabase db = dbHelper.getWritableDatabase();        Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, sortOrder);      c.setNotificationUri(getContext().getContentResolver(), uri);      db.close();      return c;  }    /**   * Called when updating a row through this content provider   */  @Override  public int update(Uri uri, ContentValues values, String where,          String[] whereArgs) {      SQLiteDatabase db = dbHelper.getWritableDatabase();      int count;        switch(sUriMatcher.match(uri)) {          case INCIDENTS:              count = db.update(TABLE_NAME, values, where, whereArgs);              break;          default:              throw new IllegalArgumentException("UNKNOWN URI " + uri);      }        getContext().getContentResolver().notifyChange(uri, null);      db.close();      return count;  }    static {      sUriMatcher = new UriMatcher(UriMatcher.NO_MATCH);      sUriMatcher.addURI(AUTHORITY, TABLE_NAME, INCIDENTS);        projectionMap = new HashMap<String, String>();      projectionMap.put(Incidents.INCIDENT_ID, Incidents.INCIDENT_ID);      projectionMap.put(Incidents.NAME, Incidents.NAME);      projectionMap.put(Incidents.CREATE_TIME, Incidents.CREATE_TIME);      projectionMap.put(Incidents.LAST_UPDATE, Incidents.LAST_UPDATE);      projectionMap.put(Incidents.IS_ACTIVE, Incidents.IS_ACTIVE);  }    

}

I also wrote a db helper class to make it easy to interact with the ContentProvider. 2 example DBHelper methods are as follows (1 to insert, 1 to query).

    /**       *  Adds a new incident to the database       **/   public void addNewIncident(ContentResolver contentResolver, Incident incident) {    ContentValues contentValues = new ContentValues();    contentValues.put(Incidents.NAME, incident.getName());    contentValues.put(Incidents.CREATE_TIME, incident.getCreateTime());    contentValues.put(Incidents.LAST_UPDATE, incident.getLastUpdate());    contentValues.put(Incidents.IS_ACTIVE, incident.isActive()?"true":"false");      contentResolver.insert(Incidents.CONTENT_URI, contentValues);     }         /**       * Retrieves all incidents from the database       **/      public ArrayList<Incident> getIncidents(ContentResolver contentResolver) {    Cursor c = contentResolver.query(Incidents.CONTENT_URI, null, null, null, null);    ArrayList<Incident> returnList = new ArrayList<Incident>();      if(c!=null && c.moveToNext()) {     for(int i=0; i<c.getCount(); i++) {      c.moveToPosition(i);      Incident incident = new Incident(c.getString(c.getColumnIndex(Incidents.NAME)));      incident.setCreateTime(c.getLong(c.getColumnIndex(Incidents.CREATE_TIME)));      incident.setLastUpdate(c.getLong(c.getColumnIndex(Incidents.LAST_UPDATE)));      incident.setActive(c.getString(c.getColumnIndex(Incidents.IS_ACTIVE)).equalsIgnoreCase("true"));      returnList.add(incident);     }     c.close();    }      return returnList;   }  

Okay! So here is my problem. I can insert into the database without any problems! If I query the database through adb shell I can see all of the records that are being inserted. When I query using SQLiteQueryBuilder, or SQLiteDatabase.rawQuery, or any other method of querying, the returned cursor comes back with -1. Does not matter if I query for an individual record, or query the entire database. The query still comes back -1.

Any ideas? Am I missing something incredibly simple?

Thanks in advance to anyone willing to help a frustrated individual!

UPDATE:

Below is example code for inserting (that works), and querying (that doesn't work)

/**    * Following db insert works   */  IncidentsDB db = IncidentsDB.getInstance();   workingIncident.setLastUpdate(System.currentTimeMillis());     // isActive, and createTime set in constructor  db.addNewIncident(this.getContentResolver(), workingIncident);    /**   * Following db queries do not work, cursor ends up with mCount=-1    */   Cursor c = IncidentsDB.getInstance().getIncidents(this.getContentResolver());  


Solution:1

Well, looks like I fixed it. Turns out it is better to close the database in the finalize method as opposed to opening and closing in the each of the delete, insert, query, and update methods like I have in the posted code above. Here is the code snippet for those of you that run into this problem like I did:

// add SQLiteDatabase member variable to content provider  private SQLiteDatabase db;    /**   * Delete a row from the database   */  @Override  public int delete(Uri uri, String where, String[] whereArgs) {      // use Content Provider's db member variable in the following code block      int count;      switch(sUriMatcher.match(uri)) {          case INCIDENTS:              count = db.delete(TABLE_NAME, where, whereArgs);              break;          default:              throw new IllegalArgumentException("Unknown URI " + uri);      }      getContext().getContentResolver().notifyChange(uri, null);      return count;  }  

Then add the following finalize code to your content provider

    @Override      protected void finalize() throws Throwable {          super.finalize();          db.close();      }  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »