Lab8 Sqlite Example 



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" >

    <EditText
        android:id="@+id/etTelNum"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/etName"
        android:layout_below="@+id/etName"
        android:layout_marginTop="18dp"
        android:ems="10"
        android:hint="Enter Tel Number"
        android:inputType="number" />

    <EditText
        android:id="@+id/etName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="27dp"
        android:ems="10"
        android:hint="Enter Name"
        android:inputType="textPersonName" />

    <Button
        android:id="@+id/btnUpdateTel"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBottom="@+id/btnInsert"
        android:layout_toRightOf="@+id/btnInsert"
        android:text="UpdateTel" />

    <Button
        android:id="@+id/btnInsert"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/etTelNum"
        android:layout_below="@+id/etTelNum"
        android:layout_marginLeft="19dp"
        android:layout_marginTop="32dp"
        android:text="Insert" />

    <TextView
        android:id="@+id/tvStatus"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/etTelNum"
        android:layout_below="@+id/btnInsert"
        android:layout_marginTop="54dp"
        android:text=" " />

</RelativeLayout>





MainActivity.java


package mdad.lab8;

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 MainActivity extends Activity {
 SQLiteDatabase db ;
 Button btnInsert,btnUpdate;
 EditText etName, etTel;
 TextView tvStatus;
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        tvStatus = (TextView) findViewById(R.id.tvStatus);
        btnInsert = (Button) findViewById(R.id.btnInsert);
        btnUpdate = (Button) findViewById(R.id.btnUpdateTel);
        etName = (EditText) findViewById(R.id.etName);
        etTel = (EditText) findViewById(R.id.etTelNum);

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

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

     public void onClick(View arg0) {
      tvStatus.setText("");
      String name= etName.getText().toString();
      String tel= etTel.getText().toString();
          
    String sql = "insert into TelDirList (Name ,Tel) values( '"+name+"','"+tel+"')";
    String result = updateTable(sql);
    tvStatus.setText(result);  }
       
        }); 
    //========btnInsert=========

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

      public void onClick(View arg0) {
       tvStatus.setText("");
       String name= etName.getText().toString();
       String tel= etTel.getText().toString();
             
     String sql = "Update TelDirList set Tel= '"+  tel +"'where name = '"+name+"'";
     String result = updateTable (sql);
     tvStatus.setText(result);
        }     
     }); 
     //========btnUpdate=========

     
        
        
    }


    String createDatabase(String sql,String dbName)
 {
     //Create SQLite database below
  try{
   
   System.out.println(sql); //Print to console for debugging
   
   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());
   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");  
   }

    
    
}
at