SQLite selecting a row from table in an interval

user_14997369 0
Viewed 46

Hello I am working on an Android SQLite project and can not find a way to get the value, The program takes an input value from the user and searches it in the database, I want to do a query like; if the input value is 3; look through the col_a and col_b if the value is between col_a and col_b prints the col_c value "AA" and col_d value "BB". Like col_a < 3 <col_b prints AA, BB. If col_a < 7 < col_b prints CC, DD. I tried this code but it always shows the first row from the table.

This is my code;

Cursor cursor = db.rawQuery("SELECT * FROM my_table WHERE col_a < 3 < col_b " , null);
    if (cursor.moveToNext()) {
        String element1 = cursor.getString(cursor.getColumnIndexOrThrow("col_c"));
        String element2 = cursor.getString(cursor.getColumnIndexOrThrow("col_d"));
        String element3 = cursor.getString(cursor.getColumnIndexOrThrow("ID"));
        cursor.close();

        Log.d(""," " +element1);
        Log.d(""," " +element2);
        Log.d(""," " +element3);
    }

This is an example table;

ID  |  col_a  |  col_b  |  col_c  |  col_d 
-------------------------------------------
1   |    1    |    5    |   AA    |   BB   
-------------------------------------------
2   |    5    |    10   |   CC    |   DD    
-------------------------------------------
android-sqlite sqlite android java
1 Answers

Assuming that the value that the user inputs is stored in a numeric variable x, construct the sql statement with ? placeholders for this value:

String sql = "SELECT * FROM my_table WHERE col_a < ?  AND ? < col_b";
Cursor cursor = db.rawQuery(sql, new String[] {String.valueOf(x), String.valueOf(x)});
if (cursor.moveToNext()) {
    String element1 = cursor.getString(cursor.getColumnIndexOrThrow("col_c"));
    String element2 = cursor.getString(cursor.getColumnIndexOrThrow("col_d"));
    String element3 = cursor.getString(cursor.getColumnIndexOrThrow("ID"));
    cursor.close();
    Log.d(""," " +element1);
    Log.d(""," " +element2);
    Log.d(""," " +element3);
}

If the data type of the columns col_a and col_b is INTEGER you could simplify the sql statement with the operator BETWEEN:

String sql = "SELECT * FROM my_table WHERE ? BETWEEN col_a + 1  AND col_b - 1";
Cursor cursor = db.rawQuery(sql, new String[] {String.valueOf(x)});
.....................................................................

Also if you expect more than 1 rows returned use a while loop instead of if:

while (cursor.moveToNext()) {
   .................................
}
user_104988280
Related