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

Question

there is 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 so that it is updated or inserted accordingly. I would like to change this because I already use this function in some places, but now I need to select whether to insert a row or update (or ignore pasting) a row with the same COLUMN_NOME . can someone help me do this?

i means that I would like to add a new line ONLY if there is no other with the same name (as usual, you know).

Answer 1

you can call int nRowsEffected = database.update(...); if there are no rows in the update, or the row does not exist (or you closed your update() !), so you need to call database.insert(...) . of course, if nRowsEffected> 0, then you are done.

Answer 2

You can use insertWithOnConflict () if you want to insert or update , depending on the presence or absence of an entry:

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

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

Answer 4

First of all, you have a write function that checks if an id exists in a particular table:

  /** * @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 need to pass table_name and id what you 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 this helps you.