SQLite Database Android, check if a record exists and save

Question

I have a Database which extends SQLiteOnHelper for an app of Then I have to make sure that if, for example, I change only the title, you should update the record, otherwise normal insert.

This is my table:

public void onCreate(SQLiteDatabase db) {
    String sql = "CREATE TABLE vasca(" +
            "_id INTEGER PRIMARY KEY,"+
            "titoloNota TEXT NOT NULL,"+
            "testoNota TEXT NOT NULL,"+
            "dataNota DATE,"+
            "coloreNota INTEGER NOT NULL,"+
            "UNIQUE(id, titoloNota)"+ // to be reviewed
            ")";
    db.execSQL(sql);
}

And my boolean method for insert:

public boolean insertNote(Nota nota){
    // boolean inizializzat a false utilizzata come return
    boolean resultInsert = false;
    // repository dei dati in modalità scrittura
    SQLiteDatabase dbLite = this.getWritableDatabase();
    // utilizza un ContentValues come mappa di valori, dove le columns rappresentano le chiavi
    ContentValues values = new ContentValues();
    values.put(SQL_INSERT_OR_REPLACE, true);
    values.put("titoloNota", nota.getTitolo());
    values.put("testoNota", nota.getTesto());
    values.put("dataNota", nota.getData()); // ritorna una string sdf.format(nota.getData())
    values.put("coloreNota", nota.getColore());
    // chiama il metodo insert su dbLite, Vasca è il nome della tabella
    // poichè ritorna un long, se negativo significa che l'inserimento non è riuscito
    long idInserimento = dbLite.insert("vasca", null, values);

    // sfrutto la variabile long per fare un controllo se andato tutto ok
    if( idInserimento <= -1 ){
        Log.e(TAG, "Inserimento della nota non avvento");
        resultInsert = false;
    }else{
        resultInsert = true;
        // inserisce la nota passata al metodo nell'arrayList listNote chiamando il metodo add
    }
    dbLite.close();
    return resultInsert;
}

I think we need to make a query. any help or advice is appreciated :)

Did you manage to solve your problem? – krebernisak Apr 10 '15 at 17:11 — krebernisak, Apr 10 '15 at 17:11
No, I'm working on right now! – sawyer Apr 10 '15 at 18:13 — sawyer, Apr 10 '15 at 18:13

krebernisak · Answer 1 · 2015-04-07T19:42:36.330

You should avoid querying the db if you can make this check in memory. Write some method that compares the note with user edits.

If not, you can query the database for an entry that has all the same fields except the title:

SQLiteDatabase db = mDbHelper.getWritableDatabase();

// Define a projection that specifies which columns from the database
// you will actually use after this query.
String[] projection = {
    "_id",
    "titoloNota"
};

// Define 'where' part of query.
String selection = "_id = ? AND testoNota = ? AND dataNota = ? AND coloreNota = ?";
// Specify arguments in placeholder order.
String[] selectionArgs = { nota.getId(), nota.getTesto(), nota.getData(), nota.getColore() };

Cursor c = db.query(
    "vasca",                                  // The table to query
    projection,                               // The columns to return
    selection,                                // The columns for the WHERE clause
    selectionArgs,                            // The values for the WHERE clause
    null,                                     // don't group the rows
    null,                                     // don't filter by row groups
    null                                      // The sort order
);

if (c.moveToFirst()) {
    // entry with the specified selection was found
    int title = c.getString(c.getColumnIndex("titoloNota"));
    // UPDATE if title changed
} else {
    // no entry found - INSERT
}

score 0 · Answer 2 · answered Apr 07 '15 at 19:22

You should either create a separate method for update record or create separate update query based on the flag which can be set on the condition whether a filled form has updated or just blank form has opened. You can check whether the opened form is blank or filled by checking each field one by one if you are opening the same form with filled data as well as for blank form. If blank form has opened, you can put the flag="insert" and fire the insert query/method or if the filled form has updated and user has changed something in that form which can be checked by comparing the previous values, you can put the flag="update" and fire relevant update query in the same method or you can call a separate update method.

SQLite Database Android, check if a record exists and save

2 Answers2