java - 如何在 Android 中将 IN 运算符与 SQLite 一起使用？

Question

我已将状态列添加到我的数据库表中，我想搜索结果并显示所有状态为打开的员工姓名。当我运行应用程序时，编译查询时出现异常 SQliteException no such column xxx。

我不知道该怎么做。

这是我的代码:

public void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.search_data);

        searchSpinner=(Spinner)findViewById(R.id.searchSpinner);
        btnSearch=(Button)findViewById(R.id.searchButton);
        newLeadDat_List = (ListView)findViewById(R.id.listView_DisplayData);

        displayNewLeadData();
        System.out.println("Data Displayed Succesfully!!!!!!!!!");

        searchSpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener()
        {
            @Override
            public void onItemSelected(AdapterView<?> parent, View v,
                    int position, long id) {
                // TODO Auto-generated method stub
                selectedSearchItem=parent.getItemAtPosition(position).toString().trim();
                System.out.println("selectedProductItem =" + selectedSearchItem);

            }

            @Override
            public void onNothingSelected(AdapterView<?> arg0) {
                // TODO Auto-generated method stub  
            }
        });

        btnSearch.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View arg0)
            {
                dbAdapter=new DataBase_Adapter(Serach_Data.this).open();
                dataBase = dbAdapter.getDatabaseInstance();


                    String query = "SELECT name FROM "+ DataBase_Adapter.TABLE_NEW_LEAD +" where "+ DataBase_Adapter.KEY_NEW_LEAD_STATUS +" IN  ("+ selectedSearchItem + ")";

                    cursor = dataBase.rawQuery(query , null);
                    adapter = new SimpleCursorAdapter(Serach_Data.this, 
                              R.layout.search_data_item_listview, 
                              cursor, 
                              new String[ ] {"name"}, 
                              new int[ ] {R.id.textView2_Name});

                    new_Lead_List_Adapter = new New_Lead_List_Adapter(Serach_Data.this  ,
                                 arrayList_newLead_Name );

                    newLeadDat_List.setAdapter(adapter);    
            }
        });
    }

    public void displayNewLeadData() 
    {
        dbAdapter=new DataBase_Adapter(Serach_Data.this).open();
        dataBase = dbAdapter.getDatabaseInstance();

        Cursor mCursor = dataBase.rawQuery("SELECT name FROM " + DataBase_Adapter.TABLE_NEW_LEAD, null);

        mCursor.moveToFirst();

        arrayList_newLead_Name.clear();

        {
            do 
            {   
                  arrayList_newLead_Name.add(mCursor.getString(mCursor.getColumnIndex(DataBase_Adapter.KEY_NEW_LEAD_NAME)));
            } while (mCursor.moveToNext());
        }

        new_Lead_List_Adapter = new New_Lead_List_Adapter(Serach_Data.this  ,
                                                        arrayList_newLead_Name );
        newLeadDat_List.setAdapter(new_Lead_List_Adapter);
        new_Lead_List_Adapter.notifyDataSetChanged();
        mCursor.close();
        System.out.printf("Data will Be Display." , new_Lead_List_Adapter);

    }
}

这是我的 Log Cat 堆栈跟踪信息。

12-13 14:43:37.880: I/System.out(528): selectedProductItem =open
12-13 14:43:39.760: I/Database(528): sqlite returned: error code = 1, msg = no such column: open
12-13 14:43:39.760: D/AndroidRuntime(528): Shutting down VM
12-13 14:43:39.760: W/dalvikvm(528): threadid=1: thread exiting with uncaught exception (group=0x40015560)
12-13 14:43:39.810: E/AndroidRuntime(528): FATAL EXCEPTION: main
12-13 14:43:39.810: E/AndroidRuntime(528): android.database.sqlite.SQLiteException: no such column: open: , while compiling: SELECT name FROM new_lead where status IN  (open)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1356)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1324)
12-13 14:43:39.810: E/AndroidRuntime(528):  at com.lead_management_project.Serach_Data$2.onClick(Serach_Data.java:86)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.view.View.performClick(View.java:2485)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.view.View$PerformClick.run(View.java:9080)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.os.Handler.handleCallback(Handler.java:587)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.os.Handler.dispatchMessage(Handler.java:92)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.os.Looper.loop(Looper.java:123)
12-13 14:43:39.810: E/AndroidRuntime(528):  at android.app.ActivityThread.main(ActivityThread.java:3683)
12-13 14:43:39.810: E/AndroidRuntime(528):  at java.lang.reflect.Method.invokeNative(Native Method)
12-13 14:43:39.810: E/AndroidRuntime(528):  at java.lang.reflect.Method.invoke(Method.java:507)
12-13 14:43:39.810: E/AndroidRuntime(528):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
12-13 14:43:39.810: E/AndroidRuntime(528):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)
12-13 14:43:39.810: E/AndroidRuntime(528):  at dalvik.system.NativeStart.main(Native Method)

Answer 1

尝试

" where "+ DataBase_Adapter.KEY_NEW_LEAD_STATUS +" IN  ('"+ selectedSearchItem + "')";

我假设您的专栏是某种字符串类型。

如果您有多个状态要查询，则
你需要遍历你的状态，在这个循环中你应该附加
1) '
2) 状态值(一些字符串，如打开、关闭等)
3) '
4) , (逗号)
到某个 StringBuilder 对象，然后删除最后一个 ,(逗号)，然后删除
从此 StringBuilder 对象生成一个 IN 子句。并确保你
产生至少一种状态，因为“IN ()”在 SQL 中无效。