Insert or update in SQlite and Android using the database.query();

Question

is there a way to change my function:

public categorie createCategoria(String categoria) {
            ContentValues values = new ContentValues();
            values.put(MySQLiteHelper.COLUMN_NOME, categoria);
            values.put(MySQLiteHelper.COLUMN_PREF, 0);

            long insertId = database.insert(MySQLiteHelper.TABLE_CATEGORIE, null,
                values);

            Cursor cursor = database.query(MySQLiteHelper.TABLE_CATEGORIE,
                allCategorieColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
                null, null, null);
            cursor.moveToFirst();
            categorie newCategoria = cursorToCategorie(cursor);
            cursor.close();
            return newCategoria;
          } 

this is a raw insert, i would like to change this function to make it update or insert accordingly. i would like to change this becouse i'm already using this function in some places, but now i need to choose if insert a row or update (or ignoring the insert) a row with the same COLUMN_NOME. can someone help me doing this?

i mean i would like to insert a new row ONLY if there isn't another with the same name (as usual you know).

Solution 1

you could call int nRowsEffected = database.update(...); if there are no rows effected by the update either the row doesn't exist (or you hosed your update()!) therefore you need to call database.insert(...). of course if nRowsEffected > 0 then you are done.

Solution 2

You can use insertWithOnConflict() if you want to insert or update, depending in whether the record exists or not:

SQLiteDatabase db = this.getWritableDatabase();

ContentValues contentValues = new ContentValues();
contentValues.put(COLUMN_ID, id);
contentValues.put(COLUMN_VALUE, value);

// this will insert if record is new, update otherwise
db.insertWithOnConflict(TABLE, null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);

Solution 3

You can use execSQL and use INSERT OR REPLACE

String[] args = {"1", "newOrOldCategory"}; // where 1 is the category id
getWritableDatabase().execSQL("INSERT OR REPLACE INTO table_name (idColoumn, categoryColumn) VALUES (?, ?)", args);

Solution 4

First of all you have write function which is check whether id is exists in particular Table like:

   /**
     * @param table_name
     * @param server_id
     * @return
     */
    public boolean isServerIdExist(String table_name, int server_id) {
        long line = DatabaseUtils.longForQuery(mDB, "SELECT COUNT(*) FROM " + table_name + " WHERE id=?",
                new String[]{Integer.toString(server_id)});
        return line > 0;
    }

You have to pass table_name and id in that like

   /**
     * INSERT in TABLE_ACCOUNT_DEVICE
     **/
    public long insertOrUpdateAccountDevice(int server_id, int account_id,
                                            String device_name, String device_id,
                                            String last_active, String itp,
                                            String utp, int status) {

        ContentValues values = new ContentValues();

        values.put(ACCOUNT_DEVICE_ACCOUNT_ID, account_id);
        values.put(ACCOUNT_DEVICE_DEVICE_NAME, device_name);
        values.put(ACCOUNT_DEVICE_DEVICE_ID, device_id);
        values.put(ACCOUNT_DEVICE_LAST_ACTIVE, last_active);
        values.put(ACCOUNT_DEVICE_ITP, itp);
        values.put(ACCOUNT_DEVICE_UTP, utp);
        values.put(ACCOUNT_DEVICE_STATUS, status); // 0=pending, 1=active, 2=Inactive, -1=not_found

        /**
         * isServerIdExists
         */
        if (isServerIdExists(TABLE_ACCOUNT_DEVICE, server_id)) {
            values.put(ACCOUNT_DEVICE_SERVER_ID, server_id);
            return mDB.insert(TABLE_ACCOUNT_DEVICE, null, values);
        } else {
            return mDB.update(TABLE_ACCOUNT_DEVICE, values, ACCOUNT_DEVICE_SERVER_ID + " =? ",
                    new String[]{Integer.toString(server_id)});
        }
    }

Hope it will helps you.

View more solutions

Comments

[Matteo Bononi 'peorthyr']

thanks, this is the way i chose, becouse i can change the args if i have to insert, since i have to put some default values.

[Matteo Bononi 'peorthyr']

thanks, i guess this is the clean solution, but i have to change arguments if it is an insert instead of update, by the way, this "insert or replace" is a really good thing :D

[gregschlom]

Careful, insert or replace may be different from what you expect from insert or update. See my answer here: stackoverflow.com/a/4253806/365596

[Vicky Chijwani]

"Replace" is not the same as "update" in some contexts. Imagine if you only want to replace only those fields that are present in the given ContentValues. This can happen in cases where web APIs return partial objects (I'm facing this problem myself, today).