SQLite Database in Android: how to

Well, we will see here today how to use SQLite Database in Android Application. In Android there are some libraries to handle or use SQLite Database. SQLite Database basically a file where everything stored, database schema, structures and data. There is no server or a different tier for handling the DBMS.

OK, Now we see how to add a db to android application. Basically, in Android there is a helper which will act like a bridge or the connector for your database and its tables. So first we have to create the helper for our database. For say, our db name is “LibraryDB”. So we will create the helper as:

	public class LibraryDB  extends SQLiteOpenHelper{
		....
		....
	}

The heper class “SQLiteOpenHelper” is an abstract class. So we must have to implement the constructor. The constructor is like –

public SQLiteOpenHelper (Context context, String name, SQLiteDatabase.CursorFactory factory, int version)

name is the name of the Database and version is the version of your database. It will handle changing your database schema version in future when we will release our next version of this application. For avoiding confusion this is not the SQLite Version. So we will implement the constructor as:

	public LibraryDB(Context context) {
		super(context, LibraryDB.class.getSimpleName(), null, 1);
	}

Here LibraryDB.class.getSimpleName() returns the simple name of the class. So the database name will be the same name of our Helper class name. We can give a different name here. And our DB version is 1 for this scenario.

Now we have to override and implement two methods, are onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int oldVersion, int currentVersion). The onCreate method are called when first time the helper is called. when the database has been created and in constructor new version number has given or the version number doesn’t match the existing current db then it will call the onUpgrade method. So we will handle this in such way where we can apply new schema by changing version number in connector.

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "";// the sql query for creating the tables
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if(oldVersion == 1){
			//write code for altering from version 1 to 2

			//and then call recursively increasing old version and handle that in the next logic and so on
			this.onUpgrade( db, ++oldVersion, newVersion);
		}
		else if(oldVersion == 2){
			//write code for altering from version 2 to 3

			//and then call recursively increasing old version and handle that in the next logic and so on
			this.onUpgrade( db, ++oldVersion, newVersion);
		}
		//....
		//....
	}

So our Connector, database and tables has been created. Now we need to insert the data and retrieve the data. So, we will see the insertion operations.

	LibraryDB dbCon = new LibraryDB(this);
	SQLiteDatabase dbw = dbCon.getWritableDatabase();  // to do write operations like insert, update and delete we have to connect trough this method

	//for name value pare we will use ContentValues object
	ContentValues values = new ContentValues();

	// now put the data to insert
	values.put("name", "the name value");
	values.put("section", "section id");
	//...

	// now inset the data
	dbw.insert("user", null, values);

	//now close the db
	dbw.close();

To insert data we used insert(String table, String nullColumnHack, ContentValues values) method, we also can use insertOrThrow(String table, String nullColumnHack, ContentValues values) method.

To update update(String table, ContentValues values, String whereClause, String[] whereArgs) and to delete delete(String table, String whereClause, String[] whereArgs) method but the same way that used for insertion operation.

This also supports Transactions.

	db.beginTransaction();
	try {
	 //do operations
	 //...
	 //...
	 db.setTransactionSuccessful();
	} finally {
	 db.endTransaction();
	}

We saw how to open the DB as writable, now we will see how to open as readable and how to get the data from the db.

To open the DB as readable we will call the getReadableDatabase() method on the connection.

	SQLiteDatabase dbr = dbCon.getReadableDatabase();
	Cursor cursor = dbr.query("user", null, null, null, null, null, "name DESC");

	startManagingCursor(cursor); //start handling cursors

	while (cursor.moveToNext()){ // runs the loop only when cursor can move to next data
		name = cursor.getString(cursor.getColumnIndex("name"));
		// do the required action with the data
	}

To get the data we use the query method which is – query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) and it returns a cursor object with found data.

For more information please refer to : http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

Feel free to contact for any necessary…
Hussain Behestee
or email here.

Advertisements