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
Video tutorial: