在SQLite Android中插入多个表时出错

Question

我有一个非常明显的错误。在我的SQLiteHelper中，我创建了2个表：

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.support.annotation.Nullable;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import com.letscook.thanhta.letscook.R;
import java.io.ByteArrayOutputStream;

public class SQLiteHelper extends SQLiteOpenHelper {

public SQLiteHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "CookBook.db";

protected static final String RECIPE_CATEGORY = "RECIPE_CATEGORY";
protected static final String INGREDIENT_TYPE = "INGREDIENT_TYPE";

public static final String CREATE_RECIPE_CATEGORY = "create table if not exists "
        + RECIPE_CATEGORY +
        "(Recipe_Category_id INTEGER PRIMARY KEY," +
        "Recipe_Category_name TEXT, " +
        "Description TEXT);";
public static final String CREATE_INGREDIENT_TYPE= "create table if not exists " +
        INGREDIENT_TYPE +
        "(Ingredient_type_id INTEGER PRIMARY KEY ," +
        "Ingredient_type_name TEXT);";
@Override
public void onCreate(SQLiteDatabase db) {
    try{
        db = getWritableDatabase();
        db.execSQL(CREATE_RECIPE_CATEGORY);
        db.execSQL(CREATE_INGREDIENT_TYPE);
        db.close();}
    catch(Exception e){
        e.printStackTrace();
    }
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)     {        
    db.execSQL("drop table if exists " + RECIPE_CATEGORY);
    db.execSQL("drop table if exists " + INGREDIENT_TYPE);
    onCreate(db);
}

在MainActivity中，我向这两个表中插入数据。

    public class DataBaseInsertActivity extends ActionBarActivity {

    ContentValues  values = new ContentValues();
    private void Insert(ContentValues values, String table) {
        db.insert(table,null,values);
    }

    public void doInsertRECIPE_CATEGORY(int id, String name, String    description){
    values.put("Recipe_Category_id", id);
    values.put("Recipe_Category_name",name);
    values.put("Description", description);
    Insert(values, "RECIPE_CATEGORY");
    }

    public  void doInsertINGREDIENT_TYPE(int id, String name){
    values.put("Ingredient_type_id",id);
    values.put("Ingredient_type_name",name);
    Insert(values, "INGREDIENT_TYPE");
    }

    SQLiteDatabase db;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_data_base);
        //CREATE database
        SQLiteHelper helper = new SQLiteHelper(this);
        db = helper.getWritableDatabase();
      }

    doInsertRECIPE_CATEGORY(0, "All", "All recipes");
    doInsertRECIPE_CATEGORY(1, "Snack", "Fast and fun!");
    doInsertINGREDIENT_TYPE(0, "meat");
    doInsertINGREDIENT_TYPE(1, "fish");
}}

错误：

09-09 18:50:58.494  12358-12358/com.letscook.thanhta.letscook E/SQLiteLog﹕ (1) near "Recipe_id": syntax error
09-09 18:50:58.518  12358-12358/com.letscook.thanhta.letscook E/SQLiteLog﹕ (1) no such table: INGREDIENT_TYPE
09-09 18:50:58.518  12358-12358/com.letscook.thanhta.letscook E/SQLiteDatabase﹕ Error inserting Ingredient_type_name=meat Description=Fast and fun! Ingredient_type_id=0 Recipe_Category_name=Snack Recipe_Category_id=1
    android.database.sqlite.SQLiteException: no such table: INGREDIENT_TYPE (code 1): , while compiling: INSERT INTO INGREDIENT_TYPE(Ingredient_type_name,Description,Ingredient_type_id,Recipe_Category_name,Recipe_Category_id) VALUES (?,?,?,?,?)

我不知道为什么在插入第二个表时，“Recipe_Category_name”和“Recipe_Category_id”仍然存在于Insert语句中。

- Thanh Ta

---

你是否考虑过使用像ActiveAndroid这样的库来满足数据库需求？ - Eenvincible

Answer 1

在SQLiteDatabaseHelper的onCreate()中，您不能调用getWritableDatabase()。这将导致"递归调用"异常。

同时，删除close()和try-catch。如果有问题，它需要被抛出而不是忽略。

卸载并重新安装您的应用程序以再次触发onCreate()。

- laalto

Answer 2

我已经找到了解决方案！ 这是因为在类DataBaseInsertActivity中出现了错误。

ContentValues  values = new ContentValues();

已经放在 onCreate 中，应该像这样放到单独的类中：

public void doInsertRECIPE_CATEGORY(int id, String name, String        description)   {
ContentValues  values = new ContentValues();
values.put("Recipe_Category_id", id);
values.put("Recipe_Category_name",name);
values.put("Description", description);
Insert(values, "RECIPE_CATEGORY");
}
public  void doInsertINGREDIENT_TYPE(int id, String name){
ContentValues  values = new ContentValues();
values.put("Ingredient_type_id",id);
values.put("Ingredient_type_name",name);
Insert(values, "INGREDIENT_TYPE");
}

- Thanh Ta