Adding a new field in SQLite in android

Question

As I said before, im very much new to android and now i faced a very troubling poblem. I have a database and i need to add a new field in the table. I don't know much of android and java.. So please guys, a little help will be appreciated... Here's my code for the database:

public class MySQLiteHelper extends SQLiteOpenHelper {

  public static final String TABLE_COMMENTS = "comments";
  public static final String COLUMN_ID = "_id";
  public static final String COLUMN_COMMENT = "comment";

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

  // Database creation sql statement
  private static final String DATABASE_CREATE = "create table "
      + TABLE_COMMENTS + "(" + COLUMN_ID
      + " integer primary key autoincrement, " + COLUMN_COMMENT
      + " text not null);";

  public MySQLiteHelper(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(MySQLiteHelper.class.getName(),
        "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);
    onCreate(db);
  }

}

This is for the functions for the database:

    public class CommentsDataSource {

  // Database fields
  private SQLiteDatabase database;
  private MySQLiteHelper dbHelper;
  private String[] allColumns = { MySQLiteHelper.COLUMN_ID,
      MySQLiteHelper.COLUMN_COMMENT };

  public CommentsDataSource(Context context) {
    dbHelper = new MySQLiteHelper(context);
  }

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

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

  public Comment createComment(String comment) {
    ContentValues values = new ContentValues();
    values.put(MySQLiteHelper.COLUMN_COMMENT, comment);
    long insertId = database.insert(MySQLiteHelper.TABLE_COMMENTS, null,
        values);
    Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
        allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
        null, null, null);
    cursor.moveToFirst();
    Comment newComment = cursorToComment(cursor);
    cursor.close();
    return newComment;
  }

  public void deleteComment(Comment comment) {
    long id = comment.getId();
    System.out.println("Comment deleted with id: " + id);
    database.delete(MySQLiteHelper.TABLE_COMMENTS, MySQLiteHelper.COLUMN_ID
        + " = " + id, null);
  }

  public List<Comment> getAllComments() {
    List<Comment> comments = new ArrayList<Comment>();

    Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
        allColumns, null, null, null, null, null);

    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
      Comment comment = cursorToComment(cursor);
      comments.add(comment);
      cursor.moveToNext();
    }
    // Make sure to close the cursor
    cursor.close();
    return comments;
  }

  private Comment cursorToComment(Cursor cursor) {
    Comment comment = new Comment();
    comment.setId(cursor.getLong(0));
    comment.setComment(cursor.getString(1));
    return comment;
  }
}

and this is the other class which the above class uses:

public class Comment {
  private long id;
  private String comment;

  public long getId() {
    return id;
  }

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

  public String getComment() {
    return comment;
  }

  public void setComment(String comment) {
    this.comment = comment;
  }

  // Will be used by the ArrayAdapter in the ListView
  @Override
  public String toString() {
    return comment;
  }
}

All this code works fine when I'm working with only one field(i.e. the comment) but I want to add three new fields named: "Address", "Age", "Gender" and if possible change the DB name and i just can't seem to be able to alter it in the right way to get what i want. Please tell me the right alterations needed. PLEASE Help me on this!

Thanks in advance, Waiting for your reply...

Do you really need to update an existing database? I assume your app is not used yet, so can't you just create a new database that suits your needs? Or is that actually what you are asking? — Vincent van der Weele, Jul 27 '13 at 12:51
If i upgrade the database with the new settings will it work then? — Shanzid SHaiham, Jul 27 '13 at 13:14

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1

if possible change the DB name and i just can't seem to be able to alter it in the right way to get what i want. Please tell me the right alterations needed.

There are two ways:

implement correctly onUpgrade() method of SQLiteOpenHelper class (if you need update)
Just update your database class (modify DDL statement e.q. add new columns and change db name if you want) and before install updated application to your device, delete application's data (in device it's in settings under applications manager). That will delete all data connected to your application (databases, preferences, etc.)

Look at

Android: upgrading DB version and adding new table

edited May 23 '17 at 11:49

Community

1
1

answered Jul 27 '13 at 12:53

Simon Dorociak

33,374
10
68
106

I started android programming just about a week ago so, i dont know its complete stucture yet... :( – Shanzid SHaiham Jul 27 '13 at 13:01
@ShanzidSHaiham i added link in my origin answer. – Simon Dorociak Jul 27 '13 at 13:03
Im really sorry for beign such a pain in the ass but can you please change my code and post it please.. – Shanzid SHaiham Jul 27 '13 at 13:05
and which one is the DDL statement? – Shanzid SHaiham Jul 27 '13 at 13:21
@ShanzidSHaiham create table statement. – Simon Dorociak Jul 27 '13 at 13:51

score 1 · Answer 2 · answered Jul 27 '13 at 13:02

1

Create new columns in table like this:

String Comment_table = String.format("CREATE TABLE %s " +
                "(%s INTEGER PRIMARY KEY AUTOINCREMENT ," +
                " %s TEXT , %s TEXT , %s TEXT ," +
                " %s TEXT  )",

                Comment_Table,
                Comment_ID ,
                Comment , Address , Age ,
                Gender );

(Here, Column names are Static String Declared as Class Variables).

answered Jul 27 '13 at 13:02

Armaan Stranger

3,140
1
14
24

where do i put this? O.o – Shanzid SHaiham Jul 27 '13 at 13:09
where in the helper class?! – Shanzid SHaiham Jul 27 '13 at 13:12
Ooo Boy!! First go and check out your existing code and learn what's happening in it. where database is created and where table create statement executes and then try to change in DB. – Armaan Stranger Jul 27 '13 at 13:15
you are a kid or 50 years old man that doesn't change anything. if you want to learn something then you need to learn by your own. I have showed you the way. now its on you where you want it. see your code and do as they are doing with your own class. so that you can make it, – Armaan Stranger Jul 29 '13 at 04:25
Thats good. As answer to your question if you want to create table when database is created then you can put this statement in onCreate() method of SQl Helper class. but you want to make it later than you can execute this statement in any method. – Armaan Stranger Jul 29 '13 at 07:15
My problem is solved. I didnt know that if the database verson already existed in the device it would not execute the onCreate method again. – Shanzid SHaiham Jul 29 '13 at 07:52
I didn't know that you needed to upgrade the database version to save new changes – Shanzid SHaiham Jul 29 '13 at 07:52
ofcourse... or you can create new database every time to make changes. – Armaan Stranger Jul 29 '13 at 08:22
ya..i didnt know that before...now i know – Shanzid SHaiham Jul 29 '13 at 08:40

Adding a new field in SQLite in android

2 Answers2