Android work with SQLITE database with copy database in phone (Create, Select, Add in database)

Today I will explain you to how to Copy database in your phone, connection with SQLite, in android development.

I am going to create one simple SQLite database in SQLite manager and connect it with my Application.For crate database i am prefer to use Mozila Firefox Add-ons tool.


Screen Short :

 

You can download Necessary files for example from HEAR 

 

First open mozila Add-ons,If you can't find Add-ons then use shortcut key "Ctrl+Shift+A" to open Add-ons.

Now search for Sqlite Manager and install it and you need to restart browser ones.

 

 Now you can find SQLite in your menu bar

"Tools -> SQLite Manager"

By opening it you will get new browser window with SQLite Manager here you can create simple database with table.

Let I create database with name AndroidTecHub with table name Information and it contains column name,PhoneNumber,MailId.

  

 Your database will save with .sqlite extension at your specified place.

Now copy database in projects assets folder.

Make one class file with name DatabaseHelper.java, this will handle copy and all the connection with database to application.



DatabaseHelper.java File :-

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
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 android.os.Environment;

public class DataBaseHelper extends SQLiteOpenHelper {
    // The Android's default system path of your application database.
    public static String DB_PATH = Environment.getExternalStorageDirectory()+"/";
    private static String DB_NAME = "AndroidTecHub.sqlite";
    private static String DB_NAME_MY = "AndroidTecHub.sqlite";
    private SQLiteDatabase AndroidTecHub;
    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 {
        // for first database;
        boolean dbExist = checkDataBase(DB_NAME);
        if (!dbExist) {
            try {
                copyDataBase(DB_NAME_MY, DB_NAME);
            } catch (Exception e) {
                throw new Error("Copying not done");
            }
        }
    }

    /**
     * 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(String DB) {
        SQLiteDatabase checkDB = null;
        try {
            String myPath = DB_PATH + DB;
            checkDB = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READONLY);

        } catch (SQLiteException e)
        {
           
        }

        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(String assetfile, String DB) {

        // Open your local db as the input stream
        InputStream myInput = null;
        // Open the empty db as the output stream
        OutputStream myOutput = null;
        try {
            myInput = myContext.getAssets().open(assetfile);

            // Path to the just created empty db
            myOutput = new FileOutputStream(Environment.getExternalStorageDirectory()+"/AndroidTecHub.sqlite");

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

            System.out.println("***************************************");
            System.out.println("####### Data base copied ##############");
            System.out.println("***************************************");

        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // Close the streams
            try {
                myOutput.flush();
                myOutput.close();
                myInput.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    public void openDataBase() {

        try {
            // Open the database
            String myPath = DB_PATH + DB_NAME;
            AndroidTecHub = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READWRITE);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    @Override
    public synchronized void close() {

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

        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
       
    }

    public boolean update(String strUpdate) {
        // TODO Auto-generated method stub
        try
        {
            AndroidTecHub.execSQL(strUpdate);
            return true;
        }
        catch (Exception ex)
        {
           
            return false;
        }
    }
    public boolean insertRecord(String strInsert)
    {
        try
        {
            AndroidTecHub.execSQL(strInsert);
            return true;
        }
        catch (Exception ex)
        {
           
            return false;
        }
    }
   
    public boolean deleteRecord(String strDelete)
    {
        try
        {
            AndroidTecHub.execSQL(strDelete);
            return true;
        }
        catch (Exception ex)
        {
           
            return false;
        }
    }
   
    public Cursor selectRecord(String strSelect)
    {
        try
        {
            Cursor cursor = AndroidTecHub.rawQuery(strSelect, null);
            cursor.moveToFirst();
            return cursor;
        }
        catch (Exception ex)
        {
           
            return null;
        }
    }
    public int CountRow(String strCount)
    {
        long numRows = DatabaseUtils.longForQuery(AndroidTecHub,strCount, null);
        int numrows=(int) numRows;
        return numrows;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
       
    }


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

}


Now For Add data Use code :

--------------------------------------------------------------
DataBaseHelper datahelper;
datahelper = new DataBaseHelper(this);
        try {
            datahelper.createDataBase();
            datahelper.openDataBase();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
datahelper.insertRecord("Insert into Information (Name,PhoneNumber,MailId) values (\""
                                + etName.getText().toString()
                                + "\",\""
                                + etPhoneNumber.getText().toString()
                                + "\",\""
                                + etEmailId.getText().toString() + "\")");

------------------------------------------------------------------


And For Fetch data Use code :

------------------------------------------------------------------

datahelper = new DataBaseHelper(this);
            datahelper.openDataBase();
            try
            {
                Cursor cursor = datahelper.selectRecord("SELECT * FROM Information");
                if (cursor.getCount() > 0) {
                    for(int i=0;i<cursor.getCount();i++)
                    {
                        txtData.append("Name : "+cursor.getString(0)+"\n");
                        txtData.append("Phone Number : "+cursor.getString(1)+"\n");
                        txtData.append("EmailId : "+cursor.getString(2)+"\n");
                        txtData.append("------------------------\n");
                        cursor.moveToNext();
                    }
                }
                datahelper.close();
            }
            catch(Exception ex)
            {
               
            }

------------------------------------------------------------------


Last And Most Impotent,

Add permission in your Manifest file   


    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

--------------------------------------------------------------------

You can download Necessary files for example from HEAR 

Hope You Help this,

Enjoy,Happy coding.