java - 为什么我的 sqlite 选择没有返回结果？

Question

我已将一些记录添加到我的 Android 应用程序数据库中。

然后我尝试检索这些行，但我总是将光标设置在位置 -1。

我的语法有什么问题？

public class PhoneDal extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;
    // Database Name
    private static final String DATABASE_NAME = Constants.DB_NAME;

    public static final String BLOCKED_PHONES_TABLE = "BLOCKED_PHONES_TABLE";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_BLOCKED_PHONES_TABLE =
                "CREATE TABLE "+ BLOCKED_PHONES_TABLE +
                        " ( "+ KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL  DEFAULT 1, "
                        + KEY_PHONE+" TEXT, "
                             + KEY_IS_BLOCKED+" BIT )";

        db.execSQL(CREATE_BLOCKED_PHONES_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            Log.w("MyAppTag", "Updating database from version " + oldVersion
                    + " to " + newVersion + " .Existing data will be lost.");
            // Drop older books table if existed
            db.execSQL("DROP TABLE IF EXISTS " + BLOCKED_PHONES_TABLE);

            // create fresh books table
            this.onCreate(db);
        }

    }



    private static final String KEY_ID = "id";
    private static final String KEY_PHONE = "KEY_PHONE";
    private static final String KEY_IS_BLOCKED = "KEY_IS_BLOCKED";

    public long addItem(Phone phone) {
        Log.d(Constants.LOGGER_TAG, "add saved-offer");
        // 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();

        // 2. create ContentValues to add key "column"/value
        ContentValues values = new ContentValues();
        //values.put(KEY_ID, phone.id);
        values.put(KEY_PHONE, phone.phone);
        values.put(KEY_IS_BLOCKED, phone.isBlocked);

        // 3. insert
        long newRowId =
                db.insertWithOnConflict(BLOCKED_PHONES_TABLE, KEY_ID,
                values, SQLiteDatabase.CONFLICT_REPLACE);

        if (newRowId > 0) {
            final String text = String.format("item was added to table: %s",
                    BLOCKED_PHONES_TABLE);
            Log.d(Constants.LOGGER_TAG, text);

        }

        // 4. close
        db.close();
        return newRowId;
    }

    public Phone getItem(String phone) {

        Phone result = null;

        Cursor cursor = this.getReadableDatabase().query(
                BLOCKED_PHONES_TABLE,
                new String[] { KEY_ID  }, ""+KEY_PHONE+" = ? AND "+KEY_IS_BLOCKED+" = ?",
                new String[] { phone, "1" }, null, null, null);

        // 3. if we got results get the first one
        if (cursor != null && cursor.getPosition() != -1) {
            result = new Phone();
            result.id = (cursor.getInt(1));
            result.phone = phone;
            result.isBlocked = true;
        }
        return result;
    }
}

我尝试创建一个 select * 查询，但尚未得到结果(光标位于位置 -1)

            BLOCKED_PHONES_TABLE,
            new String[] { KEY_ID  },null, null, null, null, null);

Answer 1

这是一个功能。当您查询和接收游标时，它首先总是指向索引-1。在尝试访问数据之前，您必须先调用其 moveTo...() 方法之一。这些方法返回一个 boolean 值，告诉您移动后光标是否指向有效行。

例如，更改此:

// 3. if we got results get the first one
if (cursor != null && cursor.getPosition() != -1) {

类似于

// 3. if we got results get the first one
if (cursor.moveToFirst()) {

SQLiteDatabase 查询方法永远不会返回 null，因此检查 null 有点多余。