How to solve all in one query?

Question

In my search module I am using SQLite database. Sometimes I'm searching more than two values. So I am using AND condition and sometimes searching only one value. These two cases I need to implement in a single query. Please help me.

This is the query:

Cursor dbcur = myDB.rawQuery("select * from "+dbtable+" where Status='"+item+"'AND ball_court='"+ball+"'AND Tovendor='"+vendor+"'", null);

Sometimes I am searching status and ball_court, tovendor and sometimes I am searching status only. How to solve this problem?

Answer 1

Build the query string in stages, rather than all at once. If you're always going to select against the status, put that in the basic WHERE clause, then add any additional expressions. Use parameters (which you can do with SQLiteDatabase.query) rather than concatenating values into the query itself to prevent SQL injection. dbtable better not come from untrusted sources (e.g. users, the database itself). My Java's a little rusty, but try something like the following to start:

List<String> argList = new LinkedList<String>();

String selection = "Status=?";
argList.add(item);
if (ball != null && ball.length()) {
    selection += " AND ball_court=?";
    argList.add(ball);
}
if (vendor != null && vendor.length()) {
    selection += " AND Tovendor=?";
    argList.add(vendor);
}

String[] argArray = new String[argList.length()];
argList.toArray(argArray);
// columns is a String[] of column names
Cursor dbcur = myDB.query(false, dbtable, columns, selection, argArray, null, null, null);

Rather than appending strings, you might want to use a StringBuilder.

As for SELECT *, read "What is the reason not to use SELECT *?"

Answer 2

You might be able to get some answers if you post a sample query as well as what you might search for! I didn't totally understand your question, as I would/should be able to help out.