Android: Accessing assets folder sqlite database file with .sqlite extension

How to read data from the assets folder sqlite database file with .sqlite extension in my Android application?

Solution 1:

Try this code:

public class DataBaseHelper extends SQLiteOpenHelper {
    private Context mycontext;

    //private String DB_PATH = mycontext.getApplicationContext().getPackageName()+"/databases/";
    private static String DB_NAME = "(datbasename).sqlite";//the extension may be .sqlite or .db
    public SQLiteDatabase myDataBase;
    /*private String DB_PATH = "/data/data/"
                        + mycontext.getApplicationContext().getPackageName()
                        + "/databases/";*/

    public DataBaseHelper(Context context) throws IOException {
        super(context,DB_NAME,null,1);
        this.mycontext=context;
        boolean dbexist = checkdatabase();
        if (dbexist) {
            //System.out.println("Database exists");
            opendatabase(); 
        } else {
            System.out.println("Database doesn't exist");
            createdatabase();
        }
    }

    public void createdatabase() throws IOException {
        boolean dbexist = checkdatabase();
        if(dbexist) {
            //System.out.println(" Database exists.");
        } else {
            this.getReadableDatabase();
            try {
                copydatabase();
            } catch(IOException e) {
                throw new Error("Error copying database");
            }
        }
    }   

    private boolean checkdatabase() {
        //SQLiteDatabase checkdb = null;
        boolean checkdb = false;
        try {
            String myPath = DB_PATH + DB_NAME;
            File dbfile = new File(myPath);
            //checkdb = SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE);
            checkdb = dbfile.exists();
        } catch(SQLiteException e) {
            System.out.println("Database doesn't exist");
        }
        return checkdb;
    }

    private void copydatabase() throws IOException {
        //Open your local db as the input stream
        InputStream myinput = mycontext.getAssets().open(DB_NAME);

        // Path to the just created empty db
        String outfilename = DB_PATH + DB_NAME;

        //Open the empty db as the output stream
        OutputStream myoutput = new FileOutputStream("/data/data/(packagename)/databases   /(datbasename).sqlite");

        // transfer byte to inputfile to 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 void opendatabase() throws SQLException {
        //Open the database
        String mypath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(mypath, null, SQLiteDatabase.OPEN_READWRITE);
    }

    public synchronized void close() {
        if(myDataBase != null) {
            myDataBase.close();
        }
        super.close();
    }

}

Solution 2:

Place old database (old.db) in your asset folder. Type this inside onCreate() of your activity:

public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
....

//=======Code For copying Existing Database file to system folder for use====//
    // Copying Existing Database into system folder
        try {

            String destPath = "/data/data/" + getPackageName()
                    + "/databases/data.db";

            File f = new File(destPath);
            if(!f.exists()){
            Log.v(TAG,"File Not Exist");
            InputStream in = getAssets().open("old.db");
            OutputStream out = new FileOutputStream(destPath);

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

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            Log.v("TAG","ioexeption");
            e.printStackTrace();
        }

        DBManager dbManager =  new DBManager(this);
        Log.v(TAG,"Database is there with version: "+dbManager.getReadableDatabase().getVersion());
        String sql = "select * from prizes";


        SQLiteDatabase db = dbManager.getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);
        Log.v(TAG,"Query Result:"+cursor);


        cursor.close();
        db.close();
        dbManager.close();

....

} 

Now you have to make a DBManager Class which subclasses SQLiteOpenHelper . Insert the abstract method and constructor. Don't forget to type correct database name inside super() of dbHelper.

public class DBManager extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;
private static final String TAG = "DATABASES";

public DBManager(Context context) {
    super(context, "data.db", null, DATABASE_VERSION);

}

@Override
public void onCreate(SQLiteDatabase db) {
    Log.v(TAG,"On create Called:"+db.getPath());
}

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

Now you can access database by instantiating DBManager.

SQLiteDatabase db = dbManager.getReadableDatabase();
Cursor cursor = db.rawQuery(sql, null);
...

Don't forget to close database or u will get a SQLiteDatabaseNotClosed Exception.

db.close();
dbManager.close();

Solution 3:

Its important that in the tutorial, when you call the file, make sure you pass the application context getApplicationContext() such that you have access to the correct assets, otherwise you might get a FileNotFound Exception.

Solution 4:

You will want to try android sqlite asset helper. It made opening a pre-existing db a piece of cake for me.

I literally had it working in about a half hour after spending 3 hours trying to do it all manually. Funny thing is, I thought I was doing the same thing the library did for me, but something was missing!

Solution 5:

You need to convert your .sqlite database to .db inorder to fit to Android.

On your app's first launch after installation

SuperDatabase database=new SuperDatabase(getApplicationContext(),"foods.db", AssetDatabaseMode.COPY_TO_SYSTEM);

On subsequent launches

SuperDatabase database=new SuperDatabase(getApplicationContext(),"foods.db", AssetDatabaseMode.READ_FROM_DEVICE);

Simply fire SQL queries

database.sqlInject("INSERT INTO food VALUES('Banana','Vitamin A');");

Get results on Array in CSV, JSON, XML

ArrayList<String> rows=new ArrayList<String>();
rows=database.sqlEjectCSV("SELECT * FROM food;");
for (int i=0;i<rows.size();i++)
{
    //Do stuffs with each row
}

You need to include my library for this. Documentations here:
https://github.com/sangeethnandakumar/TestTube