Android:索引 1 处的绑定(bind)值为空

Question

我遇到了一个错误

java.lang.IllegalArgumentException: the bind value at index 1 is null   

我研究了一下，我知道这是 sql 语句错误，我正在尝试传递一个空值。

我检查了我的代码并修复了sql语句中的一些错误和空格，但仍然出现这个错误。

  04-15 11:07:32.820: E/AndroidRuntime(2258): FATAL EXCEPTION: main
    04-15 11:07:32.820: E/AndroidRuntime(2258): Process: com.mad.naszlimerickmobile, PID: 2258
    04-15 11:07:32.820: E/AndroidRuntime(2258): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.mad.naszlimerickmobile/com.mad.naszlimerickmobile.AddEditActivity}: java.lang.IllegalArgumentException: the bind value at index 1 is null
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2195)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2245)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.app.ActivityThread.access$800(ActivityThread.java:135)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.os.Handler.dispatchMessage(Handler.java:102)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.os.Looper.loop(Looper.java:136)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.app.ActivityThread.main(ActivityThread.java:5017)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at java.lang.reflect.Method.invokeNative(Native Method)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at java.lang.reflect.Method.invoke(Method.java:515)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at dalvik.system.NativeStart.main(Native Method)
    04-15 11:07:32.820: E/AndroidRuntime(2258): Caused by: java.lang.IllegalArgumentException: the bind value at index 1 is null
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.database.sqlite.SQLiteProgram.bindString(SQLiteProgram.java:164)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.database.sqlite.SQLiteProgram.bindAllArgsAsStrings(SQLiteProgram.java:200)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at com.mad.naszlimerickmobile.NotesDB.getList(NotesDB.java:150)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at com.mad.naszlimerickmobile.AddEditActivity.onCreate(AddEditActivity.java:80)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.app.Activity.performCreate(Activity.java:5231)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2159)
    04-15 11:07:32.820: E/AndroidRuntime(2258):     ... 11 more

添加编辑 Activity

public class AddEditActivity extends Activity implements OnKeyListener {

    private EditText notetitleEditText;
    private EditText notesEditText;
    private Spinner listSpinner;

    private NotesDB db;
    private boolean editMode;
    private String currentTabName = "";
    private Note note;

//  private Button cancelNoteBtn;
//  private Button saveNoteBtn;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_add_edit_note);

        // get references to widgets
        listSpinner = (Spinner) findViewById(R.id.listSpinnerId);
        notetitleEditText = (EditText) findViewById(R.id.noteTitleEditText);
        notesEditText = (EditText) findViewById(R.id.noteContentEditText);

        // set listeners
        notetitleEditText.setOnKeyListener(this);
        notesEditText.setOnKeyListener(this);

        //get buttons 
    //  cancelNoteBtn=(Button) findViewById(R.id.);

        // get the database object
        db = new NotesDB(this);

        // set the adapter for the spinner
        ArrayList<List> lists = db.getLists();
        ArrayAdapter<List> adapter = new ArrayAdapter<List>(this,
                R.layout.spinner_list, lists);
        listSpinner.setAdapter(adapter);

        // get edit mode from intent
        Intent intent = getIntent();
        editMode = intent.getBooleanExtra("editMode", false);

        // if editing
        if (editMode) {
            // get task
            long noteId = intent.getLongExtra("noteId", -1);
            note = db.getNote(noteId);

            // update UI with task
            notetitleEditText.setText(note.getNoteTitle());
            notesEditText.setText(note.getNotes());
        }

        // set the correct list for the spinner
        long listID;
        if (editMode) { // edit mode - use same list as selected task
            listID = (int) note.getListId();
        } else { // add mode - use the list for the current tab
            currentTabName = intent.getStringExtra("tab");
            listID = (int) db.getList(currentTabName).getListId();
        }
        // subtract 1 from database ID to get correct list position
        int listPosition = (int) listID - 1;
        listSpinner.setSelection(listPosition);
    }

    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.activity_add_edit_menu, menu);
        return true;
    }

    public boolean onOptionsIteamSelected(MenuItem item) {
        switch (item.getItemId()) {
        case R.id.menuSaveNote:
            saveToDB();
            this.finish();
            break;

        case R.id.menuCancelNote:
            this.finish();
            break;

        }
        return super.onOptionsItemSelected(item);

    }

    private void saveToDB() {
        int listId = listSpinner.getSelectedItemPosition() + 1;
        String title = notetitleEditText.getText().toString();
        String notes = notesEditText.getText().toString();

        if (title == null || title.equals("")) {
            return;
        }

        if (!editMode) {
            note = new Note();
        }

        note.setListId(listId);
        note.setNoteTitle(title);
        note.setNotes(notes);

        if (editMode) {
            db.updateNote(note);
        } else {
            db.insertNote(note);
        }

    }

    @Override
    public boolean onKey(View view, int keyCode, KeyEvent event) {
        if (keyCode == KeyEvent.KEYCODE_DPAD_CENTER) {
            // hide the soft Keyboard
            InputMethodManager imm = (InputMethodManager) getSystemService(Context.INPUT_METHOD_SERVICE);
            imm.hideSoftInputFromWindow(view.getWindowToken(), 0);
            return true;
        } else if (keyCode == KeyEvent.KEYCODE_BACK) {
            saveToDB();
            return false;
        }
        return false;
    }

}

数据库

public class NotesDB {

    public static final String DB_NAME = "nlnotes.db";
    public static final int DB_VERSION = 1;

    // LIST TABLE
    public static final String LIST_TABLE = "list";

    public static final String LIST_ID = "listId";
    public static final int LIST_ID_COL = 0;

    public static final String LIST_NAME = "list_name";
    public static final int LIST_NAME_COL = 1;

    // NOTE TABLE
    public static final String NOTE_TABLE = "note";

    public static final String NOTE_ID = "noteId";
    public static final int NOTE_ID_COL = 0;

    public static final String NOTE_LIST_ID = "listId";
    public static final int NOTE_LIST_ID_COL = 1;

    public static final String NOTE_TITLE = "noteTitle";
    public static final int NOTE_TITLE_COL = 2;

    public static final String NOTE_NOTES = "notes";
    public static final int NOTE_NOTES_COL = 3;

    public static final String NOTE_COMPLETED = "date_completed";
    public static final int NOTE_COMPLETED_COL = 4;

    public static final String NOTE_HIDDEN = "hidden";
    public static final int NOTE_HIDDEN_COL = 5;

    public static final String CREATE_LIST_TABLE = 
            "CREATE TABLE " + LIST_TABLE + "(" + 
            LIST_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + LIST_NAME + " TEXT    UNIQUE)";

    public static final String CREATE_NOTE_TABLE = 
            "CREATE TABLE " + NOTE_TABLE + "(" +
            NOTE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
            NOTE_LIST_ID + " INTEGER, " +
            NOTE_TITLE + " TEXT, " + 
            NOTE_NOTES + " TEXT, " + 
            NOTE_COMPLETED + " TEXT, " + 
            NOTE_HIDDEN + " TEXT)";

    public static final String DROP_LIST_TABLE = "DROP TABLE IF EXIST"
            + LIST_TABLE;

    public static final String DROP_NOTE_TABLE = "DROP TABLE IF EXIST"
            + NOTE_TABLE;

    public static class DBHelper extends SQLiteOpenHelper {

        public DBHelper(Context context, String name, CursorFactory factory,
                int version) {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            db.execSQL(CREATE_LIST_TABLE);
            db.execSQL(CREATE_NOTE_TABLE);

            db.execSQL("INSERT INTO list VALUES (1, 'Personal')");
            db.execSQL("INSERT INTO list VALUES (2, 'Other')");

            db.execSQL("INSERT INTO note VALUES (1, 1, 'Your first note', "
                    + "'Touch to edit', '0', '0')");
            db.execSQL("INSERT INTO note VALUES (2, 1, 'Your second note ', "
                    + "'Touch to edit', '0', '0')");

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

            Log.d("Note list", "Upgrading db from version" + oldVersion
                    + " to " + newVersion);

            Log.d("Note list", "Deleting all content!");
            db.execSQL(NotesDB.DROP_LIST_TABLE);
            db.execSQL(NotesDB.DROP_NOTE_TABLE);
            onCreate(db);

        }
    }

    private SQLiteDatabase db;
    private DBHelper dbhelper;

    public NotesDB(Context context) {
        dbhelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
    }

    private void openReadableDB() {
        db = dbhelper.getReadableDatabase();
    }

    private void openWritableDB() {
        db = dbhelper.getWritableDatabase();
    }

    private void closeDB() {
        if (db != null)
            db.close();
    }

    // to many args?
    public ArrayList<List> getLists() {
        ArrayList<List> lists = new ArrayList<List>();
        openReadableDB();
        Cursor cursor = db.query(LIST_TABLE, null, null, null, null, null, null);

        while (cursor.moveToNext()) {
            List list = new List();
            list.setListId(cursor.getInt(LIST_ID_COL));
            list.setList_name(cursor.getString(LIST_NAME_COL));
            lists.add(list);

        }
        cursor.close();
        closeDB();
        return lists;

    }

    // to many args?
    public List getList(String list_name) {
        String where = LIST_NAME + "= ?";
        String[] whereArgs = { list_name };

        openReadableDB();
        Cursor cursor = db.query(LIST_TABLE, null, where, whereArgs, null,
                null, null);

        List list = null;
        cursor.moveToFirst();
        list = new List(cursor.getInt(LIST_ID_COL),
                cursor.getString(LIST_NAME_COL));

        cursor.close();
        this.closeDB();

        return list;
    }

    // //////////////////////////////////////////
    public ArrayList<Note> getNotes(String list_name) {
        String where = NOTE_LIST_ID + "= ? AND " + NOTE_HIDDEN + "!='1'";

        long listID = getList(list_name).getListId();
        String[] whereArgs = { Long.toString(listID) };

        this.openReadableDB();
        Cursor cursor = db.query(NOTE_TABLE, null, where, whereArgs, null,
                null, null);
        ArrayList<Note> notes = new ArrayList<Note>();
        while (cursor.moveToNext()) {
            notes.add(getNoteFromCursor(cursor));
        }
        if (cursor != null)
            cursor.close();
        this.closeDB();
        return notes;

    }

    public Note getNote(long noteId) {
        String where = NOTE_ID + "= ?";
        String[] whereArgs = { Long.toString(noteId) };

        this.openReadableDB();
        Cursor cursor = db.query(NOTE_TABLE, null, where, whereArgs, null,
                null, null);

        cursor.moveToFirst();
        Note note = getNoteFromCursor(cursor);
        if (cursor != null)
            cursor.close();
        this.closeDB();

        return note;
    }

    private static Note getNoteFromCursor(Cursor cursor) {
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        } else {
            try {
                Note note = new Note(cursor.getInt(NOTE_ID_COL),
                        cursor.getInt(NOTE_LIST_ID_COL),
                        cursor.getString(NOTE_TITLE_COL),
                        cursor.getString(NOTE_NOTES_COL),
                        cursor.getString(NOTE_COMPLETED_COL),
                        cursor.getString(NOTE_HIDDEN_COL));
                return note;

            } catch (Exception e) {
                return null;
            }
        }
    }

    public long insertNote(Note note) {
        ContentValues cv = new ContentValues();
        cv.put(NOTE_LIST_ID, note.getNoteId());
        cv.put(NOTE_TITLE, note.getNoteTitle());
        cv.put(NOTE_NOTES, note.getNotes());
        cv.put(NOTE_COMPLETED, note.getCompletedDate());
        cv.put(NOTE_HIDDEN, note.getHidden());

        this.openWritableDB();
        long rowID = db.insert(NOTE_TABLE, null, cv);
        this.closeDB();

        return rowID;
    }

    public int updateNote(Note note) {
        ContentValues cv = new ContentValues();
        cv.put(NOTE_LIST_ID, note.getNoteId());
        cv.put(NOTE_TITLE, note.getNoteTitle());
        cv.put(NOTE_NOTES, note.getNotes());
        cv.put(NOTE_COMPLETED, note.getCompletedDate());
        cv.put(NOTE_HIDDEN, note.getHidden());

        String where = NOTE_ID + "= ?";
        String[] whereArgs = { String.valueOf(note.getNoteId()) };

        this.openWritableDB();
        int rowCount = db.update(NOTE_TABLE, cv, where, whereArgs);
        this.closeDB();

        return rowCount;
    }

    public int deleteTask(long noteId) {
        String where = NOTE_ID + "= ?";
        String[] whereArgs = { String.valueOf(noteId) };

        this.openWritableDB();
        int rowCount = db.delete(NOTE_TABLE, where, whereArgs);
        this.closeDB();

        return rowCount;
    }

}

感谢您的帮助!

Answer 1

您的问题出在这一行:

 Cursor cursor = db.query(LIST_TABLE, null, where, whereArgs, null, null, null);

由于您的 whereArgs 是传递给方法的参数，如果此参数为 null，您将尝试将 null 值绑定(bind)到? 在 where 字符串中 - 导致此错误。

正确的方法是使用两个不同的 where 子句 - 一个用于 null ，另一个用于非 null 值，尤其是考虑到语法不同。像这样:

String whereNotNull = LIST_NAME + "= ?";
String whereNull = LIST_NAME + " IS NULL";
String[] whereArgs = { list_name };

openReadableDB();
Cursor cursor = whereArgs == null 
                ? db.query(LIST_TABLE, null, whereNull, null, null, null, null)
                : db.query(LIST_TABLE, null, whereNotNull, whereArgs, null, null, null);

这同样适用于您在同一类中的 getNotes 方法。

此外，作为旁注，我强烈建议您在查询中指定列名而不是传递 null，即使您确实需要所有列也是如此。如果您确实指定了列，则可以保证游标中的列顺序与 query 方法中的列顺序相同。如果您只是为它传递 null，那么应该与它们的创建顺序相同——但从长远来看很容易把事情搞砸如果您以后需要修改数据库。

编辑:再次查看您的 AddEditActivity 代码，我怀疑您从没想过将 null 传递到方法中，并且这段代码:

currentTabName = intent.getStringExtra("tab");
listID = (int) db.getList(currentTabName).getListId();

假设该值始终存在 - 并且是一个非null String。当您清楚地得到 null 时，我建议您检查此 Intent 的启动位置以及 String extra 是如何放入其中的。您可能还想做两件事:

1. 在将 currentTabName 传递给 getList 之前检查 currentTabName 是否为 != null - 如果是则处理错误情况。

2. 在这种情况下，将额外参数的名称 (tab) 放入 static final String 变量中并从那里引用它 - 这样您就可以排除拼错的可能性。