samedi 3 octobre 2015

Android SQLite like query with multiple, optional conditions as a prepared statement

I'm quite new to Android, and I'm trying to query a database table "Houses" with the columns size, owner and city.

I am trying to implement a method

getHousesFromDatabase(String owner, String city, Integer size) 

where city and size can be null. The method is supposed to return all the rows where the size, location, city fields match the parameters, but only if the parameter is not null. i.e.

getHousesFromDatabase("John", null, null); should return all the rows where the owner is John, regardless of the other columns.

getHousesFromDatabase("John", "San Francisco", null); should return all the rows where the owner is John and that are in San Francisco and so on.

My current implementation goes like this

   public House getHousesFromDatabase(String owner, String city, Integer size) {
        if (city == null && size == null) {
            Cursor cursor = database.rawQuery("select * from " + SQLiteHelper.TABLE_HOUSES + " where "
                    + COLUMN_OWNER + " like '" + owner + "%" + "'", null);
        }
        else if (city == null) {
            Cursor cursor = database.rawQuery("select * from " + TABLE_HOUSES + " where "
                    + COLUMN_OWNER + " like '" + owner + "%" + "'" +" and where " + COLUMN_SIZE + "like '"+ size +"%'", null);
        }
        else if (size == null) {
            ...
        }
        else {
           ...
        }
    }

where the if statements cover each possible variation; But this feels horribly clumsy to me, and I wonder if there is a simpler way, and one that results in a prepared statement? (from what I read, rawQuery() alone is vulnerable, and SQLiteStatement.execute() is not to be used for queries).

Aucun commentaire:

Enregistrer un commentaire