Introduction to Android SQLite Database

6 Comments

Android provide various options for data persistence like Preferences, File and Database etc. This post we will introduce you to one of the option i.e. Android SQLite Database.

Let us take an example of Student database. i.e. student_database (DATABASE_NAME). We will create table tb_student (DATABASE_TABLE), with 3 columns i.e. _id (KEY_ROWID), name (KEY_NAME) and grade (KEY_GRADE). Columns _id is primary key and autoincrement, while name and garde are text .

We will 1st create a class called DatabaseUtilDatabaseUtil is the main class that will used by other activities and will have various method to perform database operations like insert, delete etc

DatabaseUtil has private inner class DatabaseHelper, which is responsible for creating and updating database.

Below is the code for DatabaseUtil.

package com.dbexample;

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 DatabaseUtil{

	private static final String TAG = "DatabaseUtil";

	/**
	 * Database Name
	 */
	private static final String DATABASE_NAME = "student_database";

	/**
	 * Database Version
	 */
	private static final int DATABASE_VERSION = 1;

	/**
	 * Table Name
	 */
	private static final String DATABASE_TABLE = "tb_student";

	/**
	 * Table columns
	 */
	public static final String KEY_NAME = "name";
	public static final String KEY_GRADE = "grade";
	public static final String KEY_ROWID = "_id";

	/**
	 * Database creation sql statement
	 */
	private static final String CREATE_STUDENT_TABLE =
		"create table " + DATABASE_TABLE + " (" + KEY_ROWID + " integer primary key autoincrement, "
		+ KEY_NAME +" text not null, " + KEY_GRADE + " text not null);";

	/**
	 * Context
	 */
	private final Context mCtx;

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	/**
	 * Inner private class. Database Helper class for creating and updating database.
	 */
	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		/**
		 * onCreate method is called for the 1st time when database doesn't exists.
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i(TAG, "Creating DataBase: " + CREATE_STUDENT_TABLE);
			db.execSQL(CREATE_STUDENT_TABLE);
		}
		/**
		 * onUpgrade method is called when database version changes.
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion);
		}
	}

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 *
	 * @param ctx the Context within which to work
	 */
	public DatabaseUtil(Context ctx) {
		this.mCtx = ctx;
	}
	/**
	 * This method is used for creating/opening connection
	 * @return instance of DatabaseUtil
	 * @throws SQLException
	 */
	public DatabaseUtil open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}
	/**
	 * This method is used for closing the connection.
	 */
	public void close() {
		mDbHelper.close();
	}

	/**
	 * This method is used to create/insert new record Student record.
	 * @param name
	 * @param grade
	 * @return long
	 */
	public long createStudent(String name, String grade) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NAME, name);
		initialValues.put(KEY_GRADE, grade);
		return mDb.insert(DATABASE_TABLE, null, initialValues);
	}
	/**
	 * This method will delete Student record.
	 * @param rowId
	 * @return boolean
	 */
	public boolean deleteStudent(long rowId) {
		return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}

	/**
	 * This method will return Cursor holding all the Student records.
	 * @return Cursor
	 */
	public Cursor fetchAllStudents() {
		return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
				KEY_GRADE}, null, null, null, null, null);
	}

	/**
	 * This method will return Cursor holding the specific Student record.
	 * @param id
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor fetchStudent(long id) throws SQLException {
		Cursor mCursor =
			mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
					KEY_NAME, KEY_GRADE}, KEY_ROWID + "=" + id, null,
					null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	/**
	 * This method will update Student record.
	 * @param id
	 * @param name
	 * @param standard
	 * @return boolean
	 */
	public boolean updateStudent(int id, String name, String standard) {
		ContentValues args = new ContentValues();
		args.put(KEY_NAME, name);
		args.put(KEY_GRADE, standard);
		return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + id, null) > 0;
	}
}

In the above code as we can see DatabaseUtil have a private inner class DatabaseHelper which extends SQLiteOpenHelperSQLiteOpenHelper have following methods:

  1. onCreate
  2. onUpgrade
  3. onOpen
  4. getWritableDatabase
  5. getReadableDatabase
  6. close

Out Of the above methods, onCreate and onUpgrade are two methods which we have overridden in subclass DatabaseHelper. Method onCreate is called for the 1st time when database is created/opened, and hence is used for creating table and populating them with predefined data if needed. Method onUpgrade is called when database needs to be upgarded in terms like, adding new tables, new colums to the existing tables etc based on the database version.

Database version for this example is set to 1 via DATABASE_VERSIONvariable.

Note: this class assumes monotonically increasing version numbers for upgrades. Also, there is no concept of a database downgrade; installing a new version of your app which uses a lower version number than a previously-installed version will result in undefined behavior.

Method onOpen is called when connection to database is opened. This method can be used for instance like checking read-only before updating database.

Methods getWritableDatabase and getReadableDatabase are responsible for creating or opening the database. The first time getWritableDatabasemethod is called it will call onCreateonUpgrade and/or onOpen method is called.

Method close is used for closing the opened database.

Now let us look at the code snippet for creating/inserting  student record.

DatabaseUtil dbUtil = new DatabaseUtil(this);
dbUtil.open();
dbUtil.createStudent("Prashant Thakkar", "10th");
dbUtil.close();

As seen, first we created instance of DatabaseUtil class by passing the context of the class (Activity) in which this code is written. After creating instance call open method, which is responsible for opening database in writable mode (refer sample code above).  Now call createStudent method which accepts 2 parameters name and standard and create student record. Finally call close method to close the opened database.

After insertion, now let us look at the code snippet for fetching data from Students table.

DatabaseUtil dbUtil = new DatabaseUtil(this);
dbUtil.open();
Cursor cursor = dbUtil.fetchAllStudents();
if(cursor != null){
   while(cursor.moveToNext()){
	Log.i("Student", "Student Name: " + cursor.getString(1) +
               " Grade " + cursor.getString(2));
   }
}
dbUtil.close();

For fetching all students from student table we will call fetchAllStudents of DatabaseUtil class. Method fetchAllStudents return Cursor. We will check cursor for null and then iterate through it and print name and standard for each student.  Student name is fetched from cursor by calling cursor.getString(1), here 1 represent the position of column holding the Student Name in the String array passed while querying the database.

To know more about SQLite Datatypes.

Conclusion

This post introduces you to the basics of Android SQLite Database with sample code.

Introduction to Android UI Layouts

12 Comments

Considering the fact that available space on mobile screen is small, UI design of mobile application is very important. For Android each screen follows one or more layout.
Layouts are like container that holds various view or layouts. Placing of views on the screen depends on the layout selected.

This article will introduce following basic layouts with examples:

  • Linear Layout
  • Table Layout
  • Absolute Layout
  • Relative Layout
  • Frame Layout

Let us now look at each of them in details and understand them.

  1. Linear Layout
  2. Linear Layout is the simplest layout. As the name suggest views are place in the linear flow one after the other. However we can specify the orientation of linear flow i.e. horizontally or vertically.

    Vertical Orientation

    <?xml version="1.0" encoding="utf-8"?>
    <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    	android:orientation="vertical" android:layout_width="fill_parent"
    	android:layout_height="fill_parent">
    
    	<Button android:text="Button01" android:id="@+id/Button01"
    		android:layout_width="wrap_content"
            android:layout_height="wrap_content" />
    
    	<Button android:text="Button02" android:id="@+id/Button02"
    		android:layout_width="wrap_content"
            android:layout_height="wrap_content" />
    
    </LinearLayout>
    
    

    Above XML defines Linear Layout with vertical orientation (android:orientation=”vertical”) with 2 buttons (Button01 and Button02) . And since vertical orientation is specified, both the buttons are placed linearly one below the other.

    Linear Layout Vertical Orientation

    Linear Layout Vertical Orientation

    Horizontal Orientation

    Now for above XML change orientation for Linear Layout from vertical to horizontal (android:orientation=”horizontal”) as shown below.  And now both the buttons will be placed linearly next to each other.

    <?xml version="1.0" encoding="utf-8"?>
    <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    	android:orientation="horizontal" android:layout_width="fill_parent"
    	android:layout_height="fill_parent">
    
    	<Button android:text="Button01" android:id="@+id/Button01"
    		android:layout_width="wrap_content"
            android:layout_height="wrap_content" />
    
    	<Button android:text="Button02" android:id="@+id/Button02"
    		android:layout_width="wrap_content"
            android:layout_height="wrap_content" />
    
    </LinearLayout>
    
    
    Linear Layout Horizontal Orientation

    Linear Layout Horizontal Orientation

  3. Table Layout
  4. Again as name suggest this layout places the view/components in tabular format as seen below.

    Table Layout

    Table Layout

    <?xml version="1.0" encoding="utf-8"?>
    <TableLayout android:id="@+id/TableLayout01"
    	android:layout_width="fill_parent" android:layout_height="fill_parent"
    	xmlns:android="http://schemas.android.com/apk/res/android">
    	<TableRow android:id="@+id/TableRow01">
    		<TextView android:id="@+id/TextView01" android:text="First Name:"
    			android:width="100px" />
    		<EditText android:id="@+id/EditText01" android:width="220px" />
    	</TableRow>
    
    	<TableRow android:id="@+id/TableRow02">
    		<TextView android:id="@+id/TextView02" android:text="Second Name:" />
    		<EditText android:id="@+id/EditText02" />
    	</TableRow>
    
    	<TableRow android:id="@+id/TableRow03">
    		<Button android:id="@+id/Button01"
                android:layout_width="wrap_content"
    			android:layout_height="wrap_content" android:text="Submit" />
    
    		<Button android:id="@+id/Button02"
                android:layout_width="wrap_content"
    			android:layout_height="wrap_content" android:text="Reset"
    			android:width="100px" />
    	</TableRow>
    </TableLayout>
    
    

    As shown Table consist of three rows (Table Row) and each row as two views/components. Maximum numbers of components in a single row specify the number of columns in table. While width of column is determined by the maximum width of the components in the column across all the rows. We can see the same in this example where we have specified width of Reset button in xml to 100 pixel  (android:width=”100px”), but it’s width is same as that of Edit Text.

  5. Relative Layout
  6. This layout is the flexible layout of all. This layout allows placing of the components relative to other component or layout. Let us use the same example as that of Table layout  and do the same via Relative Layout.

    <?xml version="1.0" encoding="utf-8"?>
    <RelativeLayout android:id="@+id/RelativeLayout01"
    	android:layout_width="fill_parent" android:layout_height="fill_parent"
    	xmlns:android="http://schemas.android.com/apk/res/android">
    
    	<TextView android:id="@+id/TextView01"
            android:layout_width="wrap_content"
    		android:layout_height="wrap_content" android:text="First Name:"
    		android:width="100px" />
    
    	<EditText android:id="@+id/EditText01" android:layout_width="220px"
    		android:layout_height="wrap_content"
            android:layout_toRightOf="@+id/TextView01"
    		android:layout_below="@+id/RelativeLayout01" />
    
    	<EditText android:id="@+id/EditText02" android:layout_width="220px"
    		android:layout_height="wrap_content"
            android:layout_below="@+id/EditText01"
    		android:layout_alignLeft="@+id/EditText01" />
    
    	<TextView android:id="@+id/TextView02"
            android:layout_width="wrap_content"
    		android:layout_height="wrap_content" android:text="Second Name:"
    		android:width="100px" android:layout_below="@+id/EditText01"
    		android:layout_toLeftOf="@+id/EditText02" />
    
    	<Button android:text="Submit" android:id="@+id/Button01"
    		android:layout_width="100px" android:layout_height="wrap_content"
    		android:layout_below="@id/EditText02"
            android:layout_alignLeft="@id/EditText02" />
    
    	<Button android:text="Reset" android:id="@+id/Button02"
    		android:layout_width="100px" android:layout_height="wrap_content"
    		android:layout_below="@id/EditText02"
            android:layout_alignRight="@id/EditText02" />
    
    </RelativeLayout>
    

    Let us understand from the above XML how components are placed relative to each other.

    For example:

    EditText 01 has attributes android:layout_toRightOf=”@+id/TextView01″ and android:layout_below=”@+id/RelativeLayout01” this will place Edit Text next to Text View (First Name) . Similarly check EditText02 has attributes android:layout_below=”@+id/EditText01″ and android:layout_alignLeft=”@+id/EditText01″ this will place Edit Text box below the Edit Text 01.

    Relative Layout

    Relative Layout

  7. Absolute Layout
  8. This layout is used for placing views at the exact location on the screen using x and y co-ordinates.  As shown below in the XML, for both EditText and Button x and y coordinates have been specified via android:layout_x and android:layout_y respectively.

    <?xml version="1.0" encoding="utf-8"?>
    <AbsoluteLayout android:id="@+id/AbsoluteLayout01"
    	android:layout_width="fill_parent" android:layout_height="fill_parent"
    	xmlns:android="http://schemas.android.com/apk/res/android">
    	<EditText android:id="@+id/EditText01" android:layout_width="200px"
    		android:layout_height="wrap_content" android:layout_x="12px"
    		android:layout_y="12px" />
    	<Button android:text="Search" android:id="@+id/Button01"
    		android:layout_width="100px" android:layout_height="wrap_content"
    		android:layout_x="220px" android:layout_y="12px" />
    </AbsoluteLayout>
    
    
    Absolute Layout

    Absolute Layout

  9. Frame Layout
  10. Frame Layout is a single screen with all the views in this layout will be drawn on same screen anchored to the top left corner of the screen and hence it is likely that views can overlap each another unless  transparent.

    <?xml version="1.0" encoding="utf-8"?>
    <FrameLayout android:id="@+id/FrameLayout01"
    	android:layout_width="fill_parent" android:layout_height="fill_parent"
    	xmlns:android="http://schemas.android.com/apk/res/android">
    
    	<ImageView android:id="@+id/ImageView01" android:src="@drawable/android"
    		android:layout_width="fill_parent"
            android:layout_height="fill_parent"
    		android:scaleType="center" />
    
    	<TextView android:text="Android Partaker" android:id="@+id/TextView01"
    		android:layout_width="wrap_content"
            android:layout_height="wrap_content"
    		android:layout_marginBottom="20dip"
            android:layout_gravity="center_horizontal|bottom"
    		android:padding="10dip"
            android:textColor="#AA0000" android:textStyle="bold"
    		android:textSize="20px" android:background="#00000000" />
    
    </FrameLayout>
    

    In this example we have used Image View for image. Image to be display is provided as source via  android:src=”@drawable/android (here image android.jpg is available under res/drawable-hdpi). Text View is adjusted to it position with the help of android:layout_marginBottom=“20dip” and android:layout_gravity=“center_horizontal|bottom”

    Frame Layout

    Frame Layout

Conclusion

This article introduces the various android layouts via samples helping to understand them. Hopefully this has helped you through your first step towards android application development.