SQLite Query : Select Query with BETWEEN clause

Question

I want to run this query in Android Application :

SELECT field1 FROM table1  
where field1 = ?(enteredField1) AND field2 = ?(enteredField2)  
AND enteredField3 BETWEEN field3 , field4  
;

my DB schema

table1(field1, field2, field3, field4, field5)  

enteredField1 , 2 , 3 = are parameters which is to be checked.

I hope I am clear with my question.

here is the function -

public boolean checkData(String task1, String dte1, String startTime1 )  
{   

        // field 1 = task , fiels 2 = dte, field 3 = startHr, field 4 = endHr  
        // enteredField 1,2,3 = task1, dte1, startTime1   
        // here I want to insert the query  

    int a = cursor.getCount();  
    if(a>0)  
    {  
        return true;  
    }  
    return false;  
}  

Solution 1

you can use SQLiteQueryBuilder to solve your problem. Following is an example for you, hope it can help.

SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
SQLiteDatabase db = null; // get your database instance here.
qb.setTables("table1");
qb.appendWhere("field1 = ? AND field2 = ?");
qb.query(db, new String[] { "field1" }, "enteredField3 BETWEEN field3 AND field4",
        new String[] { "enteredField1", "enteredField2"}, null, null, null);

Solution 2

First, your query is invalid. There is no INBETWEEN in SQL; use BETWEEN:

... test_expr BETWEEN lower_bound_expr AND upper_bound_expr

Also, the comma is not a binary operator that works in WHERE clauses; use AND instead:

... WHERE binary_expression_1 AND binary_expression_2

Execute the query with one of the query methods in SQLiteDatabase. Which you use depends on your needs; the one I linked to is probably fine for yours.

Comments

[sid]

hi tony thanks for your help. but its giving runtime error. this querybuilder is new to me, so will you please explain me this. Just tell me which fields should be in double quotes. and which should nt be in.

[Tseng]

Have you instanced the SQliteDatabase?

[Tony]

SQLiteQueryBuilder is used to build query string using the args you passed in. You can use SQLiteQueryBuilder's query method to get the query result. Before you invoke query, you may want to set some arguments to the SQLiteQueryBuilder, like set which table(s) you want to query from by using setTables method, append criteria by using appendWhere method, and so on. You can get more info by reading the apidocs. BTW, pasting your exception log will give other guys more info about your problem.