Android Database

In this tutorial I will introduce you with Android Database. I will use SQLite for the application for this tutorial. By the way, if you do not read previous tutorials, you may face some difficulties to understand the project.

What is SQLite?
SQLite is a relational database management system contained in a C programming library. In contrast to other database management systems, SQLite is not implemented as a separate process that a client program running in another process accesses. Rather, it is part of the using program.

sqlite

SQLite is ACID-compliant and implements most of the SQL standard, using a dynamically and weakly typed SQL syntax that does not guarantee the domain integrity.

SQLite is a popular choice as embedded database for local/client storage in application software such as web browsers. It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, and embedded systems, among others.[5] SQLite has bindings to many programming languages.

Step 1:
We are planning to create a book database for this application. We will create minimum amount of data input for simplicity. However, if you wish, you can extend this app as much as you want.

Lets create a Android project named, “RoidDatabase“. First of all need to create our view in “res/layoutlist_layout.xml and paste the following code for the layout.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:baselineAligned="true"
    android:orientation="horizontal" >

    <TextView
        android:id="@+id/_id"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="10dp"
        android:textSize="24sp" />

    <TextView
        android:id="@+id/name"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="10dp"
        android:textSize="24sp" />

</LinearLayout>

We have our main activity layout in the same location. Now update your activity_main.xml as follows.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <ListView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@android:id/list"/>

    <Button
        android:id="@+id/fix_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Fix"
        android:layout_alignParentBottom="true" 
        android:textSize="24sp"/>

</RelativeLayout>

Step 2:

As we are planing to use SQLite, it is best practice to create a separate class to handle our database.

So, lets create a DatabaseHelperclass in the source folder.

package com.example.roiddatabase;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	final static String TABLE_NAME = "books";
	final static String _ID = "_id";
	final static String TITLE_NAME = "title";
	final static String AUTHOR_NAME = "name";
	final static String[] columns = { _ID, TITLE_NAME, AUTHOR_NAME };
	
	final private static String CREATE_CMD =

			"CREATE TABLE books (" + _ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ TITLE_NAME + " TEXT NOT NULL, "
					+ AUTHOR_NAME + " TEXT NOT NULL)";

	final private static String NAME = "books_db";
	final private static Integer VERSION = 1;
	final private Context mContext;

	public DatabaseHelper(Context context) {
		super(context, NAME, null, VERSION);
		this.mContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(CREATE_CMD);
	}

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

}

Now we need to update our MainActivity class to handle our database input and output.

package com.example.roiddatabase;

import android.app.ListActivity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.SimpleCursorAdapter;

public class MainActivity extends ListActivity {
	
	private SQLiteDatabase mDB = null;
	private DatabaseHelper mDbHelper;
	private SimpleCursorAdapter mAdapter;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
				
		mDbHelper = new DatabaseHelper(this);
	
		mDB = mDbHelper.getWritableDatabase();

		clearAll();
		
		insertBooks();
		
		Cursor c = readBooks();
		mAdapter = new SimpleCursorAdapter(this, R.layout.list_layout, c,
				DatabaseHelper.columns, new int[] { R.id._id, R.id.name },
				0);

		setListAdapter(mAdapter);

		Button fixButton = (Button) findViewById(R.id.fix_button);
				fixButton.setOnClickListener(new OnClickListener() {

					@Override
					public void onClick(View v) {

						fix();

						mAdapter.getCursor().requery();
						mAdapter.notifyDataSetChanged();
					}
				});
				
	}


	private void insertBooks() {

		ContentValues values = new ContentValues();

		values.put(DatabaseHelper.TITLE_NAME, "Android Programming: The Big Nerd Ranch Guide");
		values.put(DatabaseHelper.AUTHOR_NAME, "Bill Phillips");
		mDB.insert(DatabaseHelper.TABLE_NAME, null, values);
		
		values.clear();
		 
		values.put(DatabaseHelper.TITLE_NAME, "Professional Android 4 Application Development");
		values.put(DatabaseHelper.AUTHOR_NAME, "Reto Meier");
		mDB.insert(DatabaseHelper.TABLE_NAME, null, values);

		
		//You can insert more data into the table
		
	}


	private Cursor readBooks() {
		return mDB.query(DatabaseHelper.TABLE_NAME,
				DatabaseHelper.columns, null, new String[] {}, null, null,
				null);
	}


	private void fix() {

		mDB.delete(DatabaseHelper.TABLE_NAME,
				DatabaseHelper.AUTHOR_NAME + "=?",
				new String[] { "Reto Meier" });


		ContentValues values = new ContentValues();
		values.put(DatabaseHelper.AUTHOR_NAME, "Reto Meier");

		mDB.update(DatabaseHelper.TABLE_NAME, values,
				DatabaseHelper.AUTHOR_NAME + "=?",
				new String[] { "Reto Meier" });

	}

	private void clearAll() {

		mDB.delete(DatabaseHelper.TABLE_NAME, null, null);

	}


	@Override
	protected void onDestroy() {

		mDB.close();
		mDbHelper.deleteDatabase();

		super.onDestroy();

	}
}

Done…

Now run your application with your emulator.

Android Database

Happy coding… 🙂

Hej, I’m from Bangladesh. Learning programming is one of the freaking decisions I have taken in my life. Because, it makes me and my life crazy. I have great weakness on open source technologies. Perhaps, that’s why I do not know any closed source language. I fall in love with programming, when I started my undergraduate in East West University. Till now, I can not live without it.
Print Friendly
One comment on “Android Database

Leave a Reply

Your email address will not be published. Required fields are marked *

*