Android: How to use own sqlite database

Android has built-in support for using Sqlite database to keep persistent data.

You can also use your own database in your application copying from external file that are saved in assets directory of that application. You may go through following steps:

1. Modify your database schema/structure to make it compatible with Android OS. Change primary key fields of all tables to “_id“.
2. Create a table “android_metadata(…)” and insert one row. Create a meta-data table  - CREATE TABLE  “android_metadata” (“locale” TEXT DEFAULT ‘en_US’)  and INSERT INTO “android_metadata” VALUES (‘en_US’)
3. Copy your database file to … yourapp/assets/
4. Read this file from MyDatabaseHelper and write it to application’s database directory
5. Open and Read your database from your application using MyDatabaseHelper extends SQLiteOpenHelper
6. Query on that database
7. That’s all

For Example, application name is SearchSqlite:

1. You may open your database file using SQLite Database Browser and then modify it
2. Add new table by SQLite Database Browser
3. Let your sqlite database file name is zaman_sqlite. Copy it into C:\Users\zaman\workspace\SearchSqlite\assets\zaman_sqlite (change it as your apps directory)
   
4. Read zaman_sqlite and write into databases directory.
5. Sample partial code as following

 public class MyDataBaseHelper extends SQLiteOpenHelper
{

    //singleton/ single instance reference of database instance
    private static MyDataBaseHelper _dbHelper;

    //The Android's default system path of your application database.
    //private static String DB_PATH = "/data/data/YOUR_PACKAGE/databases/";
    private static String DB_PATH = "/data/data/com.searchsqlite.droidclient/databases/";
    //database name
    private static String DB_NAME = "zaman_sqlite";
    //reference of database
    private SQLiteDatabase _zamanSqliteDb;
    //
    private String _searchToken;

    //
    private final Context _context;
    //Contains search result when user search with any text
    private Vector<Object> _searchResultVec;

    private MyDataBaseHelper(Context context)
    {
        super(context, DB_NAME, null, 1);
        this._context = context;
        _searchResultVec = new Vector<Object>();
        _searchToken = "";
    }
    public static MyDataBaseHelper getInstance(Context context)
    {
        if(_dbHelper == null)
        {
            _dbHelper = new MyDataBaseHelper(context);
        }
        return _dbHelper;
    }
    public String getLastSearchToken()
    {
        return _searchToken;
    }

    public void createDataBase() throws IOException
    {
        boolean dbExist = checkDataBase();

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

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

        }catch(SQLiteException e)
        {
            //database does't exist yet.
        }
        if(checkDB != null)
        {
            checkDB.close();
        }

        return checkDB != null ? true : false;
    }
    private void copyDataBase() throws IOException
    {
        //Open your local db as the input stream
        InputStream myInput = _context.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 Vector<Object> search(String text)
    {
        //list of Versioni, search result with query text
        _searchResultVec = new Vector<Object>();
        _searchToken = text;

        try
        {
            //open database to query
            openDataBase();

            Cursor cursor = _zamanSqliteDb.query("TextTable",
                    new String[] { "Column1"},
                    "Column1" + " like '%"+text+"%'",
                    null ,
                    null,
                    null,
                    null);

            //mapped all rows to data object
            if (cursor.moveToFirst())
            {
                do
                {
                    //create objects
                } while (cursor.moveToNext());
            }
            //close cursor
            cursor.close();
        }
        catch(Exception ex)
        {
            System.out.println("DatabaseHelper.search()- : ex " + ex.getClass() +", "+ ex.getMessage());
        }
        //
        return _searchResultVec;
    }
}


References:

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

public class MyDataBaseHelper extends SQLiteOpenHelper

{

//singleton/ single instance reference of database instance

private static MyDataBaseHelper _dbHelper;
//The Android's default system path of your application database.

//private static String DB_PATH = "/data/data/YOUR_PACKAGE/databases/";

private static String DB_PATH = "/data/data/com.searchsqlite.droidclient/databases/";

//database name

private static String DB_NAME = "zaman_sqlite";

//reference of database

private SQLiteDatabase _zamanSqliteDb;

//

private String _searchToken;
//

private final Context _context;

//Contains search result when user search with any text

private Vector<Object> _searchResultVec;
private MyDataBaseHelper(Context context)

{

super(context, DB_NAME, null, 1);

this._context = context;

_searchResultVec = new Vector<Object>();

_searchToken = "";

}

public static MyDataBaseHelper getInstance(Context context)

{

if(_dbHelper == null)

{

_dbHelper = new MyDataBaseHelper(context);

}

return _dbHelper;

}

public String getLastSearchToken()

{

return _searchToken;

}
public void createDataBase() throws IOException

{

boolean dbExist = checkDataBase();
if(dbExist)

{

//do nothing - database already exist

}else{
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);
}catch(SQLiteException e)

{

//database does't exist yet.

}

if(checkDB != null)

{

checkDB.close();

}
return checkDB != null ? true : false;

}

private void copyDataBase() throws IOException

{

//Open your local db as the input stream

InputStream myInput = _context.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 Vector<Object> search(String text)

{

//list of Versioni, search result with query text

_searchResultVec = new Vector<Object>();

_searchToken = text;
try

{

//open database to query

openDataBase();
Cursor cursor = _zamanSqliteDb.query("TextTable",

new String[] { "Column1"},

"Column1" + " like '%"+text+"%'",

null ,

null,

null,

null);
//mapped all rows to data object

if (cursor.moveToFirst())

{

do

{

//create objects

} while (cursor.moveToNext());

}

//close cursor

cursor.close();

}

catch(Exception ex)

{

System.out.println("DatabaseHelper.search()- : ex " + ex.getClass() +", "+ ex.getMessage());

}

//

return _searchResultVec;

}

}



About these ads