android - 当我添加新表时没有这样的表

Question

我认为功能onUpgrade无法正常工作，因为在添加新表之前，这不是错误。
当我添加类别表(新表)时。当我运行模拟器.log时，我将database_version = 2从1更改为cat sue“No such table category”并且我不知道如何修改它。请检查
并告诉我为什么onUpgrade不起作用。

这是我的代码(BookDBHelper)

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

public class BooksDBHelper extends SQLiteOpenHelper {
//databse_name
public static String DATABSE_NAME = "bookguk";
// database_version
protected static final int DATABASE_VERSION = 2;  //  <<<---i have changed it to version 2 from version 1 when add a new table category

//table name
public static String TABLE_BOOK = "books";
public static String TABLE_IMAGE = "images";
public static String TABLE_INGREDIENT = "ingredients";
public static String TABLE_CATEGORY = "category";
//column_name
public static String KEY_ID = "id";
public static String KEY_TITLE = "title";
public static String KEY_SOLUTION = "solution";
public static String KEY_BOK_ID = "bookID";
public static String KEY_fILEPATH = "filepath";
public static String KEY_VALUE = "value" ;
public static String KEY_UNIT = "unit";
public static String KEY_NAME = "name";
public static String KEY_CAT_ID = "cat_id";

public static String BOOK_CREATE_SQL = "CREATE TABLE IF NOT EXISTS " + TABLE_BOOK + "(" +
                                ""+KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
                                ""+KEY_TITLE+" TEXT(50) not null," +
                                ""+KEY_SOLUTION+" TEXT(255) not null," +
                                ""+KEY_CAT_ID+" INTEGER(11)," +
                                " FOREIGN KEY("+KEY_CAT_ID+") REFERENCE "+TABLE_CATEGORY+" ("+KEY_ID+"))";

public static String IMAGE_CREATE_SQL = "CREATE TABLE IF NOT EXISTS " + TABLE_IMAGE + "(" +
                                ""+KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
                                ""+KEY_fILEPATH+" VACHAR(255)," +
                                ""+KEY_BOK_ID+" INTEGER(11)," +
                                " FOREIGN KEY("+KEY_BOK_ID+") REFERENCES "+TABLE_BOOK+" ("+KEY_ID+"))";

public static String INGREDIENT_CREATE_SQL = "CREATE TABLE IF NOT EXISTS " + TABLE_INGREDIENT + "(" +
                                ""+KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
                                ""+KEY_VALUE+" DOUBLE(11)," +
                                ""+KEY_UNIT+" VACHAR(50)," +
                                ""+KEY_fILEPATH+" VACHAR(255)," +
                                ""+KEY_BOK_ID+" INTEGER(11)," +
                                " FOREIGN KEY("+KEY_BOK_ID+")REFERENCES "+TABLE_BOOK+" ("+KEY_ID+"))";

public static String CATEGORY_CREATE_SQL = "CREATE TABLE IF NOT EXISTS " + TABLE_CATEGORY + "(" +
                                ""+KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
                                ""+KEY_NAME+" VACHAR(50) " +
                                ")";



//constructor
public BooksDBHelper(Context context) {
    super(context, DATABSE_NAME, null, DATABASE_VERSION);
    // TODO Auto-generated constructor stub
}

//create
@Override
public void onCreate(SQLiteDatabase db) {
            db.execSQL(CATEGORY_CREATE_SQL);
    db.execSQL(BOOK_CREATE_SQL);
    db.execSQL(IMAGE_CREATE_SQL);
    db.execSQL(INGREDIENT_CREATE_SQL);


}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub

    db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOK );
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_IMAGE );
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_INGREDIENT );
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORY );
    onCreate(db);

}

}

这是我的运算符(将数据插入表类别)

package com.example.mutitablesql.db;

import java.util.ArrayList;

import com.example.mutitablesql.entry.BookEntry;
import com.example.mutitablesql.entry.CategoryEntry;
import com.example.mutitablesql.entry.ImageEntry;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class BooksDB {
//นำ class booksDBHelper มาสร้างเป็น object
private BooksDBHelper helper;
private SQLiteDatabase db;

//สร้าง constructor
public BooksDB(Context context){
    // คำสั่ง เรียก constructor ของclass BooksDBHelper
    helper = new BooksDBHelper(context);
    db = helper.getWritableDatabase();
}

//insert into table category
public long insertCategory(String name){
    ContentValues values = new ContentValues();
    values.put(BooksDBHelper.KEY_NAME, name);
    return db.insert(BooksDBHelper.TABLE_CATEGORY, null, values);

}

我的主要 Activity 代码

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    context = this;
    db = new BooksDB(context);

    db.insertCategory("ต้ม");
    db.insertCategory("ผัด");
    db.insertCategory("แกง");
    db.insertCategory("ทอด");
    db.insertCategory("ประเภทเส้น");
    db.insertCategory("เครื่องดื่ม");
    db.insertCategory("ของหวาน");
}

这是我的日志编辑

09-02 09:47:29.869: E/SQLiteLog(1220): (1) near "REFERENCE": syntax error
09-02 09:47:29.889: D/AndroidRuntime(1220): Shutting down VM
09-02 09:47:29.889: W/dalvikvm(1220): threadid=1: thread exiting with uncaught exception (group=0x40a71930)
09-02 09:47:29.919: E/AndroidRuntime(1220): FATAL EXCEPTION: main
09-02 09:47:29.919: E/AndroidRuntime(1220): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.mutitablesql/com.example.mutitablesql.MainActivity}: android.database.sqlite.SQLiteException: near "REFERENCE": syntax error (code 1): , while compiling: CREATE TABLE IF NOT EXISTS books(id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT(50) not null,solution TEXT(255) not null,cat_id INTEGER(11), FOREIGN KEY(cat_id) REFERENCE category (id))
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2180)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.app.ActivityThread.access$600(ActivityThread.java:141)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.os.Handler.dispatchMessage(Handler.java:99)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.os.Looper.loop(Looper.java:137)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.app.ActivityThread.main(ActivityThread.java:5041)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at java.lang.reflect.Method.invokeNative(Native Method)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at java.lang.reflect.Method.invoke(Method.java:511)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at dalvik.system.NativeStart.main(Native Method)
09-02 09:47:29.919: E/AndroidRuntime(1220): Caused by: android.database.sqlite.SQLiteException: near "REFERENCE": syntax error (code 1): , while compiling: CREATE TABLE IF NOT EXISTS books(id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT(50) not null,solution TEXT(255) not null,cat_id INTEGER(11), FOREIGN KEY(cat_id) REFERENCE category (id))
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at com.example.mutitablesql.db.BooksDBHelper.onCreate(BooksDBHelper.java:67)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at com.example.mutitablesql.db.BooksDBHelper.onUpgrade(BooksDBHelper.java:82)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:257)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at com.example.mutitablesql.db.BooksDB.<init>(BooksDB.java:23)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at com.example.mutitablesql.MainActivity.onCreate(MainActivity.java:21)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.app.Activity.performCreate(Activity.java:5104)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
09-02 09:47:29.919: E/AndroidRuntime(1220):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
09-02 09:47:29.919: E/AndroidRuntime(1220):     ... 11 more

Answer 1

查看您的onUpgrade代码:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub

db.execSQL("DROP TABLE IF EXITS " + TABLE_BOOK );
db.execSQL("DROP TABLE IF EXITS " + TABLE_IMAGE );
db.execSQL("DROP TABLE IF EXITS " + TABLE_INGREDIENT );
db.execSQL("DROP TABLE IF EXITS " + TABLE_CATEGORY );
onCreate(db);

}
我意识到您有一个拼写错误，EXITS是EXISTS