SQLite Query : Select Query with BETWEEN clause

Question

Hi all

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;  

}

Answer 1

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.

Answer 2

Hi sid, 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);