How to use my own sqlite database?

Question

I put my database field in "assets" folder. And use the code from this blog to copy the database to "/data/data/my_packname/databases/", (This copy code i run it in the onCreate() method when i run this app) then use select * from ... to get data. But it gives me the exception: no such table.

Someone told me that if i am attempting to copy the file in SQLiteOpenHelper's onCreate(), it's too late. So the copy file code can not copy the complete file.

So i need to use adb or ddms to pull the database first?

So, Anyone can teach me how to use my own databse? Can you tell me the setup?

Solution 1

I've used the instructions in that blog post and found them, while on the right track, to severely complicate the issue by unnecessarily extending SQLiteOpenHelper. I've had much better luck doing the following:

1. Create a utility class that creates the static db by copying it into the correct directory from assets, but doesn't get itself so hung up on following the SQLiteOpenHelper format.

2. Using the same utility class to open the db by using SQLiteDatabase.openDatabase()

Edit: Here is a version of this utility class I've created; it's not quite complete, but you'll get the drift.

public class DbUtils {
    private static final String DB_PATH = "/data/data/com.mypackage.myapp/databases/";
    private static final String DB_NAME = "my.db";

    public static void createDatabaseIfNotExists(Context context) throws IOException {
        boolean createDb = false;

        File dbDir = new File(DB_PATH);
        File dbFile = new File(DB_PATH + DB_NAME);
        if (!dbDir.exists()) {
            dbDir.mkdir();
            createDb = true;
        }
        else if (!dbFile.exists()) {
            createDb = true;
        }
        else {
            // Check that we have the latest version of the db
            boolean doUpgrade = false;

            // Insert your own logic here on whether to upgrade the db; I personally
            // just store the db version # in a text file, but you can do whatever
            // you want.  I've tried MD5 hashing the db before, but that takes a while.

            // If we are doing an upgrade, basically we just delete the db then
            // flip the switch to create a new one
            if (doUpgrade) {
                dbFile.delete();
                createDb = true;
            }
        }

        if (createDb) {
            // Open your local db as the input stream
            InputStream myInput = context.getAssets().open(DB_NAME);

            // Open the empty db as the output stream
            OutputStream myOutput = new FileOutputStream(dbFile);

            // transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[1024];
            int length;
            while ((length = myInput.read(buffer)) > 0) {
                myOutput.write(buffer, 0, length);
            }

            // Close the streams
            myOutput.flush();
            myOutput.close();
            myInput.close();
        }
    }

    public static SQLiteDatabase getStaticDb() {
        return SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null, SQLiteDatabase.OPEN_READONLY);
    }
}

Solution 2

After you've copied the database, you should try closing and reopening the SQLiteDatabase object before executing any query on it. I had a similar problem with copying a db from an input stream and that's what solved it for me.