个人中心
gpt4 book ai didi

java - 使用现有数据库在 Android 应用程序中显示数据

转载 作者：行者123 更新时间：2023-11-29 18:35:34 25 4
gpt4 key购买 nike

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

数据库的名称是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();
    }

    //Copy the database from assets
    private void copyDataBase() throws IOException
    {
        InputStream mInput = mContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        OutputStream mOutput = new FileOutputStream(outFileName);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInput.read(mBuffer))>0)
        {
            mOutput.write(mBuffer, 0, mLength);
        }
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }

    //Open the database, so we can query it
    public boolean openDataBase() throws SQLException
    {
        String mPath = DB_PATH + DB_NAME;
        //Log.v("mPath", mPath);
        mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
        //mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
        return mDataBase != null;
    }

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

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }
}

这是我的 TestAdapter 类

public class TestAdapter
    {
        protected static final String TAG = "DataAdapter";

        private final Context mContext;
        private SQLiteDatabase mDb;
        private DataBaseHelper mDbHelper;

        public TestAdapter(Context context)
        {
            this.mContext = context;
            mDbHelper = new DataBaseHelper(mContext);
        }

        public TestAdapter createDatabase() throws SQLException
        {
            try
            {
                mDbHelper.createDataBase();
            }
            catch (IOException mIOException)
            {
                Log.e(TAG, mIOException.toString() + "  UnableToCreateDatabase");
                throw new Error("UnableToCreateDatabase");
            }
            return this;
        }

        public TestAdapter open() throws SQLException
        {
            try
            {
                mDbHelper.openDataBase();
                mDbHelper.close();
                mDb = mDbHelper.getReadableDatabase();
            }
            catch (SQLException mSQLException)
            {
                Log.e(TAG, "open >>"+ mSQLException.toString());
                throw mSQLException;
            }
            return this;
        }

        public void close()
        {
            mDbHelper.close();
        }

        public Cursor getTestData() {
            try
            {
                String sql ="SELECT * FROM MASTER;";

                Cursor mCur = mDb.rawQuery(sql, null);
                if (mCur!=null)
                {
                    mCur.moveToNext();
                }
                return mCur;
            }
            catch (SQLException mSQLException)
            {
                Log.e(TAG, "getTestData >>"+ mSQLException.toString());
                throw mSQLException;
            }
        }
    }

这是我的 MainActivity 类

public class MainActivity extends AppCompatActivity {

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

        Button button=findViewById(R.id.submit);
        button.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                TestAdapter mDbHelper = new TestAdapter(MainActivity.this);
                mDbHelper.createDatabase();
                mDbHelper.open();
                Cursor testdata = mDbHelper.getTestData();
                Toast.makeText(MainActivity.this,testdata.getString(0),Toast.LENGTH_SHORT).show();
                mDbHelper.close();
            }
        });

    }
}

这是日志

2019-02-04 15:47:30.227 2594-2594/com.example.myapplication E/SQLiteLog: (1) no such table: MASTER
2019-02-04 15:47:30.228 2594-2594/com.example.myapplication E/DataAdapter: getTestData >>android.database.sqlite.SQLiteException: no such table: MASTER (code 1): , while compiling: SELECT * FROM MASTER;
2019-02-04 15:47:30.228 2594-2594/com.example.myapplication D/AndroidRuntime: Shutting down VM
2019-02-04 15:47:30.242 2594-2594/com.example.myapplication E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.example.myapplication, PID: 2594
    android.database.sqlite.SQLiteException: no such table: MASTER (code 1): , while compiling: SELECT * FROM MASTER;
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:890)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:501)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        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:1392)
        at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1331)
        at com.example.myapplication.TestAdapter.getTestData(TestAdapter.java:63)
        at com.example.myapplication.MainActivity$1.onClick(MainActivity.java:38)
        at android.view.View.performClick(View.java:6297)
        at android.view.View$PerformClick.run(View.java:24797)
        at android.os.Handler.handleCallback(Handler.java:790)
        at android.os.Handler.dispatchMessage(Handler.java:99)
        at android.os.Looper.loop(Looper.java:164)
        at android.app.ActivityThread.main(ActivityThread.java:6626)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:811)

logcat 说没有 MASTER 表，但是当我通过 SQLiteDB 浏览器查看数据库时，它当时就在那里。

最佳答案

我认为您的主要问题是您使用了private static String DB_NAME ="test,db";//数据库名称

代替 private static String DB_NAME ="test.db";//数据库名称

那是你用逗号 , 而不是句点 . 编码，因此不会找到 Assets 文件夹中的数据库文件，因此不会已复制。

由于使用了 this.getReadableDatabase();，第一次运行时会创建文件 test,db ，这会导致创建数据库，该数据库将为空，因此对于后续运行，不会尝试从 Assets 文件夹中复制文件，因为数据库存在，因此由于数据库为空，因此会尝试访问表失败，因为表不存在。

  • 注意 getRedableDatabase 在大多数情况下实际上会得到一个可写的数据库

    Create and/or open a database. This will be the same object returned by getWritableDatabase() unless some problem, such as a full disk, requires the database to be opened read-only. In that case, a read-only database object will be returned. If the problem is fixed, a future call to getWritableDatabase() may succeed, in which case the read-only database object will be closed and the read/write object will be returned in the future. getReadableDatabase

我相信使用 getReadableDatabase 只是用来规避最初 databases 文件夹不存在的问题，因此尝试从 Assets 失败，因为父文件夹不存在。更好的解决方案是不使用 getReadableDatabase，而是检查目录是否存在，如果不存在则创建它。

getReabableDatabase 的这种使用在使用 Android 9+ 时引入了更大的问题，因为默认情况下是使用 WAL(预写日志记录)，这会导致额外的文件(数据库名称以 -shm 为后缀和-沃尔)。

因此使用:-

//Check that the database exists here: /data/data/your package/databases/Da Name
private boolean checkDataBase()
{
    File dbFile = new File(DB_PATH + DB_NAME);
    if (dbFile.exists()) return true;
    if (!dbFile.getParentFile().exists()) dbFile.getParentFile().mkdirs();
    return false;
}

消除了使用 getReabableDatabase 的需要以及实际创建数据库文件所产生的复杂性，即使由于 Assets 文件不存在导致复制失败也是如此。

为了更加小心并应对可能无意中存在的 -shm 和 -wal 文件，以上内容甚至可以扩展为:-

private boolean checkDataBase()
{
    File dbFile = new File(DB_PATH + DB_NAME);
    if (dbFile.exists()) return true;
    if (!dbFile.getParentFile().exists()) dbFile.getParentFile().mkdirs();
    if (new File(DB_PATH + DB_NAME + "-shm").exists())
        new File(DB_PATH + DB_NAME + "-shm").delete();
    if ((new File(DB_PATH + DB_NAME + "-wal")).exists())
        new File(DB_PATH + DB_NAME + "-wal").delete();
    return false;
}

通常不推荐使用 DB_PATH = context.getApplicationInfo().dataDir + "/databases/";，而是更具体的 DB_PATH = mContext.getDatabasePath(DB_NAME).getPath (); 是推荐的，因为不需要对文件分隔符和文件夹名称进行硬编码。

这样的下面可能是一个更好的整体数据库助手:-

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 //<<<<<<<<<< CHANGED TO FIX PRIMARY ISSUE
    private SQLiteDatabase mDataBase;
    private final Context mContext;

    public DataBaseHelper(Context context)
    {
        super(context, DB_NAME, null, 1);// 1? Its database Version
        this.mContext = context;
        DB_PATH = mContext.getDatabasePath(DB_NAME).getPath();
    }

    public void createDataBase() throws IOException
    {
        //If the database does not exist, copy it from the assets.

        boolean mDataBaseExist = checkDataBase();
        if(!mDataBaseExist)
        {
            //this.getReadableDatabase(); //<<<<<<<<<< REMOVED (commented out)
            //this.close(); //<<<<<<<<<< REMOVED ()commented out
            try
            {
                //Copy the database from assests
                copyDataBase();
                Log.e(TAG, "createDatabase database created");
            }
            catch (IOException mIOException)
            {
                mIOException.printStackTrace(); //<<<<<<<<<< might as well include the actual cause in the log
                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); //<<<<<<<<<< just the path used
        if (dbFile.exists()) return true; //<<<<<<<<<< return true of the db exists (see NOTE001)
        if (!dbFile.getParentFile().exists()) dbFile.getParentFile().mkdirs();
        if (new File(DB_PATH + "-shm").exists())
            new File(DB_PATH + "-shm").delete();
        if ((new File(DB_PATH + "-wal")).exists())
            new File(DB_PATH + "-wal").delete();
        return false;
    }

    /** NOTE001
     *  Just checking the file does leave scope for a non sqlite file to be copied from the assets folder
     *  and be copied resulting in an exception. The above could be extended to apply additional checks
     *  if considered required e.g. checking the first sixteen bytes for The header string: "SQLite format 3\000"
     */

    //Copy the database from assets
    private void copyDataBase() throws IOException
    {
        InputStream mInput = mContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH; //<<<<<<<<<< just the path used
        OutputStream mOutput = new FileOutputStream(outFileName);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInput.read(mBuffer))>0)
        {
            mOutput.write(mBuffer, 0, mLength);
        }
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }

    //Open the database, so we can query it
    public boolean openDataBase() throws SQLException
    {
        String mPath = DB_PATH;
        //Log.v("mPath", mPath);
        mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
        //mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
        return mDataBase != null;
    }

    /**
     * Note this can be added and the line uncommented (see below) to disable WAL logging which
     * from Anroid 9 (Pie) is the default
     */
    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        // db.disableWriteAheadLogging(); //<<<<<<<<<< uncomment if you want to not use WAL but use the less efficient joutnal mode.
    }

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

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }
}
  • 请注意查看整个代码中的注释

额外/测试

如果使用上面的方法(在删除应用程序的数据或卸载应用程序以删除空数据库之后)但是在 Assets 文件夹中没有合适的文件(test，db 未更改用于测试)，那么上面的结果将导致更具解释性:-

02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err: java.io.FileNotFoundException: test,db
02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:     at android.content.res.AssetManager.openAsset(Native Method)
02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:     at android.content.res.AssetManager.open(AssetManager.java:313)
02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:     at android.content.res.AssetManager.open(AssetManager.java:287)
02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:     at mjt.so54513838.DataBaseHelper.copyDataBase(DataBaseHelper.java:75)
02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:     at mjt.so54513838.DataBaseHelper.createDataBase(DataBaseHelper.java:42)
02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:     at mjt.so54513838.TestAdapter.createDatabase(TestAdapter.java:29)
02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:     at mjt.so54513838.MainActivity$1.onClick(MainActivity.java:23)
02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:     at android.view.View.performClick(View.java:4780)
02-05 10:17:03.513 5502-5502/mjt.so54513838 W/System.err:     at android.view.View$PerformClick.run(View.java:19866)
02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:     at android.os.Handler.handleCallback(Handler.java:739)
02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:     at android.os.Handler.dispatchMessage(Handler.java:95)
02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:     at android.os.Looper.loop(Looper.java:135)
02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:     at android.app.ActivityThread.main(ActivityThread.java:5254)
02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:     at java.lang.reflect.Method.invoke(Native Method)
02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:     at java.lang.reflect.Method.invoke(Method.java:372)
02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
02-05 10:17:03.514 5502-5502/mjt.so54513838 W/System.err:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
02-05 10:17:03.514 5502-5502/mjt.so54513838 D/AndroidRuntime: Shutting down VM
02-05 10:17:03.514 5502-5502/mjt.so54513838 E/AndroidRuntime: FATAL EXCEPTION: main
    Process: mjt.so54513838, PID: 5502
    java.lang.Error: ErrorCopyingDataBase
        at mjt.so54513838.DataBaseHelper.createDataBase(DataBaseHelper.java:48)
        at mjt.so54513838.TestAdapter.createDatabase(TestAdapter.java:29)
        at mjt.so54513838.MainActivity$1.onClick(MainActivity.java:23)
        at android.view.View.performClick(View.java:4780)
        at android.view.View$PerformClick.run(View.java:19866)
        at android.os.Handler.handleCallback(Handler.java:739)
        at android.os.Handler.dispatchMessage(Handler.java:95)
        at android.os.Looper.loop(Looper.java:135)
        at android.app.ActivityThread.main(ActivityThread.java:5254)
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

如果应用程序在没有任何更改的情况下再次运行，那么上述情况也会发生，而不是更令人困惑的找不到表。

注意在运行上面的代码之前或者即使只是将test,db 更改为test.db 数据库文件也必须删除。这可以通过删除/清除应用程序的数据或卸载应用程序轻松实现。

以上内容已经在 Android 5.0 (lollipop) (API 22) 和 Andorid 9 (Pie)(API 28) 上进行了测试，生成的 Toast 显示表(尽管为了方便，表从 MASTER 更改为 sqlite_master (无需创建数据库文件，因为使用了现有的数据库文件)。

关于java - 使用现有数据库在 Android 应用程序中显示数据，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/54513838/

25 4 0
文章推荐： c# - 输入提交与输入按钮
文章推荐： mysql - 站点迁移后建立数据库连接时出错？
文章推荐： javascript - 没有脚本标签的问题
文章推荐： 安卓NDK : iostream file not found
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com