Updating multiple columns in a certain row SQL LITE

Question

I have 4 methods all to update separate parts of data in a certain row. (I did this because I'm new to SQ Lite and Android). How would I condense all of this into one method?

public void updateInt(int id,String newName, String oldName){
    SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    String query = "UPDATE " + TABLE_NAME + " SET " + COL2 +
            " = '" + newName + "' WHERE " + COL1 + " = '" + id + "'" +
            " AND " + COL2 + " = '" + oldName + "'";
    sqLiteDatabase.execSQL(query);
}

public void updateAuth(int id,String newAuth, String oldAuth){
    SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    String query = "UPDATE " + TABLE_NAME + " SET " + COL3 +
            " = '" + newAuth + "' WHERE " + COL1 + " = '" + id + "'" +
            " AND " + COL3 + " = '" + oldAuth + "'";
    sqLiteDatabase.execSQL(query);
}

public void updateUser(int id,String newUser, String oldUser){
    SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    String query = "UPDATE " + TABLE_NAME + " SET " + COL5 +
            " = '" + newUser + "' WHERE " + COL1 + " = '" + id + "'" +
            " AND " + COL5 + " = '" + oldUser + "'";
    sqLiteDatabase.execSQL(query);
}

public void updateLocation(int id,String newLocation, String oldLocation){
    SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
    String query = "UPDATE " + TABLE_NAME + " SET " + COL4 +
            " = '" + newLocation + "' WHERE " + COL1 + " = '" + id + "'" +
            " AND " + COL4 + " = '" + oldLocation + "'";
    sqLiteDatabase.execSQL(query);
}

do you update the 4 columns of the row in separate time or you call all these 4 methods in sequence(once) — Mohamed Embaby
– Mohamed Embaby, Commented Jan 8, 2018 at 23:17

John3136 · Accepted Answer · 2018-01-08 23:13:39Z

2

I'd leave it as is.

Since each expression sets different things in different rows (all of the WHERE conditions are different) it can't really be merged.

answered Jan 8, 2018 at 23:13

John3136

29.3k4 gold badges55 silver badges77 bronze badges

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

2 Comments

shashaank srivastava Over a year ago

I'm passing the same variable for id into each of the methods so it's very redundant. It would make more sense to just combine it into one function

John3136 Over a year ago

Without knowing the schema, since the WHERE clauses are different then each one will be different rows. Now if it is that your WHEREs have redundant bits in them (i.e. id is the only thing you care about) then the answer is different.

MikeT · Accepted Answer · 2018-01-09 01:14:22Z

Using the SQLitedatbase update convenience method, you could use :-

public void updateAll(int id, 
        String newName, 
        String newAuth, 
        String newUser, 
        String newLocation) {

    ContentValues cv = new ContentValues();
    cv.put(COL2,newName);
    cv.put(COL3,newAuth);
    cv.put(COL4,newLocation);
    cv.put(COL5,newUser);
    SqliteDatabase sqliteDatabase = this.getWriteableDatabase();
    sqliteDatabase.update(TABLE_NAME,
        cv,
        COL1+"=?",
        new String[]{Long.toString(id)}
    );
}

This assumes that you want to update all columns.

If you wanted to just update certain columns but still use the one method, then :-

public void updateAll(int id, 
        String newName, String oldName
        String newAuth, 
        String newUser, 
        String newLocation) {

    ContentValues cv = new ContentValues();
    if (newName != null && newName.length() > 0) {
        cv.put(COL2,newName);
    }
    if (newAuth != null && newAuth.length() > 0) {
        cv.put(COL3,newAuth);
    }
    if (newLocation != null && newLocation.length() > 0) { 
        cv.put(COL4,newLocation);
    }
    if (newUser != null && newUser.length() > 0) {
        cv.put(COL5,newUser);
    }
    if (cv.size() > 0) {
        SqliteDatabase sqliteDatabase = this.getWriteableDatabase();
        sqliteDatabase.update(TABLE_NAME,
            cv,
            COL1+"=?",
            new String[]{Long.toString(id)}
        );
    }
}

This again assumes that you know the respective id.

e.g.

yourdbhelper.updateAll(id, "Fred","",null,"France");

would :-

update the row;
- replacing COL2 with Fred,
- not change COL3 (length of passed string is less than 1)
- not change COL4 (null passed)
- change COL5 to France

If you wanted to update only if the old??? values matched then you could use :-

public void updateAll(int id, 
    String newName, String oldName,
    String newAuth, String oldAuth,
    String newUser, String oldUser,
    String newLocation, String oldLocation) {

    ArrayList<String> whereargs = new ArrayList<>(Arrays.asList(String.valueOf(id)));
    StringBuilder whereclause = new StringBuilder(COL1+"=?");

    if(oldName != null && oldName.length > 0) {
        whereclause.append(" AND " + COL2 + "=?");
        whereargs.add(oldName);
    }
    if(oldAuth != null && oldAuth.length > 0) {
        whereclause.append(" AND " + COL3 + "=?");
        whereargs.add(oldAuth);
    }

    if(oldUser != null && oldUser.length > 0) {
        whereclause.append(" AND " + COL4 + "=?");
        whereargs.add(oldUser);
    }

    if(oldLocation != null && oldLocation.length > 0) {
        whereclause.append(" AND " + COL5 + "=?");
        whereargs.add(oldLocation);
    }

    ContentValues cv = new ContentValues();
    if (newName != null && newName.length() > 0) {
        cv.put(COL2,newName);
    }
    if (newAuth != null && newAuth.length() > 0) {
        cv.put(COL3,newAuth);
    }
    if (newLocation != null && newLocation.length() > 0) { 
        cv.put(COL4,newLocation);
    }
    if (newUser != null && newUser.length() > 0) {
        cv.put(COL5,newUser);
    }
    if (cv.size() > 0) {
        SqliteDatabase sqliteDatabase = this.getWriteableDatabase();
        sqliteDatabase.update(TABLE_NAME,
            cv,
            whereclause.toString(),
            whereargs.toArray(new String[whereargs.size()])        
        );
    }
}

!Note the code is in-principle and hasn't been tested so it may have some errors.

Collectives™ on Stack Overflow

Updating multiple columns in a certain row SQL LITE

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related