android - 在 android sqlite 中使用 IN 关键字

Question

我有两个表:

1) Employee -- _id, employee_name.
2) Salary -- _id, amount, emp_id.

示例数据:

Employee:
1 John
2 Rocky
3 Marry

Salary:

1 500 1 //salary for John
2 400 1 //salary for John
3 600 2 //salary for Rocky
4 700 2 //salary for Rocky
5 350 3 //salary for Marry

现在，我想在工资表中搜索以查看我支付了工资的人。假设如果我在薪水表中搜索“John”，它应该返回 John 的第 1 行和第 2 行。

这是我正在尝试的:

String where = " emp_id in (select _id from Employee where employee_name like ? )";

String[] whereArgs = new String[] {"'%" + mFilter + "%'" };

Cursor c = getDB(mContext).query("Salary", null, where, whereArgs,
                null, null, null);

但它总是没有返回结果。请帮忙。

更新

我调试了代码，发现游标中正在执行以下查询:

SELECT * FROM Salary WHERE emp_id in (select _id from Employee where employee_name like ? );

Answer 1

选择参数自动用作字符串。改变这个:

String[] whereArgs = new String[] {"'%" + mFilter + "%'" };

为此:

String[] whereArgs = new String[] {"%" + mFilter + "%" }; (removed ' from your strings)

有了额外的 '，它会取消文本并变成 ''%John%'' 构建器会自动处理 '选择参数。

编辑
也将您的查询更改为此:

String sql = "SELECT * FROM Salary WHERE emp_id in (select _id from Employee where employee_name like ?)";
Cursor c = getDB(mContext).rawQuery(sql, whereArgs);

编辑 2

我用下面的类重新创建了您的设置，我的所有代码都运行良好。我从用户 John 那里得到了两个结果。我认为问题出在您的数据库创建中，或者您的数据库中没有数据。使用 DDMS 拉取数据库并使用 SQLite 浏览器打开它。检查您的数据库中是否有任何数据。如果是，则您的表创建类型与 select 语句不匹配。当我调用 GetMyValues() 时，我从游标返回了 2 条记录。

public class DataBaseHandler extends SQLiteOpenHelper {

    private static final String TAG = "DBHandler";

    //Database VERSION
    private static final int DATABASE_VERSION = 2;

    //DATABASE NAME
    private static final String DATABASE_NAME = "test";

    //DATABASE TABLES
    private static final String TABLE_SALARY = "Salary";
    private static final String TABLE_EMP = "Employee";

    //DATABASE FIELDS
    private static final String SalaryID= "_id";
    private static final String SalaryEmpName = "employee_name";
    private static final String EmpID= "_id";
    private static final String EmpAmt = "amount";
    private static final String EmpSalID = "emp_id";

    //DATABASE TYPES
    private static final String INTPK = "INTEGER PRIMARY KEY";
    private static final String INT = "INTEGER";
    private static final String TEXT = "TEXT";

    //CREATE TABLES
    private static final String CREATE_SALARY_TABLE = "CREATE TABLE " + TABLE_SALARY + "("
                + EmpID + " " + INTPK + "," +  EmpAmt + " " + INT + ","
                + EmpSalID + " " + INT + ")";

      //CREATE TABLE Salary(_id INTEGER PRIMARY KEY,amount INTEGER,emp_id INTEGER)

    private static final String CREATE_EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMP + "("
                + SalaryID + " " + INTPK + "," + SalaryEmpName + " " + TEXT + ")";

      //CREATE TABLE Employee(_id INTEGER PRIMARY KEY, employee_name TEXT)

    public DataBaseHandler(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_EMPLOYEE_TABLE);
        db.execSQL(CREATE_SALARY_TABLE);

        insertEmployeeValues(db);
        insertSalaryValues(db);     
    }

    private void insertEmployeeValues(SQLiteDatabase db){
        ContentValues values = new ContentValues();
        values.put(SalaryEmpName, "John");
        db.insert(TABLE_EMP, null, values);
        values.clear();
        values.put(SalaryEmpName, "Rocky");
        db.insert(TABLE_EMP, null, values);
        values.clear();
        values.put(SalaryEmpName, "Marry");
        db.insert(TABLE_EMP, null, values);
        values.clear();
    }

    private void insertSalaryValues(SQLiteDatabase db){
        ContentValues values = new ContentValues();
        values.put(EmpAmt, 500);
        values.put(EmpSalID, 1);
        db.insert(TABLE_SALARY, null, values);
        values.clear();
        values.put(EmpAmt, 400);
        values.put(EmpSalID, 1);
        db.insert(TABLE_SALARY, null, values);
        values.clear();
        values.put(EmpAmt, 600);
        values.put(EmpSalID, 2);
        db.insert(TABLE_SALARY, null, values);
        values.clear();
        values.put(EmpAmt, 700);
        values.put(EmpSalID, 2);
        db.insert(TABLE_SALARY, null, values);
        values.clear();
        values.put(EmpAmt, 350);
        values.put(EmpSalID, 3);
        db.insert(TABLE_SALARY, null, values);
        values.clear();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EMP);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SALARY);
        onCreate(db);
    }

    public int GetMyValues(){
        String mFilter = "John"; 
        String[] whereArgs = new String[]{"%" + mFilter + "%"};
        int count = 0;
        SQLiteDatabase db = this.getWritableDatabase();
        String where = " emp_id in (select _id from Employee where employee_name like ? )";
        Cursor c = db.query("Salary",null, where, whereArgs,null,null,null);
        count = c.getCount();
        c.close();
        return count;
    }
}

Dev Reference:

You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings