Reading SQLite File from Asset Folder

Reading a database from the assets folder

To utilise a packaged database (i.e one included as an asset) for full use the database must be unzipped (automatic) and copied to a suitable location (most often data/data/<package_name>/databases/<database_name> where <package_name> and <database_name> will be according to the App's package name and the database name respectiviely).

To "package" the database is should be included in the assets folder and preferably to a databases folder (required if using SQLiteAssetHelper without modification).

Additionally the copy must be done before actually opening the database after which it can then be opened.

Utilising SQLiteAssetHelper

  1. The very first step is to create the database to be packaged, this is not going to be covered, as there are numerous tools available. For this example the database is a file named test.db

  2. You should then create your project in this case the Project has been called DBtest with a Compnay Domian as com.DBtest so the package name is dbtest.com.dbtest.

  3. The next stage is to copy the database into the assets folder.

    1. Creating the assets folder in the src/main folder, if it doesn't already exist.
    2. Creating the databases"" folder in the **assets folder, if it doesn't already exist.

    3. Copying the database file (test.db in this example) into the database folder.

      • Sample Image

  4. The next stage is to setup the project to utilise the SQLiteAssetHelper by including it in the App's build.gradle.

    1. Edit the build.gradle in the App folder.
    2. Add the line implementation 'com.readystatesoftware.sqliteasset:sqliteassethelper:2.0.1' within the dependencies section.
    3. Click Sync Now

Sample Image


  1. Create a class that is a subclass of the newly/now available SQLiteAssethelper class. For this exercise it will be called DBHelper.

    1. Right Click the MainActivity java class, select New and then Java Class.
    2. In the Name field input DBHelper.
    3. In the SuperClass field start typing SQLiteAsset (by now the SQliteAssetHelper class will be selectable), so select it. It should resolve to be:-
    4. Click OK.
      Sample Image

  2. Create the constructor for the DBHelper class along the lines of

:-

public class DBHelper extends SQLiteAssetHelper {

    public static final String DBNAME = "test.db"; //<<<< must be same as file name
    public static final int DBVERSION = 1;

    public DBHelper(Context context) {
        super(context,DBNAME,null,DBVERSION);
    }
}

  1. Create an instance of the DBHelper and then access the database.

    1. Note for ease another class called CommonSQLiteUtilities, as copied from Are there any methods that assist with resolving common SQLite issues?

    2. Create an instance of the DBHelper cclass using something along the lines of

      • DBHelper mDBHlpr = new DBHelper(this);

    3. using the CommonSQLiteUtilities the database was accessed using :-

      • CommonSQLiteUtilities.logDatabaseInfo(mDBHlpr.getWritableDatabase());

    4. The MainActivity in full became

:-

public class MainActivity extends AppCompatActivity {

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

        DBHelper mDBHlpr = new DBHelper(this);
        CommonSQLiteUtilities.logDatabaseInfo(mDBHlpr.getWritableDatabase());
    }
}

The result was a successful run logging :-

04-11 06:12:55.091 1401-1401/dbtest.com.dbtest W/SQLiteAssetHelper: copying database from assets...
    database copy complete
04-11 06:12:55.123 1401-1401/dbtest.com.dbtest I/SQLiteAssetHelper: successfully opened database test.db
04-11 06:12:55.127 1401-1401/dbtest.com.dbtest D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/dbtest.com.dbtest/databases/test.db
    Database Version = 1
    Table Name = mytable Created Using = CREATE TABLE mytable (
        _id INTEGER PRIAMRY KEY, 
        mydata TEXT, 
        inserted INTEGER DEFAULT CURRENT_TIMESTAMP
    )
    Table = mytable ColumnName = _id ColumnType = INTEGER PRIAMRY KEY Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = mytable ColumnName = mydata ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
    Table = mytable ColumnName = inserted ColumnType = INTEGER Default Value = CURRENT_TIMESTAMP PRIMARY KEY SEQUENCE = 0
    Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
    Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
  • The first two lines are from SQliteAssethelper, the rest are from the logDatabaseInfo method of the CommonSQLiteUtilities class.
  • On subsequnt runs the database will not be copied as it already exists.

access .sqlite database in the assets folder

The issue was, I was not calling the createDatabase(). This createDatabase() method was in the DatabaseHelper class which I downloaded from somewhere, I'm not sure where from. I put the code here.

ItemListFragment.java: (this is the class I am reading the database from)

DataBaseHelper dataBaseHelper = new DataBaseHelper(getActivity());
public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

    DataBaseHelper dataBaseHelper = new DataBaseHelper(getActivity());
        try {
            dataBaseHelper.createDataBase();
            SQLiteDatabase db = dataBaseHelper.openDataBase();
            Cursor cursor = db.rawQuery("SELECT * FROM mydb", null);
            cursor.moveToFirst();
            do {
                String data = cursor.getString(cursor.getColumnIndex("name"));
            }while (cursor.moveToNext());
        } catch (Exception e) {
            e.printStackTrace();
    }
}

DatabaseHelper.java:

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteFullException;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLException;

public class DataBaseHelper extends SQLiteOpenHelper{

    //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/com.example.myapp.myapplication/databases/";

    private static String DB_NAME = "mydb.sqlite";

    private SQLiteDatabase myDataBase;

    private final Context myContext;
    /**
     * Constructor
     * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
     * @param context
     */
    public DataBaseHelper(Context context) {

        super(context, DB_NAME, null, 1);
        this.myContext = context;
    }

    /**
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException {

        boolean dbExist = checkDataBase();

        if(dbExist){
            //do nothing - database already exist
        }else{

            //By calling this method and empty database will be created into the default system path
            //of your application so we are gonna be able to overwrite that database with our database.
            this.getReadableDatabase();

            try {

                copyDataBase();

            } catch (IOException e) {

                throw new Error("Error copying database");

            }
        }

    }

    /**
     * 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(){

        SQLiteDatabase checkDB = null;

        try{
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

        }catch(SQLiteException e){

            //database does't exist yet.

        }

        if(checkDB != null){

            checkDB.close();

        }

        return checkDB != null ? true : false;
    }

    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */
    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(outFileName);

        //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 SQLiteDatabase openDataBase() throws SQLException{

        //Open the database
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
        return myDataBase;
    }

    @Override
    public synchronized void close() {

        if(myDataBase != null)
            myDataBase.close();

        super.close();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }

    // Add your public helper methods to access and get content from the database.
    // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
    // to you to create adapters for your views.

}

how to put database and read database from assets folder android which are created and exported in sqllite

public class DataBaseHelper extends SQLiteOpenHelper {
    private SQLiteDatabase myDataBase;
    private final Context myContext;
    private static final String DATABASE_NAME = "YOURDBNAME";
    public final static String DATABASE_PATH = "/data/data/com.your.packagename/databases/";
    public static final int DATABASE_VERSION = 1;
    public DataBaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.myContext = context;

    }

    //Create a empty database on the system
    public void createDatabase() throws IOException
    {

          boolean dbExist = checkDataBase();

          if(dbExist)
          {
                Log.v("DB Exists", "db exists");
                // By calling this method here onUpgrade will be called on a
                // writeable database, but only if the version number has been
                // bumped
                //onUpgrade(myDataBase, DATABASE_VERSION_old, DATABASE_VERSION);
          }

          boolean dbExist1 = checkDataBase();
          if(!dbExist1)
          {
                this.getReadableDatabase();
                try
                {
                      this.close();    
                      copyDataBase();
                }
                catch (IOException e)
                {
                      throw new Error("Error copying database");
                }
          }

    }
    //Check database already exist or not
    private boolean checkDataBase()
    {
          boolean checkDB = false;
          try
          {
                String myPath = DATABASE_PATH + DATABASE_NAME;
                File dbfile = new File(myPath);
                checkDB = dbfile.exists();
          }
          catch(SQLiteException e)
          {
          }
          return checkDB;
    }
    //Copies your database from your local assets-folder to the just created empty database in the system folder
    private void copyDataBase() throws IOException
    {

        InputStream mInput = myContext.getAssets().open(DATABASE_NAME);
        String outFileName = DATABASE_PATH + DATABASE_NAME;
        OutputStream mOutput = new FileOutputStream(outFileName);
        byte[] mBuffer = new byte[2024];
        int mLength;
        while ((mLength = mInput.read(mBuffer)) > 0) {
            mOutput.write(mBuffer, 0, mLength);
        }
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }
  //delete database
    public void db_delete()
    {
          File file = new File(DATABASE_PATH + DATABASE_NAME);
          if(file.exists())
          {
                file.delete();
                System.out.println("delete database file.");
          }
    }
  //Open database
    public void openDatabase() throws SQLException
    {
          String myPath = DATABASE_PATH + DATABASE_NAME;
          myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }

    public synchronized void closeDataBase()throws SQLException
    {
          if(myDataBase != null)
                myDataBase.close();
          super.close();
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         if (newVersion > oldVersion)
         {
               Log.v("Database Upgrade", "Database version higher than old.");
               db_delete();
         }

    }

}

And to implement it

DataBaseHelper dbhelper = new DataBaseHelper(getApplicationContext());

        db = dbhelper.getWritableDatabase();

For that:

Cursor cur;
    cur_Herbs = db.rawQuery("select * from TABLENAME where  name like '"
            + edit_text_name.gettext.tostring() + "'", null);

        if (cur.moveToFirst()) {
            do {
                int name = cur.getColumnIndex("name");
                int pwd= cur.getColumnIndex("pwd");
str_name = cur.getString(name).toString();
                str_pwd= cur.getString(ped).toString();
if(str_name.equals(edittext_uname.gettext.tostring()and str_pwd.equals(edittext_pwd.gettext.tostring()))
{

//code for if loginn
}
} while (cur_Herbs.moveToNext());

}

Sqlite get from assets folder in android?

Use this 

 private static String DB_NAME = "your.db";

Or 

 private static String DB_NAME = "your.sqlite";

Instead of 

private static String DB_NAME = "your";

How do I read data from a existing database in the assets folder on Flutter, I am stuck with errors

I refer the sqflite doc provided by Mr. Tommie C, I changed my initializeDatabase to the following and removed _createDb function that is not require in here:

Future<Database> initializeDatabase() async {
var databasesPath = await getDatabasesPath();
var path = join(databasesPath, "bomdb.sqlite");

// Check if the database exists
var exists = await databaseExists(path);

if (!exists) {
  // Should happen only the first time you launch your application
  print("Creating new copy from asset");

  // Make sure the parent directory exists
  try {
    await Directory(dirname(path)).create(recursive: true);
  } catch (_) {}

  // Copy from asset
  ByteData data = await rootBundle.load(join("assets", "bomdb.sqlite"));
  List<int> bytes =
      data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);

  // Write and flush the bytes written
  await File(path).writeAsBytes(bytes, flush: true);
} else {
  print("Opening existing database");
}
// open the database
var bomDataTable = await openDatabase(path, readOnly: true);

return bomDataTable;
}

I still not figured why it is not worked with oncreate statement with opendatabase, but no issues with this as it is described in the document: https://github.com/tekartik/sqflite/blob/master/sqflite/doc/opening_asset_db.md


Related Topics

How to Check Which Notifications Are Active in Status Bar in Android Dev

Which Correct Flag of Autoconnect in Connectgatt of Ble

Androidstudio Cannot Find Layout

Recyclerview Inside Scrollview Not Scrolling Smoothly

Cardview Corner Radius

How to Shrink the Drawable on a Button

Proguard Hell - Can't Find Referenced Class

Adding Google Play Services Version to Your App's Manifest

How to Use Multiple Mapactivities/Mapviews Per Android Application/Process

Firebase: Query to Exclude Data Based on a Condition

How to Set Time to Device Programmatically

Android:Call Activity of Another Application

How to Send Multiple Sms to Single Number

Changing Overflow Icon in the Action Bar

How to Set Custom Font for a Whole Application in Android

Move Markers in Google Map V2 Android

How to Set Textinputlayout Error Message Colour

Android Datepicker Change to Only Month and Year


Leave a reply



Submit