Tuesday, 20 September 2011

Android database Basic

In android we create our database through SQLiteOpenHelper  class. this class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state, with the help of it's methods, we can easly tackcare of our database.

Best practice is that we have to create our application modulewise. So we will create our database class in 
separate manner. The name of our class is "DatabaseHelper".
Lets start:

package com.android.appdemo.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DBAdapter
    private static final String DATABASE_NAME = "my_database";
    private static final String DATABASE_TABLE = "records";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE =
        "create table titles (_id integer primary key autoincrement, "
        + "name text not null, address text not null, "
        + "city text not null);";
    private final Context context;
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapter(Context ctx)
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
public class DatabaseHelper extends SQLiteOpenHelper  {

DatabaseHelper(Context context)
            super(context, DATABASE_NAME, null, DATABASE_VERSION);

        public void onCreate(SQLiteDatabase db)

        public void onUpgrade(SQLiteDatabase db, int oldVersion,
        int newVersion)
            Log.w(TAG, "Upgrading database from version " + oldVersion
                    + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS titles");
    //---opens the database---
    public DBAdapter open() throws SQLException
        db = DBHelper.getWritableDatabase();
        return this;

    //---closes the database---   
    public void close()

//---insert a values into the database---
    public long insertTitle(String name, String add, String city)
        ContentValues initialValues = new ContentValues();
        initialValues.put("name", name);
        initialValues.put("address", add);
        initialValues.put("city", city);
        return db.insert(DATABASE_TABLE, null, initialValues);

//--- update the record------

public boolean updateName(int id, String name)
 ContentValues args = new ContentValues();
        args.put("name", name);
               return db.update(DATABASE_TABLE, args,
                         "_id"+ "=" + id, null) > 0;


 //---deletes a particular title---
    public boolean deleteName(long rowId)
        return db.delete(DATABASE_TABLE, "_id"+
        "=" + rowId, null) > 0;

You can also copy your database from sdcard and asset folder through code

Code copy database from SD

public void sdCardDatabase() {
private String databasePath = Environment.getExternalStorageDirectory().getAbsoluteFile()+ "/database.db";

  try {
   File f1 = new File("path of your database");
   if (f1.exists()) {
    File f2 = new File(databasePath);
    InputStream in = new FileInputStream(f1);
    OutputStream out = new FileOutputStream(f2);
    byte[] bufer = new byte[1024];
    int len;
    while ((len = in.read(bufer)) > 0) {
     out.write(bufer, 0, len);
  } catch (FileNotFoundException e) {
   Log.e(TAG, e.getMessage());
  } catch (IOException e) {


Copy DataBase from Assets:
           InputStream myInput = myContext.getAssets().open(myContext.getString("database name"));
         String outFileName = myContext.getString("database path")
                + myContext.getString(
"database name");

        OutputStream myOutput = new FileOutputStream(outFileName);

        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);



  1. Hi
    Can you please give me an idea how to insert new rows into database that is already exited in asset folder and copied .

  2. Hi, thanks for your explanation, the copy works well but I get a "Table not found" error when I try to access any table on the database, do you know why is this?