Android database: A few simple questions from a beginner

Question

So basically I have been pulling my hair out for the past few days trying to get this going. I have previous Android dev experience, but not with persistence. But enough about me, more on the application.

Its very simple, it consists of 4 classes:

Activity 1: Data input Activity 2: Data output (display what value was just entered in Activity 1)

DatabaseHelper, and DatabaseManager.

The database table itself is super simple:

---

| _id | username |

I THINK, that the values are going into the database. However, when I try to confirm this by outputting on the next activity, I get an error.

I would be super grateful if someone could clarify some trivial stuff for me:

• How does SQLite handle auto-incrementation of primary keys?Can I exclusively add entries to the username field, or should I have to find the last value of the primary key _id field, and increment it manually?

  • How do I fetch values from the cursor? Should I include the _id field and 'username' I am looking for, or can I simple just ask for the 'username' field when the cursor has been moved to the first position.

Here is a look at my code to supplement my questions

public class DatabaseHelper extends SQLiteOpenHelper {

private static final String     DATABASE_NAME       = "NutrtionIntuition";
private static final int        DATABASE_VERSION    = 2;

private static final String USER_TABLE              = "UserTable";
public static final String  KEY_ROWID               = "_id";
public static final String  KEY_USERNAME            = "username";

// Database creation sql statement
public static final String      DATABASE_CREATE     = "CREATE TABLE " + USER_TABLE + "("
            + KEY_ROWID + " INTEGER PRIMARY KEY," + KEY_USERNAME + " TEXT" + ")";


public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Method is called during creation of the database. Method is also called on upgrade of database?
@Override
public void onCreate(SQLiteDatabase database) {
            database.execSQL(DATABASE_CREATE);
}

// Method is called during an upgrade of the database,
@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {

                database.execSQL("DROP TABLE IF EXISTS UserTable");
                onCreate(database);
}

}

public class DatabaseManager{  

    private DatabaseHelper      dbHelper;  
    private SQLiteDatabase      database;  

    private static final String USER_TABLE          = "UserTable";

    public static final String  KEY_ROWID           = "_id";
    public static final String  KEY_USERNAME        = "username";


    public DatabaseManager(Context context){  
      dbHelper = new DatabaseHelper(context);  
      database = dbHelper.getWritableDatabase();  
    }

    //Insert Records into the User Table
    public long insertRecords(int id, String name){  
        ContentValues values = new ContentValues();  
        //values.put(KEY_ROWID , id); 
        Log.v("DEBUG","Inserting value....");
        values.put(KEY_USERNAME, name);  
        return database.insert(USER_TABLE, null, values);  
    }    

    public Cursor selectRecords() {
         String[] cols = new String[] {KEY_ROWID, KEY_USERNAME};  
         Cursor mCursor = database.query(true, USER_TABLE, cols, null  
              , null, null, null, null, null);  
         if (mCursor != null) {  
          mCursor.moveToFirst();  
       }  
       Log.v("DEBUG","Returning cursor");
       return mCursor; // iterate to get each value.
    }
}

Results Output Activity

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_result);

    db = new DatabaseManager(this);
    Cursor res  = db.selectRecords();
    Log.v("DEBUG","Cursor Returned");
    String name = res.getString(res.getColumnIndex("username"));



    result.findViewById(R.id.textView1);
    result.setText(name);

}

Answer 1

Auto increment works without problems.

The source of your error is the following: To actually insert the stuff into the table, you have to commit the changes by ending the connection:

database.setTransactionSuccessful();
database.endTransaction();

To fetch all the returned stuff, you have to use

res.moveToFirst();
do {
    //fetch _id
    int id = res.getInt(0);
    //fetch username
    String name = res.getString(0);
    // do the stuff you want to do with the results here
} while (!res.isLast());
res.close(); // IMPORTANT, crash if not used.