尝试使用insert_last_rowid()

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

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中？

活动

  1. protected void onCreate(Bundle savedInstanceState) {
  2.     // ... 这里省略了一些代码 ...
  3. }
  5. public void ListRecipes() {
  6.     // ... 这里省略了一些代码 ...
  7. }
  9. public void CreatePlan(){
  10.     // ... 这里省略了一些代码 ...
  11. }

数据库片段

  1. public void createPlanRecipe(Integer date, String dayOfWeek, String recipe_name) {
  2.     // ... 这里省略了一些代码 ...
  3. }
  5. public void createPlan(String plan_name, Integer plRecipe) {
  6.     // ... 这里省略了一些代码 ...
  7. }

数据库

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

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

英文:

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

  1. protected void onCreate(Bundle savedInstanceState) {
  2.     super.onCreate(savedInstanceState);
  3.     setContentView(R.layout.create_meal_plan);
  4.     BottomNavigationView navigation = (BottomNavigationView) findViewById(R.id.navigation);
  5.     navigation.setOnNavigationItemSelectedListener(new BottomNavigationView.OnNavigationItemSelectedListener() {
  6.         @Override
  7.         public boolean onNavigationItemSelected(@NonNull MenuItem item) {
  8.             switch (item.getItemId()) {
  9.                 case R.id.home:
  10.                     Intent a = new Intent(CreateMealPlan.this,MainActivity.class);
  11.                     startActivity(a);
  12.                     break;
  13.                 case R.id.recipes:
  14.                     Intent b = new Intent(CreateMealPlan.this,RecipeSearch.class);
  15.                     startActivity(b);
  16.                     break;
  17.                 /*case R.id.shoppingList:
  18.                     Intent c = new Intent(CreateMealPlan.this, ShoppingList.class);
  19.                     startActivity(c);
  20.                     break;*/
  21.                 case R.id.mealPlan:
  22.                     Intent d = new Intent(CreateMealPlan.this, MenuPlan.class);
  23.                     startActivity(d);
  24.                     break;
  25.                 /*case R.id.reminder:
  26.                     Intent e = new Intent(CreateMealPlan.this, Reminder.class);
  27.                     startActivity(e);
  28.                     break*/
  29.             }
  30.             return false;
  31.         }
  32.     });
  33.     datepicker = findViewById(R.id.calendarView);
  34.     ListRecipes();
  35.     RecipeListAdapter.OnRecipeClickListener listener = new RecipeListAdapter.OnRecipeClickListener() {
  36.         public void onRecipeClicked(int position, String recName) {
  37.             Log.d("Recipe selected",  recName);
  38.             recipe_name = recName;
  39.         }
  41.     };
  42.     adapterRecipe = new RecipeListAdapter(this, listRecipe, listener);
  43.     recipeList = findViewById(R.id.recipes);
  44.     RecyclerView.LayoutManager mLayoutManager = new LinearLayoutManager(this,
  45.             LinearLayoutManager.VERTICAL, false);
  46.     recipeList.setLayoutManager(mLayoutManager);
  47.     recipeList.setItemAnimator(new DefaultItemAnimator());
  48.     recipeList.setAdapter(adapterRecipe);
  50.     submit = (Button) findViewById(R.id.create);
  52.     // perform click event on submit button
  53.     submit.setOnClickListener(new View.OnClickListener() {
  54.         @Override
  55.         public void onClick(View v) {
  56.             CreatePlan();
  58.         }
  60.         });
  61. }
  62. public void ListRecipes() {
  63.     listRecipe.clear();
  64.     SQLiteDatabase db = (new DatabaseManager(this).getWritableDatabase());
  65.     String selectQuery = " SELECT recipe_name, image, image2, category" + " FROM " + DatabaseManager.TABLE_RECIPE + "  GROUP BY recipe_name";
  66.     c = db.rawQuery(selectQuery, null);
  67.     Log.d("Query", selectQuery);
  68.     if (c.moveToFirst()) {
  69.         do {
  70.             com.stu54259.plan2cook.Model.Category category = new com.stu54259.plan2cook.Model.Category();
  71.             category.setRecipe_name(c.getString(c.getColumnIndex("recipe_name")));
  72.             category.setImage(c.getInt(c.getColumnIndex("image")));
  73.             category.setImage2(c.getString(c.getColumnIndex("image2")));
  74.             category.setCategory_name(c.getString(c.getColumnIndex("category")));
  75.             listRecipe.add(category);
  76.         } while (c.moveToNext());
  77.         c.close();
  78.     }
  80. }
  81. public void CreatePlan(){
  82.     editPlanName = findViewById(R.id.editPlanName);
  83.     String plan_name = editPlanName.getText().toString();
  84.     DatabaseManager db;
  85.     int day = datepicker.getDayOfMonth();
  86.     int month = datepicker.getMonth();
  87.     int year = datepicker.getYear();
  88.     SimpleDateFormat sdf = new SimpleDateFormat("EEEE");
  89.     Integer d_name = day;
  90.     Log.d("Date", String.valueOf(d_name));
  91.     String dayOfTheWeek = sdf.format(d_name);
  92.     String date = day + "/" + month + "/" +year;
  93.     db = new DatabaseManager(getApplicationContext());
  94.     Log.d("Recipe name", recipe_name);
  95.     db.createPlanRecipe(d_name, dayOfTheWeek, recipe_name);
  96.     db.createPlan(plan_name, plan_recipe);
  98. }

}

Database snippet

  1.     public void createPlanRecipe(Integer date, String dayOfWeek, String recipe_name) {
  2.     SQLiteDatabase db = this.getWritableDatabase();
  3.     db.execSQL(getINSERT_PLAN_RECIPE(date, dayOfWeek, recipe_name));
  4. }
  5. public void createPlan(String plan_name, Integer plRecipe) {
  6.     SQLiteDatabase db = this.getWritableDatabase();
  7.     Cursor c = db.rawQuery("select last_insert_rowid()",null);
  8.     c.moveToFirst();
  10.     if (c != null && (c.getCount() > 0)) {plRecipe = c.getInt(c.getColumnIndex("id"));}
  11.     db.execSQL(getInsertPlan(plan_name, plRecipe));
  12. }

Database

  1.  public static final String TABLE_CATEGORY = "CATEGORY";
  2. public static final String TABLE_RECIPE = "RECIPE";
  3. public static final String TABLE_MEAL_PLAN = "MEAL_PLAN";
  4. public static final String TABLE_QUANTITY = "QUANTITY";
  5. public static final String TABLE_SHOPPING_LIST = "SHOPPING_LIST";
  6. public static final String TABLE_PLAN_RECIPES = "PLAN_RECIPES";
  7. public static final String TABLE_COURSE = "COURSE";
  8. public static final String TABLE_INGREDIENTS = "INGREDIENTS";
  9. public static final String TABLE_MEASUREMENT = "MEASUREMENT";
  10. public static final String TABLE_INGREDIENT_TYPE = "INGREDIENT_TYPE";
  11. public static final String TABLE_ALLERGENS = "ALLERGENS";
  12. public static final String TABLE_RECIPES_ALLERGENS = "RECIPES_ALLERGENS";
  13. public static final String TABLE_FAVOURITES = "FAVOURITES";
  14. // Common column names
  15. private static final String COL_ID = "id";
  16. private static final String COL_MEASUREMENT = "measurement";
  17. private static final String COL_INGREDIENT_TYPE = "ingredient_type";
  18. private static final String COL_DESCRIPTION = "description";
  19. private static final String COL_IMAGE = "image";
  20. // Category column names
  21. private static final String COL_CATEGORY_NAME = "category_name";
  22. // Recipe column names
  23. private static final String COL_RECIPE_NAME = "recipe_name";
  24. private static final String COL_SERVINGS = "servings";
  25. private static final String COL_CALORIES = "calories";
  26. private static final String COL_PREPARATION_TIME = "preparation_time";
  27. private static final String COL_METHOD = "method";
  28. private static final String COL_COURSE = "course";
  29. private static final String COL_CATEGORY = "category";
  30. private static final String COL_COST = "cost";
  31. private static final String COL_IMAGE2 = "image2";
  32. //Quantity column names
  33. private static final String COL_INGREDIENT_QUANTITY = "ingredient_quantity";
  34. private static final String COL_RECIPE = "recipe";
  35. private static final String COL_INGREDIENT = "ingredient";
  36. //Meal Plan column names
  37. private static final String COL_PLAN_NAME = "plan_name";
  38. private static final String COL_PLAN_RECIPE = "plan_recipe";
  39. //Shopping List column names
  40. private static final String COL_QUANTITY = "quantity";
  41. private static final String COL_PLANID = "planID";
  42. //Plan Recipes column names
  43. private static final String COL_DATE = "date";
  44. private static final String COL_DAY_OF_WEEK = "dayOfWeek";
  45. //Course column names
  46. private static final String COL_COURSE_NAME = "course_name";
  47. //Ingredients column names
  48. private static final String COL_INGREDIENT_NAME = "ingredient_name";
  49. //Measurement column names
  50. private static final String COL_MEASUREMENT_NAME = "measurement_name";
  51. //Ingredient Type column names
  52. private static final String COL_TYPE_NAME = "type_name";
  53. //Allergens column names
  54. private static final String COL_ALLERGEN_NAME = "allergen_name";
  55. /* *************************************************************************************
  56. ************************* CREATE TABLE STATEMENTS **************************************
  57. ****************************************************************************************
  58. */
  59. // Create Table Category
  60. private static final String CREATE_TABLE_CATEGORY = "CREATE TABLE "
  61. + TABLE_CATEGORY + "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_CATEGORY_NAME
  62. + " TEXT," + COL_IMAGE + " INTEGER)";
  63. // Create Table Meal Plan
  64. private static final String CREATE_TABLE_MEAL_PLAN = "CREATE TABLE "
  65. + TABLE_MEAL_PLAN + "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_PLAN_NAME
  66. + " TEXT," + COL_PLAN_RECIPE + " NUMERIC," + " FOREIGN KEY (" + COL_PLAN_RECIPE + ") REFERENCES " + TABLE_PLAN_RECIPES + "(" + COL_ID + "))";
  67. // Create Table Recipe
  68. private static final String CREATE_TABLE_RECIPE = "CREATE TABLE " + TABLE_RECIPE + "(" + COL_ID +
  69. " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_RECIPE_NAME + " TEXT," + COL_DESCRIPTION
  70. + " TEXT," + COL_COURSE + " TEXT," + COL_SERVINGS + " INTEGER," + COL_CALORIES + " NUMERIC,"
  71. + COL_PREPARATION_TIME + " NUMERIC," + COL_METHOD + " TEXT," + COL_CATEGORY + " TEXT,"
  72. + COL_IMAGE + " INTEGER," + COL_IMAGE2 + " TEXT," + COL_COST + " NUMERIC," + " FOREIGN KEY (" + COL_CATEGORY + ") REFERENCES " +
  73. TABLE_CATEGORY + "(" + COL_CATEGORY_NAME + "), FOREIGN KEY (" + COL_COURSE + ") REFERENCES " +
  74. TABLE_COURSE + "(" + COL_COURSE_NAME + "))";
  75. // Create Table Quantity
  76. public static final String CREATE_TABLE_QUANTITY = "CREATE TABLE " + TABLE_QUANTITY + "(" + COL_ID +
  77. " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_INGREDIENT_QUANTITY + " NUMERIC, " +
  78. COL_RECIPE + " TEXT," + COL_INGREDIENT + " TEXT, FOREIGN KEY (" + COL_RECIPE + ") " +
  79. "REFERENCES " + TABLE_RECIPE + "(" + COL_RECIPE_NAME + "), FOREIGN KEY (" + COL_INGREDIENT + ") " +
  80. "REFERENCES " + TABLE_INGREDIENTS + "(" + COL_INGREDIENT_NAME + "))";
  81. // Create Table Shopping List
  82. private static final String CREATE_TABLE_SHOPPING_LIST = "CREATE TABLE " + TABLE_SHOPPING_LIST +
  83. "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_INGREDIENT_TYPE + " INTEGER,"
  84. + COL_QUANTITY + " INTEGER," + COL_MEASUREMENT + " INTEGER," + COL_PLANID + " INTEGER," +
  85. " FOREIGN KEY (" + COL_INGREDIENT_TYPE + ") REFERENCES " + TABLE_INGREDIENT_TYPE + "(" + COL_ID + "), " +
  86. "FOREIGN KEY (" + COL_QUANTITY + ") REFERENCES " + TABLE_QUANTITY + "(" + COL_ID + "), " +
  87. "FOREIGN KEY (" + COL_MEASUREMENT + ") REFERENCES " + TABLE_MEASUREMENT + "(" + COL_ID + "), " +
  88. "FOREIGN KEY (" + COL_PLANID + ") REFERENCES " + TABLE_MEAL_PLAN + "(" + COL_ID + "))";
  89. // Create Table Course
  90. private static final String CREATE_TABLE_COURSE = "CREATE TABLE " + TABLE_COURSE + "(" + COL_ID +
  91. " INTEGER PRIMARY KEY AUTOINCREMENT, " + COL_COURSE_NAME + " TEXT," + COL_IMAGE + " INTEGER)";
  92. // Create Table Ingredients
  93. public static final String CREATE_TABLE_INGREDIENTS = "CREATE TABLE " + TABLE_INGREDIENTS + "("
  94. + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_INGREDIENT_NAME + " TEXT,"
  95. + COL_DESCRIPTION + " TEXT," + COL_MEASUREMENT_NAME + " TEXT," + COL_INGREDIENT_TYPE + " TEXT, " +
  96. "FOREIGN KEY (" + COL_MEASUREMENT_NAME + ") REFERENCES " + TABLE_MEASUREMENT + "(" + COL_MEASUREMENT_NAME + "), " +
  97. "FOREIGN KEY (" + COL_INGREDIENT_TYPE + ") REFERENCES " + TABLE_INGREDIENT_TYPE + "(" + COL_TYPE_NAME + "))";
  98. // Create Table Measurement
  99. private static final String CREATE_TABLE_MEASUREMENT = "CREATE TABLE " + TABLE_MEASUREMENT + "(" +
  100. COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_MEASUREMENT_NAME + " TEXT)";
  101. // Create Table Ingredient Type
  102. private static final String CREATE_TABLE_INGREDIENT_TYPE = "CREATE TABLE " + TABLE_INGREDIENT_TYPE +
  103. "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_TYPE_NAME + " TEXT)";
  104. // Create Table Plan Recipes
  105. private static final String CREATE_TABLE_PLAN_RECIPES = "CREATE TABLE " + TABLE_PLAN_RECIPES + "(" +
  106. COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_DATE + " DATE," +
  107. COL_DAY_OF_WEEK + " TEXT," + COL_RECIPE_NAME + " TEXT, FOREIGN KEY (" + COL_RECIPE_NAME + ") " +
  108. "REFERENCES " + TABLE_RECIPE + "(" + COL_RECIPE_NAME + "))";
  109. // Create Table Allergens
  110. private static final String CREATE_TABLE_ALLERGENS = "CREATE TABLE " + TABLE_ALLERGENS +
  111. "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_ALLERGEN_NAME + " TEXT)";
  112. // Create Table Recipes_Allergens
  113. private static final String CREATE_TABLES_RECIPES_ALLERGENS = "CREATE TABLE " + TABLE_RECIPES_ALLERGENS +
  114. "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_ALLERGEN_NAME + " TEXT," +
  115. COL_RECIPE + " TEXT," + "FOREIGN KEY (" + COL_ALLERGEN_NAME + ") REFERENCES " + TABLE_ALLERGENS +
  116. "(" + COL_ALLERGEN_NAME +"), " + "FOREIGN KEY (" + COL_RECIPE + ") REFERENCES " + TABLE_RECIPE +
  117. "(" + COL_RECIPE_NAME + "))";
  118. // Create Table Favourites
  119. private static final String CREATE_TABLE_FAVOURITES = "CREATE TABLE " + TABLE_FAVOURITES + "(" +
  120. COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_RECIPE_NAME + " TEXT," +
  121. "FOREIGN KEY (" + COL_RECIPE_NAME + ") REFERENCES " + TABLE_RECIPE + "(" + COL_RECIPE_NAME + "))";
  122. public final String INSERT_MEASUREMENT =
  123. "INSERT INTO " + TABLE_MEASUREMENT + "("
  124. + COL_MEASUREMENT_NAME + ") values ";
  125. public final String INSERT_INGREDIENT_TYPE =
  126. " INSERT INTO " + TABLE_INGREDIENT_TYPE + "("
  127. + COL_TYPE_NAME + ") values ";
  128. public final String INSERT_INGREDIENT =
  129. " INSERT INTO " + TABLE_INGREDIENTS + "("
  130. + COL_INGREDIENT_NAME + "," + COL_DESCRIPTION + ","
  131. + COL_MEASUREMENT_NAME + "," + COL_INGREDIENT_TYPE + ") values ";
  132. public final String INSERT_CATEGORY =
  133. " INSERT INTO " + TABLE_CATEGORY + "("
  134. + COL_CATEGORY_NAME + "," + COL_IMAGE + ") values ";
  135. public final String INSERT_COURSE =
  136. " INSERT INTO " + TABLE_COURSE + "("
  137. + COL_COURSE_NAME + "," + COL_IMAGE + ") values ";
  138. public final String INSERT_RECIPE =
  139. " INSERT INTO " + TABLE_RECIPE + "("
  140. + COL_RECIPE_NAME + "," + COL_DESCRIPTION + ","
  141. + COL_COURSE + "," + COL_SERVINGS + ","
  142. + COL_CALORIES + "," + COL_PREPARATION_TIME + ","
  143. + COL_METHOD + "," + COL_CATEGORY + ","
  144. + COL_IMAGE + ","
  145. + COL_IMAGE2 + ","
  146. + COL_COST + ") values";
  147. public final String INSERT_QUANTITY =
  148. " INSERT INTO " + TABLE_QUANTITY + "("
  149. + COL_INGREDIENT_QUANTITY + ","
  150. + COL_RECIPE + ","
  151. + COL_INGREDIENT + ") values";
  152. public final String INSERT_ALLERGEN =
  153. " INSERT INTO " + TABLE_ALLERGENS + "("
  154. + COL_ALLERGEN_NAME + ") values";
  155. public final String INSERT_RECIPES_ALLERGENS =
  156. " INSERT INTO " + TABLE_RECIPES_ALLERGENS + "("
  157. + COL_ALLERGEN_NAME + ","
  158. + COL_RECIPE + ") values";
  159. public final String INSERT_FAVOURITE =
  160. " INSERT INTO " + TABLE_FAVOURITES + "("
  161. + COL_RECIPE_NAME + ") values";
  162. public final String INSERT_PLAN_RECIPE =
  163. " INSERT INTO " + TABLE_PLAN_RECIPES + "("
  164. + COL_DATE + ","
  165. + COL_DAY_OF_WEEK + ","
  166. + COL_RECIPE_NAME + ") values";
  167. public final String INSERT_PLAN =
  168. " INSERT INTO " + TABLE_MEAL_PLAN + "("
  169. + COL_PLAN_NAME + ","
  170. + COL_PLAN_RECIPE + ") values";

答案1

得分: 1

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

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

然后，替代这段代码：

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

请使用以下代码：

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

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

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

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

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

Then instead of:

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

do this:

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

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

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

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

发表评论

匿名网友

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

确定