Android SQLite报错：no such column: place 问题求助

我在运行应用时遇到了no such column: place的SQLite错误。原本我的应用可以正常存储和显示事件，但是在新增了place字段后就崩溃了，想请教一下怎么解决这个问题，提前感谢大家！

我编写的代码： #

DbOpenHelper类

public class DbOpenHelper extends SQLiteOpenHelper {

private static final String CREATE_EVENTS_TABLE = "create table " + DBStructure.EVENT_TABLE_NAME + "( " + DBStructure.ID + "INTEGER PRIMARY KEY AUTOINCREMENT, " + DBStructure.EVENT+ " TEXT, " + DBStructure.TIME + " TEXT, " + DBStructure.DATE+ " TEXT, " + DBStructure.MONTH+ " TEXT, " + DBStructure.PLACE + " TEXT NOT NULL, " + DBStructure.YEAR+ " TEXT);"; 
private static final String DROP_EVENTS_TABLE = "DROP TABLE IF EXISTS " + DBStructure.EVENT_TABLE_NAME;

public DbOpenHelper(@Nullable Context context) {

super(context, DBStructure.DB_NAME, null, DBStructure.DB_VERSION);

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_EVENTS_TABLE);

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

//if(newVersion>oldVersion)

db.execSQL(DROP_EVENTS_TABLE);

onCreate(db);

}

public void SaveEvent (String event, String place, String time, String date, String month, String year, SQLiteDatabase database) {

ContentValues contentValues = new ContentValues();

contentValues.put(DBStructure.EVENT, event);

contentValues.put(DBStructure.PLACE, place);

contentValues.put(DBStructure.TIME, time);

contentValues.put(DBStructure.DATE, date);

contentValues.put(DBStructure.MONTH, month);

contentValues.put(DBStructure.YEAR, year);

database.insert(DBStructure.EVENT_TABLE_NAME, null, contentValues);

}

public Cursor ReadEvents (String date, SQLiteDatabase database){

String [] Projections = {DBStructure.EVENT, DBStructure.PLACE, DBStructure.TIME, DBStructure.DATE, DBStructure.MONTH, DBStructure.YEAR};

String Selection = DBStructure.DATE + "=?";

String [] SelectionArgs = {date};

return database.query(DBStructure.EVENT_TABLE_NAME, Projections, Selection, SelectionArgs, null, null, null );

}

public Cursor ReadEventsPerMonth (String month, String year, SQLiteDatabase database){

String [] Projections = {DBStructure.EVENT, DBStructure.PLACE, DBStructure.TIME, DBStructure.DATE, DBStructure.MONTH, DBStructure.YEAR};

String Selection = DBStructure.MONTH + "=? and " + DBStructure.YEAR + "=?";

String [] SelectionArgs = {month, year};

return database.query(DBStructure.EVENT_TABLE_NAME, Projections, Selection, SelectionArgs, null, null, null );

}

}

DBStructure类

public class DBStructure {

public static final String DB_NAME = "EVENTS_DB";

public static final int DB_VERSION = 4;

public static final String EVENT_TABLE_NAME = "eventsTable";

public static final String EVENT = "event";

public static final String PLACE = "place";

public static final String TIME = "time";

public static final String DATE = "date";

public static final String MONTH = "month";

public static final String YEAR = "year";

public static final String ID = "ID";

}

CustomCalendarView类

public class CustomCalendarView extends LinearLayout {

ImageButton PreviousButton, NextButton;

TextView CurrentDate;

GridView gridView;

private static final int MAX_CALENDAR_DAYS=42;

Calendar calendar = Calendar.getInstance(Locale.ENGLISH);

Context context;

SimpleDateFormat dateFormat = new SimpleDateFormat("MMM yyy", Locale.ENGLISH);

SimpleDateFormat monthFormat = new SimpleDateFormat("MMM", Locale.ENGLISH);

SimpleDateFormat yearFormat = new SimpleDateFormat("yyy", Locale.ENGLISH);

SimpleDateFormat eventDateFormat = new SimpleDateFormat("yyy-MM-dd", Locale.ENGLISH);

MyGridAdapter myGridAdapter;

AlertDialog alertDialog;

List <Date> dates = new ArrayList<>();

List<Events> eventsList = new ArrayList<>();

DbOpenHelper dbOpenHelper;

public CustomCalendarView(Context context) {

super(context);

}

public CustomCalendarView(Context context, @Nullable AttributeSet attrs) {

super(context, attrs);

this.context = context;

InitializeLayout ();

setUpCalendar();

PreviousButton.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

calendar.add(Calendar.MONTH, -1);

setUpCalendar();

}

});

NextButton.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

calendar.add(Calendar.MONTH, 1);

setUpCalendar();

}

});

// seems to set the clicked day

gridView.setOnItemClickListener(new AdapterView.OnItemClickListener() {

@Override

public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

AlertDialog.Builder builder = new AlertDialog.Builder(context);

builder.setCancelable(true);

View addView = LayoutInflater.from(parent.getContext()).inflate(R.layout.add_new_event_layout, null);

EditText EventName = addView.findViewById(R.id.event_name);

EditText WhenWhere = addView.findViewById(R.id.when_where);

EditText Place = addView.findViewById(R.id.place);

TextView EvenTime = addView.findViewById(R.id.event_time);

ImageButton SetTime = addView.findViewById(R.id.set_event_time);

Button AddEvent = addView.findViewById(R.id.add_event);

// this set the time. ie: thi is where the person can choose the time in term of hour...

SetTime.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

Calendar calendar = Calendar.getInstance();

int hours = calendar.get(Calendar.HOUR_OF_DAY);

int minutes = calendar.get(Calendar.MINUTE);

TimePickerDialog timePickerDialog =  new TimePickerDialog(addView.getContext(), androidx.appcompat.R.style.Theme_AppCompat_Dialog, new TimePickerDialog.OnTimeSetListener() {

@Override

public void onTimeSet(TimePicker view, int hourOfDay, int minute) {

Calendar c = Calendar.getInstance();

c.set(Calendar.HOUR_OF_DAY, hourOfDay);

c.set(Calendar.MINUTE, minute);

c.setTimeZone(TimeZone.getDefault());

SimpleDateFormat hFormat = new SimpleDateFormat("K:mm a", Locale.ENGLISH);

String event_Time = hFormat.format(c.getTime());

EvenTime.setText(event_Time);

}

}, hours, minutes, false );

timePickerDialog.show();

}

});

final String date = eventDateFormat.format(dates.get(position));

final String month = monthFormat.format(dates.get(position));

final String year = yearFormat.format(dates.get(position));

// this add an event. Notice here in the onclick the SaveEvent constructor which helps save the event added.

//that SaveEvent can be find set up down there.

AddEvent.setOnClickListener(new OnClickListener() {

@Override

public void onClick(View v) {

SaveEvent (EventName.getText().toString(), Place.getText().toString(), EvenTime.getText().toString(), date, month, year );

setUpCalendar();

alertDialog.dismiss();

}

});

builder.setView(addView);

alertDialog = builder.create();

alertDialog.show();

}

});

// here the event saved gets call and display

gridView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {

@Override

public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {

String date = eventDateFormat.format(dates.get(position));

AlertDialog.Builder builder = new AlertDialog.Builder(context);

builder.setCancelable(true);

View showView = LayoutInflater.from(parent.getContext()).inflate(R.layout.show_events_layout, null);

RecyclerView recyclerView = showView.findViewById(R.id.eventsRv);

RecyclerView.LayoutManager layoutManager = new LinearLayoutManager(showView.getContext());

recyclerView.setLayoutManager(layoutManager);

recyclerView.setHasFixedSize(true);

EventRecyclerAdapter eventRecyclerAdapter = new EventRecyclerAdapter(showView.getContext(), CollectEventByDate(date));

recyclerView.setAdapter(eventRecyclerAdapter);

eventRecyclerAdapter.notifyDataSetChanged();

builder.setView(showView);

alertDialog = builder.create();

alertDialog.show();

return true;

}

});

}

private ArrayList<Events> CollectEventByDate (String date){

ArrayList<Events> arrayList = new ArrayList<>();

dbOpenHelper = new DbOpenHelper(context);

SQLiteDatabase database = dbOpenHelper.getReadableDatabase();

Cursor cursor = dbOpenHelper.ReadEvents(date, database);

while (cursor.moveToNext()){

String event = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.EVENT));

String place = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.PLACE));

String time = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.TIME));

String Date = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.DATE));

String month = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.MONTH));

String Year = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.YEAR));

Events events = new Events(event, place, time, Date, month, Year);

arrayList.add(events);

}

cursor.close();

dbOpenHelper.close();

return arrayList;

}

public CustomCalendarView(Context context, @Nullable AttributeSet attrs, int defStyleAttr) {

super(context, attrs, defStyleAttr);

}

// this, save the event and display text saying that the event is saved.

private void SaveEvent ( String event, String place, String time, String date, String month, String year){

dbOpenHelper = new DbOpenHelper(context);

SQLiteDatabase database = dbOpenHelper.getWritableDatabase();

dbOpenHelper.SaveEvent(event, place, time, date, month, year, database);

dbOpenHelper.close();

Toast.makeText(context, "Events Saved", Toast.LENGTH_SHORT).show();

}

private void InitializeLayout (){

LayoutInflater inflater = (LayoutInflater)context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);

View view = inflater.inflate(R.layout.calendar_layout, this);

NextButton = view.findViewById(R.id.nextBtn);

PreviousButton = view.findViewById(R.id.previousBtn);

CurrentDate = view.findViewById(R.id.current_date);

gridView = view.findViewById(R.id.gridView);

}

private void setUpCalendar(){

String currentDate = dateFormat.format(calendar.getTime());

CurrentDate.setText(currentDate);

dates.clear();

Calendar monthCalendar = (Calendar) calendar.clone();

monthCalendar.set(Calendar.DAY_OF_MONTH,1);

int FirstDaysOfMonth = monthCalendar.get(Calendar.DAY_OF_WEEK)-1;

monthCalendar.add(Calendar.DAY_OF_MONTH, -FirstDaysOfMonth);

CollectEventsPerMonth(monthFormat.format(calendar.getTime()), yearFormat.format(calendar.getTime()));

while (dates.size() < MAX_CALENDAR_DAYS){

dates.add(monthCalendar.getTime());

monthCalendar.add(Calendar.DAY_OF_MONTH, 1);

}

myGridAdapter = new MyGridAdapter(context, dates, calendar, eventsList);

gridView.setAdapter(myGridAdapter);

}

//Here we gave the constructor that reads from our database

//after setting it here, the CollectEventsPerMonth is put in setUpCalendar

private void CollectEventsPerMonth (String Month, String year){

eventsList.clear();

dbOpenHelper = new DbOpenHelper(context);

Cursor cursor;

SQLiteDatabase database = dbOpenHelper.getReadableDatabase();

cursor = dbOpenHelper.ReadEventsPerMonth(Month, year, database);

while (cursor.moveToNext()){

String event = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.EVENT));

String place = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.PLACE));

String time = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.TIME));

String date = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.DATE));

String month = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.MONTH));

String Year = cursor.getString(cursor.getColumnIndexOrThrow(DBStructure.YEAR));

Events events = new Events(event, place, time, date, month, Year);

eventsList.add(events);

}

cursor.close();

dbOpenHelper.close();

}

}

报错信息： #

2023-01-10 15:29:27.408 4603-4603/? E/AndroidRuntime: Caused by: android.database.sqlite.SQLiteException: no such column: place (code 1 SQLITE_ERROR): , while compiling: SELECT event, place, time, date, month, year FROM eventsTable WHERE month=? and year=?
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1045)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:652)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:61)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1545)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1392)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1263)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1431)
at com.example.android.calendarpoubelle.DbOpenHelper.ReadEventsPerMonth(DbOpenHelper.java:60)
at com.example.android.calendarpoubelle.CustomCalendarView.CollectEventsPerMonth(CustomCalendarView.java:243)
at com.example.android.calendarpoubelle.CustomCalendarView.setUpCalendar(CustomCalendarView.java:222)
at com.example.android.calendarpoubelle.CustomCalendarView.<init>(CustomCalendarView.java:61)
... 28 more

问题解决思路： #

我帮你排查了下，核心问题出在建表语句的语法错误和版本升级的细节上：

1. 建表语句的空格缺失导致字段定义错位
   看你CREATE_EVENTS_TABLE里的这段：DBStructure.ID + "INTEGER PRIMARY KEY AUTOINCREMENT"，ID和INTEGER之间没有空格！SQLite会把它解析成一个叫IDINTEGER的字段，后面的所有字段定义都会跟着错位，最终导致place字段根本没被正确创建出来。

2. 版本升级逻辑没问题，但要确保触发条件满足
   你已经把DB_VERSION改成4了，这步是对的，但要保证之前安装的旧应用版本号低于4，或者直接卸载旧应用重新运行，这样onUpgrade方法才会执行，删除旧表并创建包含place字段的新表。

具体修复步骤： #

1. 修正建表语句的空格问题
   把建表语句改成下面这样，重点是ID后面加空格：

private static final String CREATE_EVENTS_TABLE = "create table " + DBStructure.EVENT_TABLE_NAME + "( " + DBStructure.ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + DBStructure.EVENT+ " TEXT, " + DBStructure.TIME + " TEXT, " + DBStructure.DATE+ " TEXT, " + DBStructure.MONTH+ " TEXT, " + DBStructure.PLACE + " TEXT NOT NULL, " + DBStructure.YEAR+ " TEXT);";

2. 确保数据库版本升级生效
   如果模拟器/真机上已经安装过旧版本应用，直接卸载后再重新运行；如果是首次安装，只要版本号设置正确，onCreate会直接创建正确的表结构。

3. 验证修复结果
   重新运行应用后，尝试添加带place的事件，然后查看是否能正常读取显示，应该就不会再出现no such column: place的错误了。

备注：内容来源于stack exchange，提问作者Brice Ulrich