Android SQLite Example

I am new to Android and I have to create an application where I need to use a SQLite database. I am not so well equipped with the SQLite.

Could you tell me what the purpose and use of the SQLiteOpenHelper class, and provide a simple database creation and insertion example?

Sqlite helper class helps us to manage database creation and version management. SQLiteOpenHelper takes care of all database management activities. To use it,
1.Override onCreate(), onUpgrade() methods of SQLiteOpenHelper. Optionally override onOpen() method.
2.Use this subclass to create either a readable or writable database and use the SQLiteDatabase's four API methods insert(), execSQL(), update(), delete() to create, read, update and delete rows of your table.

Example to create a MyEmployees table and to select and insert records:

public class MyDatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "DBName";

    private static final int DATABASE_VERSION = 2;

    // Database creation sql statement
    private static final String DATABASE_CREATE = "create table MyEmployees
                                 ( _id integer primary key,name text not null);";

    public MyDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Method is called during creation of the database
    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

    // Method is called during an upgrade of the database,
    @Override
    public void onUpgrade(SQLiteDatabase database,int oldVersion,int newVersion){
        Log.w(MyDatabaseHelper.class.getName(),
                         "Upgrading database from version " + oldVersion + " to "
                         + newVersion + ", which will destroy all old data");
        database.execSQL("DROP TABLE IF EXISTS MyEmployees");
        onCreate(database);
    }
}

Now you can use this class as below,

public class MyDB{  
  
    private MyDatabaseHelper dbHelper;  
  
    private SQLiteDatabase database;  

    public final static String EMP_TABLE="MyEmployees"; // name of table 
      
    public final static String EMP_ID="_id"; // id value for employee
    public final static String EMP_NAME="name";  // name of employee
    
    /** 
     * 
     * @param context 
     */  
    public MyDB(Context context){  
        dbHelper = new MyDatabaseHelper(context);  
        database = dbHelper.getWritableDatabase();  
    }


    public long createRecords(String id, String name){  
        ContentValues values = new ContentValues();  
        values.put(EMP_ID, id);  
        values.put(EMP_NAME, name);  
        return database.insert(EMP_TABLE, null, values);  
    }    

    public Cursor selectRecords() {
        String[] cols = new String[] {EMP_ID, EMP_NAME};  
        Cursor mCursor = database.query(true, EMP_TABLE,cols,null  
            , null, null, null, null, null);  
        if (mCursor != null) {  
            mCursor.moveToFirst();  
        }  
        return mCursor; // iterate to get each value.
    }
}

Now you can use MyDB class in you activity to have all the database operations. The create records will help you to insert the values similarly you can have your own functions for update and delete.

The following Links my help you

1. Android Sqlite Database

2. Tutorial 1

Database Helper Class:

A helper class to manage database creation and version management.

You create a subclass implementing onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int) and optionally onOpen(SQLiteDatabase), and 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.

This class makes it easy for ContentProvider implementations to defer opening and upgrading the database until first use, to avoid blocking application startup with long-running database upgrades.

You need more refer this link Sqlite Helper

The DBHelper class is what handles the opening and closing of sqlite databases as well sa creation and updating, and a decent article on how it all works is here. When I started android it was very useful (however I've been objective-c lately, and forgotten most of it to be any use.

Using Helper class you can access SQLite Database and can perform the various operations on it by overriding the onCreate() and onUpgrade() methods.

http://technologyguid.com/android-sqlite-database-app-example/