Android Studio中SQLite报no such table错误，数据无法显示到RecyclerView

问题根源 #

1. 数据库名称大小写不匹配：获取数据时打开的数据库是workifyDatabase（首字母D大写），而保存数据的代码中若database实例对应小写名称的数据库（如workifydatabase），两者会被视为完全独立的数据库文件。你在一个库中创建表并插入数据，却在另一个空库中查询，必然触发no such table错误。
2. 查询时未确保表已创建：获取数据的方法中未执行CREATE TABLE IF NOT EXISTS语句，若打开的是全新数据库文件，表根本不存在，直接查询会失败。

修复步骤 #

1. 统一数据库名称

将两处的数据库名称改成完全一致的字符串，比如统一使用workifyDatabase：

• 保存数据的方法中，确保database实例是通过openOrCreateDatabase("workifyDatabase", MODE_PRIVATE, null)打开的（若之前不是则修改）。
• 获取数据的方法保持当前名称不变，或统一改为小写，只要两边完全一致即可。

2. 在查询前确保表已创建

修改getData()方法，执行查询前先检查表是否存在：

private void getData() {
    try {
        SQLiteDatabase sqLiteDatabase = requireContext().openOrCreateDatabase("workifyDatabase", MODE_PRIVATE, null);
        // 先执行建表语句，确保表存在
        sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS workifydatabase(id INTEGER PRIMARY KEY,workname VARCHAR,logoid INTEGER,year VARCHAR,workinghours INTEGER)");
        Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM workifydatabase",null);
        int nameIx = cursor.getColumnIndex("workname");
        int idIx = cursor.getColumnIndex("id");
        int hourIx = cursor.getColumnIndex("workinghours");
        int yearIx = cursor.getColumnIndex("year");

        while (cursor.moveToNext()){
            String name = cursor.getString(nameIx);
            int id= cursor.getInt(idIx);
            String year = cursor.getString(yearIx);
            int hour = cursor.getInt(hourIx);
            Task task = new Task(name,id,hour,year);
            TaskArrayList.add(task);
        }

        taskAdapter.notifyDataSetChanged();
        cursor.close();
    } catch(Exception e) {
        e.printStackTrace();
    }
}

3. 优化数据库管理（推荐）

手动调用openOrCreateDatabase容易出现实例管理混乱，建议使用Android官方推荐的SQLiteOpenHelper封装数据库操作：

public class WorkDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "workifyDatabase";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_NAME = "workifydatabase";

    public WorkDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTableSql = "CREATE TABLE " + TABLE_NAME + "(" +
                "id INTEGER PRIMARY KEY," +
                "workname VARCHAR," +
                "logoid INTEGER," +
                "year VARCHAR," +
                "workinghours INTEGER)";
        db.execSQL(createTableSql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 后续版本升级时处理表结构变更
    }
}

修改保存和获取数据的方法，使用该Helper：

• 保存数据：

private void saveWorkRecord() {
    String workName = editTextTaskName.getText().toString();
    int workingHours = 0; 
    int logoId = selectedLogoResourceId; 
    Date date = new Date(); 
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy", Locale.getDefault());
    String formattedDate = dateFormat.format(date); 

    WorkDatabaseHelper dbHelper = new WorkDatabaseHelper(this);
    SQLiteDatabase database = dbHelper.getWritableDatabase();
    try {
        String sqlString = "INSERT INTO workifydatabase(workname, logoid, year, workinghours) VALUES(?,?,?,?)";
        SQLiteStatement sqLiteStatement = database.compileStatement(sqlString);
        sqLiteStatement.bindString(1, workName);
        sqLiteStatement.bindLong(2, logoId); 
        sqLiteStatement.bindString(3, formattedDate);
        sqLiteStatement.bindLong(4, workingHours); 
        sqLiteStatement.executeInsert();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        database.close();
    }
    finish();
}

• 获取数据：

private void getData() {
    WorkDatabaseHelper dbHelper = new WorkDatabaseHelper(requireContext());
    SQLiteDatabase sqLiteDatabase = dbHelper.getReadableDatabase();
    try {
        Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM workifydatabase",null);
        int nameIx = cursor.getColumnIndex("workname");
        int idIx = cursor.getColumnIndex("id");
        int hourIx = cursor.getColumnIndex("workinghours");
        int yearIx = cursor.getColumnIndex("year");

        while (cursor.moveToNext()){
            String name = cursor.getString(nameIx);
            int id= cursor.getInt(idIx);
            String year = cursor.getString(yearIx);
            int hour = cursor.getInt(hourIx);
            Task task = new Task(name,id,hour,year);
            TaskArrayList.add(task);
        }

        taskAdapter.notifyDataSetChanged();
        cursor.close();
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        sqLiteDatabase.close();
    }
}

4. 清理旧数据库残留

测试前卸载App，或通过Android Studio的Device File Explorer删除/data/data/你的应用包名/databases/下的旧数据库文件，避免残留文件干扰测试。

内容的提问来源于stack exchange，提问作者Yasin G.