SQLiteDatabase in Android(1)

About SQLite:
                     SQLite is an Open Source Database which is embedded into Android. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition it requires only little memory at runtime approx 250kb.


SQLite supports the data types TEXT, INTEGER, REAL.  All other types must be converted into one of these fields before saving them in the database.

SQLiteDatabase is available on every Android device . Using an SQLiteDatabase in Android does not require any database setup or administration.

What is SQLite?
SQLite is an Open Source Database which is embedded into Android. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition it requires only little memory at runtime (approx. 250 KByte).

SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) andREAL (similar to double in Java). All other types must be converted into one of these fields before saving them in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa.

SQLite in Android?
SQLite is available on every Android device. Using an SQLite database in Android does not require any database setup or administration.

You only have to define the SQL statements for creating and updating the database. Afterwards the database is automatically managed for you by the Android platform.

Access to an SQLite database involves accessing the filesystem. This can be slow. Therefore it is recommended to perform database operations asynchronously, for example inside the AsyncTaskclass.

If your application creates a database, this database is by default saved in the directoryDATA/data/APP_NAME/databases/FILENAME.

The parts of the above directory are constructed based on the following rules. DATA is the path which the Environment.getDataDirectory() method returns. APP_NAME is your application name.FILENAME is the name you specify in your application code for the database.

SQLiteOpenHelper:
To create and upgrade a database in your Android application you usually subclassSQLiteOpenHelper. In the constructor of your subclass you call the super() method ofSQLiteOpenHelper, specifying the database name and the current database version.

In this class you need to override the onCreate() and onUpgrade() methods.

onCreate() is called by the framework, if the database does not exists.

onUpgrade() is called, if the database version is increased in your application code. This method allows you to update the database schema.

Both methods receive an SQLiteDatabase object as parameter which represents the database.

SQLiteOpenHelper provides the      methods getReadableDatabase() andgetWriteableDatabase() to get access to an SQLiteDatabase object; either in read or write mode.

The database tables should use the identifier _id for the primary key of the table. Several Android functions rely on this standard.

It is best practice to create a separate class per table. This class defines static onCreate() andonUpgrade() methods. These methods are called in the corresponding methods ofSQLiteOpenHelper. This way your implementation of SQLiteOpenHelper will stay readable, even if you have several tables.

SQLiteDatabase?
SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.

More specifically SQLiteDatabase provides the insert(), update() and delete() methods.

In addition it provides the execSQL() method, which allows to execute an SQL statement directly.

The object ContentValues allows to define key/values. The "key" represents the table column identifier and the "value" represents the content for the table record in this column. ContentValuescan be used for inserts and updates of database entries.

Queries can be created via the rawQuery() and query() methods or via theSQLiteQueryBuilder class .

rawQuery() directly accepts an SQL select statement as input.

query() provides a structured interface for specifying the SQL query.

SQLiteQueryBuilder is a convenience class that helps to build SQL queries.

Cursor:
A query returns a Cursor object. A Cursor represents the result of a query and basically points to one row of the query result. This way Android can buffer the query results efficiently; as it does not have to load all data into memory.

To get the number of elements of the resulting query use the getCount() method.

To move between individual data rows, you can use the moveToFirst() and moveToNext() methods. The isAfterLast() method allows to check if the end of the query result has been reached.

Cursor provides typed get*() methods, e.g. getLong(columnIndex), getString(columnIndex) to access the column data for the current position of the result. The "columnIndex" is the number of the column you are accessing.

Cursor also provides the getColumnIndexOrThrow(String) method which allows to get the column index for a column name of the table.

A Cursor needs to be closed with the close() method call.

We need to write our own class to handle all database CRUD(Create, Read, Update and Delete) operations.

Example:

Step1: Create a new class called StudentDB.java

Step2: Now extend y StudentDB.java class from SQLiteOpenHelper.

Step3: After extending your class from SQLiteOpenHelper you need to override two methodsonCreate() and onUpgrage()

onCreate() – These is where we need to write create table statements. This is called when database is created.

onUpgrade() – This method is called when database is upgraded like modifying the table structure, adding constraints to database etc.,

Step4: Now we need to write methods for handling all database read and write operations. 

In the following example you can learn completing all operations like insert,view,delete all records,delete one record and updating record in one activity. Inorder to understand this example you need to have knowledge in Custom ListView, Menu's and Dialogs



ScreenShots:

Include following xml files under  layout folder:

activity_main.xml:
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/LinearLayout1"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity" >

    <ListView
        android:id="@+id/listView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >
    </ListView>

</LinearLayout>

details.xml:

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:orientation="vertical" >

    <TextView

        android:id="@+id/textView1"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

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

    <TextView

        android:id="@+id/textView2"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

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

    <TextView

        android:id="@+id/textView3"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

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

</LinearLayout>

insert.xml:

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

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Enter firstname"
        android:textAppearance="?android:attr/textAppearanceLarge" />

    <EditText
        android:id="@+id/fnameeditText"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10" >
    </EditText>

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Enter lastname"
        android:textAppearance="?android:attr/textAppearanceLarge" />

    <EditText
        android:id="@+id/lnameeditText"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10" />

    <TableRow
        android:id="@+id/tableRow1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >

        <Button
            android:id="@+id/submitbutton"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="Submit" />

        <Button
            android:id="@+id/cancelbutton"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="Cancel" />
    </TableRow>

</LinearLayout>

updatedialog.xml:


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

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Enter updated firstname"
        android:textAppearance="?android:attr/textAppearanceLarge" />

    <EditText
        android:id="@+id/updatefnameeditText"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10" >
    </EditText>

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Enter updated lastname"
        android:textAppearance="?android:attr/textAppearanceLarge" />

    <EditText
        android:id="@+id/updatelnameeditText"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10" />

    <TableRow
        android:id="@+id/tableRow1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >

        <Button
            android:id="@+id/update_updatebutton"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="Update" />

        <Button
            android:id="@+id/update_cancelbutton"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="Cancel" />
    </TableRow>

</LinearLayout>

include following xml files under menu folder:



activity_main.xml:

<menu xmlns:android="http://schemas.android.com/apk/res/android" >

    <item
        android:id="@+id/insertRecord"
        android:title="InsertRecord">
    </item>
    <item
        android:id="@+id/DeleteAllRecords"
        android:title="DeleteAllRecords">
    </item>

</menu>

contextxml.xml:

<?xml version="1.0" encoding="utf-8"?>
<menu xmlns:android="http://schemas.android.com/apk/res/android" >

    <item
        android:id="@+id/updateRecordOption"
        android:title="Update">
    </item>
    <item
        android:id="@+id/updateDeleteRecordOption"
        android:title="Delete">
    </item>

</menu>

include following java files under src folder:

DBAdapter.java:

package com.ram.sqlitedatabase;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBAdapter {

public static final String DATABASE_NAME = "ramsdb";
public static final String TABLE_NAME = "sampletable";
public static final int DATABASE_VERSION = 1;

public static final String COL_ROWID = "rowid";
public static final String COL_FIRSTNAME = "firstname";
public static final String COL_LASTNAME = "lastname";

String CREATE_TABLE = "create table sampletable (rowid integer primary key autoincrement,firstname text not null,lastname text not null)";

SQLiteDatabase db;

DBHelper dbHelper;

public DBAdapter(Context context) {
// TODO Auto-generated constructor stub
dbHelper = new DBHelper(context);
}

DBAdapter openDatabase(){
db = dbHelper.getWritableDatabase();
return this;
}


void closeDatabase(){
dbHelper.close();
}

void insertValues(String fname,String lname){
ContentValues con = new ContentValues();
con.put(COL_FIRSTNAME, fname);
con.put(COL_LASTNAME, lname);

db.insert(TABLE_NAME, null, con);
}

Cursor getAllValues(){

String[] columns = {COL_ROWID,COL_FIRSTNAME,COL_LASTNAME};

return db.query(TABLE_NAME, columns, null, null, null, null, null);
}

void deleteAllRecords(){
db.delete(TABLE_NAME, null, null);
}

void deleteOneRecord(String rowid){
db.delete(TABLE_NAME, rowid +"="+COL_ROWID, null);
}

void updateOneRecord(String rowid,String fname,String lname){

ContentValues con = new ContentValues();
con.put(COL_FIRSTNAME, fname);
con.put(COL_LASTNAME, lname);

db.update(TABLE_NAME, con, rowid +"="+COL_ROWID, null);
}



class DBHelper extends SQLiteOpenHelper{

public DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
// TODO Auto-generated constructor stub
}

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(CREATE_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub

}

}
}

MainActivity.java:


package com.ram.sqlitedatabase;

import android.app.Activity;
import android.app.AlertDialog;
import android.app.AlertDialog.Builder;
import android.app.Dialog;
import android.content.DialogInterface;
import android.database.Cursor;
import android.os.Bundle;
import android.view.ContextMenu;
import android.view.ContextMenu.ContextMenuInfo;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemLongClickListener;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends Activity implements OnItemLongClickListener {
DBAdapter db;
Cursor cursor;
int INSERT_DIALOG = 1;
ListView list;
int DELETEALLRECORDS_DIALOG = 2;
int UPDATE_DIALOG = 3;
int position;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = new DBAdapter(this);
db.openDatabase();

list = (ListView) findViewById(R.id.listView1);

updateListView();

registerForContextMenu(list);

list.setOnItemLongClickListener(this);

}

@Override
public void onCreateContextMenu(ContextMenu menu, View v,
ContextMenuInfo menuInfo) {
getMenuInflater().inflate(R.menu.contextxml, menu);
super.onCreateContextMenu(menu, v, menuInfo);
}

@Override
public boolean onContextItemSelected(MenuItem item) {
// TODO Auto-generated method stub
if (item.getItemId() == R.id.updateDeleteRecordOption) {

cursor.moveToPosition(position);
String rowid = cursor.getString(0);
db.deleteOneRecord(rowid);
updateListView();

} else if (item.getItemId() == R.id.updateRecordOption) {

showDialog(UPDATE_DIALOG);

}

return super.onContextItemSelected(item);
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
// TODO Auto-generated method stub
getMenuInflater().inflate(R.menu.activity_main, menu);
return super.onCreateOptionsMenu(menu);
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
// TODO Auto-generated method stub
if (item.getItemId() == R.id.insertRecord) {
showDialog(INSERT_DIALOG);
} else if (item.getItemId() == R.id.DeleteAllRecords) {
showDialog(DELETEALLRECORDS_DIALOG);
}
return super.onOptionsItemSelected(item);
}

class CustomAdapter extends BaseAdapter {

@Override
public int getCount() {
// TODO Auto-generated method stub
return cursor.getCount();
}

@Override
public Object getItem(int arg0) {
// TODO Auto-generated method stub
return null;
}

@Override
public long getItemId(int arg0) {
// TODO Auto-generated method stub
return 0;
}

@Override
public View getView(int position, View view, ViewGroup arg2) {
// TODO Auto-generated method stub
view = getLayoutInflater().inflate(R.layout.details, null);

TextView t1 = (TextView) view.findViewById(R.id.textView1);
TextView t2 = (TextView) view.findViewById(R.id.textView2);
TextView t3 = (TextView) view.findViewById(R.id.textView3);

cursor.moveToPosition(position);

String rowid = cursor.getString(0);
String fname = cursor.getString(1);
String lname = cursor.getString(2);

t1.setText(rowid);
t2.setText(fname);
t3.setText(lname);

return view;
}

}

@Override
protected Dialog onCreateDialog(int id) {
// TODO Auto-generated method stub
if (id == INSERT_DIALOG) {
final Dialog d = new Dialog(MainActivity.this);
d.setTitle("Enter Details");
d.setContentView(R.layout.insert);
d.show();

Button submitBtn = (Button) d.findViewById(R.id.submitbutton);

final EditText e1 = (EditText) d.findViewById(R.id.fnameeditText);
final EditText e2 = (EditText) d.findViewById(R.id.lnameeditText);

submitBtn.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
db.insertValues(e1.getText().toString(), e2.getText()
.toString());
Toast.makeText(getApplicationContext(),
"record inserted successfully", Toast.LENGTH_LONG)
.show();
updateListView();
d.cancel();
}
});
Button cancelBtn = (Button) d.findViewById(R.id.cancelbutton);
cancelBtn.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
d.cancel();
}
});

} else if (id == DELETEALLRECORDS_DIALOG) {
Builder builder = new Builder(MainActivity.this);
builder.setTitle("Delete All Records");
builder.setMessage("are you sure..");
builder.setPositiveButton("yes",
new DialogInterface.OnClickListener() {

@Override
public void onClick(DialogInterface dialog, int which) {
// TODO Auto-generated method stub
db.deleteAllRecords();
updateListView();
Toast.makeText(getApplicationContext(),
"All Records Deleted", Toast.LENGTH_LONG)
.show();

}
});
builder.setNegativeButton("No",
new DialogInterface.OnClickListener() {

@Override
public void onClick(DialogInterface dialog, int which) {
// TODO Auto-generated method stub

}
});
AlertDialog alert = builder.create();
alert.show();
} else if (id == UPDATE_DIALOG) {

final Dialog ud = new Dialog(MainActivity.this);
ud.setTitle("Enter updated detials");
ud.setContentView(R.layout.updatedialog);
ud.show();

Button updateBtn = (Button) ud
.findViewById(R.id.update_updatebutton);

final EditText ue1 = (EditText) ud
.findViewById(R.id.updatefnameeditText);
final EditText ue2 = (EditText) ud
.findViewById(R.id.updatelnameeditText);

cursor.moveToPosition(position);

String fname = cursor.getString(1);
String lname = cursor.getString(2);
ue1.setText(fname);
ue2.setText(lname);

updateBtn.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
String rowid = cursor.getString(0);

db.updateOneRecord(rowid, ue1.getText().toString(), ue2
.getText().toString());
updateListView();
Toast.makeText(getApplicationContext(),
"record updated successfully", Toast.LENGTH_LONG)
.show();

ud.cancel();
}
});
Button updatecancelBtn = (Button) ud
.findViewById(R.id.update_cancelbutton);
updatecancelBtn.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
ud.cancel();
}
});
}

return super.onCreateDialog(id);
}

void updateListView() {
cursor = db.getAllValues();
CustomAdapter cs = new CustomAdapter();
list.setAdapter(cs);

}

@Override
public boolean onItemLongClick(AdapterView<?> arg0, View arg1, int pos,
long arg3) {
this.position = pos;
// TODO Auto-generated method stub
return false;
}
}