Update multiple rows in Android sqlite

Question

I have table contains columns id, name, profession, age, hobby, country, sex. Now I want to update the fields where sex is female and age is 30. All the fields are text (String). First, I am counting all the rows then running a loop to update the rows. Loop is running as per the total rows but rows are not updated... WHY? Where I have done the mistake? Here is my code:

METHODS FOR ANDROID SQLITE DATABASE QUERY:

public void updateUser(String newProfession, String newCountry, String sx, String ag) {
    SQLiteDatabase db = this.getWritableDatabase();
    String query = "UPDATE "+TABLE_USER+" SET "+KEY_PROFESSION+"='"+newProfession+"', "+KEY_COUNTRY+"='"+newCountry+"' WHERE "+KEY_SEX+"='"+sx+"' AND "+KEY_AGE+"='"+ag+"'";
    Cursor cursor = db.rawQuery(query, null);
    cursor.close();
    db.close();
}

public int countAll() {
    String countQuery = "SELECT  * FROM " + TABLE_USER;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(countQuery, null);
    int cnt = cursor.getCount();
    cursor.close();
    db.close();
    return cnt;
}

CALLING THE METHODS

public void updateUsersClicked(View view) {
    int allData = db.countAll();
    for (int i = 0; i < allData; i++) {
        db.updateUser("SENIOR ENGINEER", "CANADA", "female", "30");
        System.out.println("T H I S    I S    T H E    R E S U L T: " + i);
    }
}

You are vulnerable to SQL injection attacks

Marc B
– Marc B

2014-06-24 21:20:14 +00:00
Commented Jun 24, 2014 at 21:20 — Marc B
– Marc B, Commented Jun 24, 2014 at 21:20
I know but this is not my concern now...

Mehdi
– Mehdi

2014-06-24 21:21:54 +00:00
Commented Jun 24, 2014 at 21:21 — Mehdi
– Mehdi, Commented Jun 24, 2014 at 21:21

laalto · Accepted Answer · 2014-06-24 21:56:03Z

2

Use execSQL() and not rawQuery() for updates.

rawQuery() just compiles the SQL and requires one of the moveTo...() methods on the returned Cursor to execute it. execSQL() both compiles and runs the SQL.

Also consider using ? parameters with bind args in your SQL to avoid escaping special characters and being vulnerable to SQL injection.

answered Jun 24, 2014 at 21:56

laalto

153k68 gold badges293 silver badges306 bronze badges

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

1 Comment

Mehdi Over a year ago

Thanks... Your suggestion works for me. I should take good care of SQL injection for sure 😊. Thanks again.

yossico · Accepted Answer · 2014-06-24 21:47:35Z

1

You don't need to do the for loop a single QSL "Update" query is enough if you want to update All the female with age 30.

If you are new to SQL you can view a simple example here: Simple SQL Update example

If you want to do something else - please edit your question

answered Jun 24, 2014 at 21:47

yossico

3,5315 gold badges45 silver badges80 bronze badges

4 Comments

Mehdi Over a year ago

Ya, but let us consider a case that I have age limit from 15 to 45. And I have to change only ages for 23, 43, 24, 27, and this value may vary time to time. What will be the case then?

yossico Over a year ago

This will change the WHERE clause of your query to someting like:

yossico Over a year ago

...WHERE age>15 AND age<45 - and all the records that returns "true" for that condition will be updated to the value you put in the SET clause of the query

yossico Over a year ago

You neet to learn the UPDATE sql query syntax and code something that will transform the desiered logic to query in the right UPDATE syntax

Collectives™ on Stack Overflow

Update multiple rows in Android sqlite

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related