MDAD17: Lab6 Sqlite (Insert, Delete, Update, Select , Create)

Part1: Creating a simple Android application with SQLite Database

Executing SQL statements via method



//#1 Create Database and Table 
  String sql="create table if not exists EmailList (recId integer PRIMARY KEY autoincrement, Name text, Email text)";
  String result = createDatabase(sql,"sampleData.db")


 String sql = "Update EmailList set email= '"+  email +"'where name = '"+name+"'";
 String result = updateTable (sql);

String sql = "insert into EmailList (name ,email) values( '"+name+"','"+email+"')";
String result = updateTable(sql);

String sql = "delete from EmailList where name = '"+name+"'";
String result = updateTable (sql);

String sql = "Select * from EmailList where name = '"+name+"'";
        String result =queryTable(sql, tvDisplayResults);
        tvStatus.setTextColor(Color.RED);
        tvStatus.setText(result);


activity_main.xml



<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    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" >

  <Button
        android:id="@+id/btnInsert"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignRight="@+id/etName"
        android:layout_centerVertical="true"
        android:text="Insert" />

    <Button
        android:id="@+id/btnDelete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/btnInsert"
        android:layout_alignTop="@+id/btnInsert"
        android:layout_marginTop="52dp"
        android:text="Delete" />

    <Button
        android:id="@+id/btnUpdate"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/btnDelete"
        android:layout_below="@+id/btnDelete"
        android:text="Update" />

    <Button
        android:id="@+id/btnSearch"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/btnUpdate"
        android:layout_below="@+id/btnUpdate"
        android:text="Search" />

    <TextView
        android:id="@+id/tvStatus"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_above="@+id/btnInsert"
        android:layout_alignRight="@+id/btnUpdate" />

    <EditText
        android:id="@+id/etName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_centerHorizontal="true"
        android:layout_marginTop="24dp"
        android:ems="10"
        android:hint="Enter Your Name:" >

        <requestFocus />
    </EditText>

    <Button
        android:id="@+id/btnPassValues"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentBottom="true"
        android:layout_alignRight="@+id/btnUpdate"
        android:text="Pass values to Pg 2" />

    <EditText
        android:id="@+id/etEmail"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/etName"
        android:layout_below="@+id/etName"
        android:layout_marginTop="24dp"
        android:ems="10"
        android:hint="Email" />

    <TextView
        android:id="@+id/tvDisplayResults"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/etEmail"
        android:layout_below="@+id/tvStatus"
        android:layout_toLeftOf="@+id/btnInsert"
        android:minLines="10" />

</RelativeLayout>

MainActivity.java

package mdad.Lab6;

 
import android.os.Bundle;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends Activity {
 SQLiteDatabase db ;
 Button btnInsert,btnDelete,btnUpdate,btnSearch,btnPassValues;
 EditText etName, etEmail;
 TextView tvStatus,tvDisplayResults;
 
 @Override
 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_main);
  tvStatus = (TextView) findViewById(R.id.tvStatus);
  tvDisplayResults= (TextView) findViewById(R.id.tvDisplayResults);
  etName = (EditText) findViewById(R.id.etName);
  etEmail = (EditText) findViewById(R.id.etEmail);
  
  
  //#1 Create Database and Table 
  String sql="create table if not exists EmailList (recId integer PRIMARY KEY autoincrement, Name text, Email text)";
  String result = createDatabase(sql,"sampleData.db");
  
  
  tvStatus.setTextColor(Color.BLACK);
  tvStatus.setText(result);
   
  //#2 Add Listeners on the Insert, Delete, Update, Search Buttons
  addListenerOnButton();
  
  
 }
 
 
  
 
 String createDatabase(String sql,String dbName)
 {
     //Create SQLite database below
  try{
   
   System.out.println(sql);
   
   db = SQLiteDatabase.openOrCreateDatabase("sdcard/"+dbName,null);
   db.beginTransaction();
   db.execSQL(sql);
   db.setTransactionSuccessful();
   db.endTransaction();
  }
  catch (Exception e) {
   Toast.makeText(this,e.getMessage(),Toast.LENGTH_LONG).show();
     
   System.out.println(e.toString()); //Print to console for debugging
   return ("error open DB");
  }
  
  return "DB"+dbName+" Created...";

   
 }//end of createDatabase method
 
 
   //SQL statements for Insert, Delete , Update
 String updateTable(String sql)
 {
  try{
   System.out.println(sql);
   db.beginTransaction();
   db.execSQL(sql);
   db.setTransactionSuccessful();
   db.endTransaction();
   
  }
  catch (Exception e) {
   System.out.println(e.toString());
   return ("Error updating DB");
  }
  
  return ("DB updated");

   
 }
 
 // SQL query statement or Select statement
 String queryTable(String sql, TextView displayResults)
 {
  boolean noData= true;
  
  try{
   System.out.println(sql);
   displayResults.setText("");  //clear text from display
   
   Cursor cursor = db.rawQuery(sql,null);
   while (cursor.moveToNext()) {
       
    
    noData =false;  //there is data found
    
    String name = cursor.getString(cursor.getColumnIndex("Name"));
    String email = cursor.getString(cursor.getColumnIndex("Email"));                 
        displayResults.append(name+"  "+email+"\n");
        
   }
   
   
  }
  catch (Exception e) {
   System.out.println(e.toString());
   return ("Error Retrieving DB");
  }
  
  if (noData) 
  { 
    
   return("Not found"); 
  }
  else
  return ("Search Results");

   
 }
 
 
 
 public void addListenerOnButton() {
  

  
  //========btnInsert=========
    btnInsert = (Button) findViewById(R.id.btnInsert);
    btnInsert.setOnClickListener(new OnClickListener() {

     @Override
     public void onClick(View arg0) {
      tvStatus.setText("");
      String name= etName.getText().toString();
      String email= etEmail.getText().toString();
      
      String sql = "insert into EmailList (name ,email) values( '"+name+"','"+email+"')";
      String result = updateTable(sql);
      tvStatus.setText(result);
     }
   
    }); 
  //========btnInsert=========
    
     //========btnDelete=========
    btnDelete = (Button) findViewById(R.id.btnDelete);
    btnDelete.setOnClickListener(new OnClickListener() {

     @Override
     public void onClick(View arg0) {
      tvStatus.setText("");
      String name= etName.getText().toString();
      String email= etEmail.getText().toString();
      
      String sql = "delete from EmailList where name = '"+name+"'";
      String result = updateTable (sql);
      tvStatus.setText(result);
     }
   
    }); 
  //========btnDelete=========   
  
    
       //========btnUpdate=========
    btnUpdate = (Button) findViewById(R.id.btnUpdate);
    btnUpdate.setOnClickListener(new OnClickListener() {

       @Override
       public void onClick(View arg0) {
        tvStatus.setText("");
        String name= etName.getText().toString();
        String email= etEmail.getText().toString();
        
        String sql = "Update EmailList set email= '"+  email +"'where name = '"+name+"'";
        String result = updateTable (sql);
        tvStatus.setText(result);
       }
     
      }); 
    //========btnUpdate=========     
    
    
       //========btnSearch=========
    btnSearch = (Button) findViewById(R.id.btnSearch);
    btnSearch.setOnClickListener(new OnClickListener() {

       @Override
       public void onClick(View arg0) {
        tvStatus.setText("");
        String name= etName.getText().toString();
        String email= etEmail.getText().toString();
        
        String sql = "Select * from EmailList where name = '"+name+"'";
        String result =queryTable(sql, tvDisplayResults);
        tvStatus.setTextColor(Color.RED);
        tvStatus.setText(result);
        
       }
     
      }); 
    //========btnSearch========= 
    
    
    
       //========btnPassValues==Pass Values to next Page=======
    btnPassValues = (Button) findViewById(R.id.btnPassValues);
    btnPassValues.setOnClickListener(new OnClickListener() {

       @Override
       public void onClick(View arg0) {
        tvStatus.setText("");
        String name= etName.getText().toString();
        String email= etEmail.getText().toString();
        
       
          
        Intent intent = new Intent(MainActivity.this, SecondActivity.class);
          
           intent.putExtra("UserName",name);
           intent.putExtra("UserEmail",email);
          
           startActivity(intent);
            
        
       }
     
      }); 
       //========btnPassValues==Pass Values to next Page=======
    
    
 } 
 
 
 
 
 
 

}//end of MainActivity

activity_second.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    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=".SecondActivity" >

   <EditText
        android:id="@+id/etEmail2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/etName2"
        android:layout_alignParentTop="true"
        android:layout_marginTop="114dp"
        android:ems="10"
        android:hint="Email" />

    <EditText
        android:id="@+id/etName2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBottom="@+id/etEmail2"
        android:layout_centerHorizontal="true"
        android:layout_marginBottom="51dp"
        android:ems="10"
        android:hint="Name" />
    
        <TextView
        android:id="@+id/tvStatus2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/etEmail2"
        android:layout_alignRight="@+id/etEmail2"
        android:layout_below="@+id/etEmail2"
        android:layout_marginTop="27dp" />

    <Button
        android:id="@+id/btnUpdate2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignRight="@+id/tvStatus2"
        android:layout_below="@+id/tvStatus2"
        android:layout_marginTop="40dp"
        android:text="updateEmail" />

</RelativeLayout>

SecondActivity.java

package mdad.Lab6;
 
import android.view.Menu;
import android.os.Bundle;
import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class SecondActivity extends Activity {

 EditText etName2, etEmail2;
 SQLiteDatabase db ;
 Button btnUpdate2;
 TextView tvStatus2;
 
 @Override
 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_second);
  
  
  String name="", email="";
  
  
  etName2 = (EditText) findViewById(R.id.etName2);
  etEmail2 = (EditText) findViewById(R.id.etEmail2);
  tvStatus2 = (TextView) findViewById(R.id.tvStatus2);
  
  etName2.setEnabled(false);  //Disable Editing 
  
  
  Bundle bundle = getIntent().getExtras();

        if(bundle!= null)
        {
         
         name = bundle.getString("UserName");
         email = bundle.getString("UserEmail");
         
         
        }
        
        etName2.setText(name);
        etEmail2.setText(email);
  String result =   openDatabase("sampleData.db");
  
  
  //========btnUpdate=========
  btnUpdate2 = (Button) findViewById(R.id.btnUpdate2);
  btnUpdate2.setOnClickListener(new OnClickListener() {

     @Override
     public void onClick(View arg0) {
      tvStatus2.setText("");
      String name= etName2.getText().toString();
      String email= etEmail2.getText().toString();
      
      String sql = "Update EmailList set email= '"+  email +"'where name = '"+name+"'";
      String result = updateTable (sql);
      tvStatus2.setText(result);
     }
   
    }); 
  //========btnUpdate=========  
  
  
  
 }
 
 
 
 
 String openDatabase( String dbName)
 {
     //Create SQLite database below
  try{
      
    db = SQLiteDatabase.openOrCreateDatabase("sdcard/"+dbName,null);
    
  }
  catch (Exception e) {
   Toast.makeText(this,e.getMessage(),Toast.LENGTH_LONG).show();
     
   System.out.println(e.toString());
   return ("error open DB");
  }
  
  return "DB"+dbName+" Opened...";

   
 }
 
 
 
 String updateTable(String sql)
 {
  try{
   System.out.println(sql);
   db.beginTransaction();
   db.execSQL(sql);
   db.setTransactionSuccessful();
   db.endTransaction();
   
  }
  catch (Exception e) {
   System.out.println(e.toString());
   return ("Error updating DB");
  }
  
  return ("DB updated");

   
 }
 
  
 

 

}

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="mdad.Lab6"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="18"
        android:targetSdkVersion="18" />

    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="mdad.Lab6.MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <activity
            android:name="mdad.Lab6.SecondActivity"
            android:label="@string/title_activity_second" >
        </activity>
        <activity
            android:name="mdad.Lab6.MainActivity22"
            android:label="@string/title_activity_main_activity22" >
        </activity>
    </application>

</manifest>

MDAD17

Lab6 Sqlite (Insert, Delete, Update, Select , Create)

Report Abuse

Labels