how to add data on sqlite database without loosing old data?

Question

Now i published my quotes app and it has a section for favorites quotes. Users have ability to add any quote to favorites but if i add some quotes in database and updated the app on Google Play, when users updated it they lose their favorites as if the old database is deleted

How they can update it to get new quotes without loosing their favorites ??

public DAO(Context context) {
        dbHandler = new DataBaseHandler(context);
        try {

            dbHandler.createDataBase();

        } catch (IOException ioe) {

            throw new Error("Unable to create database");

        }
        try {

            dbHandler.openDataBase();

        } catch (SQLException sqle) {

            throw sqle;

        }
        Log.e("path2", context.getDatabasePath("SuccessQuotes").toString());
        // open();
    }

    // ==============================================================================

    // Getting All Quotes
    public Cursor getQuotes(String start) {
        // Select All Query
        String query = "SELECT * FROM " + TABLE_QUOTES + " JOIN " + TABLE_AUTHORS + " ON " + QU_AUTHOR + " = " + AU_ID
                + " LIMIT " + start + ",50";
        Cursor cursor = database.rawQuery(query, null);

        cursor.moveToFirst();
        return cursor;

    }

    // ==============================================================================

    // Getting All Quotes
    public Cursor getFavoriteQuotes() {
        // Select All Query

        String query = "SELECT * FROM " + TABLE_QUOTES + " JOIN " + TABLE_AUTHORS + " ON " + QU_AUTHOR + " = " + AU_ID
                + " WHERE " + QU_FAVORITE + " = " + "1";
        Cursor cursor = database.rawQuery(query, null);

        cursor.moveToFirst();
        return cursor;

    }

    // ==============================================================================

    // Getting All Quotes
    public Cursor getAuthorQuotes(String authorID) {
        // Select All Query

        String query = "SELECT * FROM " + TABLE_QUOTES + " JOIN " + TABLE_AUTHORS + " ON " + QU_AUTHOR + " = " + AU_ID
                + " WHERE " + QU_AUTHOR + " = '" + authorID + "'";
        Cursor cursor = database.rawQuery(query, null);

        cursor.moveToFirst();
        return cursor;

    }

    // ==============================================================================

    // Getting All Quotes
    public Cursor getOneQuote(String quoteID) {
        // Select All Query

        String query = "SELECT * FROM " + TABLE_QUOTES + " JOIN " + TABLE_AUTHORS + " ON " + QU_AUTHOR + " = " + AU_ID
                + " WHERE " + QU_ID + " = '" + quoteID + "'";
        Cursor cursor = database.rawQuery(query, null);

        cursor.moveToFirst();
        return cursor;

    }

    // ==============================================================================

    public void addOrRemoveFavorites(String id, String value) {

        ContentValues values = new ContentValues();
        values.put(QU_FAVORITE, value);

        // Update Row
        database.update(TABLE_QUOTES, values, QU_ID + "=?", new String[] { id });
    }

    // ==============================================================================

    // Getting All Quotes
    public Cursor getAllAuthors() {
        // Select All Query

        String query = "SELECT *, COUNT(" + QU_AUTHOR + ") AS count FROM " + TABLE_AUTHORS + " JOIN " + TABLE_QUOTES
                + " ON " + AU_ID + " = " + QU_AUTHOR + " GROUP BY  " + AU_NAME + " ORDER BY  " + AU_NAME + " ASC";
        Cursor cursor = database.rawQuery(query, null);

        cursor.moveToFirst();
        return cursor;

    }

    // ==============================================================================

    public void updateSetting(String field, String value) {
        open();
        ContentValues values = new ContentValues();
        values.put(field, value);

        // Update Row
        database.update(TABLE_SETTINGS, values, null, null);
    }

    // ==============================================================================

    public Cursor getSettings() {
        open();
        String query = "SELECT * FROM " + TABLE_SETTINGS;
        Cursor cursor = database.rawQuery(query, null);

        cursor.moveToFirst();
        return cursor;
    }

    // ==============================================================================

    public void open() throws SQLException {
        database = dbHandler.getReadableDatabase();

    }

    // ==============================================================================

    public void closeDatabase() {
        dbHandler.close();
    }

if you dot change the DATABASE_VERSION nothing to worry, else you changed the DATABASE_VERSION there is a chance to loss data, — Sree, Apr 07 '14 at 05:12
possible duplicate of [Android SQLite Database, WHY drop table on upgrade](http://stackoverflow.com/questions/19793004/android-sqlite-database-why-drop-table-on-upgrade) — Drew, Apr 07 '14 at 06:25

score 1 · Answer 1 · answered Apr 07 '14 at 05:08

When user update your app from play Store you should have to follow following steps:-

1- take backup of your favourite from db to temp arraylist.
2- delete old database.
3- create new database and and insert record in new database.

Note :- if process is big then use IntentService and static broadcasting.I think it will solved your issue.

how to add data on sqlite database without loosing old data?

1 Answers1