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).

lenooh · Accepted Answer · 2015-08-11 21:19:31Z

36

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);

answered Aug 11, 2015 at 21:19

lenooh

10.7k5 gold badges60 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vicky Chijwani Over a year ago

"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).

David M · Accepted Answer · 2013-01-03 16:53:19Z

15

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.

answered Jan 3, 2013 at 16:53

David M

2,5411 gold badge17 silver badges19 bronze badges

1 Comment

Matteo Bononi 'peorthyr' Over a year ago

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.

ashokgelal · Accepted Answer · 2013-01-03 16:53:38Z

8

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);

answered Jan 3, 2013 at 16:53

ashokgelal

81.7k26 gold badges74 silver badges85 bronze badges

2 Comments

Matteo Bononi 'peorthyr' Over a year ago

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 Over a year ago

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

Pratik Butani · Accepted Answer · 2015-08-08 09:44:16Z

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.

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related