Understanding SQLite

Question

I'm trying to understand how to deal with SQLite. I have a Members database with the following class:

package com.appiclife.saldo.data;

public class Member {

    private long id;
    private String name;
    private String phone;
    private String email;
    private int weight;

     public long getId() {
            return id;
          }

          public void setId(long id) {
            this.id = id;
          }

      public String getName() {
        return name;
      }

      public void setName(String name) {
        this.name = name;
      }

      public String getPhone() {
            return phone;
          }

      public void setPhone(String phone) {
            this.phone = phone;
          }

      public String getEmail() {
                    return email;
                  }

      public void setEmail(String email) {
                    this.email = email;
                  }   
      public int getWeight() {
                    return weight;
                          }

      public void setWeight(int weight) {
                    this.weight = weight;
                          }

Then I have the following HelperClass to create and update the database:

public class MemberSQLiteHelper extends SQLiteOpenHelper {

  public static final String TABLE_MEMBERS = "members";
  public static final String COLUMN_ID = "_id";
  public static final String COLUMN_NAME = "name";
  public static final String COLUMN_PHONE = "phone";
  public static final String COLUMN_EMAIL = "email";
  public static final String COLUMN_WEIGHT = "weight";

  private static final String DATABASE_NAME = "members.db";
  private static final int DATABASE_VERSION = 1;

  // Database creation sql statement
  private static final String DATABASE_CREATE = 
          "create table "
        + TABLE_MEMBERS + "(" + 
          COLUMN_ID + " integer primary key autoincrement, " + 
          COLUMN_NAME + " text not null, "+
          COLUMN_PHONE + " text, "+
          COLUMN_EMAIL + " text, "+
          COLUMN_WEIGHT + " integer);";

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

  @Override
  public void onCreate(SQLiteDatabase database) {
    database.execSQL(DATABASE_CREATE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(MemberSQLiteHelper.class.getName(),
        "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_MEMBERS);
    onCreate(db);
  }

} 

I have the following methods:

public class MembersDataSource {

  // Database fields
  private SQLiteDatabase database;
  private MemberSQLiteHelper dbHelper;
  private String[] allColumns = { MemberSQLiteHelper.COLUMN_ID,
      MemberSQLiteHelper.COLUMN_NAME, MemberSQLiteHelper.COLUMN_PHONE, MemberSQLiteHelper.COLUMN_EMAIL, MemberSQLiteHelper.COLUMN_WEIGHT};

  public MembersDataSource(Context context) {
    dbHelper = new MemberSQLiteHelper(context);
  }

  public void open() throws SQLException {
    database = dbHelper.getWritableDatabase();
  }

  public void close() {
    dbHelper.close();
  }

  public Member createMember(String name, String phone, String email, int weight) {
    ContentValues values = new ContentValues();
    values.put(MemberSQLiteHelper.COLUMN_NAME, name);
    values.put(MemberSQLiteHelper.COLUMN_PHONE, phone);
    values.put(MemberSQLiteHelper.COLUMN_EMAIL, email);
    values.put(MemberSQLiteHelper.COLUMN_WEIGHT, weight);
    long insertId = database.insert(MemberSQLiteHelper.TABLE_MEMBERS, null, values);
    Cursor cursor = database.query(MemberSQLiteHelper.TABLE_MEMBERS, allColumns, MemberSQLiteHelper.COLUMN_ID + " = " + insertId, null,
        null, null, null);
    cursor.moveToFirst();
    Member newMember = cursorToMember(cursor);
    cursor.close();
    return newMember;
  }

  public void deleteMember(Member member) {
    long id = member.getId();
    database.delete(MemberSQLiteHelper.TABLE_MEMBERS, MemberSQLiteHelper.COLUMN_ID
        + " = " + id, null);
  }

  public List<Member> getAllMembers() {
    List<Member> members = new ArrayList<Member>();

    Cursor cursor = database.query(MemberSQLiteHelper.TABLE_MEMBERS,
        allColumns, null, null, null, null, null);

    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
      Member member = cursorToMember(cursor);
      members.add(member);
      cursor.moveToNext();
    }
    // Make sure to close the cursor
    cursor.close();
    return members;
  }

  private Member cursorToMember(Cursor cursor) {
    Member member = new Member();
    member.setId(cursor.getLong(0));
    member.setName(cursor.getString(1));
    member.setPhone(cursor.getString(2));
    member.setEmail(cursor.getString(3));
    member.setWeight(cursor.getInt(4));
    return member;
  }
} 

And the questions,

How can I change for example the name of a member that is already in the DB? (1)

And is it possible to save an Arraylist, for example if they have more phonenumbers? (2)

And, if I delete a row in the database, do the ID's change of the following rows? Or can I always refer to the same ID when I want to retrieve the same member? So by other means, once created does the member keep the same ID? (3)

Answer 1

You just need to add a method to MembersDataSource to update a member:

public void updateMember(Member member) {
    long id = member.getId();
    ContentValues values = new ContentValues();
    values.put(MemberSQLiteHelper.COLUMN_NAME, name);
    values.put(MemberSQLiteHelper.COLUMN_PHONE, phone);
    values.put(MemberSQLiteHelper.COLUMN_EMAIL, email);
    values.put(MemberSQLiteHelper.COLUMN_WEIGHT, weight);
    database.update(MemberSQLiteHelper.TABLE_MEMBERS, values,
        MemberSQLiteHelper.COLUMN_ID + " = ?",
        new String[] { Integer.toString(id) });
}

As to keeping multiple phone numbers, you are probably better off creating a second table. It could have columns _id, phone, and phone_type. You could then either eliminate phone as a column from your main table, or else use that for the "primary" phone number, and have the phones table for additional numbers.

Regarding your new question 3: when you delete a row, no other rows are affected (barring triggers). In particular, no other ID values will change.