Deleting multiple rows using IN sql statement in whereClause [duplicate]

282
July 23, 2017, at 5:40 PM

This question already has an answer here:

  • IN clause and placeholders 7 answers

I have a list that performs multiple selections then delete the items that are selected using this method.

 public Integer deleteDesc(ArrayList<String> rows) {
    String[] args = rows.toArray(new String[rows.size()]);
    Log.i("AMS", "Args: " + args);
    db = this.getWritableDatabase();
    return db.delete("DropdownList", "Description IN (?)", args);
}

where the parameter ArrayList contains the items that were selected. I works when I only select one item but returns an error "bind or column index out of range".

I'm pretty sure it's the whereClause which cause it because I'm not sure how to correctly use the "?"

Answer 1

I used this statement TextUtils.join(",", Collections.nCopies(args.length, "?"));

working code:

 public Integer deleteDesc(ArrayList<String> rows) {
    String[] args = rows.toArray(new String[rows.size()]);
    db = this.getWritableDatabase();
    return db.delete("DropdownList", "Description IN (" + TextUtils.join(",", Collections.nCopies(args.length, "?"))
            + ")", args);
}
Answer 2

You can build the WHERE IN clause using ? placeholders for each value using a prepared statement:

SQLiteDatabase db = this.getWritableDatabase();
StringBuilder sql = new StringBuilder("DELETE FROM yourTable WHERE Description IN (?");
for (int i=1; i < args.length; i++) {
    sql.append(",?");
}
sql.append(")");
SQLiteStatement stmt = db.compileStatement(sql.toString());
for (int i=0; i < args.length; ++i) {
    stmt.bindString(i+1, args[i]);
}
stmt.execute();

Note that using a prepared statement here is probably highly recommended, since you are deleting data. Allowing a SQL injection in this method could have bad side effects.

READ ALSO
Signing APK in Ionic 2+. Getting error for alias password.

Signing APK in Ionic 2+. Getting error for alias password.

I have followed this step of running this command - 'keytool -genkey -v -keystore my-release-keyjks -keyalg RSA -keysize 2048 -validity 10000 -alias my-alias'

249
Scroll with a lot of lag in recycler view with multiple view type

Scroll with a lot of lag in recycler view with multiple view type

I am using a recycler view with multiple views in my applicationthis works fine but I have a problem with scrolling this recycle view! it has lag when I want to scroll it up and down

288
Is it good to publish our apps without asking Run Time Permission By Targeting sdk to 22

Is it good to publish our apps without asking Run Time Permission By Targeting sdk to 22

I found our apps runs even without asking permission like camera, location by setting the Target SDK 22 in API 24,25,26Is it good to do that? Or we should make our app to ask permission to use Camera, Location of device?

206
Android:App lagging for higher API device

Android:App lagging for higher API device

Why does an app built in Android Studio lag for my higher API device(API 23:Android Marshmallow) though it was built for minimum SDK for API level 19(Android KitKat 44) and is seen to run smoothly for relatively lower API devices?

223