android - 在 sqlite 中搜索，基于 String 获取描述

Question

我想在我的数据库中使用搜索。所以我使用了几个代码来标记它们。我使用 2 行，一个用于名称，另一个用于名称描述。(类似于字典)。现在这是我的 code这是我的helper用于搜索。带有 new 注释的行，我将它们手动添加到代码中。

public class SQLiteCountryAssistant extends SQLiteOpenHelper
{
private static final String DB_NAME = "usingsqlite.db";
private static final int DB_VERSION_NUMBER = 1;
private static final String DB_TABLE_NAME = "countries";
private static final String DB_COLUMN_1_NAME = "country_name";
private static final String DB_COLUMN_2_DESCRIPTION = "country_description"; //new

private static final String DB_CREATE_SCRIPT = "create table " + DB_TABLE_NAME +
                        " (_id integer primary key autoincrement, " +
                        "country_name text not null);)";

private SQLiteDatabase sqliteDBInstance = null;

public SQLiteCountryAssistant(Context context)
{
    super(context, DB_NAME, null, DB_VERSION_NUMBER);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
    // TODO: Implement onUpgrade
}

@Override
public void onCreate(SQLiteDatabase sqliteDBInstance)
{
    Log.i("onCreate", "Creating the database...");
    sqliteDBInstance.execSQL(DB_CREATE_SCRIPT);
}

public void openDB() throws SQLException
{
    Log.i("openDB", "Checking sqliteDBInstance...");
    if(this.sqliteDBInstance == null)
    {
        Log.i("openDB", "Creating sqliteDBInstance...");
        this.sqliteDBInstance = this.getWritableDatabase();
    }
}

public void closeDB()
{
    if(this.sqliteDBInstance != null)
    {
        if(this.sqliteDBInstance.isOpen())
            this.sqliteDBInstance.close();
    }
}

public long insertCountry(String countryName, String Discription) //new
{
    ContentValues contentValues = new ContentValues();
    contentValues.put(DB_COLUMN_1_NAME, countryName);
    contentValues.put(DB_COLUMN_2_DESCRIPTION, Discription); //new
    Log.i(this.toString() + " - insertCountry", "Inserting: " + countryName);
    Log.i(this.toString() + " - insertDescription", "Inserting: " + Discription); //new
    return this.sqliteDBInstance.insert(DB_TABLE_NAME, null, contentValues);
}

public boolean removeCountry(String countryName)
{
    int result = this.sqliteDBInstance.delete(DB_TABLE_NAME, "country_name='" 
            + countryName + "'", null);

    if(result > 0)
        return true;
    else
        return false;
}

public long updateCountry(String oldCountryName, String newCountryName, String Description)
{ //new
    ContentValues contentValues = new ContentValues();
    contentValues.put(DB_COLUMN_1_NAME, newCountryName);
    contentValues.put(DB_COLUMN_2_DESCRIPTION, Description); //new
    return this.sqliteDBInstance.update(DB_TABLE_NAME, contentValues, "country_name='" 
            + oldCountryName + "'", null);
}

public String[] getAllCountries()
{
    Cursor cursor = this.sqliteDBInstance.query(DB_TABLE_NAME, 
            new String[] {DB_COLUMN_1_NAME}, null, null, null, null, null);

    if(cursor.getCount() >0)
    {
        String[] str = new String[cursor.getCount()];
        int i = 0;

        while (cursor.moveToNext())
        {
             str[i] = cursor.getString(cursor.getColumnIndex(DB_COLUMN_1_NAME));
             i++;
         }
        return str;
    }
    else
    {
        return new String[] {};
    }
}

public String[] getAllDescriptions()
{
    Cursor cursor = this.sqliteDBInstance.query(DB_TABLE_NAME, 
            new String[] {DB_COLUMN_2_DESCRIPTION}, null, null, null, null, null);

    if(cursor.getCount() > 0)
    {
        String[] str = new String[cursor.getCount()];
        int i = 0;

        while (cursor.moveToNext())
        {
             str[i] = cursor.getString(cursor.getColumnIndex(DB_COLUMN_2_DESCRIPTION));
             i++;
         }
        return str;
    }
    else
    {
        return new String[] {};
    }
}

/*** new ***/
public String getDescription(String username) {
    Cursor cursor = this.sqliteDBInstance.query(DB_TABLE_NAME, 
            new String[] {DB_COLUMN_2_DESCRIPTION}, null, null, null, null, null);


    if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
        return null;
    }

    String DES = new String(); 
    DES = cursor.getString(cursor.getColumnIndex(DB_COLUMN_2_DESCRIPTION));
    return DES;
}  
}

在 /*** new ***/ 的最后一个函数中，我使用了搜索代码。我想我的问题一定出在这里...

public class UsingSQLite extends Activity
{
private SQLiteCountryAssistant sqlliteCountryAssistant;

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

    final AutoCompleteTextView textView = (AutoCompleteTextView) 
        findViewById(R.id.autocompleteCountry);

    sqlliteCountryAssistant = new SQLiteCountryAssistant(UsingSQLite.this);
    sqlliteCountryAssistant.openDB();

    // Insert a few countries that begin with "C"
    sqlliteCountryAssistant.insertCountry("Cambodia", "good country");
    sqlliteCountryAssistant.insertCountry("Cameroon", "where is it??");
    sqlliteCountryAssistant.insertCountry("Canada", "i like it :D");
    sqlliteCountryAssistant.insertCountry("Cape Verde", "!!!!!");
    sqlliteCountryAssistant.insertCountry("Cayman Islands", "it must be an island");
    sqlliteCountryAssistant.insertCountry("Chad", "what is that?");
    sqlliteCountryAssistant.insertCountry("Chile", "really, i don't know it!");
    sqlliteCountryAssistant.insertCountry("China", "TOO much big...");

    //sqlliteCountryAssistant.removeCountry("Chad");
    //sqlliteCountryAssistant.updateCountry("Canada", "Costa Rica");

    String[] countries = sqlliteCountryAssistant.getAllCountries();

    // Print out the values to the log
    for(int i = 0; i < countries.length; i++)
    {
        Log.i(this.toString(), countries[i]);
    }

    ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, R.layout.list_item, 
            countries);

    textView.setAdapter(adapter);
    textView.setOnItemClickListener(new OnItemClickListener() {

        @Override
        public void onItemClick(AdapterView<?> parent, View view, int position,
                long rowId) {
            String selection = (String) parent.getItemAtPosition(position);
            TextView tv1 = (TextView) findViewById(R.id.textView1);
            tv1.setText("Description : " + sqlliteCountryAssistant.getDescription(selection));
        }
    });

}

public void onDestroy()
{
    super.onDestroy();
    sqlliteCountryAssistant.close();
}
}

这是我的代码。我输入我的单词，单词出现然后我触摸它。在这个位置，我必须看到描述。但我得到错误。这是我的日志猫。

08-04 14:14:26.713: D/dalvikvm(25317): GC_EXTERNAL_ALLOC freed 56K, 52% free 2741K/5639K,      external 264K/519K, paused 79ms
08-04 14:14:28.715: I/Database(25317): sqlite returned: error code = 1, msg = no such column: country_description
08-04 14:14:28.715: D/AndroidRuntime(25317): Shutting down VM
08-04 14:14:28.715: W/dalvikvm(25317): threadid=1: thread exiting with uncaught exception (group=0x401ec560)
08-04 14:14:28.735: E/AndroidRuntime(25317): FATAL EXCEPTION: main
08-04 14:14:28.735: E/AndroidRuntime(25317): android.database.sqlite.SQLiteException: no such column: country_description: , while compiling: SELECT country_description FROM countries
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1356)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1235)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1189)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1271)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at com.usingsqlite.SQLiteCountryAssistant.getDescription(SQLiteCountryAssistant.java:140)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at com.usingsqlite.UsingSQLite$1.onItemClick(UsingSQLite.java:61)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.widget.AutoCompleteTextView.performCompletion(AutoCompleteTextView.java:952)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.widget.AutoCompleteTextView.access$1400(AutoCompleteTextView.java:92)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.widget.AutoCompleteTextView$DropDownItemClickListener.onItemClick(AutoCompleteTextView.java:1489)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.widget.AdapterView.performItemClick(AdapterView.java:284)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.widget.ListView.performItemClick(ListView.java:3513)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.widget.AbsListView$PerformClick.run(AbsListView.java:1849)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.os.Handler.handleCallback(Handler.java:587)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.os.Handler.dispatchMessage(Handler.java:92)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.os.Looper.loop(Looper.java:130)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at android.app.ActivityThread.main(ActivityThread.java:3835)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at java.lang.reflect.Method.invokeNative(Native Method)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at java.lang.reflect.Method.invoke(Method.java:507)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:858)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
08-04 14:14:28.735: E/AndroidRuntime(25317):    at dalvik.system.NativeStart.main(Native Method)
08-04 14:14:30.957: I/Process(25317): Sending signal. PID: 25317 SIG: 9

我希望有人能帮助我。

Answer 1

[1] 首先从您的 AVD 中删除创建的数据库

或者如果您已经在真实设备上安装了它，那么从设备上卸载它

因为已经在那里创建的表没有列 DB_COLUMN_2_DESCRIPTION("country_description")

[2] 如下更新您的 DB_CREATE_SCRIPT

private static final String DB_CREATE_SCRIPT = "CREATE TABLE " + DB_TABLE_NAME
     + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
     + DB_COLUMN_1_NAME + " TEXT, "
     + DB_COLUMN_2_DESCRIPTION + " TEXT);";

然后从开始 Activity 安装并运行整个应用程序

---

编辑对于你的 bean 类可以是一个 java 文件 Countries.java::

public Countries {
    String name, desc;

    public Countries(String name, String desc) {
         this.name = name;
         this.desc = desc;
    }
}

当您填充游标时，从数据库表中读取名称和描述的数据。

然后从中填写你的

ArrayList<Countries> countriesList = new ArrayList<Countries>();

使用游标的迭代器。

当你想制作搜索数据时，然后从这个填充的countriesList中搜索它。