Android SQLite Issues

Question

Android非常新，并且刚刚完成了数据库教程并遇到以下问题。谁能看到任何令人眼花的问题，让我继续解决此问题？

我的三个课程：

头等舱是主要活动类：

package com.example.sqlite;

import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.view.View;
import android.widget.SimpleCursorAdapter;
import android.widget.AdapterView.AdapterContextMenuInfo;
public class MainActivity extends ListActivity {
private DBAdapter mDBHelper;
private static final int REQUEST_ADD = 1;
private static final int REQUEST_EDIT = 2;
private Cursor mC;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

mDBHelper = new DBAdapter(this);
mDBHelper = mDBHelper.open();
refreshList();
registerForContextMenu(getListView());

}
// Option menu first
@Override
public boolean onCreateOptionsMenu(Menu menu) {
    MenuInflater inflater = getMenuInflater();
    inflater.inflate (R.menu.mymenu, menu);
    return true;
}
@Override
public boolean onOptionsItemSelected(MenuItem item) {
    switch(item.getItemId())
    {

    case R.id.miAdd:
        Intent i = new Intent ();
        i.setClass(this, Input.class);
        i.putExtra(DBAdapter.COL_ID, 0);
        startActivityForResult(i, REQUEST_ADD);
        return true;
    case R.id.miClear:
    mDBHelper.deleteAllBookmark();
    refreshList();
    return true;
    default:
    return super.onOptionsItemSelected(item);
}
}
@Override
public boolean onContextItemSelected(MenuItem item) {
    AdapterContextMenuInfo info = (AdapterContextMenuInfo) item.getMenuInfo();
    int position = info.position;
    long id = info.id;
    switch(item.getItemId())
    {
    case R.id.miEdit:
        Cursor c = mC;
        c.moveToPosition(position);

        Intent i  = new Intent();
        i.setClass(this, Input. class);
        i.putExtra(DBAdapter.COL_ID, id);
        i.putExtra(DBAdapter.COL_TITLE, c.getString(c.getColumnIndexOrThrow(DBAdapter.COL_TITLE)));
        i.putExtra(DBAdapter.COL_URL, c.getString(c.getColumnIndexOrThrow(DBAdapter.COL_URL)));
        startActivityForResult(i, REQUEST_EDIT);
        return true;

    case R.id.miDelete:
        mDBHelper.deleteBookmark(id);
        refreshList();
        return true;
        default:
        return super.onContextItemSelected(item);
    }
}
@Override
public void onCreateContextMenu(ContextMenu menu, View v,
        ContextMenuInfo menuInfo) {
    super.onCreateContextMenu(menu, v, menuInfo);
MenuInflater inflater = getMenuInflater();
inflater.inflate(R.menu.contextmenu, menu);

}
private void refreshList() 
{
    mC = mDBHelper.GetAllBookmarks();
    startManagingCursor(mC);
    SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.row,
            mC, new String[] {DBAdapter.COL_TITLE,DBAdapter.COL_URL},
            new int[] {R.id.txtText1, R.id.txtText2});
    setListAdapter(adapter);
}
@Override
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
    switch(requestCode)
    {
    case REQUEST_ADD:
        String url = data.getStringExtra(DBAdapter.COL_URL);
        String title = data.getStringExtra(DBAdapter.COL_TITLE);
        mDBHelper.createBookmark(title, url);
        refreshList();
        break;
    case REQUEST_EDIT:
        String editUrl = data.getStringExtra(DBAdapter.COL_URL);
        String editTitle = data.getStringExtra(DBAdapter.COL_TITLE);
        long updateId = data.getLongExtra(DBAdapter.COL_ID, 0);
        mDBHelper.updateBookmark(updateId, editTitle, editUrl);
        break;
        default:
            super.onActivityResult(requestCode, resultCode, data);
    }
}
}

第二类是数据库适配器类：

package com.example.sqlite;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBAdapter {
private static final String DB_NAME = "Bookmark";
private static final String DB_TABLE = "Bookmark";
private static final int DB_VERSION = 1;

private static final String DB_CREATE = 
        "CREATE TABLE Bookmark (" +
        "_id integer PRIMARY KEY AUTOINCREMENT," + 
        "Title text," +
        "url text);";
private static final String DB_UPGRADE = "DROP TABLE IF EXCISTS Bookmark";

public static final String COL_TITLE = "Title";
public static final String COL_URL = "url";
public static final String COL_ID = "id";
private SQLiteDatabase mDB;
private DBHelper mDBHelper;
private Context mCtx;

private static class DBHelper extends SQLiteOpenHelper
{
    public DBHelper (Context context)
    {
        super (context, DB_NAME, null, DB_VERSION);
    }

public void onCreate(SQLiteDatabase db)
{
    db.execSQL(DB_CREATE);

}
public void onUpgrade(SQLiteDatabase db, int newVersion, int oldVersion)
{
    db.execSQL(DB_UPGRADE);
}

}

public DBAdapter(Context ctx)
{
    mCtx = ctx;
}
public DBAdapter open()
{
    mDBHelper = new DBHelper(mCtx);
    mDB = mDBHelper.getWritableDatabase();
    return this;
}
public DBAdapter close()
{
    mDBHelper.close();
    return this;
}
public long createBookmark(String title, String url)
{
    ContentValues v = new ContentValues();
    v.put(COL_TITLE, title);
    v.put(COL_URL, url);
    return mDB.insert(DB_TABLE, null, v);
}
public boolean deleteBookmark(long id)
{
    return mDB.delete(DB_TABLE, COL_ID + "=" + id, null)>0;
}
public boolean deleteAllBookmark()
{
    return mDB.delete(DB_TABLE,null,null)>0;
}

public Cursor GetAllBookmarks()
{
    return mDB.query(DB_TABLE, 
                    new String[] {COL_ID, COL_TITLE, COL_URL}, 
                    null, null, null, null, null);
}
public Cursor GetBookmark(long id)
{
    Cursor mCursor = mDB.query(true, DB_TABLE,
            new String[] {COL_ID, COL_TITLE, COL_URL},
            COL_ID + "=" + id,
            null, null, null, null, null);
    if (mCursor != null)
        mCursor.moveToFirst();
        return mCursor;
}

public boolean updateBookmark(long id, String title, String url)
{
    ContentValues v = new ContentValues();
    v.put(COL_TITLE, title);
    v.put(COL_URL, url);
    return mDB.update(DB_TABLE, v, COL_ID + "=" + id, null)>0;
}

}

第三类是输入类：

package com.example.sqlite;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
public class Input extends Activity {
private EditText txtTitle;
private EditText txtURL;
private Button btnOK;
private long id;
private boolean bUpdateMode = false;
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    setContentView(R.layout.input);

    txtTitle = (EditText) findViewById(R.id.txtTitle);
    txtURL = (EditText) findViewById(R.id.txtURL);
    btnOK = (Button) findViewById(R.id.btnOK);
    Intent i = this.getIntent();
    id = i.getLongExtra(DBAdapter.COL_ID, 0);

    if (id == 0)
    {
        bUpdateMode = false;
    }
    else
    {
        bUpdateMode = true;
        String title = i.getStringExtra(DBAdapter.COL_TITLE);
        String url = i.getStringExtra(DBAdapter.COL_URL);
        txtTitle.setText(title);
        txtURL.setText(url);
    }
    btnOK.setOnClickListener(new View.OnClickListener() 
    {
        public void onClick (View V) 
        {
            Intent i = new Intent ();
            i.putExtra(DBAdapter.COL_URL,    txtURL.getText().toString());
            i.putExtra(DBAdapter.COL_TITLE,  txtTitle.getText().toString());
            if(bUpdateMode)
                i.putExtra(DBAdapter.COL_ID,  id);
            setResult(RESULT_OK, i);
            finish();
        }
    });
}
}       

logcat：

12-26 18:25:20.360: I/Database(271): sqlite returned: error code = 1, msg = no such   column: id
12-26 18:25:20.379: D/AndroidRuntime(271): Shutting down VM
12-26 18:25:20.379: W/dalvikvm(271): threadid=1: thread exiting with uncaught exception (group=0x4001d800)
12-26 18:25:20.419: E/AndroidRuntime(271): FATAL EXCEPTION: main
12-26 18:25:20.419: E/AndroidRuntime(271): java.lang.RuntimeException: Unable to start   activity ComponentInfo{com.example.sqlite/com.example.sqlite.MainActivity}:   android.database.sqlite.SQLiteException: no such column: id: , while compiling: SELECT id,  Title, url FROM Bookmark
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2663)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.app.ActivityThread.access$2300(ActivityThread.java:125)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.os.Handler.dispatchMessage(Handler.java:99)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.os.Looper.loop(Looper.java:123)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.app.ActivityThread.main(ActivityThread.java:4627)
12-26 18:25:20.419: E/AndroidRuntime(271):  at java.lang.reflect.Method.invokeNative(Native Method)
12-26 18:25:20.419: E/AndroidRuntime(271):  at java.lang.reflect.Method.invoke(Method.java:521)
12-26 18:25:20.419: E/AndroidRuntime(271):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
12-26 18:25:20.419: E/AndroidRuntime(271):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
12-26 18:25:20.419: E/AndroidRuntime(271):  at dalvik.system.NativeStart.main(Native Method)
12-26 18:25:20.419: E/AndroidRuntime(271): Caused by: android.database.sqlite.SQLiteException: no such column: id: , while compiling: SELECT id, Title, url FROM Bookmark
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:46)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1345)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1229)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1184)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1264)
12-26 18:25:20.419: E/AndroidRuntime(271):  at com.example.sqlite.DBAdapter.GetAllBookmarks(DBAdapter.java:105)
12-26 18:25:20.419: E/AndroidRuntime(271):  at com.example.sqlite.MainActivity.refreshList(MainActivity.java:141)
12-26 18:25:20.419: E/AndroidRuntime(271):  at com.example.sqlite.MainActivity.onCreate(MainActivity.java:33)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
12-26 18:25:20.419: E/AndroidRuntime(271):  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
12-26 18:25:20.419: E/AndroidRuntime(271):  ... 11 more
12-26 18:25:32.789: I/Process(271): Sending signal. PID: 271 SIG: 9

Answer 1

SQLiteException: no such column: id: , while compiling: SELECT id,  Title, url FROM Bookmark

您正在尝试选择"id"，但您的列被命名为"_id"：

private static final String DB_CREATE = 
        "CREATE TABLE Bookmark (" +
        "_id integer PRIMARY KEY AUTOINCREMENT," + 
        "Title text," +
        "url text);";

您需要更改COL_ID以包括下划线：

public static final String COL_ID = "_id";

Answer 2

如log所说：

由：android.database.sqlite.sqliteException：没有这样的列： id：，在编译时：选择ID，标题，从书签中的URL

表示'id列中不存在于当前表中。更改您的

public static final String COL_ID = "id";

to

public static final String COL_ID = "_id";