java - 让 SQLite 在我的 Android 应用程序中运行时遇到问题

Question

我在让 SQLite 在我正在开发的 Android 应用程序中正常工作时遇到问题。似乎没有根据 LogCat 消息正确创建该表。我感觉我盯着屏幕看太久了。这里有人能发现我的代码中的问题吗？

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {

    // table name
    public static final String TABLE_JOKES = "jokes";

    // database field names
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_CATEGORY = "category";
    public static final String COLUMN_SUBCATEGORY = "subcategory";
    public static final String COLUMN_JOKE_TYPE = "jokeType";
    public static final String COLUMN_DESCRIPTION = "description";
    public static final String COLUMN_QUESTION_TEXT = "questionText";
    public static final String COLUMN_ANSWER_TEXT = "answerText";
    public static final String COLUMN_MONOLOGUE_TEXT = "monologueText";
    public static final String COLUMN_RATING_SCALE = "ratingScale";
    public static final String COLUMN_COMMENTS = "comments";
    public static final String COLUMN_JOKE_SOURCE = "jokeSource";
    public static final String COLUMN_RELEASE_STATUS = "releaseStatus";
    public static final String COLUMN_CREATED = "created";
    public static final String COLUMN_MODIFIED = "modified";

    private static final String DATABASE_NAME = "jokes.db"; // file name
    private static final int DATABASE_VERSION = 1;

    // Database creation raw SQL statement
    private static final String DATABASE_CREATE = "create table " + TABLE_JOKES
            + "( " + COLUMN_ID + " integer primary key autoincrement, "
            + COLUMN_CATEGORY + " text not null, " + COLUMN_SUBCATEGORY
            + " text not null, " + COLUMN_JOKE_TYPE + " integer not null, "
            + COLUMN_DESCRIPTION + " text," + COLUMN_QUESTION_TEXT + "text,"
            + COLUMN_ANSWER_TEXT + "text," + COLUMN_MONOLOGUE_TEXT + "text,"
            + COLUMN_RATING_SCALE + "integer," + COLUMN_COMMENTS + "text,"
            + COLUMN_JOKE_SOURCE + "text," + COLUMN_RELEASE_STATUS + "integer,"
            + COLUMN_CREATED + "text," + COLUMN_MODIFIED + "text" + ");";

    // static instance to share DBHelper
    private static DBHelper dbHelper = null;
    private SQLiteDatabase db ;

    /**
     * Constructor
     * 
     * @param context
     * @param name
     * @param factory
     * @param version
     */
    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        db = getWritableDatabase();
    }

    /**
     * This is a static method that makes sure that only one database helper
     * exists across the app's lifecycle
     * 
     * @param context
     * @return
     */
    public static DBHelper getDBHelper(Context context) {
        if (dbHelper == null) {
            dbHelper = new DBHelper(context.getApplicationContext());
        }
        return dbHelper;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
     * .SQLiteDatabase)
     */
    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
     * .SQLiteDatabase, int, int)
     */
    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        Log.w(DBHelper.class.getName(), "Upgrading database from version "
                + oldVersion + " to " + newVersion);
    }

    /**
     * CRUD - Update
     * 
     * @param category
     * @param subcategory
     * @param jokeType
     * @param description
     * @param questionText
     * @param answerText
     * @param monologueText
     * @param comments
     * @param jokeSource
     * @param ratingScale
     * @param releaseStatus
     * @return
     */
    public long insertNewJoke(String category, String subcategory,
            int jokeType, String description, String questionText,
            String answerText, String monologueText, String comments,
            String jokeSource, int ratingScale, int releaseStatus) {
        ContentValues cv = new ContentValues();
        cv.put(COLUMN_CATEGORY, category);
        cv.put(COLUMN_SUBCATEGORY, subcategory);
        cv.put(COLUMN_JOKE_TYPE, jokeType);
        cv.put(COLUMN_DESCRIPTION, description);
        cv.put(COLUMN_QUESTION_TEXT, questionText);
        cv.put(COLUMN_ANSWER_TEXT, answerText);
        cv.put(COLUMN_MONOLOGUE_TEXT, monologueText);
        cv.put(COLUMN_COMMENTS, comments);
        cv.put(COLUMN_JOKE_SOURCE, jokeSource);
        cv.put(COLUMN_RATING_SCALE, ratingScale);
        cv.put(COLUMN_RELEASE_STATUS, releaseStatus);
        cv.put(COLUMN_CREATED, ""); // how to put date??
        cv.put(COLUMN_CREATED, "");

        long code = getWritableDatabase().insert(TABLE_JOKES, null, cv);
        return code;
    }

    /**
     * CRUD - Retrieve
     * 
     * @return
     */
    public Cursor getJokes() {
        String[] columns = { COLUMN_CATEGORY, COLUMN_SUBCATEGORY,
                COLUMN_JOKE_TYPE, COLUMN_DESCRIPTION, COLUMN_ANSWER_TEXT,
                COLUMN_MONOLOGUE_TEXT, COLUMN_RATING_SCALE, COLUMN_COMMENTS,
                COLUMN_JOKE_SOURCE, COLUMN_RELEASE_STATUS, COLUMN_CREATED,
                COLUMN_MODIFIED }; // might need the _id column
        return getWritableDatabase().query(TABLE_JOKES, columns, null, null,
                null, null, null);
    }

    /**
     * CRUD - Delete
     * 
     * @param id
     */
    public void deleteJoke(int id) {
        getWritableDatabase().delete(TABLE_JOKES, COLUMN_ID + "=?",
                new String[] { String.valueOf(id) });
    }

}

因此，在 onCreate() (在我的 MainActivity 中)中，我尝试将新记录插入数据库，然后尝试将其检索到游标中。然而，它似乎被插入语句所困扰。

@Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        // the projection (fields from the database that we want to use)
        String from[] = { DBHelper.COLUMN_DESCRIPTION };
        // matching fields on the layout to be used with the adapter
        int to[] = { R.id.tv1 };

        setContentView(R.layout.activity_main);

        lv = (ListView) findViewById(R.id.lvMainJokes);
        dbHelper = DBHelper.getDBHelper(this);

        long code = dbHelper.insertNewJoke("Holiday", "", 2, "Joke 1 Description", "", "",
                "joke", "", "", 5, 1);

        if (code != -1)
        cursor = dbHelper.getJokes();
...

这是 LogCat 输出:

11-15 15:19:08.040: I/Choreographer(1016): Skipped 45 frames!  The application may be doing too much work on its main thread.
11-15 15:19:35.358: D/dalvikvm(1064): GC_FOR_ALLOC freed 58K, 4% free 2726K/2836K, paused 42ms, total 45ms
11-15 15:19:35.368: I/dalvikvm-heap(1064): Grow heap (frag case) to 3.315MB for 635812-byte allocation
11-15 15:19:35.438: D/dalvikvm(1064): GC_FOR_ALLOC freed 2K, 4% free 3344K/3460K, paused 63ms, total 63ms
11-15 15:19:35.548: E/SQLiteLog(1064): (1) table jokes has no column named releaseStatus
11-15 15:19:35.568: E/SQLiteDatabase(1064): Error inserting category=Holiday releaseStatus=1 jokeType=2 created= monologueText=joke description=Joke 1 Description subcategory= answerText= questionText= jokeSource= ratingScale=5 comments=
11-15 15:19:35.568: E/SQLiteDatabase(1064): android.database.sqlite.SQLiteException: table jokes has no column named releaseStatus (code 1): , while compiling: INSERT INTO jokes(category,releaseStatus,jokeType,created,monologueText,description,subcategory,answerText,questionText,jokeSource,ratingScale,comments) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at com.teamdotgetname.android.phase1.jokeapplication.persistence.DBHelper.insertNewJoke(DBHelper.java:143)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at com.teamdotgetname.android.phase1.jokeapplication.presentation.MainActivity.onCreate(MainActivity.java:53)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.Activity.performCreate(Activity.java:5133)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2175)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2261)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.ActivityThread.access$600(ActivityThread.java:141)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1256)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.os.Handler.dispatchMessage(Handler.java:99)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.os.Looper.loop(Looper.java:137)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at android.app.ActivityThread.main(ActivityThread.java:5103)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at java.lang.reflect.Method.invokeNative(Native Method)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at java.lang.reflect.Method.invoke(Method.java:525)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
11-15 15:19:35.568: E/SQLiteDatabase(1064):     at dalvik.system.NativeStart.main(Native Method)
11-15 15:19:35.798: I/Choreographer(1064): Skipped 42 frames!  The application may be doing too much work on its main thread.
11-15 15:19:35.868: D/gralloc_goldfish(1064): Emulator without GPU emulation detected.

感谢您的浏览。

Answer 1

乍一看，您的代码看起来不错。异常(exception):

    android.database.sqlite.SQLiteException: table jokes has no column named releaseStatus (code 1)

似乎是错误的，因为显然您正在创建表 DATABASE_CREATE 字符串中创建 releaseStatus 列。我遇到了一个问题，我正在修改数据库的架构，但这些更改没有在我的应用程序中生效，因为我没有增加 DATABASE_VERSION 。

您可以尝试的选项将导致数据库重新创建:

1. 通过“设置”应用程序清除应用程序数据
2. 手动卸载然后重新安装您的应用程序。 (仅运行 gradle installDebug 或让 Eclipse 安装您的应用程序还不够)
3. 增加您的 DATABASE_VERSION 值