How to link app to external SQlite database file

Question

I'm on the final hurdle of my app, and been stuck on trying to connect the app to an external SQLite database file that i have already created called "health1.db". I want my app to read data from that file. I have already written some code but that code reads from the default database folder in /data/data/...

I have already copied that database file into the assets/databases folder. Here is a sample of my code ...

Table class definition

package com.example.ahmed.doctorsinbahrain9.database;

public class FacilitiesTable {
    public static final String TABLE_FACILITY = "facility";
    public static final String COLUMN_ID = "facilityId";
    public static final String COLUMN_TYPE = "type";
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_PHONE_NUMBER = "phoneNumber";
    public static final String COLUMN_RATING = "rating";
    public static final String COLUMN_DISTANCE = "distance";
    public static final String COLUMN_DESCRIPTION = "description";
    public static final String COLUMN_IMAGE = "image";

    public static final String[] ALL_COLUMNS =
            {COLUMN_ID, COLUMN_TYPE, COLUMN_NAME, COLUMN_PHONE_NUMBER,
            COLUMN_RATING, COLUMN_DISTANCE, COLUMN_DESCRIPTION, COLUMN_IMAGE};
}

My DataSource class:

    package com.example.ahmed.doctorsinbahrain9.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import com.example.ahmed.doctorsinbahrain9.Facility;

import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class DataSource {

    private Context mContext;
    private SQLiteDatabase mDatabase;
    SQLiteOpenHelper mDbHelper;

    public DataSource(Context context) {
        this.mContext = context;
        mDbHelper = new DBHelper(mContext);
        mDatabase = mDbHelper.getWritableDatabase();
    }

    public void open() {
        mDatabase = mDbHelper.getWritableDatabase();
    }

    public void close() {
        mDbHelper.close();
    }

    public List<Facility> getAllFacilities() {
        List<Facility> facilities = new ArrayList<>();
        Cursor cursor = mDatabase.query(FacilitiesTable.TABLE_FACILITY, FacilitiesTable.ALL_COLUMNS,
                        null, null, null, null, null);

        while(cursor.moveToNext()) {
            Facility facility = new Facility();
            facility.setFacilityID(cursor.getString(cursor.getColumnIndex(FacilitiesTable.COLUMN_ID)));
            facility.setFacilityType(cursor.getString(cursor.getColumnIndex(FacilitiesTable.COLUMN_TYPE)));
            facility.setFacilityName(cursor.getString(cursor.getColumnIndex(FacilitiesTable.COLUMN_NAME)));
            facility.setFacilityPhoneNumber(cursor.getString(cursor.getColumnIndex(FacilitiesTable.COLUMN_PHONE_NUMBER)));
            facility.setFacilityRating(cursor.getFloat(cursor.getColumnIndex(FacilitiesTable.COLUMN_RATING)));
            facility.setFacilityDistance(cursor.getDouble(cursor.getColumnIndex(FacilitiesTable.COLUMN_DISTANCE)));
            facility.setFacilityDescription(cursor.getString(cursor.getColumnIndex(FacilitiesTable.COLUMN_DESCRIPTION)));
            facility.setFacilityImage(cursor.getString(cursor.getColumnIndex(FacilitiesTable.COLUMN_IMAGE)));
            facilities.add(facility);
        }

        return facilities;
    }
}

My DBHelper class:

public class DBHelper extends SQLiteOpenHelper {

    public static final String DB_FILE_NAME = "health1.db";
    public static final int DB_VERSION = 1;

    public DBHelper(Context context) {
        super(context, DB_FILE_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }
}

And finally code in my activity class:

public class DisplayActivity extends AppCompatActivity {

    DataSource mDataSource;                  // Make an instance of the DataSource class

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_display);
        Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);
        getSupportActionBar().setDisplayHomeAsUpEnabled(true);

        mDataSource = new DataSource(this);
        mDataSource.open();                 // Open database file

        List<Facility> listFromDB = mDataSource.getAllFacilities();
        FacilitiesAdapter adapter = new FacilitiesAdapter(this, listFromDB);
        RecyclerView recyclerView = (RecyclerView) findViewById(R.id.displayActivityRecyclerView);
        recyclerView.setAdapter(adapter);
    }
}

score 0 · Accepted Answer · answered May 11 '17 at 17:52

You below code to access your sqlite database from asset folder

    public class Databasehelper extends SQLiteOpenHelper
{
      private SQLiteDatabase myDataBase;
      private final Context myContext;
      private static final String DATABASE_NAME = "db.sqlite";
      public final static String DATABASE_PATH ="/data/data/com.shir60bhushan/databases/";
      public static final int DATABASE_VERSION = 1;
      //public static final int DATABASE_VERSION_old = 1;

      //Constructor
      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
      {
            String outFileName = DATABASE_PATH + DATABASE_NAME;
            OutputStream myOutput = new FileOutputStream(outFileName);
            InputStream myInput = myContext.getAssets().open(DATABASE_NAME);

            byte[] buffer = new byte[1024];
            int length;
            while ((length = myInput.read(buffer)) > 0)
            {
                  myOutput.write(buffer, 0, length);
            }
            myInput.close();
            myOutput.flush();
            myOutput.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();
      }

      public void onCreate(SQLiteDatabase db)
      {
      }

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

 //add your public methods for insert, get, delete and update data in database.
}

score 0 · Answer 2 · answered May 11 '17 at 18:11

In order to perform what you want; in given class: DBHelper.class defined by:

public class DBHelper extends SQLiteOpenHelper {
    public static final String DB_FILE_NAME = "health1.db";
  /*
   code , code...
  */

Replace DB_FILE_NAME value ("health1.db") by external data base full path:

    /*
     In this exemple, my data base with name "health1.db"  is currently present 
     on my sdcard, inside directory "myAppDir/"
    */
    public static final String DB_FILE_NAME = "/sdcard/myAppDir/health1.db";

How to link app to external SQlite database file

2 Answers2