sqliteLog 14:无法在行处打开文件

Question

我不知道我的错误在哪里。当数据库不存在时，当我的lyrics.db checkDatabase方法返回false，或者我的DB已经过时时，我正试图将该文件存储在DB_PATH中。

然而，我得到以下信息:-

E/SQLiteLog: (14) cannot open file at line 36356 of [605907e73a]
    (14) os_unix.c:36356: (2) open(/data/user/0/id.ac.umn.project_uts_mobile_16862/filesid.ac.umn.project_uts_mobile_16862/databases/lyrics.db) - 
    (1) Process ts_mobile_16862 : Pid (12455) Uid (10196) Euid (10196) Gid (10196) Egid (10196)
    (1) osStat failed "/data/user/0/id.ac.umn.project_uts_mobile_16862/filesid.ac.umn.project_uts_mobile_16862/databases/lyrics.db" due to error (2)
    (1) osStat failed "/data/user/0/id.ac.umn.project_uts_mobile_16862/filesid.ac.umn.project_uts_mobile_16862/databases" due to error (2)
    (1) osStat failed "/data/user/0/id.ac.umn.project_uts_mobile_16862/filesid.ac.umn.project_uts_mobile_16862" due to error (2)
    (1) Stat of /data/user/0/id.ac.umn.project_uts_mobile_16862 : st_mode(40700) st_uid(10196) st_gid(10196) st_ino(265643)
    (1) Stat of /data/user/0 : st_mode(40771) st_uid(1000) st_gid(1000) st_ino(262147)
    (1) Stat of /data/user : st_mode(40711) st_uid(1000) st_gid(1000) st_ino(196619)
    (1) Stat of /data : st_mode(40771) st_uid(1000) st_gid(1000) st_ino(2)
E/SQLiteDatabase: Failed to open database '/data/user/0/id.ac.umn.project_uts_mobile_16862/filesid.ac.umn.project_uts_mobile_16862/databases/lyrics.db'.
    android.database.sqlite.SQLiteCantOpenDatabaseException: unknown error (code 1294): Could not open database
    #################################################################
    Error Code : 1294 (SQLITE_CANTOPEN_ENOENT)
    Caused By : Specified directory or database file does not exist.
        (unknown error (code 1294): Could not open database)
    #################################################################
        at android.database.sqlite.SQLiteConnection.nativeOpen(Native Method)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:272)
        at android.database.sqlite.SQLiteConnection.open(SQLiteConnection.java:213)
        at android.database.sqlite.SQLiteConnectionPool.openConnectionLocked(SQLiteConnectionPool.java:701)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:272)
        at android.database.sqlite.SQLiteConnectionPool.open(SQLiteConnectionPool.java:239)
        at android.database.sqlite.SQLiteDatabase.openInner(SQLiteDatabase.java:1276)
        at android.database.sqlite.SQLiteDatabase.open(SQLiteDatabase.java:1231)
        at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:915)
        at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:864)
        at id.ac.umn.project_uts_mobile_16862.DbHelper.checkDatabase(DbHelper.java:50)
        at id.ac.umn.project_uts_mobile_16862.DbHelper.createDB(DbHelper.java:85)
        at id.ac.umn.project_uts_mobile_16862.show_lyrics.onCreate(show_lyrics.java:22)
        at android.app.Activity.performCreate(Activity.java:7258)
        at android.app.Activity.performCreate(Activity.java:7249)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1222)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2927)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3059)
        at android.app.ActivityThread.-wrap11(Unknown Source:0)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1724)
        at android.os.Handler.dispatchMessage(Handler.java:106)
        at android.os.Looper.loop(Looper.java:164)
        at android.app.ActivityThread.main(ActivityThread.java:7000)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:441)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1408)

这是我加载DB的代码

public class DbHelper extends SQLiteOpenHelper {

    private static String DB_PATH = "";
    private static String DB_NAME = "lyrics.db";
    private SQLiteDatabase vDatabase;
    private Context vContext = null;

    public DbHelper(Context context) {
        super(context, DB_NAME, null, 1);
        if( Build.VERSION.SDK_INT >= 17)
            DB_PATH = context.getFilesDir().getPath()+context.getPackageName()+"/databases/";
        else
            DB_PATH = context.getApplicationInfo().dataDir+"/databases/";

        this.vContext = context;
    }

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

    private boolean checkDatabase() {
        SQLiteDatabase dbTemp = null;
        try{
            String path = DB_PATH + DB_NAME;
            dbTemp = SQLiteDatabase.openDatabase(path, null, OPEN_READWRITE);
        } catch ( SQLiteException e) { }
        if (dbTemp != null ) {
            dbTemp.close();
        }
        return dbTemp != null;
    }

    public void copyDB() throws SQLiteException{
        try {
            InputStream myInput = vContext.getAssets().open(DB_NAME);
            String outputFileName = DB_PATH + DB_NAME;
            Log.d("LIFECYCLE", outputFileName);
            OutputStream myOutput = new FileOutputStream(outputFileName);

            byte[] buffer = new byte[1024];
            int length;
            while( (length=myInput.read(buffer)) > 0 ){
                myOutput.write(buffer, 0, length);
            }

            myOutput.flush();
            myOutput.close();
            myInput.close();
        } catch ( IOException e) {
            e.printStackTrace();
        }
    }

    public void openDB() {
        String path = DB_PATH + DB_NAME;
        vDatabase = SQLiteDatabase.openDatabase(path, null, OPEN_READWRITE);
    }

    public void createDB() {
        boolean dbExist = checkDatabase();
        if ( dbExist ){

        }
        else {
            this.getReadableDatabase();
            copyDB();
        }
    }

    public List<Lyric> getAllSong(){
        List<Lyric> temp = new ArrayList<>();
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor;
        try{
            cursor = db.rawQuery( "SELECT * FROM lyrics" , null);
            if( cursor == null) return null;

            cursor.moveToFirst();
            do {
                Lyric lyric = new Lyric(
                        cursor.getString(cursor.getColumnIndex("index")),
                        cursor.getString(cursor.getColumnIndex("song")),
                        cursor.getString(cursor.getColumnIndex("year")),
                        cursor.getString(cursor.getColumnIndex("artist")),
                        cursor.getString(cursor.getColumnIndex("genre")),
                        cursor.getString(cursor.getColumnIndex("lyrics"))
                        );
                temp.add(lyric);
            } while (cursor.moveToNext());
            cursor.close();
        } catch ( Exception e){ }
        db.close();
        return temp;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }
}

我已经试着调试了将近15个小时的代码，甚至连一个线索都没有，我的错误在哪里呢？

如何检查，是否存在这条路径？如果目录/路径不存在，如何创建它？

Answer 1

一步一步的指导。

1.使用适当的SQLite管理工具创建数据库，表抒情诗中填充了所需的数据。确保数据库被保存。

• 在本例中，使用了NaviCat，下面的SQL用于创建和填充歌词表。

:-

CREATE TABLE IF NOT EXISTS lyrics (
    id INTEGER PRIMARY KEY,
    song TEXT, 
    year TEXT,
    artist TEXT,
    genre TEXT,
    lyrics TEXT
);
INSERT INTO lyrics (song, year, artist, genre, lyrics) VALUES
    ('song1','1970','Fred','Rock','Rock rock rock'),
    ('song2','1980','Mary','Pop','Pop pop pop'),
    ('song3','1960','Sue','Folk','Folk folk folk');

• 注索引是一个关键字，因此除非包含它，否则它不是有效的列名，因此使用了id而不是索引。

2.将文件保存为lyrics.db并复制到项目的资产文件夹中。

​

​

3. DbHelper.java (各种修改)

public class DbHelper extends SQLiteOpenHelper {
    
    private static String DB_NAME = "lyrics.db";
    private SQLiteDatabase vDatabase;
    private Context vContext;

    public DbHelper(Context context) {
        super(context, DB_NAME, null, 1);
        this.vContext = context;
        // Copy the DB if need be when instantiating the DbHelper
        if (!checkDataBase()) {
            copyDB();
        }
        vDatabase = this.getWritableDatabase(); //Get the database when instantiating
    }

    /**
     * No need for build version check as getDataBasePath works for all versions
     * No need for open and close of Database, just open it once for the lifetime (more efficient)
     */

    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */
    private boolean checkDataBase() {
        /**
         * Does not open the database instead checks to see if the file exists
         * also creates the databases directory if it does not exists
         * (the real reason why the database is opened, which appears to result in issues)
         */
        File db = new File(vContext.getDatabasePath(DB_NAME).getPath()); //Get the file name of the database
        Log.d("DBPATH","DB Path is " + db.getPath());
        if (db.exists()) return true; // If it exists then return doing nothing

        // Get the parent (directory in which the database file would be)
        File dbdir = db.getParentFile();
        // If the directory does not exist then make the directory (and higher level directories)
        if (!dbdir.exists()) {
            db.getParentFile().mkdirs();
            dbdir.mkdirs();
        }
        return false;
    }

    public void copyDB() throws SQLiteException{
        try {
            InputStream myInput = vContext.getAssets().open(DB_NAME);
            String outputFileName = vContext.getDatabasePath(DB_NAME).getPath(); //<<<<<<<<<< changed
            Log.d("LIFECYCLE", outputFileName);
            OutputStream myOutput = new FileOutputStream(outputFileName);

            byte[] buffer = new byte[1024];
            int length;
            while( (length=myInput.read(buffer)) > 0 ){
                myOutput.write(buffer, 0, length);
            }
            myOutput.flush();
            myOutput.close();
            myInput.close();
        } catch ( IOException e) {
            e.printStackTrace();
        }
    }

    public List<Lyric> getAllSong(){
        List<Lyric> temp = new ArrayList<>();
        Cursor cursor = vDatabase.query("lyrics",null,null,null,null,null,null);
        //Cursor cursor = db.rawQuery( "SELECT * FROM lyrics" , null); // used query method generally preferred to rawQuery
        //if( cursor == null) return null; // Cursor will not be null may be empty
        //cursor.moveToFirst(); // changed to use simpler loop
        while (cursor.moveToNext()) {
            Lyric lyric = new Lyric(
                    //cursor.getString(cursor.getColumnIndex("index")), //<<<<<<< changed due to column name change
                    cursor.getString(cursor.getColumnIndex("id")),
                    cursor.getString(cursor.getColumnIndex("song")),
                    cursor.getString(cursor.getColumnIndex("year")),
                    cursor.getString(cursor.getColumnIndex("artist")),
                    cursor.getString(cursor.getColumnIndex("genre")),
                    cursor.getString(cursor.getColumnIndex("lyrics"))
            );
            temp.add(lyric);
        }
        cursor.close();
        //db.close(); // inefficient to keep on opening and closing db
        return temp;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

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

• 检查注释重新修改

4.卸载现有应用程序或删除应用程序的数据(重要)

5.测试。

下面是将测试上述内容的活动。

public class MainActivity extends AppCompatActivity {

    DbHelper vDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        vDBHlpr = new DbHelper(this);
        List<Lyric> mylyricslist =  vDBHlpr.getAllSong();
        for (Lyric l: mylyricslist) {
            Log.d("LYRICFROMDB","Song is " + l.getSong() + " Year is " + l.getYear() + " Artist is " + l.getArtist() + " Genre is " + l.getGenre() + " Lyrics are " + l.getLyrics());
        }
    }
}

日志应包括(第一次运行) :-

03-17 19:42:11.067 16057-16057/? D/DBPATH: DB Path is /data/data/com.example.so55199382lyrics/databases/lyrics.db
03-17 19:42:11.067 16057-16057/? D/LIFECYCLE: /data/data/com.example.so55199382lyrics/databases/lyrics.db
03-17 19:42:11.086 16057-16057/? D/LYRICFROMDB: Song is song1 Year is 1970 Artist is Fred Genre is Rock Lyrics are Rock rock rock
03-17 19:42:11.086 16057-16057/? D/LYRICFROMDB: Song is song2 Year is 1980 Artist is Mary Genre is Pop Lyrics are Pop pop pop
03-17 19:42:11.086 16057-16057/? D/LYRICFROMDB: Song is song3 Year is 1960 Artist is Sue Genre is Folk Lyrics are Folk folk folk

或以后的运行:-

03-17 19:49:11.275 16136-16136/? D/DBPATH: DB Path is /data/data/com.example.so55199382lyrics/databases/lyrics.db
03-17 19:49:11.279 16136-16136/? D/LYRICFROMDB: Song is song1 Year is 1970 Artist is Fred Genre is Rock Lyrics are Rock rock rock
03-17 19:49:11.279 16136-16136/? D/LYRICFROMDB: Song is song2 Year is 1980 Artist is Mary Genre is Pop Lyrics are Pop pop pop
03-17 19:49:11.279 16136-16136/? D/LYRICFROMDB: Song is song3 Year is 1960 Artist is Sue Genre is Folk Lyrics are Folk folk folk

• 注意，运行两次，以检查现有数据库的复制进程和处理。

上面的测试是在运行Android和Pie的模拟器上进行的。

Answer 2

我已经试着调试了将近15个小时的代码，甚至连一个线索都没有，我的错误在哪里呢？

您的问题是，当您尝试在checkDatabase方法中打开数据库时，目录并不存在。

线索是:-

Error Code : 1294 (SQLITE_CANTOPEN_ENOENT)
    Caused By : Specified directory or database file does not exist.
        (unknown error (code 1294): Could not open database)

路径通常为data/data/your_package/databases/your_database_file。

data/data将作为android的一部分而存在。

包子目录将作为安装软件包的一部分而存在.

数据库目录最初并不存在，使用SQLiteOpenHelper的子类将在创建数据库时创建数据库文件夹，因此您将看到通过这样一个助手打开数据库的尝试，只需创建数据库目录。但是，这可能会出现问题，因为默认使用Android模式，除非在数据库副本之前删除SQLite和-wal文件，否则将创建一个空数据库(- WAL /-shm文件不属于/与复制的数据库相关，因此删除/清空数据库文件)。

我建议您不要尝试打开您检查的数据库，查看文件是否存在，如果没有，则检查文件的父文件是否存在(数据库目录)，如果不存在，则使用mkdirs方法创建目录。

下面是执行上述所有检查的示例方法：

/**
 * Check if the database already exist to avoid re-copying the file each time you open the application.
 * @return true if it exists, false if it doesn't
 */
private boolean checkDataBase() {
    /**
     * Does not open the database instead checks to see if the file exists
     * also creates the databases directory if it does not exists
     * (the real reason why the database is opened, which appears to result in issues)
     */

    File db = new File(myContext.getDatabasePath(DB_NAME).getPath()); //Get the file name of the database
    Log.d("DBPATH","DB Path is " + db.getPath());
    if (db.exists()) return true; // If it exists then return doing nothing

    // Get the parent (directory in which the database file would be)
    File dbdir = db.getParentFile();
    // If the directory does not exist then make the directory (and higher level directories)
    if (!dbdir.exists()) {
        db.getParentFile().mkdirs();
        dbdir.mkdirs();
    }

    return false;
}

• 注意，上面使用了推荐的getDatabasePath，所以除了数据库名称之外，不需要硬编码任何东西(您可能希望在代码中采用这一点)。