Android读取assets中SQLite数据库报no such table错误求助

错误日志 #

(1) no such table: Dictionary in "SELECT Arabic FROM Dictionary WHERE Arabic LIKE ?  ORDER BY Arabic"
2023-04-20 03:27:40.261 17406-17406 AndroidRuntime          com.example.e_qtishaddictionary3     D  Shutting down VM
2023-04-20 03:27:40.264 17406-17406 AndroidRuntime          com.example.e_qtishaddictionary3     E  FATAL EXCEPTION: main
Process: com.example.e_qtishaddictionary3, PID: 17406
android.database.sqlite.SQLiteException: no such table: Dictionary (code 1 SQLITE_ERROR[1]): , while compiling: SELECT Arabic FROM Dictionary WHERE Arabic LIKE ?  ORDER BY Arabic
at com.example.e_qtishaddictionary3.DbHelper.getArabicWord(DbHelper.java:84)
at com.example.e_qtishaddictionary3.MainActivity$1.onTextChanged(MainActivity.java:44)

相关代码 #

DbHelper.java #

package ...
import ...

public class DbHelper extends SQLiteOpenHelper {
    String dbName;
    Context context;
    String dbPath;

    String tableName = "Dictionary";
    String ArabicCol = "Arabic";

    public DbHelper(MainActivity mcontext, String name, int version) {
        super(mcontext, name, null, version);

        this.context = mcontext;
        this.dbName = name;
        this.dbPath = "/data/data" + "com.example.e_qtishaddictionary3" + "databases";
    }
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if(newVersion>oldVersion)
            CopyDatabase();
    }
    public void CheckDb(){
        SQLiteDatabase CheckDb =  null;
        try {
            String filePath = dbPath + dbName;

            CheckDb = SQLiteDatabase.openDatabase(filePath, null, 0);
        }catch (Exception e) {
        }

        if (CheckDb != null) {
            Log.d("CheckDb", "Database already exists");
            CheckDb.close();
        } else {
            CopyDatabase();
        }
    }
    public void CopyDatabase() {
        this.getReadableDatabase();
        try{
            InputStream is = context.getAssets().open(dbName);
            OutputStream os = new FileOutputStream(dbPath + dbName);

            byte[] buffer = new byte[1024];

            int len;
            while((len = is.read(buffer))>0){
                os.write(buffer,0,len);
            }
            os.flush();
            is.close();
            os.close();
        }catch (Exception e) {e.printStackTrace();}
        Log.d("CopyDb", "Database Copied");
    }
    public void OpenDatabase(){
        String filepath = dbPath + dbName;
        SQLiteDatabase.openDatabase(filepath,null,0);
    }
    public ArrayList<String> getArabicWord(String query){
        ArrayList<String> arabicList = new ArrayList<>();

        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();

        Cursor cursor;
        cursor = sqLiteDatabase.query(
                tableName,
                new String[]{ArabicCol},
                ArabicCol + " LIKE ? ",
                new String[]{query + "%"},
                null, null,
                ArabicCol
        );
        int index = cursor.getColumnIndex(ArabicCol);
        while (cursor.moveToNext()){
            arabicList.add(cursor.getString(index));
        }

        sqLiteDatabase.close();
        cursor.close();
        return arabicList;
    }
}

MainActivity.java #

package ...
import ...

public class MainActivity extends AppCompatActivity {
    AutoCompleteTextView autoCompleteTextView;
    DbHelper dbHelper;

    ArrayList<String> newList;

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

        autoCompleteTextView = findViewById(R.id.autotxt);
        dbHelper = new DbHelper(this,"sample.db",1);
        try{
            dbHelper.CheckDb();
            dbHelper.OpenDatabase();
        }catch(Exception e) {
            e.printStackTrace();
        }

        newList = new ArrayList<>();

        autoCompleteTextView.addTextChangedListener(new TextWatcher() {
            @Override
            public void beforeTextChanged(CharSequence s, int i, int i1, int i2) {

            }

            @Override
            public void onTextChanged(CharSequence s, int i, int i1, int i2) {
                if(s.length() > 0 ){
                    newList.addAll(dbHelper.getArabicWord(s.toString()));

                    autoCompleteTextView.setAdapter(new ArrayAdapter<>(MainActivity.this, android.R.layout.simple_list_item_1, newList));
                }
            }

            @Override
            public void afterTextChanged(Editable s) {

            }
        });

    }
}

问题排查与解决 #

核心问题 #

代码存在几个关键错误，导致assets中的数据库未被正确复制到应用的数据库目录，最终SQLiteOpenHelper创建了空数据库，自然找不到Dictionary表：

1. 数据库路径拼接错误：dbPath末尾缺少分隔符/，导致路径无效
2. getReadableDatabase()提前创建空数据库，覆盖后续复制的文件
3. OpenDatabase()方法打开数据库后未持有引用，后续仍使用空数据库
4. 未检查并创建databases目录，复制文件可能失败

修复步骤 #

1. 修正数据库路径与目录创建

修改DbHelper构造方法和CopyDatabase方法，确保路径正确并创建目录：

public DbHelper(MainActivity mcontext, String name, int version) {
    super(mcontext, name, null, version);
    this.context = mcontext;
    this.dbName = name;
    // 通过Context获取正确的数据库父路径，避免硬编码包名
    this.dbPath = context.getDatabasePath(name).getParent() + "/";
}

public void CopyDatabase() {
    try {
        File dbDir = new File(dbPath);
        // 不存在则创建数据库目录
        if (!dbDir.exists()) {
            dbDir.mkdirs();
        }

        InputStream is = context.getAssets().open(dbName);
        OutputStream os = new FileOutputStream(dbPath + dbName);

        byte[] buffer = new byte[1024];
        int len;
        while((len = is.read(buffer))>0){
            os.write(buffer,0,len);
        }
        os.flush();
        is.close();
        os.close();
    }catch (Exception e) {
        e.printStackTrace();
    }
    Log.d("CopyDb", "Database Copied");
}

2. 移除CopyDatabase中不必要的getReadableDatabase()调用

删除CopyDatabase方法内的this.getReadableDatabase();，避免提前创建空数据库覆盖assets文件。

3. 修正CheckDb方法的路径判断逻辑

先判断文件是否存在，再尝试打开：

public void CheckDb(){
    SQLiteDatabase CheckDb = null;
    try {
        String filePath = dbPath + dbName;
        File dbFile = new File(filePath);
        if (dbFile.exists()) {
            CheckDb = SQLiteDatabase.openDatabase(filePath, null, SQLiteDatabase.OPEN_READONLY);
        }
    }catch (Exception e) {
        e.printStackTrace();
    }

    if (CheckDb != null) {
        Log.d("CheckDb", "Database already exists");
        CheckDb.close();
    } else {
        CopyDatabase();
    }
}

4. 修改getArabicWord方法，直接打开复制后的数据库

绕过SQLiteOpenHelper的默认数据库，直接使用你复制的文件：

public ArrayList<String> getArabicWord(String query){
    ArrayList<String> arabicList = new ArrayList<>();
    SQLiteDatabase sqLiteDatabase = null;
    Cursor cursor = null;
    try {
        String filePath = dbPath + dbName;
        sqLiteDatabase = SQLiteDatabase.openDatabase(filePath, null, SQLiteDatabase.OPEN_READONLY);
        
        cursor = sqLiteDatabase.query(
                tableName,
                new String[]{ArabicCol},
                ArabicCol + " LIKE ? ",
                new String[]{query + "%"},
                null, null,
                ArabicCol
        );
        
        int index = cursor.getColumnIndex(ArabicCol);
        while (cursor.moveToNext()){
            arabicList.add(cursor.getString(index));
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 确保资源关闭
        if (cursor != null) cursor.close();
        if (sqLiteDatabase != null) sqLiteDatabase.close();
    }
    return arabicList;
}

5. 验证assets中的数据库文件

• 确认sample.db在assets根目录，无嵌套子文件夹
• 用SQLite浏览器打开sample.db，确认Dictionary表存在，表名、列名拼写完全一致

额外优化 #

在TextWatcher的onTextChanged中清空旧数据，避免重复添加：

@Override
public void onTextChanged(CharSequence s, int i, int i1, int i2) {
    if(s.length() > 0 ){
        newList.clear();
        newList.addAll(dbHelper.getArabicWord(s.toString()));
        autoCompleteTextView.setAdapter(new ArrayAdapter<>(MainActivity.this, android.R.layout.simple_list_item_1, newList));
    } else {
        newList.clear();
        autoCompleteTextView.setAdapter(null);
    }
}

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