尝试使用insert_last_rowid()

huangapple go评论67阅读模式
英文:

Attempting to use insert_last_rowid()

问题

我尝试在createPlan方法中将plan_recipe变量分配给createPlanRecipe方法的自动增量id。我尝试使用insert_last_rowid()方法,但遇到错误"Couldn't read row 0, col -1 from CursorWindow"。我是否做错了,还是有其他方法可以做到这一点?
createPlan方法中,如何将从createPlanRecipe创建的id插入到变量plRecipe中?

活动

protected void onCreate(Bundle savedInstanceState) {
    // ... 这里省略了一些代码 ...
}

public void ListRecipes() {
    // ... 这里省略了一些代码 ...
}

public void CreatePlan(){
    // ... 这里省略了一些代码 ...
}

数据库片段

public void createPlanRecipe(Integer date, String dayOfWeek, String recipe_name) {
    // ... 这里省略了一些代码 ...
}

public void createPlan(String plan_name, Integer plRecipe) {
    // ... 这里省略了一些代码 ...
}

数据库

public static final String TABLE_CATEGORY = "CATEGORY";
public static final String TABLE_RECIPE = "RECIPE";
public static final String TABLE_MEAL_PLAN = "MEAL_PLAN";
// ... 这里省略了一些表格 ...

请注意,这只是您提供的代码的一部分,缺少一些关键信息,如数据库架构和表之间的关系。如果您需要更详细的帮助,请提供更多上下文和代码细节。

英文:

I am attempting to assign the plan_recipe variable in the createPlan method with teh autoincremented id of the createPlanRecipe method. I've attempted to use the insert_last_rowid() method but am getting the error of Couldn't read row 0, col -1 from CursorWindow. Have I not done this correctly or is there another method I could use to do this?
Essentially how do I insert the id created from createPlanRecipe into variable plRecipe in createPlan()?

Activity

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.create_meal_plan);
    BottomNavigationView navigation = (BottomNavigationView) findViewById(R.id.navigation);
    navigation.setOnNavigationItemSelectedListener(new BottomNavigationView.OnNavigationItemSelectedListener() {
        @Override
        public boolean onNavigationItemSelected(@NonNull MenuItem item) {
            switch (item.getItemId()) {
                case R.id.home:
                    Intent a = new Intent(CreateMealPlan.this,MainActivity.class);
                    startActivity(a);
                    break;
                case R.id.recipes:
                    Intent b = new Intent(CreateMealPlan.this,RecipeSearch.class);
                    startActivity(b);
                    break;
                /*case R.id.shoppingList:
                    Intent c = new Intent(CreateMealPlan.this, ShoppingList.class);
                    startActivity(c);
                    break;*/
                case R.id.mealPlan:
                    Intent d = new Intent(CreateMealPlan.this, MenuPlan.class);
                    startActivity(d);
                    break;
                /*case R.id.reminder:
                    Intent e = new Intent(CreateMealPlan.this, Reminder.class);
                    startActivity(e);
                    break*/
            }
            return false;
        }
    });
    datepicker = findViewById(R.id.calendarView);
    ListRecipes();
    RecipeListAdapter.OnRecipeClickListener listener = new RecipeListAdapter.OnRecipeClickListener() {
        public void onRecipeClicked(int position, String recName) {
            Log.d("Recipe selected",  recName);
            recipe_name = recName;
        }

    };
    adapterRecipe = new RecipeListAdapter(this, listRecipe, listener);
    recipeList = findViewById(R.id.recipes);
    RecyclerView.LayoutManager mLayoutManager = new LinearLayoutManager(this,
            LinearLayoutManager.VERTICAL, false);
    recipeList.setLayoutManager(mLayoutManager);
    recipeList.setItemAnimator(new DefaultItemAnimator());
    recipeList.setAdapter(adapterRecipe);

    submit = (Button) findViewById(R.id.create);

    // perform click event on submit button
    submit.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            CreatePlan();

        }

        });
}
public void ListRecipes() {
    listRecipe.clear();
    SQLiteDatabase db = (new DatabaseManager(this).getWritableDatabase());
    String selectQuery = " SELECT recipe_name, image, image2, category" + " FROM " + DatabaseManager.TABLE_RECIPE + "  GROUP BY recipe_name";
    c = db.rawQuery(selectQuery, null);
    Log.d("Query", selectQuery);
    if (c.moveToFirst()) {
        do {
            com.stu54259.plan2cook.Model.Category category = new com.stu54259.plan2cook.Model.Category();
            category.setRecipe_name(c.getString(c.getColumnIndex("recipe_name")));
            category.setImage(c.getInt(c.getColumnIndex("image")));
            category.setImage2(c.getString(c.getColumnIndex("image2")));
            category.setCategory_name(c.getString(c.getColumnIndex("category")));
            listRecipe.add(category);
        } while (c.moveToNext());
        c.close();
    }

}
public void CreatePlan(){
    editPlanName = findViewById(R.id.editPlanName);
    String plan_name = editPlanName.getText().toString();
    DatabaseManager db;
    int day = datepicker.getDayOfMonth();
    int month = datepicker.getMonth();
    int year = datepicker.getYear();
    SimpleDateFormat sdf = new SimpleDateFormat("EEEE");
    Integer d_name = day;
    Log.d("Date", String.valueOf(d_name));
    String dayOfTheWeek = sdf.format(d_name);
    String date = day + "/" + month + "/" +year;
    db = new DatabaseManager(getApplicationContext());
    Log.d("Recipe name", recipe_name);
    db.createPlanRecipe(d_name, dayOfTheWeek, recipe_name);
    db.createPlan(plan_name, plan_recipe);

}

}

Database snippet

    public void createPlanRecipe(Integer date, String dayOfWeek, String recipe_name) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL(getINSERT_PLAN_RECIPE(date, dayOfWeek, recipe_name));
}
public void createPlan(String plan_name, Integer plRecipe) {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = db.rawQuery("select last_insert_rowid()",null);
    c.moveToFirst();

    if (c != null && (c.getCount() > 0)) {plRecipe = c.getInt(c.getColumnIndex("id"));}
    db.execSQL(getInsertPlan(plan_name, plRecipe));
}

Database

 public static final String TABLE_CATEGORY = "CATEGORY";
public static final String TABLE_RECIPE = "RECIPE";
public static final String TABLE_MEAL_PLAN = "MEAL_PLAN";
public static final String TABLE_QUANTITY = "QUANTITY";
public static final String TABLE_SHOPPING_LIST = "SHOPPING_LIST";
public static final String TABLE_PLAN_RECIPES = "PLAN_RECIPES";
public static final String TABLE_COURSE = "COURSE";
public static final String TABLE_INGREDIENTS = "INGREDIENTS";
public static final String TABLE_MEASUREMENT = "MEASUREMENT";
public static final String TABLE_INGREDIENT_TYPE = "INGREDIENT_TYPE";
public static final String TABLE_ALLERGENS = "ALLERGENS";
public static final String TABLE_RECIPES_ALLERGENS = "RECIPES_ALLERGENS";
public static final String TABLE_FAVOURITES = "FAVOURITES";
// Common column names
private static final String COL_ID = "id";
private static final String COL_MEASUREMENT = "measurement";
private static final String COL_INGREDIENT_TYPE = "ingredient_type";
private static final String COL_DESCRIPTION = "description";
private static final String COL_IMAGE = "image";
// Category column names
private static final String COL_CATEGORY_NAME = "category_name";
// Recipe column names
private static final String COL_RECIPE_NAME = "recipe_name";
private static final String COL_SERVINGS = "servings";
private static final String COL_CALORIES = "calories";
private static final String COL_PREPARATION_TIME = "preparation_time";
private static final String COL_METHOD = "method";
private static final String COL_COURSE = "course";
private static final String COL_CATEGORY = "category";
private static final String COL_COST = "cost";
private static final String COL_IMAGE2 = "image2";
//Quantity column names
private static final String COL_INGREDIENT_QUANTITY = "ingredient_quantity";
private static final String COL_RECIPE = "recipe";
private static final String COL_INGREDIENT = "ingredient";
//Meal Plan column names
private static final String COL_PLAN_NAME = "plan_name";
private static final String COL_PLAN_RECIPE = "plan_recipe";
//Shopping List column names
private static final String COL_QUANTITY = "quantity";
private static final String COL_PLANID = "planID";
//Plan Recipes column names
private static final String COL_DATE = "date";
private static final String COL_DAY_OF_WEEK = "dayOfWeek";
//Course column names
private static final String COL_COURSE_NAME = "course_name";
//Ingredients column names
private static final String COL_INGREDIENT_NAME = "ingredient_name";
//Measurement column names
private static final String COL_MEASUREMENT_NAME = "measurement_name";
//Ingredient Type column names
private static final String COL_TYPE_NAME = "type_name";
//Allergens column names
private static final String COL_ALLERGEN_NAME = "allergen_name";
/* *************************************************************************************
************************* CREATE TABLE STATEMENTS **************************************
****************************************************************************************
*/
// Create Table Category
private static final String CREATE_TABLE_CATEGORY = "CREATE TABLE "
+ TABLE_CATEGORY + "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_CATEGORY_NAME
+ " TEXT," + COL_IMAGE + " INTEGER)";
// Create Table Meal Plan
private static final String CREATE_TABLE_MEAL_PLAN = "CREATE TABLE "
+ TABLE_MEAL_PLAN + "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_PLAN_NAME
+ " TEXT," + COL_PLAN_RECIPE + " NUMERIC," + " FOREIGN KEY (" + COL_PLAN_RECIPE + ") REFERENCES " + TABLE_PLAN_RECIPES + "(" + COL_ID + "))";
// Create Table Recipe
private static final String CREATE_TABLE_RECIPE = "CREATE TABLE " + TABLE_RECIPE + "(" + COL_ID +
" INTEGER PRIMARY KEY AUTOINCREMENT," + COL_RECIPE_NAME + " TEXT," + COL_DESCRIPTION
+ " TEXT," + COL_COURSE + " TEXT," + COL_SERVINGS + " INTEGER," + COL_CALORIES + " NUMERIC,"
+ COL_PREPARATION_TIME + " NUMERIC," + COL_METHOD + " TEXT," + COL_CATEGORY + " TEXT,"
+ COL_IMAGE + " INTEGER," + COL_IMAGE2 + " TEXT," + COL_COST + " NUMERIC," + " FOREIGN KEY (" + COL_CATEGORY + ") REFERENCES " +
TABLE_CATEGORY + "(" + COL_CATEGORY_NAME + "), FOREIGN KEY (" + COL_COURSE + ") REFERENCES " +
TABLE_COURSE + "(" + COL_COURSE_NAME + "))";
// Create Table Quantity
public static final String CREATE_TABLE_QUANTITY = "CREATE TABLE " + TABLE_QUANTITY + "(" + COL_ID +
" INTEGER PRIMARY KEY AUTOINCREMENT," + COL_INGREDIENT_QUANTITY + " NUMERIC, " +
COL_RECIPE + " TEXT," + COL_INGREDIENT + " TEXT, FOREIGN KEY (" + COL_RECIPE + ") " +
"REFERENCES " + TABLE_RECIPE + "(" + COL_RECIPE_NAME + "), FOREIGN KEY (" + COL_INGREDIENT + ") " +
"REFERENCES " + TABLE_INGREDIENTS + "(" + COL_INGREDIENT_NAME + "))";
// Create Table Shopping List
private static final String CREATE_TABLE_SHOPPING_LIST = "CREATE TABLE " + TABLE_SHOPPING_LIST +
"(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_INGREDIENT_TYPE + " INTEGER,"
+ COL_QUANTITY + " INTEGER," + COL_MEASUREMENT + " INTEGER," + COL_PLANID + " INTEGER," +
" FOREIGN KEY (" + COL_INGREDIENT_TYPE + ") REFERENCES " + TABLE_INGREDIENT_TYPE + "(" + COL_ID + "), " +
"FOREIGN KEY (" + COL_QUANTITY + ") REFERENCES " + TABLE_QUANTITY + "(" + COL_ID + "), " +
"FOREIGN KEY (" + COL_MEASUREMENT + ") REFERENCES " + TABLE_MEASUREMENT + "(" + COL_ID + "), " +
"FOREIGN KEY (" + COL_PLANID + ") REFERENCES " + TABLE_MEAL_PLAN + "(" + COL_ID + "))";
// Create Table Course
private static final String CREATE_TABLE_COURSE = "CREATE TABLE " + TABLE_COURSE + "(" + COL_ID +
" INTEGER PRIMARY KEY AUTOINCREMENT, " + COL_COURSE_NAME + " TEXT," + COL_IMAGE + " INTEGER)";
// Create Table Ingredients
public static final String CREATE_TABLE_INGREDIENTS = "CREATE TABLE " + TABLE_INGREDIENTS + "("
+ COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_INGREDIENT_NAME + " TEXT,"
+ COL_DESCRIPTION + " TEXT," + COL_MEASUREMENT_NAME + " TEXT," + COL_INGREDIENT_TYPE + " TEXT, " +
"FOREIGN KEY (" + COL_MEASUREMENT_NAME + ") REFERENCES " + TABLE_MEASUREMENT + "(" + COL_MEASUREMENT_NAME + "), " +
"FOREIGN KEY (" + COL_INGREDIENT_TYPE + ") REFERENCES " + TABLE_INGREDIENT_TYPE + "(" + COL_TYPE_NAME + "))";
// Create Table Measurement
private static final String CREATE_TABLE_MEASUREMENT = "CREATE TABLE " + TABLE_MEASUREMENT + "(" +
COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_MEASUREMENT_NAME + " TEXT)";
// Create Table Ingredient Type
private static final String CREATE_TABLE_INGREDIENT_TYPE = "CREATE TABLE " + TABLE_INGREDIENT_TYPE +
"(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_TYPE_NAME + " TEXT)";
// Create Table Plan Recipes
private static final String CREATE_TABLE_PLAN_RECIPES = "CREATE TABLE " + TABLE_PLAN_RECIPES + "(" +
COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_DATE + " DATE," +
COL_DAY_OF_WEEK + " TEXT," + COL_RECIPE_NAME + " TEXT, FOREIGN KEY (" + COL_RECIPE_NAME + ") " +
"REFERENCES " + TABLE_RECIPE + "(" + COL_RECIPE_NAME + "))";
// Create Table Allergens
private static final String CREATE_TABLE_ALLERGENS = "CREATE TABLE " + TABLE_ALLERGENS +
"(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_ALLERGEN_NAME + " TEXT)";
// Create Table Recipes_Allergens
private static final String CREATE_TABLES_RECIPES_ALLERGENS = "CREATE TABLE " + TABLE_RECIPES_ALLERGENS +
"(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_ALLERGEN_NAME + " TEXT," +
COL_RECIPE + " TEXT," + "FOREIGN KEY (" + COL_ALLERGEN_NAME + ") REFERENCES " + TABLE_ALLERGENS +
"(" + COL_ALLERGEN_NAME +"), " + "FOREIGN KEY (" + COL_RECIPE + ") REFERENCES " + TABLE_RECIPE +
"(" + COL_RECIPE_NAME + "))";
// Create Table Favourites
private static final String CREATE_TABLE_FAVOURITES = "CREATE TABLE " + TABLE_FAVOURITES + "(" +
COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_RECIPE_NAME + " TEXT," +
"FOREIGN KEY (" + COL_RECIPE_NAME + ") REFERENCES " + TABLE_RECIPE + "(" + COL_RECIPE_NAME + "))";
public final String INSERT_MEASUREMENT =
"INSERT INTO " + TABLE_MEASUREMENT + "("
+ COL_MEASUREMENT_NAME + ") values ";
public final String INSERT_INGREDIENT_TYPE =
" INSERT INTO " + TABLE_INGREDIENT_TYPE + "("
+ COL_TYPE_NAME + ") values ";
public final String INSERT_INGREDIENT =
" INSERT INTO " + TABLE_INGREDIENTS + "("
+ COL_INGREDIENT_NAME + "," + COL_DESCRIPTION + ","
+ COL_MEASUREMENT_NAME + "," + COL_INGREDIENT_TYPE + ") values ";
public final String INSERT_CATEGORY =
" INSERT INTO " + TABLE_CATEGORY + "("
+ COL_CATEGORY_NAME + "," + COL_IMAGE + ") values ";
public final String INSERT_COURSE =
" INSERT INTO " + TABLE_COURSE + "("
+ COL_COURSE_NAME + "," + COL_IMAGE + ") values ";
public final String INSERT_RECIPE =
" INSERT INTO " + TABLE_RECIPE + "("
+ COL_RECIPE_NAME + "," + COL_DESCRIPTION + ","
+ COL_COURSE + "," + COL_SERVINGS + ","
+ COL_CALORIES + "," + COL_PREPARATION_TIME + ","
+ COL_METHOD + "," + COL_CATEGORY + ","
+ COL_IMAGE + ","
+ COL_IMAGE2 + ","
+ COL_COST + ") values";
public final String INSERT_QUANTITY =
" INSERT INTO " + TABLE_QUANTITY + "("
+ COL_INGREDIENT_QUANTITY + ","
+ COL_RECIPE + ","
+ COL_INGREDIENT + ") values";
public final String INSERT_ALLERGEN =
" INSERT INTO " + TABLE_ALLERGENS + "("
+ COL_ALLERGEN_NAME + ") values";
public final String INSERT_RECIPES_ALLERGENS =
" INSERT INTO " + TABLE_RECIPES_ALLERGENS + "("
+ COL_ALLERGEN_NAME + ","
+ COL_RECIPE + ") values";
public final String INSERT_FAVOURITE =
" INSERT INTO " + TABLE_FAVOURITES + "("
+ COL_RECIPE_NAME + ") values";
public final String INSERT_PLAN_RECIPE =
" INSERT INTO " + TABLE_PLAN_RECIPES + "("
+ COL_DATE + ","
+ COL_DAY_OF_WEEK + ","
+ COL_RECIPE_NAME + ") values";
public final String INSERT_PLAN =
" INSERT INTO " + TABLE_MEAL_PLAN + "("
+ COL_PLAN_NAME + ","
+ COL_PLAN_RECIPE + ") values";

答案1

得分: 1

你必须为由last_insert_rowid()返回的列使用类似于id的别名:

Cursor c = db.rawQuery("select last_insert_rowid() as id", null);

然后,替代这段代码:

if (c != null && (c.getCount() > 0)) {plRecipe = c.getInt(c.getColumnIndex("id"));}

请使用以下代码:

if (c.moveToFirst()) {plRecipe = c.getInt(c.getColumnIndex("id"));}

如果没有为列使用别名,则应在c.getInt()内部直接使用0

if (c.moveToFirst()) {plRecipe = c.getInt(0);}
英文:

You must use an alias like id for the column that is returned by last_insert_rowid():

Cursor c = db.rawQuery("select last_insert_rowid() as id", null);

Then instead of:

if (c != null && (c.getCount() > 0)) {plRecipe = c.getInt(c.getColumnIndex("id"));}

do this:

if (c.moveToFirst()) {plRecipe = c.getInt(c.getColumnIndex("id"));}

Without the alias of the column, you should directly use 0 inside c.getInt():

if (c.moveToFirst()) {plRecipe = c.getInt(0);}

huangapple
  • 本文由 发表于 2020年10月1日 05:34:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/64146066.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定