SQLiteDatabase

SQLiteDatabase :

Android provides several ways to store user and app data. SQLite is one way of storing user data. SQLite is a very light weight database which comes with Android OS. In this tutorial I’ll be discussing how to write classes to handle all SQLite operations.

Objectives:

How to start using SQLite API?

How to create new database & database tables?

How to perform CRUD “Create, Read, Update and Delete” operations?

We will build an App that can store & retrieve Person Details like, First Name, Last Name, Location, Address and Phone Number.
SQLiteDatabase Source Code

Steps to Create Android Application:

Create a new project in Eclipse from File ⇒ New ⇒ Android ⇒ Application Project. I named my Application as SQLiteDatabase and Package name as com.sqlite.sqlitedatabase and left the main activity name as MainActivity.java

Keep other defualt selections, click Next until you reach Finish

Before Enter in to Project Let us know about SQLiteOpenHelper and SQLiteDatabase  Class.
SQLiteOpenHelper class provides the functionality to use the SQLite database.

SQLiteOpenHelper Class

The android.database.sqlite.SQLiteOpenHelper class is used for database creation and version management. For performing any database operation, you have to provide the implementation of onCreate() and onUpgrade() methods of SQLiteOpenHelper class.

SQLiteDatabase Class

It contains methods to be performed on sqlite database such as create, update, delete, select etc.

Example of Android SQLiteDatabase:

Write  SQLite Database handler Class

Create the DatabaseHelper  class which extends SQLiteOpenHelper.

public class DatabaseHelper extends SQLiteOpenHelper{

private static final String DATABASE_NAME="sqlitedemo";
private static final int DATABASE_VERSION=1;
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
                // Create a Tables in onCreate Methos as showen bellow
db.execSQL(" CREATE TABLE IF NOT EXISTS UserDetails("
+ "id INTEGER PRIMARY KEY AUTOINCREMENT,"
+ "fname  TEXT,"
+ "lname  TEXT,"
+ "location   TEXT,"
+ "phone   TEXT,"
+ "address   TEXT"
+ ");");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

}

Write activity_main.xml Xml file:

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

   >

<RelativeLayout

    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="com.sqlite.sqlitedatabase.MainActivity" >

    <TextView

        android:id="@+id/textView1"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignParentTop="true"

        android:layout_centerHorizontal="true"

        android:text="@string/sqlite"

        android:textAppearance="?android:attr/textAppearanceLarge" />

    <EditText

        android:id="@+id/fname"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_below="@+id/textView1"

        android:layout_centerHorizontal="true"

        android:layout_marginTop="17dp"

        android:hint="@string/fname"

        android:ems="10" >

        <requestFocus />

    </EditText>

    <EditText

        android:id="@+id/lname"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_below="@+id/fname"

        android:layout_centerHorizontal="true"

        android:hint="@string/lname"

        android:ems="10" />

    <EditText

        android:id="@+id/location"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_below="@+id/lname"

        android:layout_centerHorizontal="true"

        android:hint="@string/location"

        android:ems="10" />

    <EditText

        android:id="@+id/phone"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_below="@+id/location"

        android:layout_centerHorizontal="true"

        android:hint="@string/phone"

        android:ems="10"

        android:inputType="phone" />

    <EditText

        android:id="@+id/address"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_centerHorizontal="true"

        android:layout_below="@+id/phone"

        android:hint="@string/address"

        android:ems="10"

        android:inputType="textPostalAddress" />

    <Button

        android:id="@+id/clear"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignLeft="@+id/address"

        android:layout_below="@+id/address"

        android:text="@string/clear" />

    <Button

        android:id="@+id/save"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignBaseline="@+id/clear"

        android:layout_alignBottom="@+id/clear"

        android:layout_alignRight="@+id/address"

        android:text="@string/save" />

    <Button

        android:id="@+id/view "

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_below="@+id/clear"

        android:layout_centerHorizontal="true"

        android:text="@string/view" />

</RelativeLayout>

</ScrollView> 

now write the MainActivity  for activity_main.xml:

public class MainActivity extends ActionBarActivity {

SQLiteDatabase db;
DatabaseHelper databasehelper;
Context context; 
EditText fname,lname,location,phone,address;
Button save,clear,view;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        context=MainActivity.this;
databasehelper= new DatabaseHelper(context);
db=databasehelper.getWritableDatabase(); 

        fname=(EditText) findViewById(R.id.fname);
        lname=(EditText) findViewById(R.id.lname);
        location=(EditText) findViewById(R.id.location);
        phone=(EditText) findViewById(R.id.phone);
        address=(EditText) findViewById(R.id.address);

        save=(Button) findViewById(R.id.save);
        clear=(Button) findViewById(R.id.clear);
        view=(Button) findViewById(R.id.view);
        clear.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
fname.setText("");
lname.setText("");
location.setText("");
phone.setText("");
address.setText("");
}
});
        save.setOnClickListener(new OnClickListener() 
        {
@Override
public void onClick(View v)
{
if(fname.getText().toString()==null || fname.getText().toString().length()<=0)
{
fname.setError("First Name Should not be blank");
fname.requestFocus();
}else if(phone.getText().toString()==null || phone.getText().toString().length()<=0)
{
phone.requestFocus();
phone.setError("Phone Should not be blank");

}else if(address.getText().toString()==null || address.getText().toString().length()<=0)
{
address.requestFocus();
address.setError("Address Should not be blank");

}else
{
Toast.makeText(getApplicationContext(),"save",Toast.LENGTH_LONG).show();
                     // inserting Values into Database using 
  ContentValues value=new ContentValues();
value.put("fname", fname.getText().toString());
value.put("lname", lname.getText().toString());
value.put("location", location.getText().toString());
value.put("phone", phone.getText().toString());
value.put("address", address.getText().toString());
db.insert("UserDetails", null, value);
fname.setText("");
lname.setText("");
location.setText("");
phone.setText("");
address.setText("");
}
}
});
        
        view.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Intent intent =new Intent(MainActivity.this, ViewRecord.class);
finish();
startActivity(intent);
}
});
   }
}

Write a Class for Retrieving Data 
XML File: viewrecord.xml
<?xml version="1.0" encoding="utf-8"?>

<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
   >
<RelativeLayout 
    android:layout_width="match_parent"
    android:layout_height="match_parent" >
    <EditText
        android:id="@+id/fname"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true"
        android:ems="10" 
        android:hint="@string/fname">
        <requestFocus />
    </EditText>

    <ListView
        android:id="@+id/list"
        android:layout_width="match_parent"
        android:layout_height="350dp"
        android:layout_alignParentLeft="true" 
        android:layout_below="@+id/fname">

    </ListView>

    <Button
        android:id="@+id/back"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/list"
        android:layout_centerHorizontal="true"
        android:text="@string/back" />

</RelativeLayout>

</ScrollView>

List Xml File : 

viewrecord_list.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal" 
        android:id="@+id/name">

        <TextView
            android:id="@+id/fname"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/fname" 
        android:layout_marginLeft="14dp" />

        <TextView
            android:id="@+id/lname"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/lname" 
        android:layout_marginLeft="14dp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal" 
        android:id="@+id/addressll"
        android:layout_below="@+id/name">

        <TextView
            android:id="@+id/address"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/address"
        android:layout_marginLeft="14dp"  />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:id="@+id/phonell"
        android:layout_below="@+id/addressll" >

        <TextView
            android:id="@+id/phone"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/phone"
        android:layout_marginLeft="14dp"  />

        <TextView
            android:id="@+id/location"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/location"
        android:layout_marginLeft="14dp"  />
    </LinearLayout>


</RelativeLayout>

Class file for ViewRecord ,java
public class ViewRecord extends Activity {
ListView listview;
ViewRecordAdapter viewRecordAdapter;
ArrayList<ViewRecordList> viewRecordList = new ArrayList<ViewRecordList>();

SQLiteDatabase db;
DatabaseHelper databasehelper;
Context context;

Button back;
EditText fname;

@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.viewrecords);
context=ViewRecord.this;
databasehelper= new DatabaseHelper(context);
db=databasehelper.getWritableDatabase();

fname=(EditText) findViewById(R.id.fname);
listview=(ListView) findViewById(R.id.list);

record();


back=(Button) findViewById(R.id.back);
fname.addTextChangedListener(new TextWatcher() {

@Override
public void onTextChanged(CharSequence s, int start, int before, int count) {
// TODO Auto-generated method stub
record();
}

@Override
public void afterTextChanged(Editable s) {
// TODO Auto-generated method stub

}

@Override
public void beforeTextChanged(CharSequence s, int start, int count,
int after) {
// TODO Auto-generated method stub

}


});
back.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View v) {
// TODO Auto-generated method stub
Intent intent = new Intent(getApplicationContext(), MainActivity.class);
finish();
startActivity(intent);
}
});

}

void record(){
Cursor curRecord=db.rawQuery("SELECT * from UserDetails WHERE fname LIKE ?", new String[]{"%"+fname.getText().toString()+"%"});
viewRecordList.clear();
if(!(curRecord==null) && curRecord.getCount()>0){
if(curRecord.moveToFirst()){
do{
viewRecordList.add(new ViewRecordList(curRecord.getString(curRecord.getColumnIndex("fname")), curRecord.getString(curRecord.getColumnIndex("lname")), curRecord.getString(curRecord.getColumnIndex("address")), curRecord.getString(curRecord.getColumnIndex("phone")), curRecord.getString(curRecord.getColumnIndex("location"))));
}while(curRecord.moveToNext());
}
}
viewRecordAdapter=new ViewRecordAdapter(context,R.layout.viewrecord_list,viewRecordList);
listview.setItemsCanFocus(false);
listview.setAdapter(viewRecordAdapter);

}

}

 class ViewRecordList {
private String fname;
private String lname;
private String address;
private String phone;
private String location;


public ViewRecordList(String fname,String lname,String address,String phone,String location) {
super();
this.fname = fname;
this.lname = lname;
this.address = address;
this.phone = phone;
this.location = location;
}


public CharSequence getfname() {
return fname;
}
public void setfname(String fname) {
this.fname = fname;
}

public String getlname() {
return lname;
}
public void setlname(String lname) {
this.lname = lname;
}

public String getaddress() {
return address;
}
public void setaddress(String address) {
this.address = address;
}


public String getphone() {
return phone;
}
public void setphone(String phone) {
this.phone = phone;
}

public String getlocation() {
return location;
}

public void setlocation(String location) {
this.location = location;
}

}

now write Adapter Class for ListView 

public class ViewRecordAdapter extends ArrayAdapter<ViewRecordList>{

Context context;

int viewrecordListid;

ArrayList<ViewRecordList> viewRecordListArray = new ArrayList<ViewRecordList>();

ViewRecordList viewRecordList;

ViewRecord ViewRecordClass;

public ViewRecordAdapter(Context context, int viewrecordListid,

ArrayList<ViewRecordList> viewRecordListArray) {

// TODO Auto-generated constructor stub

super(context, viewrecordListid, viewRecordListArray);

this.context=context;

this.viewrecordListid=viewrecordListid;

this.viewRecordListArray=viewRecordListArray;

}

public View getView(final int position, View convertView, ViewGroup parent) {

View item = convertView;

VierRecordWrapper viewRecordWrapper=null;

if(item==null){

LayoutInflater  inflater=((Activity) context).getLayoutInflater();

item = inflater.inflate(viewrecordListid, parent, false);

viewRecordWrapper= new VierRecordWrapper();

viewRecordWrapper.fname=(TextView) item.findViewById(R.id.fname);

viewRecordWrapper.lname=(TextView) item.findViewById(R.id.lname);

viewRecordWrapper.address=(TextView) item.findViewById(R.id.address);

viewRecordWrapper.phone=(TextView) item.findViewById(R.id.phone);

viewRecordWrapper.location=(TextView) item.findViewById(R.id.location);

item.setTag(viewRecordWrapper);

}else{

viewRecordWrapper=(VierRecordWrapper) item.getTag();

}

viewRecordList=viewRecordListArray.get(position);

viewRecordWrapper.fname.setText(viewRecordList.getfname());

viewRecordWrapper.lname.setText(viewRecordList.getlname());

viewRecordWrapper.address.setText(viewRecordList.getaddress());

viewRecordWrapper.phone.setText(viewRecordList.getphone());

viewRecordWrapper.location.setText(viewRecordList.getlocation());

return item;

}

class VierRecordWrapper{

TextView fname,lname,address,phone,location;

}

}