Android using external database

we can use external databse in our applications.

If we want to use a SQLite database in Android, you can use the procedure described in the Storage page.

 This example performs the following operations:

• Copy
• Open
• Select
• Insert
• Close

Note : Database facts:

• Don't forget to add the android_metadata table in your SQLite database! :)

make a new project with name ExternalDataBase and a mainactivity with name  ExternalDBActivity.java, copy the following given code to it

package use.jitesh.externaldb;

import java.io.IOException;

import android.app.Activity;

import android.content.ContentValues;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

import android.os.Bundle;

import android.util.Log;

import android.widget.TextView;

public class ExternalDBActivity extends Activity {

private static final String TAG = "ExtDB";

/** Called when the activity is first created. */

@Override

public void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.main);

TextView contentLog = (TextView) findViewById(R.id.content_log);

// Create the database

DataBaseHelper myDbHelper = new DataBaseHelper(

this.getApplicationContext());

myDbHelper = new DataBaseHelper(this);

try {

myDbHelper.createDataBase();

contentLog.append("Database Created\n");

} catch (IOException ioe) {

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

}

// Open the database

try {

myDbHelper.openDataBase();

contentLog.append("Database Opened\n");

} catch (SQLException sqle) {

sqle.printStackTrace();

}

// Get the readable version

SQLiteDatabase db = myDbHelper.getReadableDatabase();

contentLog.append("Get the readable database\n");

// Make a select

Cursor cur = db.rawQuery(

"SELECT name FROM serval_developers ORDER BY name ASC;", null);

cur.moveToPosition(0);

Log.v(TAG, "Nb Col:" + cur.getColumnCount());

Log.v(TAG, "Nb Records:" + cur.getCount());

cur.close();

contentLog.append("Select:\t" + cur.getColumnCount() + " cols, "

+ cur.getCount() + " rows\n");

// Make an insert

ContentValues values = new ContentValues();

values.put("name", "Jitesh");

values.put("surname", "Upadhyay");

long servalCatID = db.insert("serval_developers", null, values);

Log.v(TAG, "Serval Cat Inserted @: " + servalCatID);

contentLog.append("Insert @ \t" + servalCatID + "\n");

// Check insert

cur = db.rawQuery(

"SELECT name FROM serval_developers ORDER BY name ASC;", null);

cur.moveToPosition(0);

Log.v(TAG, "Nb Col:" + cur.getColumnCount());

Log.v(TAG, "Nb Records:" + cur.getCount());

cur.close();

contentLog.append("Select:\t" + cur.getColumnCount() + " cols, "

+ cur.getCount() + " rows\n");

// dumb

cur = db.rawQuery(

"SELECT name, surname FROM serval_developers ORDER BY name ASC;",

null);

contentLog.append("\nDUMP\n");

int i = 0;

cur.moveToFirst();

while (cur.isAfterLast() == false) {

contentLog.append("(" + i++ + ")\t\t" + cur.getString(0) + "\t"

+ cur.getString(1) + "\n");

cur.moveToNext();

}

cur.moveToPosition(0);

// Close

myDbHelper.close();

contentLog.append("Database closed.");

// YEAH

}

}

make a java class with name DataBaseHelper.java and copy following code to it

package use.jitesh.externaldb;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import android.content.Context;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteException;

import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper{

 

    private static String DB_PATH = "/data/data/use.jitesh.externaldb/databases/";

 

    private static String DB_NAME = "db.sqlite3";

 

    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;

    }

 

    /* (non-Javadoc)

* @see android.database.sqlite.SQLiteOpenHelper#getReadableDatabase()

*/

@Override

public synchronized SQLiteDatabase getReadableDatabase() {

return super.getReadableDatabase();

}

/**

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

 

    //Open the database

        String myPath = DB_PATH + DB_NAME;

    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY|SQLiteDatabase.NO_LOCALIZED_COLLATORS);

 

    }

 

    @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.

 

}

the main.xml layout should have following layout 

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    android:orientation="vertical" >

    <TextView

        android:id="@+id/textView1"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="@string/op_log" >

    </TextView>

    <TextView

        android:id="@+id/content_log"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:text="" >

    </TextView>

</LinearLayout>

it is omportant to put a an sqlite file with the name "db.sqlite3" and it should have following structure as in given images/screenshots