Pr.Pg Next Pg

SQLite Database Tutorials

 

What is SQLite?

  • 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.

More about SQLite

  • Things to consider when dealing with SQLite:

  • Data type integrity is not maintained in SQLite, you can put a value of a certain data type in a column of another datatype (put string in an integer and vice versa).

  • Referential integrity is not maintained in SQLite, there is no FOREIGN KEY constraints or JOIN statements.

  • SQLite Full Unicode support is optional and not installed by default.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 AsyncTask class.

  • 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 theEnvironment.getDataDirectory() method returns.

  •  APP_NAME is your application name. FILENAME is the name you specify in your application code for the database.

SQLiteOpenHelper Class

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

  • After extending your class from SQLiteOpenHelper you need to override two methods onCreate() 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.

 

SQLite Database

  • Exposes methods to manage a SQLite database.

  • SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.

  • Database names must be unique within an application, not across all applications.

  • 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. 

  • ContentValues can be used for inserts and updates of database entries.

  • This interface provides random read-write access to the result set returned by a database query.

  • 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.

Example

  • We will now illustrate how to build an application which uses a database and we will use a table layout to store the values into the database.

  • The database of the application will have 4 columns, i.e, the Serial number, Name, Roll Number and the Grade.

  • The serial number will be autoincremented every time a record is inserted into the database

  • Note that when the database is cleared, even then the serial number wont be reset to 1.

  • For that to happen you will have to set the type to Integer and then reset it to 1.

  • Below is code for the mail layout file named sql_example.xml.

 

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

 

<EditText

android:id="@+id/etName"

android:layout_width="match_parent"

android:layout_height="wrap_content"

android:ems="10"

android:hint="Enter the Name">

 

<requestFocus />

</EditText>

 

<EditText

android:id="@+id/etRollNo"

android:layout_width="match_parent"

android:layout_height="wrap_content"

android:ems="10"

android:hint="Enter the Roll Number"/>

 

<EditText

android:id="@+id/etGrade"

android:layout_width="match_parent"

android:layout_height="wrap_content"

android:ems="10"

android:hint="Enter the Grade"/>

 

<LinearLayout

android:layout_width="match_parent"

android:layout_height="wrap_content"

android:orientation="horizontal"

android:weightSum="10">

 

<Button

android:id="@+id/bUpdateDb"

android:layout_gravity="center"

android:gravity="center"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="Add to Database"

android:layout_weight="5"

/>

 

<Button

android:id="@+id/bViewDb"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="View Database"

android:layout_gravity="center"

android:gravity="center"

android:layout_weight="5"

/>

</LinearLayout>

<LinearLayout

android:layout_width="match_parent"

android:layout_height="wrap_content"

android:orientation="horizontal"

android:weightSum="10">

 

<Button

android:id="@+id/bDeleteDb"

android:layout_gravity="center"

android:gravity="center"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="Clear"

android:layout_weight="5"

/>

 

<Button

android:id="@+id/bModifyDb"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="Modify"

android:layout_gravity="center"

android:gravity="center"

android:layout_weight="5"

/>

</LinearLayout>

 

<EditText

android:id="@+id/etSNO"

android:layout_width="match_parent"

android:layout_height="wrap_content"

android:inputType="number"

android:hint="Enter the serial number"

android:visibility="invisible"/>

<Button

android:id="@+id/bConfirmDb"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="Confirm"

android:layout_gravity="center"

android:gravity="center"

android:visibility="invisible"

/>

 

 

</LinearLayout>

 

 

  • This is how the layout will look like.

 

 

  • We will now show the code of the class SQLite_Student which has an inner class named DbHelper which is a subclass of SQLiteHelper.

  • The inner class will create the database and the outer class will be used to access the database with the help of various methods which we have created.

  • Below is the code of the file.

 

SQLite_Student.java

 

package com.example.helloandroid;

 

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

import android.widget.EditText;

import android.widget.TableLayout;

import android.widget.TableRow;

import android.widget.TextView;

 

public class SQLite_Student {

public static final String KEY_SNO = "serial_no";

public static final String KEY_GRADE = "grade";

public static final String KEY_NAME = "name";

public static final String KEY_ROLLNO = "roll_no";

private static final String DATABASE_NAME = "Students";

staticfinal String DATABASE_TABLE = "Students_Details";

private static finalintDATABASE_VERSION = 2;

boolean b = true;

private DbHelper ourHelper;

privatefinal Context ourContext;

private SQLiteDatabase ourDB;

 

private static 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 " + DATABASE_TABLE + " ("

+ KEY_SNO + " INTEGER PRIMARY KEY AUTOINCREMENT, "

+ KEY_NAME + " TEXT NOT NULL, "

+ KEY_ROLLNO + " TEXT NOT NULL, "

+ KEY_GRADE + " TEXT NOT NULL);");

 

}

 

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

// TODO Auto-generated method stub

if (oldVersion >= newVersion)

return;

 

db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);

onCreate(db);

}

 

}

 

public SQLite_Student(Context c) {

ourContext = c;

}

 

public SQLite_Student open() throws SQLException {

ourHelper = new DbHelper(ourContext);

ourDB = ourHelper.getWritableDatabase();

returnthis;

}

 

public void close() {

ourHelper.close();

}

//insert into the database

publiclong addEntry(String name, String rollno, String grade) {

ContentValues cv = new ContentValues();

cv.put(KEY_GRADE, grade);

cv.put(KEY_ROLLNO, rollno);

cv.put(KEY_NAME, name);

return ourDB.insert(DATABASE_TABLE, null, cv);

}

//Get the reference to Database

public SQLiteDatabase getDatabase(){

return ourDB;

}

//Clear all the rows of the database

public void clearDatabase() {

// TODO Auto-generated method stub

ourDB.delete(DATABASE_TABLE, null, null);

}

 

}

 

 

 

  • We then have a class named SQL_Example.java with this layout and accesses the database depending upon the operation chosen by the user and performs them using the object of SQL_Student class and hence using the various methods defined in it.

  • Below is the code for it.

 

SQL_Example.java

 

package com.example.helloandroid;

 

import android.app.Activity;

import android.app.Dialog;

import android.content.ContentValues;

import android.content.Intent;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.os.Bundle;

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 SQL_Example extends Activity implements OnClickListener{

Button view;

Button update;

Button clear;

Button modify;

Button confirm;

EditText name;

EditText rollno;

EditText grade;

EditText sno;

int count=0;

@Override

protected void onCreate(Bundle savedInstanceState) {

// TODO Auto-generated method stub

super.onCreate(savedInstanceState);

setContentView(R.layout.sql_example);

name = (EditText)findViewById(R.id.etName);

sno = (EditText)findViewById(R.id.etSNO);

rollno = (EditText)findViewById(R.id.etRollNo);

grade = (EditText)findViewById(R.id.etGrade);

update =(Button)findViewById(R.id.bUpdateDb);

confirm =(Button)findViewById(R.id.bConfirmDb);

view = (Button)findViewById(R.id.bViewDb);

clear = (Button)findViewById(R.id.bDeleteDb);

modify = (Button)findViewById(R.id.bModifyDb);

update.setOnClickListener(this);

modify.setOnClickListener(this);

view.setOnClickListener(this);

clear.setOnClickListener(this);

confirm.setOnClickListener(this);

count=0;

}

@Override

public void onClick(View arg0) {

// TODO Auto-generated method stub

switch(arg0.getId()){

case R.id.bUpdateDb:

boolean success=true;

try{

String entry_name = name.getText().toString();

String entry_rollno = rollno.getText().toString();

String entry_grade = grade.getText().toString();

SQLite_Student entry = new SQLite_Student(SQL_Example.this);

entry.open();

entry.addEntry(entry_name, entry_rollno, entry_grade);

entry.close();

}catch(Exception e){

e.printStackTrace();

success=false;

}finally{

if(success){

Dialog d = new Dialog(this);

d.setTitle("Success");

TextView t = new TextView(this);

t.setText("The entry was successfully added");

d.setContentView(t);

d.show();

}

else{

Toast t = Toast.makeText(SQL_Example.this, "Failed!Please try again", Toast.LENGTH_SHORT);

t.show();

}

}

break;

case R.id.bViewDb:

Intent i = new Intent("com.example.helloandroid.STUDENTS");

startActivity(i);

break;

case R.id.bDeleteDb:

SQLite_Student clear = new SQLite_Student(SQL_Example.this);

clear.open();

clear.clearDatabase();

clear.close();

break;

case R.id.bModifyDb:

confirm.setVisibility(View.VISIBLE);

sno.setVisibility(View.VISIBLE);

name.setFocusable(false);

rollno.setFocusable(false);

grade.setFocusable(false);

modify.setClickable(false);

break;

case R.id.bConfirmDb:

boolean success2 =true;

if(count==0){

SQLite_Student info = new SQLite_Student(SQL_Example.this);

String modify_sno = sno.getText().toString();

info.open();

String[] columns = new String[] { info.KEY_SNO, info.KEY_NAME,

info.KEY_ROLLNO, info.KEY_GRADE };

SQLiteDatabase modify = info.getDatabase();

//Get the data corresponding to the serial Number entered.

Cursor c = modify.query(info.DATABASE_TABLE, columns, info.KEY_SNO+"="+modify_sno, null, null,

null, null);

c.moveToFirst();

name.setText(c.getString(1));

rollno.setText(c.getString(2));

grade.setText(c.getString(3));

sno.setFocusable(false);

confirm.setText("Make Changes");

count++;

info.close();

}

else{

String modify_name = name.getText().toString();

String modify_rollno = rollno.getText().toString();

String modify_grade = grade.getText().toString();

String modify_sno = sno.getText().toString();

long l = Long.parseLong(modify_sno);

try{SQLite_Student info = new SQLite_Student(SQL_Example.this);

info.open();

SQLiteDatabase modify = info.getDatabase();

ContentValues cv = new ContentValues();

cv.put(info.KEY_GRADE, modify_grade);

cv.put(info.KEY_ROLLNO, modify_rollno);

cv.put(info.KEY_NAME, modify_name);

//Update the database record with the newly entered values

modify.update(info.DATABASE_TABLE,cv, info.KEY_SNO + "=" + l, null);

info.close();}catch(Exception e){

e.printStackTrace();

success2=false;

}finally{

if(success2){

name.setText("");

rollno.setText("");

grade.setText("");

sno.setVisibility(View.INVISIBLE);

confirm.setVisibility(View.INVISIBLE);

Toast t = Toast.makeText(this,"Changes Successful!", Toast.LENGTH_SHORT);

t.show();

}else{

Toast t = Toast.makeText(this,"Failed!Please try again", Toast.LENGTH_SHORT);

t.show();

}

}

}

break;

}

}

}

 

 

  • In our application we have added a Toast that will get displayed incase the operation on the database is failed.

  • We have also set the visibility for a few view like for example, an EditText will come to the screen only when the modify button is clicked.

  • After the confirm button is clicked then the Edit Text and the Confirm button will again get invisible.

  • We also can view the database in the form of table using a TableLayout.

  • For this we create an activity class named Students.java which will make a table dyanimically depending upon the number of records in the database.

  • Below is the code for the file.

 

Students.java

 

package com.example.helloandroid;

 

import android.app.Activity;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

 

import android.os.Bundle;

import android.view.Gravity;

import android.widget.EditText;

import android.widget.TableLayout;

import android.widget.TableRow;

import android.widget.TextView;

 

public class Students extends Activity {

@Override

protected void onCreate(Bundle savedInstanceState) {

// TODO Auto-generated method stub

super.onCreate(savedInstanceState);

TableLayout table = new TableLayout(Students.this);

table.setStretchAllColumns(true);

table.setShrinkAllColumns(true);

TableRow title = new TableRow(this);

title.setGravity(Gravity.CENTER_HORIZONTAL);

SQLite_Student info = new SQLite_Student(this);

TextView tvtitle = new TextView(this);

tvtitle.setGravity(Gravity.CENTER);

tvtitle.setText("Student Database");

TableRow.LayoutParams params = new TableRow.LayoutParams();

params.span = 4;

title.addView(tvtitle, params);

table.addView(title);

TableRow col = new TableRow(this);

TextView tvSrNo = new TextView(this);

tvSrNo.setText("Serial Number");

TextView tvName = new TextView(this);

tvName.setText("Name");

TextView tvRollNo = new TextView(this);

tvRollNo.setText("Roll Number");

TextView tvGrade = new TextView(this);

tvGrade.setText("Grade");

col.addView(tvSrNo);

col.addView(tvName);

col.addView(tvRollNo);

col.addView(tvGrade);

table.addView(col);

info.open();

SQLiteDatabase sql = info.getDatabase();

String[] columns = new String[] { info.KEY_SNO, info.KEY_NAME,

info.KEY_ROLLNO, info.KEY_GRADE };

Cursor c = sql.query(info.DATABASE_TABLE, columns, null, null, null,

null, null);

c.moveToFirst();

for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {

TableRow tr = new TableRow(this);

 

for (int i = 0; i < 4; i++) {

TextView tv = new TextView(this);

tv.setText(c.getString(i));

tr.addView(tv);

}

table.addView(tr);

}

info.close();

setContentView(table);

}

}

 

 

  • Below are the screenshots of the application.

  • Adding entry to the Database.

 

  • Viewing the Database.

 

  • Modifying a record in the database.

 

Pr.Pg border                                              Next Pg