Android SQLite Database – Part 2/2

This is the second post in SQLite series. In this post we will create an app to perform insert, update, delete and select operation on database. We will be using same class created in previous post to interact with database.

Let’s first create a layout for providing an interface to interact with database to perform CRUD operations.

activity_main.xml

<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/LinearLayout1"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity" >
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal" >
        <EditText
            android:id="@+id/etName"
            android:layout_width="0dip"
            android:layout_height="wrap_content"
            android:layout_weight=".5"
            android:hint="@string/etName"
            android:singleLine="true" />
        <EditText
            android:id="@+id/etContact"
            android:layout_width="0dip"
            android:layout_height="wrap_content"
            android:layout_weight=".5"
            android:hint="@string/etContact"
            android:inputType="number"
            android:singleLine="true" />
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal" >
        <Button
            android:id="@+id/btnInsert"
            android:layout_width="0dip"
            android:layout_height="wrap_content"
            android:layout_weight=".25"
            android:text="@string/btnInsert" />
        <Button
            android:id="@+id/btnUpdate"
            android:layout_width="0dip"
            android:layout_height="wrap_content"
            android:layout_weight=".25"
            android:text="@string/btnUpdate" />
        <Button
            android:id="@+id/btnDelete"
            android:layout_width="0dip"
            android:layout_height="wrap_content"
            android:layout_weight=".25"
            android:text="@string/btnDelete" />
        <Button
            android:id="@+id/btnSelect"
            android:layout_width="0dip"
            android:layout_height="wrap_content"
            android:layout_weight=".25"
            android:text="@string/btnSelect" />
    </LinearLayout>
    <ScrollView
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
        <TextView
            android:id="@+id/tvOutput"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
    </ScrollView>
</LinearLayout>

This layout uses some string constants declared in strings.xml and dimen.xml

strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="app_name">DatabaseDemoApp</string>
    <string name="action_settings">Settings</string>
    <!-- string constants -->
    <string name="etName">Name</string>
    <string name="etContact">Contact</string>
    <string name="btnInsert">Insert</string>
    <string name="btnUpdate">Update</string>
    <string name="btnDelete">Delete</string>
    <string name="btnSelect">Select</string>
</resources>

dimen.xml

<resources>
    <!-- Default screen margins, per the Android Design guidelines. -->
    <dimen name="activity_horizontal_margin">16dp</dimen>
    <dimen name="activity_vertical_margin">16dp</dimen>
</resources>

The code for implementing database is in MainActivity.java

MainActivity.java

package pcsalt.example.databasedemoapp;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
public class MainActivity extends Activity implements OnClickListener, DBConsts {
	EditText etName, etContact;
	TextView tvOutput;
	Button btnInsert, btnUpdate, btnDelete, btnSelect;
	final String TAG = getClass().getSimpleName().toString();
	final Context context = MainActivity.this;
	// Create database object
	MyDatabase mDB = new MyDatabase(context);
	ContentValues values = new ContentValues();
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		etName		= (EditText) findViewById(R.id.etName);
		etContact	= (EditText) findViewById(R.id.etContact);
		tvOutput	= (TextView) findViewById(R.id.tvOutput);
		btnInsert	= (Button)   findViewById(R.id.btnInsert);
		btnUpdate	= (Button)   findViewById(R.id.btnUpdate);
		btnDelete	= (Button)   findViewById(R.id.btnDelete);
		btnSelect	= (Button)   findViewById(R.id.btnSelect);
		// Add listeners to button
		btnInsert.setOnClickListener(this);
		btnUpdate.setOnClickListener(this);
		btnDelete.setOnClickListener(this);
		btnSelect.setOnClickListener(this);
		// Open database connection in onCreate()
		mDB.open();
	}
	@Override
	protected void onDestroy() {
		super.onDestroy();
		// Close database connection when application is destroyed
		mDB.close();
	}
	@Override
	public void onClick(View v) {
		switch(v.getId()) {
		case R.id.btnInsert:
			insert();
			break;
		case R.id.btnUpdate:
			update();
			break;
		case R.id.btnDelete:
			delete();
			break;
		case R.id.btnSelect:
			select();
			break;
		}
	}
	public void insert() {
		// Insert statement uses ContentValues type to store column values
		// in ContentValues: key => column name & value => column data
		values.put(NAME, getName());
		values.put(CONTACT, getContact());
		long id = mDB.insert(TBL_CONTACT, values);
		output("inserted row id: " + id);
		iLog("inserted row id: " + id);
	}
	public void update() {
		// the ? will be replaced by the whereArgs element
		// So always keep matching string for whereClause and whereArgs
		String whereClause = NAME + "=?";
		String[] whereArgs = new String[] { getName() };
		values.put(CONTACT, getContact());
		int updated_rows = mDB.update(TBL_CONTACT, values, whereClause, whereArgs);
		output("Number of updated rows: " + updated_rows);
		iLog("Number of updated rows: " + updated_rows);
	}
	public void delete() {
		String whereClause = NAME + "=?" + AND + CONTACT + "=?";
		String[] whereArgs = new String[] {
				getName(),
				getContact()
		};
		int deleted_rows = mDB.delete(TBL_CONTACT, whereClause, whereArgs);
		output("Number of deleted rows: " + deleted_rows);
		iLog("Number of deleted rows: " + deleted_rows);
	}
	public void select() {
		StringBuilder result = new StringBuilder();
		int id;
		String name, contact;
		String[] columns = new String[] {
				ID,
				NAME,
				CONTACT
		};
		String selection = null; 	// no selection
		String[] selectionArgs = null;	// not needed
		Cursor cur = mDB.select(TBL_CONTACT, columns, selection, selectionArgs);
		//check if cursor returned from select statement is not null
		if(cur != null) {
			while(cur.moveToNext()) {
				id = cur.getInt(cur.getColumnIndex(ID));
				name = cur.getString(cur.getColumnIndex(NAME));
				contact = cur.getString(cur.getColumnIndex(CONTACT));
				result.append("[id: " + id + "]\t[name: " + name + "]\t[contact: " + contact + "]\n");
			}
		} else {
			result.append("No result found");
		}
		output(result);
		iLog(result);
	}
	public String getName() {
		return etName.getText().toString();
	}
	public String getContact() {
		return etContact.getText().toString();
	}
	// used for LogCat output
	public void iLog(Object message) {
		Log.i(TAG, message.toString());
	}
	public void output(Object message) {
		tvOutput.setText(message.toString());
	}
}

Download source

From GitHub

Video tutorial: