CODEWITHSUNDEEP
javaandroidsqlite
Ashish Mishra
Ashish Mishra

Reputation: 389

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

How do you read data from the sqlite database file in the res/assets/ folder with the .sqlite extension in my Android application?

Upvotes: 38

Views: 77593

Answers (7)

Almero Rick
Almero Rick

Reputation: 118

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

Upvotes: 1

Jakir Hossain
Jakir Hossain

Reputation: 1072

You can only read database from asset folder as the resources folders are read-only. If you need to do more operation like create, update, delete you can do a tricks. Copy the database from assets folder to storage and then you can do anything you want.

Here is a quick example of Working with Android Pre Built Database.

There is a easy to use library too for accessing database from assets folder. You can check Android SQLiteAssetHelper (https://github.com/jgilfelt/android-sqlite-asset-helper). Good luck!

Upvotes: 0

craned
craned

Reputation: 3051

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!

Upvotes: 1

Jaydeep Khamar
Jaydeep Khamar

Reputation: 5985

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();
    }

}

Upvotes: 34

Nikhil Ranjan
Nikhil Ranjan

Reputation: 992

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();

Upvotes: 7

Jon
Jon

Reputation: 794

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.

Upvotes: 2

franklins
franklins

Reputation: 3738

If you are planning to create a new SQLite database then over ride and implement the onCreate() Method as shown in the tutorial.

But if you are using a SQLite database that is created by another external source and you are going to pull it down, then leave the onCreate() method empty.

Upvotes: 0

Related Questions