Insert, Delete and View Functionalities in Database Through Android Studio

Introduction

 
This tutorial explains database connectivity in Android. We will see how to create a database and insert and delete in a database and view the database. To explain this I am making a database for a bank.
 
First, the user will select whether he/she is an admin or a customer. An admin can add and delete accounts and view all accounts. Customers will be able to withdraw cash, deposit cash and view his account details.
 
In this tutorial, we will create functionalities only for the Admin. In the next article, we will create customer functionalities.
 
Step 1
 
Right-click on "Values" -> "New" -> "Values Resource File". Name this file as "color". Add the following code to this file:
  1. <resources>  
  2.   <color name="txt">#FFFFFF</color>  
  3.   <color name="bg">#454545</color>  
  4.   <color name="withdar1">#4d2177</color>  
  5. </resources> 
Step 2
 
Make the following changes in "strings.xml":
  1. <resources>  
  2.   <string name="app_name" >BankDB </string>  
  3.   <string name="action_settings" >Settings</string>  
  4.   <string name="hello_world" >Welcome to the BANK..... </string>  
  5.   <string name="admin">Admin</string>  
  6.   <string name="admintxt">Welcome Admin...</string>  
  7. </resources> 
Step 3
 
Do the following changes in "dimens.xml":
  1. <resources>  
  2.   <!-- Default screen margins, per the Android Design guidelines. -->  
  3.   <dimen name="activity_horizontal_margin">16dp</dimen>  
  4.   <dimen name="activity_vertical_margin">16dp</dimen>  
  5.   <dimen name="wel_admin">30dp</dimen>  
  6.   <dimen name="form_ele">20dp</dimen>  
  7. </resources> 
Step 4
 
Make the following changes in "activity_main.xml" in the layout (your main layout file): Use LinearLayout element.
  1. <TextView  
  2.        android:layout_width="wrap_content"  
  3.        android:layout_height="wrap_content"  
  4.        android:text="@string/hello_world"  
  5.        android:layout_marginLeft="70dp"  
  6.        android:textColor="@color/txt"/>  
  7. <Button  
  8.    android:id="@+id/admin"  
  9.    android:layout_height="wrap_content"  
  10.    android:layout_width="wrap_content"  
  11.    android:layout_marginTop="50dp"  
  12.    android:background="@drawable/admin_design"  
  13.    android:text="@string/admin"/> 
Note that we are only creating an Admin here. A customer will be explained in my next article. The layout looks like:
 
im1.jpg
 
Step 5
 
Make the layout file for the Admin. Right-click on layout then select "New" -> "Layout Resource File". Name this file "admin_layout". Add the following code to this XML file (using LinearLayout element):
  1. <TextView  
  2.             android:layout_width="wrap_content"  
  3.             android:layout_height="wrap_content"  
  4.             android:text="@string/admintxt"  
  5.             android:layout_marginLeft="80dp"  
  6.             android:textColor="@color/txt"  
  7.             android:layout_marginTop="20dp"  
  8.             android:textSize="@dimen/wel_admin"/>  
  9. <Button  
  10.     android:id="@+id/create"  
  11.     android:layout_height="wrap_content"  
  12.     android:layout_width="wrap_content"  
  13.     android:layout_marginTop="70dp"  
  14.     android:layout_marginLeft="40dp"  
  15.     android:background="@drawable/admin_design"  
  16.     android:text="Add new"  
  17.     android:paddingLeft="10dp"  
  18.     android:paddingRight="10dp"/>  
  19. <Button  
  20.         android:id="@+id/del"  
  21.         android:layout_height="wrap_content"  
  22.         android:layout_width="wrap_content"  
  23.         android:layout_marginTop="70dp"  
  24.         android:layout_marginLeft="40dp"  
  25.         android:background="@drawable/admin_design"  
  26.         android:text="Delete Acc"  
  27.         android:paddingLeft="10dp"  
  28.         android:paddingRight="10dp"/>  
  29. <Button  
  30.         android:id="@+id/view"  
  31.         android:layout_height="wrap_content"  
  32.         android:layout_width="wrap_content"  
  33.         android:layout_marginTop="70dp"  
  34.         android:layout_marginLeft="40dp"  
  35.         android:background="@drawable/admin_design"  
  36.         android:text="View Acc"  
  37.         android:paddingLeft="10dp"  
  38.         android:paddingRight="10dp"/> 
The layout looks like:
 
im2.jpg
 
Step 6
 
Create a new layout file for adding a new account to the database in the same way as was done above and name this new file as "create_layout". Add the following to this file, again inside LinearLayout element:
  1. <TextView  
  2.             android:layout_width="wrap_content"  
  3.             android:layout_height="wrap_content"  
  4.             android:text="Create an account...."  
  5.             android:layout_marginLeft="60dp"  
  6.             android:layout_marginTop="20dp"  
  7.             android:textSize="@dimen/wel_admin"  
  8.             />  
  9. <RelativeLayout  
  10.     android:layout_width="fill_parent"  
  11.     android:layout_height="wrap_content">  
  12.    
  13.   <TextView  
  14.           android:id="@+id/t1"  
  15.           android:layout_width="wrap_content"  
  16.           android:layout_height="wrap_content"  
  17.           android:text="Acc id: "  
  18.           android:layout_marginLeft="20dp"  
  19.           android:layout_marginTop="40dp"  
  20.           android:textSize="@dimen/form_ele"/>  
  21.   <EditText  
  22.       android:id="@+id/accid"  
  23.       android:layout_height="wrap_content"  
  24.       android:layout_width="fill_parent"  
  25.       android:layout_marginTop="40dp"  
  26.       android:layout_toRightOf="@id/t1"  
  27.       android:layout_marginLeft="30dp"/>  
  28. </RelativeLayout>  
  29.    
  30. <RelativeLayout  
  31.         android:layout_width="fill_parent"  
  32.         android:layout_height="wrap_content">  
  33.    
  34.   <TextView  
  35.           android:id="@+id/t2"  
  36.           android:layout_width="wrap_content"  
  37.           android:layout_height="wrap_content"  
  38.           android:text="Acc type: "  
  39.           android:layout_marginLeft="20dp"  
  40.           android:layout_marginTop="40dp"  
  41.           android:textSize="@dimen/form_ele"/>  
  42.   <EditText  
  43.           android:id="@+id/acctype"  
  44.           android:layout_height="wrap_content"  
  45.           android:layout_width="fill_parent"  
  46.           android:layout_marginTop="40dp"  
  47.           android:layout_toRightOf="@id/t2"  
  48.           android:layout_marginLeft="10dp"/>  
  49. </RelativeLayout>  
  50.    
  51. <RelativeLayout  
  52.         android:layout_width="fill_parent"  
  53.         android:layout_height="wrap_content">  
  54.    
  55.   <TextView  
  56.           android:id="@+id/t3"  
  57.           android:layout_width="wrap_content"  
  58.           android:layout_height="wrap_content"  
  59.           android:text="Bal: "  
  60.           android:layout_marginLeft="20dp"  
  61.           android:layout_marginTop="40dp"  
  62.           android:textSize="@dimen/form_ele"/>  
  63.   <EditText  
  64.           android:id="@+id/accbal"  
  65.           android:layout_height="wrap_content"  
  66.           android:layout_width="fill_parent"  
  67.           android:layout_marginTop="40dp"  
  68.           android:layout_toRightOf="@id/t3"  
  69.           android:layout_marginLeft="60dp"/>  
  70. </RelativeLayout>  
  71.    
  72. <Button  
  73.     android:id="@+id/bCreate"  
  74.     android:layout_height="wrap_content"  
  75.     android:layout_width="wrap_content"  
  76.     android:layout_marginTop="80dp"  
  77.     android:layout_marginLeft="130dp"  
  78.     android:text="Create"  
  79.     android:background="@drawable/admin_design"/> 
Relative Layout is added wherever needed. The layout looks like:
 
im3.jpg
 
Step 7
 
Create yet another layout file for deleting and name this file "del_layout". Add the following code in the LinearLayout element of this file:
  1. <TextView  
  2.            android:layout_width="wrap_content"  
  3.            android:layout_height="wrap_content"  
  4.            android:text="Delete an account...."  
  5.            android:layout_marginLeft="60dp"  
  6.            android:layout_marginTop="20dp"  
  7.            android:textSize="@dimen/wel_admin"  
  8.             />  
  9. <RelativeLayout  
  10.         android:layout_width="fill_parent"  
  11.         android:layout_height="wrap_content">  
  12.    
  13.   <TextView  
  14.           android:id="@+id/d1"  
  15.           android:layout_width="wrap_content"  
  16.           android:layout_height="wrap_content"  
  17.           android:text="Acc id: "  
  18.           android:layout_marginLeft="20dp"  
  19.           android:layout_marginTop="60dp"  
  20.           android:textSize="@dimen/form_ele"/>  
  21.   <EditText  
  22.           android:id="@+id/acciddel"  
  23.           android:layout_height="wrap_content"  
  24.           android:layout_width="fill_parent"  
  25.           android:layout_marginTop="60dp"  
  26.           android:layout_toRightOf="@id/t1"  
  27.           android:layout_marginLeft="110dp"/>  
  28. </RelativeLayout>  
  29.    
  30. <Button  
  31.     android:id="@+id/bdel"  
  32.     android:layout_height="wrap_content"  
  33.     android:layout_width="wrap_content"  
  34.     android:layout_marginTop="80dp"  
  35.     android:layout_marginLeft="130dp"  
  36.     android:text="Delete"  
  37.     android:background="@drawable/admin_design"/> 
The layout looks like:
 
im4.jpg
 
Step  8
 
Create another layout file for viewing and name it as "view_layout2". Add the following code in the LinearLayout element of this file:
  1. <TextView  
  2.         android:id="@+id/v"  
  3.         android:layout_height="fill_parent"  
  4.         android:layout_width="fill_parent"  
  5.         /> 
Let us now start with the Java part.
 
Step 9
 
Open "MainACtivity.java" (created by default) and add the following code in it:
  1. package com.example.bankdb;  
  2.    
  3. import android.content.Context;  
  4. import android.content.Intent;  
  5. import android.os.Bundle;  
  6. import android.app.Activity;  
  7. import android.view.Menu;  
  8. import android.view.View;  
  9. import android.widget.Button;  
  10.    
  11. public class MainActivity extends Activity {  
  12.    
  13.     Button b;  
  14.     @Override  
  15.     protected void onCreate(Bundle savedInstanceState) {  
  16.         super.onCreate(savedInstanceState);  
  17.         setContentView(R.layout.activity_main);  
  18.         b=(Button)findViewById(R.id.admin);  
  19.         final Context context=this;  
  20.         b.setOnClickListener(new View.OnClickListener() {  
  21.             @Override  
  22.             public void onClick(View v) {  
  23.               // final Context context=this;  
  24.                 Intent i=new Intent(context,Admin.class);  
  25.                 startActivity(i);  
  26.             }  
  27.         });  
  28.     }  
  29.     @Override  
  30.     public boolean onCreateOptionsMenu(Menu menu) {  
  31.         // Inflate the menu; this adds items to the action bar if it is present.  
  32.         getMenuInflater().inflate(R.menu.main, menu);  
  33.         return true;  
  34.     }  

On button click, another activity, namely Admin, is loaded.
 
Step 10
 
Create a Java class in the same package and name it as "Admin". Write the following code in this Java file:
  1. package com.example.bankdb;  
  2. import android.app.Activity;  
  3. import android.content.Context;  
  4. import android.content.Intent;  
  5. import android.os.Bundle;  
  6. import android.view.View;  
  7. import android.widget.Button;  
  8. import com.example.bankdb.R;  
  9.    
  10. public class Admin extends Activity  
  11. {  
  12.     Button create;  
  13.     Button del;  
  14.     Button view;  
  15.     @Override  
  16.     protected void onCreate(Bundle savedInstanceState) {  
  17.         super.onCreate(savedInstanceState);  
  18.         setContentView(R.layout.admin_layout);  
  19.         create=(Button)findViewById(R.id.create);  
  20.         del=(Button)findViewById(R.id.del);  
  21.         view=(Button)findViewById(R.id.view);  
  22.         final Context context=this;  
  23.         create.setOnClickListener(new View.OnClickListener() {  
  24.             @Override  
  25.             public void onClick(View v) {  
  26.                 Intent i= new Intent(context,Create.class);  
  27.                 startActivity(i);  
  28.             }  
  29.         });  
  30.    
  31.         del.setOnClickListener(new View.OnClickListener() {  
  32.             @Override  
  33.             public void onClick(View v) {  
  34.                 Intent i= new Intent(context,Del.class);  
  35.                 startActivity(i);  
  36.             }  
  37.         });  
  38.         view.setOnClickListener(new View.OnClickListener() {  
  39.             @Override  
  40.             public void onClick(View v) {  
  41.                 Intent i= new Intent(context,ViewAcc.class);  
  42.                 startActivity(i);  
  43.             }  
  44.         });  
  45.     }  

The Create, Del and View activities are loaded on clicking the respective buttons in this activity.
 
Step 11
 
Create a Java class in the same package and name it as "Create". Write the following code in this Java file:
  1. package com.example.bankdb;  
  2.    
  3. import android.app.Activity;  
  4. import android.content.ContentValues;  
  5. import android.content.Context;  
  6. import android.content.Intent;  
  7. import android.database.Cursor;  
  8. import android.database.sqlite.SQLiteDatabase;  
  9. import android.os.Bundle;  
  10. import android.view.View;  
  11. import android.widget.Button;  
  12. import android.widget.EditText;  
  13. import android.widget.TextView;  
  14. import android.widget.Toast;  
  15.     
  16. public class Create extends Activity {  
  17.     Button b;  
  18.     EditText t1;  
  19.     EditText t2;  
  20.     EditText t3;  
  21.     SQLiteDatabase db;  
  22.    
  23.     @Override  
  24.     protected void onCreate(Bundle savedInstanceState) {  
  25.         super.onCreate(savedInstanceState);  
  26.         setContentView(R.layout.create_layout);  
  27.         b=(Button)findViewById(R.id.bCreate);  
  28.         t1=(EditText)findViewById(R.id.accid);  
  29.         t2=(EditText)findViewById(R.id.acctype);  
  30.         t3=(EditText)findViewById(R.id.accbal);  
  31.         final Context context=this;  
  32.         try  
  33.         {  
  34.           db=openOrCreateDatabase("Banking1",SQLiteDatabase.CREATE_IF_NECESSARY,null);  
  35.           db.execSQL("CREATE TABLE bank (id integer PRIMARY KEY, type text, bal integer)");  
  36.         }  
  37.         catch(Exception e)  
  38.         {  
  39.             e.printStackTrace();  
  40.         }  
  41.         b.setOnClickListener(new View.OnClickListener() {  
  42.             @Override  
  43.             public void onClick(View v) {  
  44.    
  45.                 String s=t1.getText().toString();  
  46.                 String s1=t2.getText().toString();  
  47.                 String s2=t3.getText().toString();  
  48.                 //db.execSQL("INSERT INTO log VALUES (s)");  
  49.    
  50.                 ContentValues values=new ContentValues();  
  51.                 values.put("id",s);  
  52.                 values.put("type",s1);  
  53.                 values.put("bal",s2);  
  54.                 if((db.insert("bank",null,values))!= -1)  
  55.                 {  
  56.                     Toast.makeText(Create.this"Inserted..."2000).show();  
  57.                  }  
  58.                 else  
  59.                 {  
  60.                     Toast.makeText(Create.this,"Error...",2000).show();  
  61.                 }  
  62.                 t1.setText("");  
  63.                 t2.setText("");  
  64.                 t3.setText("");  
  65.                 Intent i=new Intent(context,Admin.class);  
  66.                 startActivity(i);  
  67.    
  68.             }  
  69.         });  
  70.     }  

In the code above I have created a database. The values entered by the admin are then entered into the database. The "if" statement checks if the record is inserted properly or not. Also, note that "id" is a primary key in the database.
 
Step 12
 
Create a Java class in the same package and name it as "Del". Write the following code in this Java file:
  1. package com.example.bankdb;  
  2.    
  3. import android.app.Activity;  
  4. import android.content.Context;  
  5. import android.content.Intent;  
  6. import android.database.sqlite.SQLiteDatabase;  
  7. import android.database.sqlite.SQLiteException;  
  8. import android.database.sqlite.SQLiteOpenHelper;  
  9. import android.os.Bundle;  
  10. import android.view.View;  
  11. import android.widget.Button;  
  12. import android.widget.EditText;  
  13. import android.widget.Toast;  
  14.    
  15. public class Del extends Activity {  
  16.     Button b;  
  17.     EditText e;  
  18.     SQLiteDatabase db;  
  19.     SQLiteOpenHelper d;  
  20.    
  21.     @Override  
  22.     protected void onCreate(Bundle savedInstanceState) {  
  23.         super.onCreate(savedInstanceState);  
  24.         setContentView(R.layout.del_layout);  
  25.    
  26.         b=(Button)findViewById(R.id.bdel);  
  27.         e=(EditText)findViewById(R.id.acciddel);  
  28.         final Context context=this;  
  29.         try  
  30.         {  
  31.            db=openOrCreateDatabase("Banking1",SQLiteDatabase.CREATE_IF_NECESSARY,null);  
  32.         }  
  33.         catch(SQLiteException e)  
  34.         {  
  35.           e.printStackTrace();  
  36.           System.out.print("ERROR.............");  
  37.         }  
  38.         b.setOnClickListener(new View.OnClickListener() {  
  39.             @Override  
  40.             public void onClick(View v) {  
  41.                 String t=(e.getText().toString());  
  42.                try  
  43.                 {  
  44.                    String d="DELETE FROM bank WHERE id="+t;  
  45.                     db.execSQL(d);  
  46.                 }  
  47.                 catch(Exception e)  
  48.                 {  
  49.                    System.out.print("Error..................");  
  50.                 }  
  51.                  
  52.                 e.setText("");  
  53.                 Toast.makeText(Del.this"Deleted..."2000).show();  
  54.                 Intent i=new Intent(context,Admin.class);  
  55.                 startActivity(i);  
  56.             }  
  57.         });  
  58.     }  

This code removes the record from the database having the id the same as the id entered by the admin.
 
Step 13
 
Create a Java class in the same package and name it "ViewAcc". Write the following code in this Java file:
  1. package com.example.bankdb;  
  2.    
  3. import android.app.Activity;  
  4. import android.app.ListActivity;  
  5. import android.database.Cursor;  
  6. import android.database.sqlite.SQLiteDatabase;  
  7. import android.database.sqlite.SQLiteException;  
  8. import android.os.Bundle;  
  9. import android.view.ViewGroup;  
  10. import android.widget.*;  
  11. import java.util.ArrayList;  
  12. import java.util.List;  
  13.    
  14. public class ViewAcc extends Activity {  
  15.     SQLiteDatabase db;  
  16.    
  17.     @Override  
  18.     protected void onCreate(Bundle savedInstanceState) {  
  19.         super.onCreate(savedInstanceState);  
  20.         setContentView(R.layout.view_layout2);  
  21.    
  22.          try  
  23.         {  
  24.             db=openOrCreateDatabase("Banking1",SQLiteDatabase.CREATE_IF_NECESSARY,null);  
  25.             Cursor c= db.rawQuery("SELECT * FROM bank",null);  
  26.    
  27.             TextView v=(TextView)findViewById(R.id.v);  
  28.             c.moveToFirst();  
  29.    
  30.             String temp="";  
  31.             while(! c.isAfterLast())  
  32.             {  
  33.                 String s2=c.getString(0);  
  34.                 String s3=c.getString(1);  
  35.                 String s4=c.getString(2);  
  36.                 temp=temp+"\n Id:"+s2+"\tType:"+s3+"\tBal:"+s4;  
  37.                 c.moveToNext();  
  38.             }  
  39.             v.setText(temp);  
  40.         }  
  41.         catch(SQLiteException e)  
  42.         {  
  43.    
  44.         }  
  45.     }  

This code displays all the records in the database.
 
Step 14
 
In the end, don't forget to add the newly created activities in the manifest file "AndroidManifest.xml". Add the following code in you manifest file:
  1. <?xml version="1.0" encoding="utf-8"?>  
  2. <manifest xmlns:android="http://schemas.android.com/apk/res/android"  
  3.     package="com.example.bankdb"  
  4.     android:versionCode="1"  
  5.     android:versionName="1.0" >  
  6.    
  7.   <uses-sdk  
  8.       android:minSdkVersion="7"  
  9.       android:targetSdkVersion="16" />  
  10.    
  11.   <application  
  12.       android:allowBackup="true"  
  13.       android:icon="@drawable/ic_launcher"  
  14.       android:label="@string/app_name"  
  15.       android:theme="@style/AppTheme" >  
  16.     <activity  
  17.         android:name="com.example.bankdb.MainActivity"  
  18.         android:label="@string/app_name" >  
  19.       <intent-filter>  
  20.         <action android:name="android.intent.action.MAIN" />  
  21.    
  22.         <category android:name="android.intent.category.LAUNCHER" />  
  23.       </intent-filter>  
  24.     </activity>  
  25.    
  26.     <activity android:name="com.example.bankdb.ViewAcc"  
  27.               android:label="View">  
  28.     </activity>  
  29.    
  30.     <activity android:name=".Create"  
  31.               android:label="Create"/>  
  32.    
  33.     <activity android:name=".Del"  
  34.               android:label="Delete"/>  
  35.    
  36.     <activity android:name=".Admin"  
  37.               android:label="Admin"/>  
  38.   </application>  
  39.    
  40. </manifest> 
The output screens look like:
 
im5.jpg
 
On clicking Admin you will get
 
im6.jpg
 
On clicking Add New you will get (I have filled in some values)
 
im7.jpg
 
The "Toast" will be generated once you click on the Create button.
 
On clicking Delete Acc you will get (I have filled in the value):
 
im8.jpg
 
The "Toast" will be generated once you click on the Delete button.
 
On clicking View Acc you will get:
 
im9.jpg
 
This ends our tutorial. The article containing the Customer functionalities will be uploaded soon.
 
Enjoy coding :)