执行原始查询时未找到Android SQLite列错误

Question

我正在制作一个搜索SQLite数据库中的餐厅的android应用程序。我目前使用自定义加载器（我在线找到）从数据库加载游标，然后使用该游标并使用SimpleCursorAdapter填充列表视图。我想要绑定所有数据，但只显示要显示的餐厅名称（因为当我单击列表项时，我想要获取所有数据并将其发送到显示数据的新活动）。如果我指定的餐馆名称和餐厅类型的搜索参数，我跑了原始查询：

"SELECT * FROM Restaurant WHERE name = " + name + " AND rType = " + restaurantType; 

在我SQLiteCursorLoader类，我得到一个错误，指出该列舍入类型不存在，甚至以为我知道列存在于Restuarant表中。看起来我得到这个错误，当我有两个WHERE子句在我的查询，但是这不会发生，当我有一个WHERE子句。任何帮助将不胜感激！这里是我的代码：

RestuarantSearchResultsActivity：

package com.studentapplication; 
import android.app.Activity; 
import android.app.ListActivity; 
import android.app.LoaderManager; 
import android.content.Loader; 
import android.database.Cursor; 
import android.os.Bundle; 
import android.util.Log; 
import android.view.View; 
import android.widget.ListView; 
import android.widget.SimpleCursorAdapter; 

public class RestaurantSearchResultsActivity extends ListActivity implements 
LoaderManager.LoaderCallbacks<Cursor>{ 

DatabaseHelper databaseHelper; 
SimpleCursorAdapter mAdapter; 
Bundle searchParameters; 

@Override 
public void onCreate(Bundle savedInstanceState) 
{ 
    Log.w("CoolBeans", "Results Activity Created"); 
    super.onCreate(savedInstanceState); 

    databaseHelper = new DatabaseHelper(getApplicationContext()); 
    //setContentView(R.layout.restaurant_search_results); 

    Bundle searchParameters = getIntent().getExtras(); 
    String n = searchParameters.getString("name"); 
    Log.i("0)", n); 


    //first N columns should match toViews first N views 
    String[] fromColumns = {getString(R.string.database_column_name), 
          getString(R.string.database_column_restaurant_type), 
          getString(R.string.database_column_phone_number), 
          getString(R.string.database_column_address), 
       getString(R.string.database_column_csz), 
       getString(R.string.database_column_brief_description), 
       getString(R.string.database_column_url), 
       getString(R.string.database_column_price)}; 

    int[] toViews = {android.R.id.text1}; 

    mAdapter = new SimpleCursorAdapter(getApplicationContext(), 
         this.getListView().getId(), 
         null, 
          fromColumns, 
         toViews, 
         0); 
    setListAdapter(mAdapter); 
    getLoaderManager().initLoader(0, searchParameters, this);  
} 

@Override 
public Loader<Cursor> onCreateLoader(int id, Bundle searchParameters) { 
    Log.i("3)", "Correct Loader Being Built"); 
    return new SQLiteCursorLoader(getApplicationContext(), 
      databaseHelper, 
      searchParameters, 
      null); 

} 

@Override 
public void onLoadFinished(Loader<Cursor> loader, Cursor data) { 
    mAdapter.swapCursor(data); 

} 

@Override 
public void onLoaderReset(Loader<Cursor> loader) { 
    mAdapter.swapCursor(null); 

} 

@Override 
    public void onListItemClick(ListView l, View v, int position, long id) 
{ 
    Cursor myCursor = (Cursor)l.getItemAtPosition(position); 
    if(l.isItemChecked(position) == true) 
    { 
     l.setItemChecked(position, false); 
     //shut down previously selected item details 

    } 
    else 
    { 
     l.setItemChecked(position, true); 

     //start previously selected item details 

    } 
} 


} 

SQLiteCursorLoader：

package com.studentapplication; 

import java.io.FileDescriptor; 
import java.io.PrintWriter; 
import java.util.Arrays; 
import android.content.ContentValues; 
import android.content.Context; 
import android.content.res.Resources; 
import android.database.Cursor; 
import android.database.sqlite.SQLiteDatabase; 
import android.database.sqlite.SQLiteOpenHelper; 
import android.database.sqlite.SQLiteQueryBuilder; 
import android.os.Bundle; 
import android.util.Log; 

public class SQLiteCursorLoader extends AbstractCursorLoader 
{ 
SQLiteOpenHelper db=null; 
String rawQuery=null; 
String[] args=null; 

/** 
* Creates a fully-specified SQLiteCursorLoader. See 
* {@link SQLiteDatabase#rawQuery(SQLiteDatabase, String, String[]) 
* SQLiteDatabase.rawQuery()} for documentation on the 
* meaning of the parameters. These will be passed as-is 
* to that call. 
*/ 
public SQLiteCursorLoader(Context context, SQLiteOpenHelper db, Bundle 
    searchParameters, String[] args) 
{ 
    super(context); 
    this.db=db; 
    this.rawQuery=createQuery(searchParameters); 
    this.args=args; 
} 

private String createQuery(Bundle searchParameters) { 
    String name = "\'"; 
    name = name + searchParameters.getString("name"); 
    name = name + "\'"; 
    int price = searchParameters.getInt("price"); 
    String restaurantType = "\'"; 
    restaurantType = restaurantType + 
      searchParameters.getString("restaurant_type"); 
    restaurantType = restaurantType + "\'"; 

    //String dist = searchParameters.getString("dist"); 

    String theQuery = ""; 

    if(!name.equals("\'\'")) 
    { 
     if(!restaurantType.equals("\'_\'")) 
     { 
      theQuery = "SELECT * FROM Restaurant WHERE name 
= "+ name + " AND rType = " + restaurantType; 
     } 
     else if(price != 0) 
     { 
      theQuery = "SELECT * FROM Restaurant WHERE name = " + name 
+ " AND price = " + ((Integer)price).toString(); 
     } 
     else 
     { 
      theQuery="SELECT * FROM Restaurant WHERE name = " + name; 
     } 
    } 
    else if(!restaurantType.equals("\'\'")) 
    { 
     theQuery = "SELECT * FROM Restaurant WHERE type = " + 
restaurantType + " SORT BY name"; 
    } 
    else if(price != 0) 
    { 
     theQuery="SELECT * FROM Restaurant WHERE price=" + price + " SORT 
BY name"; 
    } 



    return theQuery; 
} 


/** 
* Runs on a worker thread and performs the actual 
* database query to retrieve the Cursor. 
*/ 
@Override 
protected Cursor buildCursor() 
{ 
    return(db.getReadableDatabase().rawQuery(rawQuery, args)); 
} 

/** 
* Writes a semi-user-readable roster of contents to 
* supplied output. 
*/ 
@Override 
public void dump(String prefix, FileDescriptor fd, PrintWriter writer, String[] 
    args) 
{ 
    super.dump(prefix, fd, writer, args); 
    writer.print(prefix); 
    writer.print("rawQuery="); 
    writer.println(rawQuery); 
    writer.print(prefix); 
    writer.print("args="); 
    writer.println(Arrays.toString(args)); 
} 

public void insert(String table, String nullColumnHack, ContentValues values) 
{ 
    new InsertTask(this).execute(db, table, nullColumnHack, values); 
} 

public void update(String table, ContentValues values, String whereClause, String[] 
    whereArgs) 
{ 
    new UpdateTask(this).execute(db, table, values, whereClause, whereArgs); 
} 

public void replace(String table, String nullColumnHack, ContentValues values) 
{ 
    new ReplaceTask(this).execute(db, table, nullColumnHack, values); 
} 

public void delete(String table, String whereClause, String[] whereArgs) 
{ 
    new DeleteTask(this).execute(db, table, whereClause, whereArgs); 
} 

public void execSQL(String sql, Object[] bindArgs) 
{ 
    new ExecSQLTask(this).execute(db, sql, bindArgs); 
} 

//Private Classes 

private class InsertTask extends ContentChangingTask<Object, Void, Void> 
{ 
    InsertTask(SQLiteCursorLoader loader) 
    { 
     super(loader); 
    } 

    @Override 
    protected Void doInBackground(Object... params) 
    { 
     SQLiteOpenHelper db=(SQLiteOpenHelper)params[0]; 
     String table=(String)params[1]; 
     String nullColumnHack=(String)params[2]; 
     ContentValues values=(ContentValues)params[3]; 

     db.getWritableDatabase().insert(table, nullColumnHack, values); 

     return(null); 
    } 
} 

private class UpdateTask extends ContentChangingTask<Object, Void, Void> 
{ 
    UpdateTask(SQLiteCursorLoader loader) 
    { 
     super(loader); 
    } 

    @Override 
    protected Void doInBackground(Object... params) 
    { 
     SQLiteOpenHelper db=(SQLiteOpenHelper)params[0]; 
     String table=(String)params[1]; 
     ContentValues values=(ContentValues)params[2]; 
     String where=(String)params[3]; 
     String[] whereParams=(String[])params[4]; 

     db.getWritableDatabase().update(table, values, where, whereParams); 

     return(null); 
    } 
} 

private class ReplaceTask extends ContentChangingTask<Object, Void, Void> 
{ 
    ReplaceTask(SQLiteCursorLoader loader) 
    { 
     super(loader); 
    } 

    @Override 
    protected Void doInBackground(Object... params) 
    { 
     SQLiteOpenHelper db=(SQLiteOpenHelper)params[0]; 
     String table=(String)params[1]; 
     String nullColumnHack=(String)params[2]; 
     ContentValues values=(ContentValues)params[3]; 

     db.getWritableDatabase().replace(table, nullColumnHack, values); 

     return(null); 
    } 
} 

private class DeleteTask extends ContentChangingTask<Object, Void, Void> 
{ 
    DeleteTask(SQLiteCursorLoader loader) 
    { 
     super(loader); 
    } 

    @Override 
    protected Void doInBackground(Object... params) 
    { 
     SQLiteOpenHelper db=(SQLiteOpenHelper)params[0]; 
     String table=(String)params[1]; 
     String where=(String)params[2]; 
     String[] whereParams=(String[])params[3]; 

     db.getWritableDatabase().delete(table, where, whereParams); 

     return(null); 
    } 
} 

private class ExecSQLTask extends ContentChangingTask<Object, Void, Void> 
{ 
    ExecSQLTask(SQLiteCursorLoader loader) 
    { 
     super(loader); 
    } 

    @Override 
    protected Void doInBackground(Object... params) 
    { 
     SQLiteOpenHelper db=(SQLiteOpenHelper)params[0]; 
     String sql=(String)params[1]; 
     Object[] bindParams=(Object[])params[2]; 

     db.getWritableDatabase().execSQL(sql, bindParams); 

    return(null); 
    } 
} 
} 

AbstractCursorLoader：

package com.studentapplication; 

import android.annotation.TargetApi; 
import android.content.AsyncTaskLoader; 
import android.content.Context; 
import android.database.Cursor; 
import android.os.Build; 

@TargetApi(Build.VERSION_CODES.HONEYCOMB) 
abstract public class AbstractCursorLoader extends AsyncTaskLoader<Cursor> { 

abstract protected Cursor buildCursor(); 
Cursor lastCursor=null; 

public AbstractCursorLoader(Context context) { 
    super(context); 
} 

/** 
* Runs on a worker thread, loading in our data. Delegates 
* the real work to concrete subclass' buildCursor() method. 
*/ 
@Override 
public Cursor loadInBackground() { 
    Cursor cursor=buildCursor(); 

    if (cursor!=null) { 
    // Ensure the cursor window is filled 
    cursor.getCount(); 
    } 

    return(cursor); 
} 

/** 
* Runs on the UI thread, routing the results from the 
* background thread to whatever is using the Cursor 
* (e.g., a CursorAdapter). 
*/ 
@Override 
public void deliverResult(Cursor cursor) { 
    if (isReset()) { 
    // An async query came in while the loader is stopped 
    if (cursor!=null) { 
     cursor.close(); 
    } 

    return; 
    } 

    Cursor oldCursor=lastCursor; 
    lastCursor=cursor; 

    if (isStarted()) { 
    super.deliverResult(cursor); 
    } 

    if (oldCursor!=null && oldCursor!=cursor && !oldCursor.isClosed()) { 
    oldCursor.close(); 
    } 
} 

/** 
* Starts an asynchronous load of the list data. 
* When the result is ready the callbacks will be called 
* on the UI thread. If a previous load has been completed 
* and is still valid the result may be passed to the 
* callbacks immediately. 
* 
* Must be called from the UI thread. 
*/ 
@Override 
protected void onStartLoading() { 
    if (lastCursor!=null) { 
    deliverResult(lastCursor); 
    } 

    if (takeContentChanged() || lastCursor==null) { 
    forceLoad(); 
    } 
} 

/** 
* Must be called from the UI thread, triggered by a 
* call to stopLoading(). 
*/ 
@Override 
protected void onStopLoading() { 
    // Attempt to cancel the current load task if possible. 
    cancelLoad(); 
} 

/** 
* Must be called from the UI thread, triggered by a 
* call to cancel(). Here, we make sure our Cursor 
* is closed, if it still exists and is not already closed. 
*/ 
@Override 
public void onCanceled(Cursor cursor) { 
    if (cursor!=null && !cursor.isClosed()) { 
    cursor.close(); 
    } 
} 

/** 
* Must be called from the UI thread, triggered by a 
* call to reset(). Here, we make sure our Cursor 
* is closed, if it still exists and is not already closed. 
*/ 
@Override 
protected void onReset() { 
    super.onReset(); 

    // Ensure the loader is stopped 
    onStopLoading(); 

    if (lastCursor!=null && !lastCursor.isClosed()) { 
    lastCursor.close(); 
    } 

    lastCursor=null; 
    } 
    } 

DatabaseHelper：

package com.studentapplication; 

import android.content.Context; 
import android.database.sqlite.SQLiteDatabase; 
import android.database.sqlite.SQLiteDatabase.CursorFactory; 
import android.database.sqlite.SQLiteOpenHelper; 

public class DatabaseHelper extends SQLiteOpenHelper { 

// All Static variables 
// Database Version 
private static final int DATABASE_VERSION = 1; 

// Database Name 
private static final String DATABASE_NAME = "LOCAL_DATABASE"; 

// table Names 
private static final String TABLE_Restaurant = "Restaurant"; 
private static final String TABLE_Coupon = "Coupon"; 
private static final String TABLE_Linker = "Linker"; 

//restaurant columns 
private static final String R_Name = "name"; 
private static final String R_Description = "description"; 
private static final String R_Address = "address"; 
private static final String R_CSZ = "CSZ"; 
private static final String R_URL = "url"; 
private static final String R_Price = "price"; 
private static final String R_Type = "rType"; 
private static final String R_ID = "_id"; 

//coupon columns 
private static final String C_ID = "_id"; 
private static final String C_NAME = "name"; 
private static final String C_Description = "description"; 

//linker table columns 
private static final String r_id = "r_id"; 
private static final String c_id = "c_id"; 
private static final String date = "date"; 
private static final String l_id = "l_id"; 

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

} 

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

// Creating Tables 
@Override 
public void onCreate(SQLiteDatabase db) { 
    String CREATE_Restaurant_TABLE = "CREATE TABLE " + TABLE_Restaurant + "(" 
      + R_ID + " INTEGER PRIMARY KEY, " 
      + R_Name + " TEXT NOT NULL, " 
      + R_Type + " TEXT NOT NULL, " 
      + R_Address + " TEXT NOT NULL, " 
      + R_CSZ + " TEXT NOT NULL, " 
      + R_Description + " TEXT NOT NULL, " 
      + R_URL + " TEXT NOT NULL, " 
      + R_Price +" INTEGER NOT NULL" 
      + ")"; 

    String CREATE_Coupon_TABLE = "CREATE TABLE " + TABLE_Coupon + "(" 
      + C_ID + " INTEGER PRIMARY KEY, " 
      + C_NAME + " TEXT, " 
      + C_Description + " TEXT" 
      + ")"; 

    String CREATE_Linker_TABLE = "CREATE TABLE " + TABLE_Linker + "(" 
      + r_id + " INTEGER, " 
      + c_id + " INTEGER, " 
      + date + " DATE, " 
      + l_id + "INTEGER PRIMARY KEY, " 
      + "FOREIGN KEY(r_id) REFERENCES Restaurant(_id), " 
      + "FOREIGN KEY(c_id) REFERENCES Coupon(_id))"; 

    db.execSQL(CREATE_Restaurant_TABLE); 
    db.execSQL(CREATE_Coupon_TABLE); 
    db.execSQL(CREATE_Linker_TABLE); 
} 

// Upgrading database 
@Override 
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
    // Drop older table if existed 
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_Restaurant); 
    // Create tables again 
    onCreate(db); 
} 

} 

堆栈跟踪：

04-23 13:47:29.691: E/SQLiteLog(2600): (1) no such column: rType 
04-23 13:47:29.691: W/dalvikvm(2600): threadid=11: thread exiting with uncaught 
    exception (group=0x40a13300) 
04-23 13:47:29.852: E/AndroidRuntime(2600): FATAL EXCEPTION: AsyncTask #1 
04-23 13:47:29.852: E/AndroidRuntime(2600): java.lang.RuntimeException: An error 
    occured while executing doInBackground() 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.os.AsyncTask$3.done(AsyncTask.java:299) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    java.util.concurrent.FutureTask$Sync.innerSetException(FutureTask.java:273) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    java.util.concurrent.FutureTask.setException(FutureTask.java:124) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:307) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    java.util.concurrent.FutureTask.run(FutureTask.java:137) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1076) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:569) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    java.lang.Thread.run(Thread.java:856) 
04-23 13:47:29.852: E/AndroidRuntime(2600): Caused by: 
    android.database.sqlite.SQLiteException: no such column: rType (code 1): , while 
    compiling: SELECT * FROM Restaurant WHERE name = 'RName' AND rType = 'Fancy' 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.database.sqlite.SQLiteConnection.acquirePreparedStatement 
    (SQLiteConnection.java:882) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.database.sqlite.SQLiteDirectCursorDriver.query 
    (SQLiteDirectCursorDriver.java:44) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    com.studentapplication.SQLiteCursorLoader.buildCursor(SQLiteCursorLoader.java:88) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    com.studentapplication.AbstractCursorLoader.loadInBackground 
    (AbstractCursorLoader.java:24) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    com.studentapplication.AbstractCursorLoader.loadInBackground 
    (AbstractCursorLoader.java:1) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.content.AsyncTaskLoader.onLoadInBackground(AsyncTaskLoader.java:301) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:68) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:56) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    android.os.AsyncTask$2.call(AsyncTask.java:287) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  at 
    java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305) 
04-23 13:47:29.852: E/AndroidRuntime(2600):  ... 4 more 
04-23 13:47:29.902: W/ActivityManager(161): Force finishing activity 
    com.studentapplication/.RestaurantSearchResultsActivity 
04-23 13:47:29.902: W/WindowManager(161): Failure taking screenshot for (266x425) to 
    layer 21020 

Answer 1

在生产中，当您修改数据库的模式，你碰到你传递给SQLiteOpenHelper构造架构版本号，所以SQLiteOpenHelper会叫onUpgrade()和影响的变化。

在开发中，如果你愿意，你也可以这样做。但是，很容易忘记这一点。因此，如果你的数据库结构似乎并没有被匹配的期望，无论是：

• 跳车该架构版本号，以强制onUpgrade()呼叫，或

• 消灭现有的数据库，通过卸载“清除数据”从设置等应用的进入，迫使调用的onCreate()您SQLiteOpenHelper