使用现有数据库在 Android 应用中显示数据

Question

我是 android 开发的新手。我已经创建了 SQLite 数据库并将其保存在 Android Studio 的资产文件夹中。我的应用程序必须使用现有数据库而不是创建新数据库。我面临的问题是，当我想在屏幕上显示数据时，它会在执行 SQL 语句的光标处引发错误。请帮忙。

数据库名是test.db，表名是MASTER。这是我的 DataBaseHelper 类

public class DataBaseHelper extends SQLiteOpenHelper {

    private static String TAG = "DataBaseHelper"; // Tag just for the LogCat window

    //destination path (location) of our database on device

    private static String DB_PATH = "";

    private static String DB_NAME ="test,db";// Database name

    private SQLiteDatabase mDataBase;

    private final Context mContext;

    public DataBaseHelper(Context context)

    {

        super(context, DB_NAME, null, 1);// 1? Its database Version

        if(android.os.Build.VERSION.SDK_INT >= 17){

            DB_PATH = context.getApplicationInfo().dataDir + "/databases/";

        }

        else

        {

            DB_PATH = context.getApplicationInfo().dataDir + "/databases/";

        }

        this.mContext = context;

    }

    public void createDataBase() throws IOException

    {

        //If the database does not exist, copy it from the assets.

        boolean mDataBaseExist = checkDataBase();

        if(!mDataBaseExist)

        {

            this.getReadableDatabase();

            this.close();

            try

            {

                //Copy the database from assests

                copyDataBase();

                Log.e(TAG, "createDatabase database created");

            }

            catch (IOException mIOException)

            {

                throw new Error("ErrorCopyingDataBase");

            }

        }

    }

    //Check that the database exists here: /data/data/your package/databases/Da Name

    private boolean checkDataBase()

    {

        File dbFile = new File(DB_PATH + DB_NAME);

        //Log.v("dbFile", dbFile + "   "+ dbFile.exists());

        return dbFile.exists();

    }

Answer 1

我相信您的主要问题是您使用过private static String DB_NAME ="test,db";// Database name代替private static String DB_NAME ="test.db";// Database name也就是说，您编写了逗号,而不是句点.，因此将找不到资产文件夹中的数据库文件，因此不会被复制。第一次运行文件test,db时，由于使用this.getReadableDatabase();，将创建数据库，这将导致创建数据库，该数据库将为空，因此对于后续运行将不会尝试从资产文件夹作为数据库存在，因此由于数据库为空，因此访问该表的尝试失败，因为该表不存在。注意 getRedableDatabase 在大多数情况下实际上会得到一个可写的数据库创建和/或打开数据库。这将与 getWritableDatabase() 返回的对象相同，除非某些问题（例如磁盘已满）需要以只读方式打开数据库。在这种情况下，将返回一个只读数据库对象。如果问题得到解决，未来对 getWritableDatabase() 的调用可能会成功，在这种情况下，只读数据库对象将被关闭，并且将来会返回读/写对象。&nbsp;获取可读数据库我相信 usinggetReadableDatabase仅用于规避最初数据库文件夹不存在的问题，因此尝试从资产复制文件失败，因为父文件夹不存在。更好的解决方案是不使用getReadableDatabase，而是检查目录是否存在，如果不存在则创建它。当使用 Android 9+ 作为默认值时，这种使用getReabableDatabase引入了更大的问题，然后使用 WAL（预写日志记录），这会产生额外的文件（以 -shm 和 -wal 为后缀的数据库名称）。因此使用：-//Check that the database exists here: /data/data/your package/databases/Da Nameprivate boolean checkDataBase(){&nbsp; &nbsp; File dbFile = new File(DB_PATH + DB_NAME);&nbsp; &nbsp; if (dbFile.exists()) return true;&nbsp; &nbsp; if (!dbFile.getParentFile().exists()) dbFile.getParentFile().mkdirs();&nbsp; &nbsp; return false;}getReabableDatabase即使由于资产文件不存在而导致随后的副本失败，也无需使用以及实际创建数据库文件所产生的复杂性。为了更加小心并应对 -shm 和 -wal 文件可能无意中存在的可能性，那么上述内容甚至可以扩展到：-private boolean checkDataBase(){&nbsp; &nbsp; File dbFile = new File(DB_PATH + DB_NAME);&nbsp; &nbsp; if (dbFile.exists()) return true;&nbsp; &nbsp; if (!dbFile.getParentFile().exists()) dbFile.getParentFile().mkdirs();&nbsp; &nbsp; if (new File(DB_PATH + DB_NAME + "-shm").exists())&nbsp; &nbsp; &nbsp; &nbsp; new File(DB_PATH + DB_NAME + "-shm").delete();&nbsp; &nbsp; if ((new File(DB_PATH + DB_NAME + "-wal")).exists())&nbsp; &nbsp; &nbsp; &nbsp; new File(DB_PATH + DB_NAME + "-wal").delete();&nbsp; &nbsp; return false;}通常DB_PATH = context.getApplicationInfo().dataDir + "/databases/";不建议使用，而是建议使用更具体DB_PATH = mContext.getDatabasePath(DB_NAME).getPath();的，因为不需要硬编码文件分隔符和文件夹名称。以下可能是更好的整体数据库助手：-public class DataBaseHelper extends SQLiteOpenHelper {&nbsp; &nbsp; private static String TAG = "DataBaseHelper"; // Tag just for the LogCat window&nbsp; &nbsp; //destination path (location) of our database on device&nbsp; &nbsp; private static String DB_PATH = "";&nbsp; &nbsp; private static String DB_NAME ="test.db";// Database name //<<<<<<<<<< CHANGED TO FIX PRIMARY ISSUE&nbsp; &nbsp; private SQLiteDatabase mDataBase;&nbsp; &nbsp; private final Context mContext;&nbsp; &nbsp; public DataBaseHelper(Context context)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; super(context, DB_NAME, null, 1);// 1? Its database Version&nbsp; &nbsp; &nbsp; &nbsp; this.mContext = context;&nbsp; &nbsp; &nbsp; &nbsp; DB_PATH = mContext.getDatabasePath(DB_NAME).getPath();&nbsp; &nbsp; }&nbsp; &nbsp; public void createDataBase() throws IOException&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; //If the database does not exist, copy it from the assets.&nbsp; &nbsp; &nbsp; &nbsp; boolean mDataBaseExist = checkDataBase();&nbsp; &nbsp; &nbsp; &nbsp; if(!mDataBaseExist)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //this.getReadableDatabase(); //<<<<<<<<<< REMOVED (commented out)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //this.close(); //<<<<<<<<<< REMOVED ()commented out&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; try&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //Copy the database from assests&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; copyDataBase();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Log.e(TAG, "createDatabase database created");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; catch (IOException mIOException)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; mIOException.printStackTrace(); //<<<<<<<<<< might as well include the actual cause in the log&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; throw new Error("ErrorCopyingDataBase");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; //Check that the database exists here: /data/data/your package/databases/Da Name&nbsp; &nbsp; private boolean checkDataBase()&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; File dbFile = new File(DB_PATH); //<<<<<<<<<< just the path used&nbsp; &nbsp; &nbsp; &nbsp; if (dbFile.exists()) return true; //<<<<<<<<<< return true of the db exists (see NOTE001)&nbsp; &nbsp; &nbsp; &nbsp; if (!dbFile.getParentFile().exists()) dbFile.getParentFile().mkdirs();&nbsp; &nbsp; &nbsp; &nbsp; if (new File(DB_PATH + "-shm").exists())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new File(DB_PATH + "-shm").delete();&nbsp; &nbsp; &nbsp; &nbsp; if ((new File(DB_PATH + "-wal")).exists())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new File(DB_PATH + "-wal").delete();&nbsp; &nbsp; &nbsp; &nbsp; return false;&nbsp; &nbsp; }&nbsp; &nbsp; /** NOTE001&nbsp; &nbsp; &nbsp;*&nbsp; Just checking the file does leave scope for a non sqlite file to be copied from the assets folder&nbsp; &nbsp; &nbsp;*&nbsp; and be copied resulting in an exception. The above could be extended to apply additional checks&nbsp; &nbsp; &nbsp;*&nbsp; if considered required e.g. checking the first sixteen bytes for The header string: "SQLite format 3\000"&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; //Copy the database from assets&nbsp; &nbsp; private void copyDataBase() throws IOException&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; InputStream mInput = mContext.getAssets().open(DB_NAME);&nbsp; &nbsp; &nbsp; &nbsp; String outFileName = DB_PATH; //<<<<<<<<<< just the path used&nbsp; &nbsp; &nbsp; &nbsp; OutputStream mOutput = new FileOutputStream(outFileName);&nbsp; &nbsp; &nbsp; &nbsp; byte[] mBuffer = new byte[1024];&nbsp; &nbsp; &nbsp; &nbsp; int mLength;&nbsp; &nbsp; &nbsp; &nbsp; while ((mLength = mInput.read(mBuffer))>0)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; mOutput.write(mBuffer, 0, mLength);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; mOutput.flush();&nbsp; &nbsp; &nbsp; &nbsp; mOutput.close();&nbsp; &nbsp; &nbsp; &nbsp; mInput.close();&nbsp; &nbsp; }&nbsp; &nbsp; //Open the database, so we can query it&nbsp; &nbsp; public boolean openDataBase() throws SQLException&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; String mPath = DB_PATH;&nbsp; &nbsp; &nbsp; &nbsp; //Log.v("mPath", mPath);&nbsp; &nbsp; &nbsp; &nbsp; mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);&nbsp; &nbsp; &nbsp; &nbsp; //mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);&nbsp; &nbsp; &nbsp; &nbsp; return mDataBase != null;&nbsp; &nbsp; }&nbsp; &nbsp; /**&nbsp; &nbsp; &nbsp;* Note this can be added and the line uncommented (see below) to disable WAL logging which&nbsp; &nbsp; &nbsp;* from Anroid 9 (Pie) is the default&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; @Override&nbsp; &nbsp; public void onConfigure(SQLiteDatabase db) {&nbsp; &nbsp; &nbsp; &nbsp; super.onConfigure(db);&nbsp; &nbsp; &nbsp; &nbsp; // db.disableWriteAheadLogging(); //<<<<<<<<<< uncomment if you want to not use WAL but use the less efficient joutnal mode.&nbsp; &nbsp; }&nbsp; &nbsp; @Override&nbsp; &nbsp; public synchronized void close()&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; if(mDataBase != null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; mDataBase.close();&nbsp; &nbsp; &nbsp; &nbsp; super.close();&nbsp; &nbsp; }&nbsp; &nbsp; @Override&nbsp; &nbsp; public void onCreate(SQLiteDatabase db) {&nbsp; &nbsp; }&nbsp; &nbsp; @Override&nbsp; &nbsp; public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {&nbsp; &nbsp; }}注意查看代码中的注释额外/测试如果使用上述（删除应用程序的数据或卸载应用程序以删除空数据库后）但资产文件夹中没有合适的文件（test，db未更改以进行测试），那么上述将导致更多解释： -02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err: java.io.FileNotFoundException: test,db02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at android.content.res.AssetManager.openAsset(Native Method)02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at android.content.res.AssetManager.open(AssetManager.java:313)02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at android.content.res.AssetManager.open(AssetManager.java:287)02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at mjt.so54513838.DataBaseHelper.copyDataBase(DataBaseHelper.java:75)02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at mjt.so54513838.DataBaseHelper.createDataBase(DataBaseHelper.java:42)02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at mjt.so54513838.TestAdapter.createDatabase(TestAdapter.java:29)02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at mjt.so54513838.MainActivity$1.onClick(MainActivity.java:23)02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at android.view.View.performClick(View.java:4780)02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at android.view.View$PerformClick.run(View.java:19866)02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at android.os.Handler.handleCallback(Handler.java:739)02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at android.os.Handler.dispatchMessage(Handler.java:95)02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at android.os.Looper.loop(Looper.java:135)02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at android.app.ActivityThread.main(ActivityThread.java:5254)02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at java.lang.reflect.Method.invoke(Native Method)02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at java.lang.reflect.Method.invoke(Method.java:372)02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:&nbsp; &nbsp; &nbsp;at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)02-05 10:17:03.514 5502-5502/mjt.so54513838 D/AndroidRuntime: Shutting down VM02-05 10:17:03.514 5502-5502/mjt.so54513838 E/AndroidRuntime: FATAL EXCEPTION: main&nbsp; &nbsp; Process: mjt.so54513838, PID: 5502&nbsp; &nbsp; java.lang.Error: ErrorCopyingDataBase&nbsp; &nbsp; &nbsp; &nbsp; at mjt.so54513838.DataBaseHelper.createDataBase(DataBaseHelper.java:48)&nbsp; &nbsp; &nbsp; &nbsp; at mjt.so54513838.TestAdapter.createDatabase(TestAdapter.java:29)&nbsp; &nbsp; &nbsp; &nbsp; at mjt.so54513838.MainActivity$1.onClick(MainActivity.java:23)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.View.performClick(View.java:4780)&nbsp; &nbsp; &nbsp; &nbsp; at android.view.View$PerformClick.run(View.java:19866)&nbsp; &nbsp; &nbsp; &nbsp; at android.os.Handler.handleCallback(Handler.java:739)&nbsp; &nbsp; &nbsp; &nbsp; at android.os.Handler.dispatchMessage(Handler.java:95)&nbsp; &nbsp; &nbsp; &nbsp; at android.os.Looper.loop(Looper.java:135)&nbsp; &nbsp; &nbsp; &nbsp; at android.app.ActivityThread.main(ActivityThread.java:5254)&nbsp; &nbsp; &nbsp; &nbsp; at java.lang.reflect.Method.invoke(Native Method)&nbsp; &nbsp; &nbsp; &nbsp; at java.lang.reflect.Method.invoke(Method.java:372)&nbsp; &nbsp; &nbsp; &nbsp; at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)&nbsp; &nbsp; &nbsp; &nbsp; at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)如果应用程序在没有任何更改的情况下再次运行，那么上述情况也会发生，而不是更令人困惑的table not found。注意在运行上述代码之前，或者即使只是将 test,db 更改为 test.db，也必须删除数据库文件。这可以通过删除/清除应用程序的数据或卸载应用程序来轻松实现。以上内容已在 Android 5.0 (lollipop) (API 22) 和 Andorid 9 (Pie)(API 28) 上进行了测试，生成的 Toast 显示表（尽管为方便起见，表已从 MASTER 更改为 sqlite_master（保存使用现有数据库文件创建数据库文件））。

Answer 2

将您的数据路径直接设置为字符串，希望它会起作用&nbsp;private final static String DATABASE_PATH ="/data/data/com.yourpackagename/databases/";public SQLiteDatabase openDatabase() throws SQLException&nbsp; &nbsp; {&nbsp; &nbsp;String myPath = DATABASE_PATH + "DB_NAME";myDataBase = SQLiteDatabase.openOrCreateDatabase(myPath, null, null);&nbsp; &nbsp; &nbsp; &nbsp; return myDataBase;&nbsp; &nbsp; }`