SQLite在一个间隔内从表中选择一行

Question

你好，我正在做一个Android SQLite项目，找不到获取值的方法，程序从用户那里获取输入值并在数据库中搜索，我想做一个类似的查询；如果输入值是3；如果值在cola和colb之间，则查看cola和COLb；AA"；col_ d值"；BB"；。像col_a<3<col_b打印AA、BB。如果col_a<7<col_b打印CC，DD。我尝试过这个代码，但它总是显示表中的第一行。

这是我的密码；

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);
}

这是一个示例表；

ID  |  col_a  |  col_b  |  col_c  |  col_d 
-------------------------------------------
1   |    1    |    5    |   AA    |   BB   
-------------------------------------------
2   |    5    |    10   |   CC    |   DD    
-------------------------------------------

Answer 1

假设用户输入的值存储在数值变量x中，则为该值构造带有?占位符的sql语句：

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);
}

如果列col_a和col_b的数据类型是INTEGER，则可以使用运算符BETWEEN:简化sql语句

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)});
.....................................................................

此外，如果您希望返回超过1行，请使用while循环，而不是if:

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