1

I have a user interface in my Android app that allows users to choose from sqlite database what they want to see.
For example user can choose to see male contacts, but the user can also choose "doesn't matter" to see both male and female.I have written the following code to get contacts from database. 

 public List<Contact> get_contact(String name, String age, String gender){
   List<Contact> contacts=new ArrayList<>();
    DatabaseHelper dbHelper=new DatabaseHelper(context);
    SQLiteDatabase db=dbHelper.getReadableDatabase();
    String sql="SELECT * FROM Tablename WHERE name = ? AND" +
                                             " age = ? AND" +
                                             " gender = ?";
    String[] selectionArgs = new String[]{name, age, gender};
    Cursor cursor=db.rawQuery(sql,selectionArgs);
    if (cursor.moveToFirst()) {
        do {
            Contact contact = new Contact();
            contact.setName(cursor.getString(cursor.getColumnIndex("name")));
            contact.setAge(cursor.getString(cursor.getColumnIndex("age")));
            contact.setGender(cursor.getString(cursor.getColumnIndex("gender")));
            contacts.add(contact);

        } while (cursor.moveToNext());
    }
    if (cursor != null && !cursor.isClosed()) {
        cursor.close();
    }
    return contacts;

}

but the problem is when user chooses "doesn't matter", for example, for age or gender, I don't know how to do that. I used this code to get all contacts with age 20 but it didn't work.

List<Contact> contacts = get_contact(null,"20",null);

And i don't want to write a different query for each state, because there is too many states and in fact I have 5 arguments instead of 3, for get_contact() method.

Here is the error I get.

java.lang.IllegalArgumentException: the bind value at index 3 is null

beh-f
  • 103
  • 1
  • 9

1 Answers1

1

To handle so many conditions without hard-coding a multitude of "select" statements, you will have to build up the "where" clause in code. Something like this:

String where = "";
if (name != null) {
    where = " AND name = ?";
}
if (age != null) {
    where += " AND age = ?";
}
if (gender != null) {
        where += " AND gender = ?";
}
String sqlStatement = "SELECT * FROM Tablename";
if (where.length() > 0) {
    sqlStatement += " WHERE " + where.substring(5);
}

You will notice that the final "where" clause will start with " AND". where.substring(5) gets rid of " AND" before concatenating the "where" clause to the select statement.

In a similar fashion, you will also need to build up the selection arguments. Probably using a List and converting it to a String[] makes most sense. See this documentation on how to do the conversion.

You may also want to consider using the StringBuilder class to build up the "where" clause, but that is secondary to what you are attempting.

Cheticamp
  • 61,413
  • 10
  • 78
  • 131