Can I Use SupportSQLiteOpenHelper to open a database in the Internal Directory or from the Assets Folder?

Question

I have a pre-populated database file with over 100 tables. I usually use SQLiteDatabase to read and write from the db file but currently, I was checking on how to use SQLite to include using reactive queries etc...

the problem is that all the tutorials on that subject include a database that is created in code and I can't find a simple tutorial for my need

is there a simple tutorial/library/guide on how to use such libraries like SQLite and room to read/write from a pre-populated database without having to create the database tables as classes inside my code?

also as the title says, is there a way to cast an SQLiteDatabase to SupportSQLiteOpenHelper to use in SQLite.

Answer 1

i have a class name is Databaseasset you can put your database in asset folder and then use this class and model to insert update or ... tables here my database asset

package appdesin.myapp.database;

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

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseAssets extends SQLiteOpenHelper {
String DB_PATH = null;
//here name of your database
private static String DB_NAME = "appdb";
// and here define your tables
public static final String TABLE_USERS = "users_tbl";
public static final String TABLE_PRODUCT = "product_tbl";

// TABLE_CONTENT

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
 */
@SuppressLint("SdCardPath")
public DatabaseAssets(Context context) {

    super(context, DB_NAME, null, 1);//1 is database  version
    this.myContext = context;
    // DB_PATH = "/data/data/" + context.getPackageName() + "/" +
    // "databases/";
    DB_PATH = "/data/data/" + myContext.getPackageName() + "/" + "databases/";
}

/**
 * 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");

        }
    }

}

private boolean checkDataBase() {

    SQLiteDatabase checkDB = null;

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

    } 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.
 * */
public 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 void openDataBase() throws SQLException {

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

}

@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) {

}

}

create model and action class

here my action class

package appdesin.myapp.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;

import appdesin.myapp.models.Products;

/**
 * Created by hp on 7/4/2018.
 */

public class ProductActions extends DatabaseAssets {
/**
 * Constructor Takes and keeps a reference of the passed context in order to
 * access to the application assets and resources.
 *
 * @param context
 */

public  static String KEY_ID="id";
public  static String KEY_TITLE="title";
public  static String KEY_DES="des";
public  static String KEY_RATE="rate";
public  static String KEY_IMAGE_PATH="image_path";
public  static String KEY_ICON="image_icons";
public  static String KEY_PRICE="price";
public  static String KEY_PRODCUTDATE="product_date";

public ProductActions(Context context) {
    super(context);
}


public long InsertProduct(Products products)
{
    SQLiteDatabase db=getWritableDatabase();
    ContentValues values=new ContentValues();
    values.put(KEY_TITLE,products.getTitle());
    values.put(KEY_DES,products.getDes());
    values.put(KEY_RATE,products.getRate());
    values.put(KEY_ICON,products.getImage());
    return db.insert(TABLE_PRODUCT,null,values);

}

public long updateProduct(Products products)
{
    SQLiteDatabase db=getWritableDatabase();
    ContentValues values=new ContentValues();
    values.put(KEY_ID,products.getId());
    values.put(KEY_TITLE,products.getTitle());
    values.put(KEY_DES,products.getDes());
    values.put(KEY_RATE,products.getRate());
    values.put(KEY_ICON,products.getImage());
    return db.update(TABLE_PRODUCT,values,""+KEY_ID+"="+products.getId()+"" ,mull);

}

public int deleteId(int id)
{
    SQLiteDatabase db=getWritableDatabase();
    return db.delete(TABLE_PRODUCT,""+KEY_ID+"="+id+"",null);



}

public ArrayList<Products> getAllProducts()
{
    SQLiteDatabase db=getReadableDatabase();
    String  query="select * from " + TABLE_PRODUCT;
     Cursor cursor= db.rawQuery(query,null);

    ArrayList<Products> productsArrayList=new ArrayList<>();

    while(cursor.moveToNext())
    {
        Products products=new Products();
        int id=cursor.getInt(cursor.getColumnIndex(KEY_ID));
        String title=cursor.getString(cursor.getColumnIndex(KEY_TITLE));
        String des=cursor.getString(cursor.getColumnIndex(KEY_DES));
        String rate=cursor.getString(cursor.getColumnIndex(KEY_RATE));
        byte[] image_icon=cursor.getBlob(cursor.getColumnIndex(KEY_ICON));

        products.setId(id);
        products.setTitle(title);
        products.setDes(des);
        products.setRate(rate);
        products.setImage(image_icon);
        productsArrayList.add(products);

    }
    return  productsArrayList;
}

public  Products  getProductId(int id)
{
    SQLiteDatabase db=getReadableDatabase();
    Cursor cursor=db.rawQuery("select * from "+TABLE_PRODUCT+" where "+KEY_ID+"="+id+" ",null);
    Products product=new Products();


    if(cursor.moveToNext()) {
        int myid = cursor.getInt( cursor.getColumnIndex(KEY_ID));

        String title = cursor.getString(cursor.getColumnIndex(KEY_TITLE));
        String des = cursor.getString(cursor.getColumnIndex(KEY_DES));
        String rate = cursor.getString(cursor.getColumnIndex(KEY_RATE));
        byte[] img_icon = cursor.getBlob(cursor.getColumnIndex(KEY_ICON));
        product.setId(myid);
        product.setTitle(title);
        product.setDes(des);
        product.setRate(rate);
        product.setImage(img_icon);
    }

    return  product;
}


}

your can copy your database in datafolder of device with this line

 DatabaseAssets databaseAssets = new DatabaseAssets(MainActivity.this);
    try {
        databaseAssets.createDataBase();
    } catch (IOException e) {
        e.printStackTrace();
    }

hope this help your or some one