Today I’m going to tell you how to developed Android database application with SQLite .
In above video you can see the working example.
Now we look into details of this example app. In here I have two main layouts first one is to
- Create Database & Table
- Add Data to a table
- Drop Database
This is my activity_main,xml its a main layout of my sample app
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
tools:context=".MainActivity"
android:background="@drawable/b" >
<AbsoluteLayout
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentBottom="true"
android:layout_alignParentLeft="true"
android:layout_alignParentRight="true"
android:layout_alignParentTop="true" >
<Button
android:id="@+id/button1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_x="12dp"
android:layout_y="10dp"
android:onClick="createDB"
android:text="CreateDB and Table" />
<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_x="17dp"
android:layout_y="100dp"
android:text="Name"
android:textColor="@android:color/white"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/editText1"
android:layout_width="275dp"
android:layout_height="wrap_content"
android:layout_x="13dp"
android:layout_y="129dp"
android:ems="10" />
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_x="18dp"
android:layout_y="187dp"
android:text="Age"
android:textColor="@android:color/white"
android:textAppearance="?android:attr/textAppearanceMedium" />
<Button
android:id="@+id/button4"
android:layout_width="142dp"
android:layout_height="wrap_content"
android:layout_x="167dp"
android:layout_y="10dp"
android:onClick="drop"
android:text="DropDB" />
<Button
android:id="@+id/button3"
android:layout_width="266dp"
android:layout_height="wrap_content"
android:layout_x="26dp"
android:layout_y="357dp"
android:onClick="viewData"
android:text="View Data" />
<EditText
android:id="@+id/editText2"
android:layout_width="148dp"
android:layout_height="wrap_content"
android:layout_x="15dp"
android:layout_y="215dp"
android:ems="10"
android:inputType="number" />
<Button
android:id="@+id/button2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_x="188dp"
android:layout_y="214dp"
android:onClick="addData"
android:text="Save Data" />
</AbsoluteLayout>
</RelativeLayout>
This is my MainActivity class which is related to above layout.
package com.madushanka.dbtest;
import android.os.Bundle;
import android.app.Activity;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.View;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends Activity {
SQLiteDatabase myDB = null;
String TableName = "Student";
String Data = "";
EditText studentName;
EditText studentAge;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
studentName = (EditText) findViewById(R.id.editText1); // getting text fields (Edit Text)
studentAge= (EditText) findViewById(R.id.editText2);
}
// this method will trigger when user clicked " CreateDB & Table " button
public void createDB(View v) {
try {
// Get the database if database is not exists create new database
// Database name is " test "
myDB = this.openOrCreateDatabase("test", MODE_PRIVATE, null);
// Create table with tow columns (Name and Age)
myDB.execSQL("CREATE TABLE IF NOT EXISTS " + TableName
+ " (Name VARCHAR, Age INT(3));");
Toast.makeText(getBaseContext(), "Database & Table Created",Toast.LENGTH_LONG).show();
} catch (Exception e) {
Log.e("Error", "Error", e);
Toast.makeText(getBaseContext(),
"Error in creating atabase or Table", Toast.LENGTH_LONG).show();
}
}
// this method will trigger when user clicked " Drop DB " button
public void drop(View v) {
try {
// we use deleteDatabse("Database name ") method to delete database
// we have to pass String parameter to give the name of database
deleteDatabase("test");
Toast.makeText(getBaseContext(), "Databse Deleted", Toast.LENGTH_LONG)
.show();
} catch (Exception e) {
Log.e("Error", "Error", e);
Toast.makeText(getBaseContext(), "Error in deleting",Toast.LENGTH_LONG).show();
}
}
// this method will trigger when user clicked " Save Data " button
public void addData(View v) {
try{
// getting created database or if database is not exists create new database
myDB = this.openOrCreateDatabase("test", MODE_PRIVATE, null);
String name =studentName.getText().toString();
int age = Integer.parseInt(studentAge.getText().toString().trim());
myDB.execSQL("INSERT INTO " + TableName + " (Name, Age)" + " VALUES ('"
+ name + "', " + age + ");");
Toast.makeText(getBaseContext(), "Date Saved ", Toast.LENGTH_LONG).show();
}
catch(Exception e){
Log.e("Error", "Error", e);
Toast.makeText(getBaseContext(), "No Database found ", Toast.LENGTH_LONG).show();
}
}
// this method will trigger when user clicked " View Data " button
public void viewData(View v) {
// creating new intent using ViewActivity Class and start activity to show table data
Intent i = new Intent(this,ViewActivity.class);
startActivity(i);
}
}
Second Layout is activity_view.xml using this layout I will dispaly the table content (table recodes)
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
tools:context=".ViewActivity"
android:background="@drawable/b" >
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_centerHorizontal="true"
android:layout_centerVertical="true"
android:text="No Data Found"
android:textSize="18dp"
android:textColor="@android:color/white"/>
</RelativeLayout>
This is my View Activity Class which is related to above layout
package com.madushanka.dbtest;
import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.util.Log;
import android.widget.TextView;
import android.widget.Toast;
public class ViewActivity extends Activity {
SQLiteDatabase myDB = null;
String TableName = "Student";
String Data = "";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_view);
try {
myDB = this.openOrCreateDatabase("test", MODE_PRIVATE, null);
//getting the cursor object
Cursor c = myDB.rawQuery("SELECT * FROM " + TableName, null);
int Column1 = c.getColumnIndex("Name");
int Column2 = c.getColumnIndex("Age");
c.moveToFirst();
if (c != null) {
do {
String Name = c.getString(Column1);
int Age = c.getInt(Column2);
Data = Data + Name + "\t\t" + Age + "\n\n";
} while (c.moveToNext());
}
TextView tv = new TextView(this); // creating Text View to show data in the app
tv.setTextColor(Color.WHITE);
tv.setBackgroundResource(R.drawable.b);
tv.setTextSize(18F);
tv.setText("\n"+"Name \t| Age \n ----------------------\n"+Data);
setContentView(tv); // set created text view as Content View
}
catch (Exception e) {
Log.e("Error", "Error", e);
Toast.makeText(getBaseContext(), "No Data found ", Toast.LENGTH_LONG).show();
} finally {
if (myDB != null)
myDB.close();
}
}
}
You can download sample apk file from here
Madushanka's IT Blog..... 