Android 9及以下版本使用现有SQLite数据库报No such table错误

问题描述 #

在Android Studio中使用现有SQLite数据库时，该数据库在Android 10及以上版本可正常运行，但在Android 9及以下版本抛出No such table错误。

相关代码 #

DatabaseHelper类

package com.example.testlocaldatabase;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Build;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

public class DatabaseHelper extends SQLiteOpenHelper {

  private static String DB_NAME = "myDatabase.SqLite";
  private static String DB_PATH = "";
  private static int DB_VERSION = 1;

  private SQLiteDatabase database;
  private Context context;
  private boolean needUpdate = false;

  public DatabaseHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
    if (Build.VERSION.SDK_INT >= 17) {
      DB_PATH = context.getApplicationInfo().dataDir + "/databases/";

    } else {
      DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
    }
    this.context = context;
    copyDatabase();
    this.getReadableDatabase();
  }

  @Override
  public void onCreate(SQLiteDatabase sqLiteDatabase) {

  }

  @Override
  public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
    if (i1 > i) {
      needUpdate = true;
    }

  }

  private void copyDatabase() {

    if (!checkDatabase()) {
      this.getReadableDatabase();
      this.close();
      try {
        copyDBFile();
      } catch (IOException e) {
        throw new Error("Error copying database");
      }//catch
    }//if
  }//copyDatabase

  private boolean checkDatabase() {
    File dbFile = new File(DB_PATH + DB_NAME);
    if (dbFile.exists()) {
      return true;
    } else {
      return false;
    }
  }

  private void copyDBFile() throws IOException {
    InputStream inputStream = context.getAssets().open(DB_NAME);
    OutputStream outputStream = new FileOutputStream(DB_PATH + DB_NAME);
    byte[] buffer = new byte[1024];
    int length;
    while ((length = inputStream.read(buffer)) > 0) {
      outputStream.write(buffer, 0, length);
    }
    outputStream.flush();
    outputStream.close();
    inputStream.close();
  }

  public void updateDatabase() throws IOException {
    if (needUpdate) {
      File dbFile = new File(DB_PATH + DB_NAME);
      if (dbFile.exists())
        dbFile.delete();
      copyDatabase();
      needUpdate = false;

    }//if
  }//updateDatabase

  @Override
  public synchronized void close() {
    if (database != null) {
      database.close();
    }
    super.close();

  }
}

MainActivity类

package com.example.testlocaldatabase;

import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import java.io.IOException;

public class MainActivity extends AppCompatActivity {

  private DatabaseHelper databaseHelper;
  private SQLiteDatabase database;

  @Override
  protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    databaseHelper = new DatabaseHelper(MainActivity.this);
    try {
      databaseHelper.updateDatabase();
    } catch (IOException e) {
      e.printStackTrace();
    }
    database = databaseHelper.getReadableDatabase();

    // pull out data from database
    Cursor cursor = database.rawQuery("select * from drugs", null);
    while (cursor.moveToNext()) {
      String b = cursor.getString(cursor.getColumnIndexOrThrow("drugName"));
      Log.i("LOG", "" + b);
    }
    cursor.close();

  }

}

错误日志 #

android.database.sqlite.SQLiteException: no such table: drugs (code 1 SQLITE_ERROR): , while compiling: select * from drugs

问题根源 #

1. 文件名大小写不匹配：Android 9及以下基于Linux内核，文件系统区分大小写。若代码中DB_NAME的后缀为SqLite，而assets目录下实际文件后缀为sqlite，会导致无法读取资源，复制出空数据库。
2. 数据库有效性检查缺失：原代码仅判断文件是否存在，若之前getReadableDatabase()自动生成过空数据库文件，即使文件存在也无目标表。
3. 初始化流程冲突：构造函数中先复制数据库再调用getReadableDatabase()，可能导致空数据库被优先加载，覆盖刚复制的有效数据库。

修复方案 #

1. 统一文件名大小写 #

确保assets目录下的数据库文件名与代码中DB_NAME完全一致，建议统一使用小写后缀：

private static String DB_NAME = "myDatabase.sqlite";

2. 完善数据库有效性检查 #

修改checkDatabase()方法，不仅检查文件存在，还要验证数据库内是否存在目标表：

private boolean checkDatabase() {
    File dbFile = new File(DB_PATH + DB_NAME);
    if (!dbFile.exists()) {
        return false;
    }
    // 验证数据库是否包含目标表
    SQLiteDatabase checkDB = null;
    try {
        checkDB = SQLiteDatabase.openDatabase(dbFile.getPath(), null, SQLiteDatabase.OPEN_READONLY);
        Cursor cursor = checkDB.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='drugs'", null);
        boolean hasTable = cursor.moveToFirst();
        cursor.close();
        return hasTable;
    } catch (SQLiteException e) {
        return false;
    } finally {
        if (checkDB != null) checkDB.close();
    }
}

3. 调整初始化流程 #

移除构造函数中多余的getReadableDatabase()调用，确保复制完成后再打开数据库，同时提前创建databases目录：

public DatabaseHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
    if (Build.VERSION.SDK_INT >= 17) {
        DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
    } else {
        DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
    }
    this.context = context;
    // 创建数据库目录（若不存在）
    File dbDir = new File(DB_PATH);
    if (!dbDir.exists()) dbDir.mkdirs();
    copyDatabase();
}

4. 修正更新逻辑 #

更新数据库时先关闭连接再删除旧文件，避免文件占用：

public void updateDatabase() throws IOException {
    if (needUpdate) {
        File dbFile = new File(DB_PATH + DB_NAME);
        if (dbFile.exists()) {
            // 关闭所有打开的数据库连接
            if (database != null) database.close();
            super.close();
            // 删除旧数据库
            if (!dbFile.delete()) {
                throw new IOException("无法删除旧数据库文件");
            }
        }
        copyDatabase();
        needUpdate = false;
    }
}

5. 优化MainActivity数据库获取流程 #

确保更新完成后再获取数据库实例，避免加载无效文件：

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    databaseHelper = new DatabaseHelper(MainActivity.this);
    try {
        databaseHelper.updateDatabase();
    } catch (IOException e) {
        e.printStackTrace();
        return; // 更新失败时终止后续操作
    }
    database = databaseHelper.getReadableDatabase();

    Cursor cursor = database.rawQuery("select * from drugs", null);
    while (cursor.moveToNext()) {
        String b = cursor.getString(cursor.getColumnIndexOrThrow("drugName"));
        Log.i("LOG", b);
    }
    cursor.close();
}

内容的提问来源于stack exchange，提问作者Amirhussein