android :Export data from asset's folder (sqllite db) and import it in app's database

Question

Update

I found this link

http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

can anyone explain how will i write update/insert statements using this

___________________________________________________________________________

Question

I am trying to create a method where i can copy data stored in an sqllite db into my app's DB. The structure is similiar. I just dont want to insert rows one by one.

I am looking for a method through which i copy pre-entered data stored in assets folder or external storage to my apps db.

I am having trouble filling data in my app. I do not want to write 5000 insert statements programitically for each row and column.

Can anyone suggest a method. I am new to android so please be explain in detail or provide a link.

My DB Helper class

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

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

public class DBHelper extends SQLiteOpenHelper {
    public  SQLiteDatabase db1;


    // Static Final Variable database meta information

    static final String DATABASE = "assesmenttool.db";
    static final int DATABASE_VERSION = 1;

    //Table Student Details
    static final String TABLEStudent = "StudentDetails";
    static final String S_ID = "_id";
    static final String SchoolID = "SchoolID";
    static final String SchoolName = "schoolname";
    static final String StudentFirstName = "StudentFirstName";
    static final String StudentLastName ="StudentLastName";
    static final String StudentClassLevel ="StudentClassLevel";
    static final String RollNo="RollNo";
    static final String TestDate ="TestDate";

     //Table Response Details   
    static final String TABLEResponse = "TableResponse";
    static final String R_ID = "_id";
    static final String StudentID = "StudentID";
    static final String R_QuestionID = "QuestionID";
    static final String QuestOptionID = "QuestOptionID";

    //Table Question Master
    static final String TableQuestionMaster = "TableQuestionMaster";
    static final String Q_ID= "_id";
    static final String Module_ID = "Module_ID";
    static final String SubModule_ID = "SubModule_ID";
    static final String SubModuleQuestion_ID ="SubModuleQuestion_ID";
    static final String Question_ID= "Question_ID"; 
    static final String Title = "Title";
    static final String Module = "Module";
    static final String TitleDescription = "TitleDescription";
    static final String QuestionText = "QuestionText";
    static final String QuestionImage = "QuestionImage";
    static final String QuestionTemplate = "QuestionTemplate";
    static final String CorrectOptionID = "CorrectOptionID";

    //Table Template Master
    static final String TableTemplateMaster = "TemplateMaster";
    static final String T_ID= "_id";
    static final String Template_ID= "Template_ID"; 
    static final String Name = "Name";
    static final String Description = "Description";

    //Table Question Option 
    static final String TableQuestionOption = "TableQuestionOption";
    static final String TQP_ID= "_id";
    static final String TQP_QuestionID = "QuestionID";
    static final String OptionText = "OptionText";

    //Table Class Master
    static final String TableClassMaster = "TableClassMaster";
    static final String Class_ID= "_id";
    static final String Class = "class";


    // Override constructor
    public DBHelper(Context context) {
        super(context, DATABASE, null, DATABASE_VERSION);

    }


    // Override onCreate method
    @Override
    public void onCreate(SQLiteDatabase db) {


        //Create Table Student Details
        db.execSQL("CREATE TABLE " + TABLEStudent + " ( " + S_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + SchoolID + " text, "
                + SchoolName + " text, " + StudentFirstName + " text, "  + StudentLastName + " text, " + RollNo + " text," + TestDate + " text," + StudentClassLevel + " text)");


        //Create Table Response Details     
        db.execSQL("CREATE TABLE " + TABLEResponse + " ( " + R_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + StudentID + " text, "
                + R_QuestionID + " text, " + QuestOptionID + " text)");


        //Create Table Question Master
        db.execSQL("CREATE TABLE " + TableQuestionMaster + " ( " + Q_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " +  Question_ID + " text, " +  Module_ID + " text, " +  SubModule_ID + " text, " +  SubModuleQuestion_ID + " text,  " + Title + " text, "  +  Module + " text," 
                + TitleDescription + " text, " + QuestionText + " text, "  + QuestionImage + " text, " + QuestionTemplate + " text," + CorrectOptionID + " text)");


        //Create Table Template Master
        db.execSQL("CREATE TABLE " + TableTemplateMaster + " ( " + T_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + Name + " text, "
                + Description + " text)");


        //Create Table Question Option  
        db.execSQL("CREATE TABLE " + TableQuestionOption + " ( " + TQP_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + TQP_QuestionID + " text, "
                + OptionText + " text)");   

        //Create Table Class Master 
        db.execSQL("CREATE TABLE " + TableClassMaster + " ( " + Class_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT, " + Class + " text)");   

    }

       public List<String> getAllClasses(){
            List<String> labels = new ArrayList<String>();

            // Select All Query
            String selectQuery = "SELECT  * FROM " + TableClassMaster;

            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.rawQuery(selectQuery, null);

            // looping through all rows and adding to list
            if (cursor.moveToFirst()) {
                do {
                    labels.add(cursor.getString(1));
                } while (cursor.moveToNext());
            }

            // closing connection
            cursor.close();
            db.close();

            // returning lables
            return labels;
        }

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

        // Drop old version table
        db.execSQL("Drop table " + TABLEStudent);
        db.execSQL("Drop table " + TABLEResponse);
        db.execSQL("Drop table " + TableQuestionMaster);
        db.execSQL("Drop table " + TableTemplateMaster);
        db.execSQL("Drop table " + TableQuestionOption);
        db.execSQL("Drop table " + TableClassMaster);
        // Create New Version table
        onCreate(db);
    }




}

SQLLITE copy class which i found on stackoverflow

How do i mix these two together. how do i sync these two sqllite tables. ?

  private static final String DB_NAME = "asset.db";

    private Context context;

    public AssetDatabaseOpenHelper(Context context) {
        this.context = context;
    }

    public SQLiteDatabase openDatabase() {
        File dbFile = context.getDatabasePath(DB_NAME);

        if (!dbFile.exists()) {
            try {
                copyDatabase(dbFile);
            } catch (IOException e) {
                throw new RuntimeException("Error creating source database", e);
            }
        }

        return SQLiteDatabase.openDatabase(dbFile.getPath(), null, SQLiteDatabase.OPEN_READONLY);
    }

    private void copyDatabase(File dbFile) throws IOException {
        InputStream is = context.getAssets().open(DB_NAME);
        OutputStream os = new FileOutputStream(dbFile);

        byte[] buffer = new byte[1024];
        while (is.read(buffer) > 0) {
            os.write(buffer);
        }

        os.flush();
        os.close();
        is.close();
    }

}

[Use `SQLiteAssetHelper`](https://github.com/jgilfelt/android-sqlite-asset-helper) for packaging a database as an asset within your app. — CommonsWare, Jul 21 '15 at 11:28
I just want to copy the values inside the tables in my db from assets folder to app's db — Tushar Narang, Jul 23 '15 at 10:37
I do not intend to create a fresh table. I just want to a simple copy mechanism — Tushar Narang, Jul 23 '15 at 10:38

Tushar Narang · Accepted Answer · 2015-07-27T06:19:07.967

This will Solve my answer..

Just use the help of sqlliteOPenHelper Class..

Step1

Add the copyDataBase() function..

Step2

Initiate it in your MainActivity like this

helper = new DBHelper(this);

        try {

            helper.createDataBase();

    } catch (IOException ioe) {

        throw new Error("Unable to create database");

    }

    try {

        helper.openDataBase();

    }catch(SQLException sqle){

        throw sqle;

    }

YOur DBHelper Class Looks Like

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.MatrixCursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {
    public  SQLiteDatabase db1;


    // Static Final Variable database meta information

    static final String DATABASE = "assesmenttool.db";
    static final int DATABASE_VERSION = 1;

    //Table Student Details
    static final String TABLEStudent = "StudentDetails";
    static final String S_ID = "_id";
    static final String SchoolID = "SchoolID";
    static final String SchoolName = "schoolname";
    static final String StudentFirstName = "StudentFirstName";
    static final String StudentLastName ="StudentLastName";
    static final String StudentClassLevel ="StudentClassLevel";
    static final String RollNo="RollNo";
    static final String TestDate ="TestDate";

     //Table Response Details   
    static final String TABLEResponse = "TableResponse";
    static final String R_ID = "_id";
    static final String StudentID = "StudentID";
    static final String R_QuestionID = "QuestionID";
    static final String QuestOptionID = "QuestOptionID";

    //Table Question Master
    static final String TableQuestionMaster = "TableQuestionMaster";
    static final String Q_ID= "_id";
    static final String Module_ID = "Module_ID";
    static final String SubModule_ID = "SubModule_ID";
    static final String SubModuleQuestion_ID ="SubModuleQuestion_ID";
    static final String Question_ID= "Question_ID"; 
    static final String Title = "Title";
    static final String Module = "Module";
    static final String TitleDescription = "TitleDescription";
    static final String QuestionText = "QuestionText";
    static final String QuestionImage = "QuestionImage";
    static final String QuestionTemplate = "QuestionTemplate";
    static final String CorrectOptionID = "CorrectOptionID";

    //Table Template Master
    static final String TableTemplateMaster = "TemplateMaster";
    static final String T_ID= "_id";
    static final String Template_ID= "Template_ID"; 
    static final String Name = "Name";
    static final String Description = "Description";

    //Table Question Option 
    static final String TableQuestionOption = "TableQuestionOption";
    static final String TQP_ID= "_id";
    static final String TQP_QuestionID = "QuestionID";
    static final String TQP_OptionID = "TQP_OptionID";
    static final String OptionText = "OptionText";

    //Table Class Master
    static final String TableClassMaster = "TableClassMaster";
    static final String Class_ID= "_id";
    static final String Class = "class";




       private static String DB_PATH = "/data/data/com.cldonline.assesmenttool/databases/";

        private static String DB_NAME = "assesmenttool.db";

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

        }





        // Override onCreate method
        @Override
        public void onCreate(SQLiteDatabase db) {



        }

           public List<String> getAllClasses(){
                List<String> labels = new ArrayList<String>();

                // Select All Query
                String selectQuery = "SELECT  * FROM " + TableClassMaster;

                SQLiteDatabase db = this.getReadableDatabase();
                Cursor cursor = db.rawQuery(selectQuery, null);

                // looping through all rows and adding to list
                if (cursor.moveToFirst()) {
                    do {
                        labels.add(cursor.getString(1));
                    } while (cursor.moveToNext());
                }

                // closing connection
                cursor.close();
                db.close();

                // returning lables
                return labels;
            }

           public List<String> getAllOptions(String Qid){
                List<String> options = new ArrayList<String>();

                // Select All Query
                String selectQuery = "SELECT * FROM " + TableQuestionOption +" "+"where QuestionID ='"+Qid+"'";

                SQLiteDatabase db = this.getReadableDatabase();
                Cursor cursor = db.rawQuery(selectQuery, null);

                // looping through all rows and adding to list
                if (cursor.moveToFirst()) {
                    do {
                        options.add(cursor.getString(2));
                    } while (cursor.moveToNext());
                }

                // closing connection
                cursor.close();
                db.close();

                // returning lables
                return options;
            }

           public void openDataBase() throws SQLException{

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

            }

            @Override
            public synchronized void close() {

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

                    super.close();

            }

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


        }

        public ArrayList<Cursor> getData(String Query){
            //get writable database
            SQLiteDatabase sqlDB = this.getWritableDatabase();
            String[] columns = new String[] { "mesage" };
            //an array list of cursor to save two cursors one has results from the query 
            //other cursor stores error message if any errors are triggered
            ArrayList<Cursor> alc = new ArrayList<Cursor>(2);
            MatrixCursor Cursor2= new MatrixCursor(columns);
            alc.add(null);
            alc.add(null);


            try{
                String maxQuery = Query ;
                //execute the query results will be save in Cursor c
                Cursor c = sqlDB.rawQuery(maxQuery, null);


                //add value to cursor2
                Cursor2.addRow(new Object[] { "Success" });

                alc.set(1,Cursor2);
                if (null != c && c.getCount() > 0) {


                    alc.set(0,c);
                    c.moveToFirst();

                    return alc ;
                }
                return alc;
            } catch(SQLException sqlEx){
                Log.d("printing exception", sqlEx.getMessage());
                //if any exceptions are triggered save the error message to cursor an return the arraylist
                Cursor2.addRow(new Object[] { ""+sqlEx.getMessage() });
                alc.set(1,Cursor2);
                return alc;
            } catch(Exception ex){

                Log.d("printing exception", ex.getMessage());

                //if any exceptions are triggered save the error message to cursor an return the arraylist
                Cursor2.addRow(new Object[] { ""+ex.getMessage() });
                alc.set(1,Cursor2);
                return alc;
            }


        }




}

Instead of put only bunch of code try provide some info how you can resolved ur problem. — Haresh Chhelana, Jul 27 '15 at 06:05

alltooconfusingthereforesleep · Answer 2 · 2015-07-25T14:27:28.250

I use the same way. I have a helper class for this:

public class DatabaseImport {
private static final String TAG = "DataAdapter";

private final DataBaseHelper mDbHelper;

public DatabaseImport(Context context) {
    Context mContext = context;
    mDbHelper = new DataBaseHelper(mContext);
}

public void createDatabase() {
    try {
        mDbHelper.createDataBase();
    } catch (Exception mIOException) {
        Log.e(TAG, mIOException.toString() + "  UnableToCreateDatabase");
        throw new Error("UnableToCreateDatabase");
    }
}

which I call in my DBHelper class.

class DataBaseHelper extends SQLiteOpenHelper {

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

private static final String DB_NAME = "databasename";

private static final String TAG = "DataBaseHelper"; // Tag just for the LogCat window
//destination path (location) of our database on device
private final Context mContext;
private SQLiteDatabase mDataBase;

public DataBaseHelper(Context context) {
    super(context, DB_NAME, null, 1);// 1? its Database Version
    if (android.os.Build.VERSION.SDK_INT >= 17) {
        DB_PATH = context.getApplicationInfo().dataDir + "/databases/";
    } else {
        DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
    }
    this.mContext = context;
}

public void createDataBase() {
    //If database not exists copy it from the assets

    boolean mDataBaseExist = checkDataBase();
    if (!mDataBaseExist) {
        this.getReadableDatabase();
        this.close();
        try {
            //Copy the database from assests
            copyDataBase();
            Log.e(TAG, "createDatabase database created");
        } catch (IOException mIOException) {
            throw new Error("ErrorCopyingDataBase");
        }
    }
}

//Check that the database exists here: /data/data/your package/databases/Da Name
private boolean checkDataBase() {
    File dbFile = new File(DB_PATH + DB_NAME);
    //Log.v("dbFile", dbFile + "   "+ dbFile.exists());
    return dbFile.exists();
}

//Copy the database from assets
private void copyDataBase() throws IOException {
    InputStream mInput = mContext.getResources().openRawResource(R.raw.kindersporttabelle);
    String outFileName = DB_PATH + DB_NAME;
    OutputStream mOutput = new FileOutputStream(outFileName);
    byte[] mBuffer = new byte[1024];
    int mLength;
    while ((mLength = mInput.read(mBuffer)) > 0) {
        mOutput.write(mBuffer, 0, mLength);
    }
    mOutput.flush();
    mOutput.close();
    mInput.close();
}

@Override
public synchronized void close() {
    if (mDataBase != null)
        mDataBase.close();
    super.close();
}

@Override
public void onCreate(SQLiteDatabase db) {

}

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

}

}

Accessing and using your Database is possible using your normal DataBaseHandler:

public class DatabaseHandler extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;
//databasename
private static final String DATABASE_NAME = "databasename";
//table names
[...]
//game name column names

[....]

private static Context myContext;

private static DatabaseHandler mInstance = null;

/**
 * Constructor
 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
 */
private DatabaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

public static DatabaseHandler getInstance(Context context) {
    if (mInstance == null) {
        mInstance = new DatabaseHandler(context.getApplicationContext());
    }
    myContext = context;
    return mInstance;
}

@Override
public void onCreate(SQLiteDatabase db) {

    Intent intent = new Intent(myContext, MainActivity.class);
    myContext.startActivity(intent);

    DatabaseImport mDbHelper = new DatabaseImport(myContext);
    try {
        mDbHelper.createDatabase();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + tablename);
    myContext.deleteDatabase(DATABASE_NAME);

    onCreate(db);
}

}

You just need to adapt the right class names. This should work. Good luck.

But this replaces my current DBHelper class right ?? How will I use my string variables.. which I declared un my original DBHelper static final String TABLEStudent = "StudentDetails"; static final String S_ID = "_id"; static final String SchoolID = "SchoolID"; static final String SchoolName = "schoolname"; static final String StudentFirstName = "StudentFirstName"; static final String StudentLastName ="StudentLastName"; – — Tushar Narang, Jul 25 '15 at 04:35
you can still use your DbHelper class for accessing the database (and your strings and so on). You use the DatabaseHelper and Import class only this one time (or when copying again). — alltooconfusingthereforesleep, Jul 25 '15 at 07:59
can u edit your answer and make two different classes which serve my purpose — Tushar Narang, Jul 25 '15 at 10:47

score 0 · Answer 3 · edited May 23 '17 at 12:13

Check this.Give the path of your database that is located in the asset folder of your project.And you are are able to copy the entire database with the help of SQLiteOpenHelper .

public class PlanDetailsSQLiteOpenHelper extends SQLiteOpenHelper {
    private static final String TAG = "SQLiteOpenHelper";

    private final Context context;
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "my_custom_db";

    private boolean createDb = false, upgradeDb = false;

    public PlanDetailsSQLiteOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    /**
     * Copy packaged database from assets folder to the database created in the
     * application package context.
     *
     * @param db
     *            The target database in the application package context.
     */
    private void copyDatabaseFromAssets(SQLiteDatabase db) {
        Log.i(TAG, "copyDatabase");
        InputStream myInput = null;
        OutputStream myOutput = null;
        try {
            // Open db packaged as asset as the input stream
            myInput =context.getAssets().open("your_database_path");

            // Open the db in the application package context:
            myOutput = new FileOutputStream(db.getPath());

            // Transfer db file contents:
            byte[] buffer = new byte[1024];
            int length;
            while ((length = myInput.read(buffer)) > 0) {
                myOutput.write(buffer, 0, length);
            }
            myOutput.flush();

            // Set the version of the copied database to the current
            // version:

            SQLiteDatabase copiedDb = context.openOrCreateDatabase(
                    DATABASE_NAME, 0, null);
            copiedDb.execSQL("PRAGMA user_version = " + DATABASE_VERSION);
           // copiedDb.close();


        } catch (IOException e) {
            e.printStackTrace();
            throw new Error(TAG + " Error copying database");
        } finally {
            // Close the streams
            try {
                if (myOutput != null) {
                    myOutput.close();
                }
                if (myInput != null) {
                    myInput.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
                throw new Error(TAG + " Error closing streams");
            }
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i(TAG, "onCreate db");
        createDb = true;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i(TAG, "onUpgrade db");
        upgradeDb = true;

    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        Log.i(TAG, "onOpen db");
        if (createDb) {// The db in the application package
            // context is being created.
            // So copy the contents from the db
            // file packaged in the assets
            // folder:
            createDb = false;
            copyDatabaseFromAssets(db);

        }
        if (upgradeDb) {// The db in the application package
            // context is being upgraded from a lower to a higher version.
            upgradeDb = false;
            // Your db upgrade logic here:

        }

    }
}

But this replaces my current DBHelper class right ?? How will I use my string variables.. which I declared un my original DBHelper static final String TABLEStudent = "StudentDetails"; static final String S_ID = "_id"; static final String SchoolID = "SchoolID"; static final String SchoolName = "schoolname"; static final String StudentFirstName = "StudentFirstName"; static final String StudentLastName ="StudentLastName"; — Tushar Narang, Jul 25 '15 at 04:35

android :Export data from asset's folder (sqllite db) and import it in app's database

3 Answers3