Dynamic SQLite queries

Question

I'm trying to implement dynamic queries in my Android app, to let the users search according to some criteria. In this case I'm trying to search simply by an integer value. Here's my attempt:

...
public String[][] listarNegocio(int idProyecto,
    int minimo,
    int maximo)

{
    String[][] arrayDatos = null;

    String[] parametros = {String.valueOf(idProyecto)};

    Cursor cursor = null;

    cursor = querySQL("SELECT *" +
        " FROM negocio" +
        " WHERE ? in (0, id_proyecto)", parametros);

    if(cursor.getCount() > 0)
    {
        int i = minimo - 1;
        arrayDatos = new String[maximo - minimo + 1][20];

        while(cursor.moveToNext() && i < maximo)
        {
            // Here I fill the array with data

            i = i + 1;
        }
    }

    cursor.close();
    CloseDB();
    return(arrayDatos);
}

public Cursor querySQL(String sql, String[] selectionArgs)
{
    Cursor oRet = null;
    // Opens the database object in "write" mode.
    db = oDB.getReadableDatabase();
    oRet = db.rawQuery(sql, selectionArgs);
    return(oRet);
}

...

I tested this query using SQLFiddle, and it should return only the rows where the column id_proyecto equals the parameter idProyecto, or every row if idProyecto equals 0. But it doesn't return anything. If I remove the WHERE clause and replace "parametros" with "null", it works fine.

Additionally, I need to search by text values, using LIKE. For example, WHERE col_name LIKE strName + '%' OR strName = ''. How should I format my parameters and the query to make it work?

Answer 1

You should do one query for each case. For an id that exists, do SELECT * FROM negocio WHERE id_proyecto = ?. For an id that doesn't exist (I'm assuming 0 isn't a real id), just query everything with SELECT * FROM negocio.

Code should be something like this:

if(parametros[0] != 0){
    cursor = querySQL("SELECT *" +
        " FROM negocio" +
        " WHERE id_proyecto = ?", parametros);
} else {
    cursor = querySQL("SELECT *" +
        " FROM negocio", null);
}

Regarding your second question, it depends on what you're looking for, you could use LIKE '%param%' or CONTAINS for occurrences in between text, LIKE param for partial matches or just = param if you're looking an exact match.