sqlite - 从 SQLite 数据库中检索 Int 值

Question

我正在尝试从我的 SQLite 数据库中检索一个整数，而我当前的查询使我的程序崩溃。这是我到目前为止所拥有的：

 */
public int getWin(String id){

    SQLiteDatabase db = this.getWritableDatabase();

    String query = "SELECT " + COL3 + " FROM " + TABLE_NAME +
            " WHERE " + COL2 + " = '" + id + "'";

    Log.d(TAG, "updateName: query: " + query);
    db.execSQL(query);
    int win = Integer.parseInt(query);
    return win;
}

我不确定为什么这不起作用。提前致谢。

标签： sqliteinteger

Answer 1

您正在尝试将值 SELECT ......... 转换为按int win = Integer.parseInt(query);.

对于 SELECT 语句，您需要通过query或rawQuery SQLiteDatabase 方法检索游标（结果集），然后从方法中提取值，然后从相应的行访问相应的列.

我相信你会使用类似的东西： -

public int getWin(String id){

    SQLiteDatabase db = this.getWritableDatabase();
    int rv = -1; //<<<<<<<<<< ADDED default value to return if no row found

    String query = "SELECT " + COL3 + " FROM " + TABLE_NAME +
            " WHERE " + COL2 + " = '" + id + "'";

    Log.d(TAG, "updateName: query: " + query);
    Cursor csr = db.rawQuery(query,null); //<<<<<<<<<< CHANGED to get the Cursor returned
    // ADDED the following IF construct
    if (csr.moveToFirst()) {
        rv = csr.getInt(csr.getColumnIndex(COL3));
    }
    //int win = Integer.parseInt(query); //<<<<<<<<<< DELETED (commented out)
    csr.close(); //<<<<<<<<<< ADDED should always close a Cursor when done with it
    return rv; //<<<<<<<<<< return the value (-1 if no row found)
}

• 这假设您只需要 WHERE 子句标识的单行中的值。

如果可能，建议 a) 不使用直接值构建查询（使其容易受到 SQL 注入的攻击），并且 b) 使用便捷查询方法。

同时应用a和b，您的代码可能是：-

public int getWin(String id){

    SQLiteDatabase db = this.getWritableDatabase();
    int rv = -1;
    String whereclause = COL2 + "=?"; //<<<<<<<<<< where clause without where and ? for value that will be passed
    String[] whereargs = new String[]{String.valueOf(id)}; //<<<<<<<<<< arguments used by the whereclause ? replaced on a 1 for 1 basis
    String[] columns = new String[]{COL3}; //<<<<<<<<<< the columns to extract as a String array
    Cursor csr = db.query(TABLE_NAME,columns,whereclause,whereargs,null,null,null);
    if (csr.moveToFirst()) {
        rv = csr.getInt(csr.getColumnIndex(COL3));
    }
    csr.close();
    return rv;
}