In this blog I am going to explain SQLite database in android. It is an Open Source Database, Also Its is embedded to all the android devices so there is no need to add any library to the application. You are need to import SQLiteDatabase and SQLiteOpenHelper for that.
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
Also I an going to explain How to create a Database, Tables, Inserting values, Updating values, Deleting values from the Database operations in Android.
More Information about SQLite :
http://www.sqlite.org
More Information about SQLite Data-types:
http://www.sqlite.org/datatype3.html
Creating Database
First you are needed to create a class for database. Here I am going to create a class "
DbHelper".
public class DbHelper {
}
Now I need to extend
SQLiteOpenHelper on this class
. While extending
this we need to add constructor and some unimplemented methods. Also I am going to declare the "
DATABASE_VERSION" and
"DATABASE_NAME" on this class.
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DbHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 3;
public static final String DATABASE_NAME = "MyExample.sqlite";
private Context context;
public DbHelper(Context myContext) {
super(myContext, DATABASE_NAME, null, DATABASE_VERSION);
this.context = myContext;
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
}
Here
"DATABASE_VERSION" is the version if the database. If there is a version change it will automatically call the unimplemented method
"onUpgrade". In this method we need to specify the modification we need to done on the database.
"DATABASE_NAME" is the name which the database is to be saved. While Initializing the database class id we add a path here "
DbPath" is the path of Db to be stored, It will save the database on that location, like,
public DbHelper(Context myContext) {
super(myContext, DbPath+DATABASE_NAME, null, DATABASE_VERSION);
this.context = myContext;
}
Table Creation:
Here I am going to create a table for storing user details,
public static final String USERS_TABLE_NAME = "Users"; //User Table Name
/
/User Table Fields.
private static final String UserID = "UserID";
private static final String Email = "Email";
private static final String Name = "Name";
private static final String Role = "Role";
//Table Creation Query
private static final String USERS_TABLE_CREATE = "CREATE TABLE " + USERS_TABLE_NAME + " (" + UserID + " INTEGER PRIMARY KEY, " + Email + " TEXT , "+ Name + " TEXT , "+ Role + " INTEGER );";
Insertion:
Here is a function to insert the details, Here I am passing an object of User and after inserting it will return the Id of inserted object.
public long SaveUsers(Users users){
SQLiteDatabase ceilDb = this.getWritableDatabase();
long id = 0;
Cursor c = null;
try {
ContentValues values = new ContentValues();
values.put(UserID, users.getUserID());
values.put(Email, users.getEmail());
values.put(Name, users.getName());
values.put(Role, users.getRole());
id = ceilDb.insert(USERS_TABLE_NAME, null, values);
System.out.println("Db :> User Saved ID : "+id);
} catch (Exception e) {
System.out.println("Db :> Failed to Save User, Error is: "+ e.getMessage().toString());
e.printStackTrace();
}
c.close();
ceilDb.close();
return id;
}
Deletion:
Here is a function to insert the details, Here I am passing the id of User first it will check if the user is exist if the user exist then it will delete the row.
public long DeleteUsers(long Id){
SQLiteDatabase ceilDb = getReadableDatabase();
Cursor c = null;
String[] cols = { UserID, Email, Name, Role};
try{
c = ceilDb.query(USERS_TABLE_NAME, cols, UserID + "=" + Id, null, null, null, null);
int k=c.getCount();
if (k!=0) {
if (c.moveToFirst()) {
do {
ceilDb.delete(USERS_TABLE_NAME, UserID + "=" + Id , null);
} while (c.moveToNext());
}
System.out.println("Db :> User Deleted ID : "+Id);
}
} catch (Exception e) {
System.out.println("Db :> Failed to Save User, Error is: "+ e.getMessage().toString());
e.printStackTrace();
}
c.close();
ceilDb.close();
return id;
}
Updation:
Here is a function to Update the details, Here I am passing an object of User first it will check if the user is exist if the user exist then it will delete the row.
public long UpdateUsers(Users users){
SQLiteDatabase ceilDb = this.getWritableDatabase();
long id = 0;
Cursor c = null;
try {
ContentValues values = new ContentValues();
values.put(UserID, users.getUserID());
values.put(Email, users.getEmail());
values.put(Name, users.getName());
values.put(Role, users.getRole());
String[] cols = { UserID , Email};
c = ceilDb.query(USERS_TABLE_NAME, cols, UserID + "=" + users.getUserID(), null, null, null, null);
int k=c.getCount();
if (k!=0) {
if (c.moveToFirst()) {
do {
id = ceilDb.update(USERS_TABLE_NAME, values, UserID + "=" + users.getUserID() , null);
} while (c.moveToNext());
}
System.out.println("Db :> User Updated ID : "+users.getUserID());
}
} catch (Exception e) {
System.out.println("Db :> Failed to Save User, Error is: "+ e.getMessage().toString());
e.printStackTrace();
}
c.close();
ceilDb.close();
return id;
}
Retrive:
Here is a function to insert the details, Here I am passing the id of User first it will check if the user is exist if the user exist then it will take the details from db and return it.
public Users getUser(long Id){
SQLiteDatabase ceilDb = getReadableDatabase();
Users users = null;
Cursor c = null;
String[] cols = { UserID, Email, Name, Role};
try{
c = ceilDb.query(USERS_TABLE_NAME, cols, UserID + "=" + Id, null, null, null, null);
int k=c.getCount();
if (k!=0) {
if (c.moveToFirst()) {
do {
users = new Users();
users.setUserID(c.getInt(0));
users.setEmail(c.getString(1));
users.setName(c.getString(2));
users.setRole(c.getInt(3));
} while (c.moveToNext());
}
System.out.println("Db :>Get Users ");
}
} catch (Exception e) {
System.out.println("Db :> Failed to Retrive Users Error is: "+ e.getMessage().toString());
e.printStackTrace();
}
c.close();
ceilDb.close();
return users;
}
Implementation to DbHelper class.
public class DbHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 3;
public static final String DATABASE_NAME = "MyExample.sqlite";
private Context context;
public static final String USERS_TABLE_NAME = "Users"; //User Table Name
//User Table Fields.
private static final String UserID = "UserID";
private static final String Email = "Email";
private static final String Name = "Name";
private static final String Role = "Role";
//User Table Creation Query
private static final String USERS_TABLE_CREATE = "CREATE TABLE " + USERS_TABLE_NAME + " (" + UserID + " INTEGER PRIMARY KEY, " + Email + " TEXT , "+ Name + " TEXT , "+ Role + " INTEGER );";
public DbHelper(Context myContext) {
super(myContext, DATABASE_NAME, null, DATABASE_VERSION);
this.context = myContext;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(USERS_TABLE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
//Function to insert User details
public long SaveUsers(Users users){
SQLiteDatabase ceilDb = this.getWritableDatabase();
long id = 0;
Cursor c = null;
try {
ContentValues values = new ContentValues();
values.put(UserID, users.getUserID());
values.put(Email, users.getEmail());
values.put(Name, users.getName());
values.put(Role, users.getRole());
id = ceilDb.insert(USERS_TABLE_NAME, null, values);
System.out.println("Db :> User Saved ID : "+id);
} catch (Exception e) {
System.out.println("Db :> Failed to Save User, Error is: "+ e.getMessage().toString());
e.printStackTrace();
}
c.close();
ceilDb.close();
return id;
}
//Function to get user details
public Users getUser(long Id){
SQLiteDatabase ceilDb = getReadableDatabase();
Users users = null;
Cursor c = null;
String[] cols = { UserID, Email, Name, Role};
try{
c = ceilDb.query(USERS_TABLE_NAME, cols, UserID + "=" + Id, null, null, null, null);
int k=c.getCount();
if (k!=0) {
if (c.moveToFirst()) {
do {
users = new Users();
users.setUserID(c.getInt(0));
users.setEmail(c.getString(1));
users.setName(c.getString(2));
users.setRole(c.getInt(3));
} while (c.moveToNext());
}
System.out.println("Db :>Get Users ");
}
} catch (Exception e) {
System.out.println("Db :> Failed to Retrive Users Error is: "+ e.getMessage().toString());
e.printStackTrace();
}
c.close();
ceilDb.close();
return users;
}
//Function to Update Table
public long UpdateUsers(Users users){
SQLiteDatabase ceilDb = this.getWritableDatabase();
long id = 0;
Cursor c = null;
try {
ContentValues values = new ContentValues();
values.put(UserID, users.getUserID());
values.put(Email, users.getEmail());
values.put(Name, users.getName());
values.put(Role, users.getRole());
String[] cols = { UserID , Email};
c = ceilDb.query(USERS_TABLE_NAME, cols, UserID + "=" + users.getUserID(), null, null, null, null);
int k=c.getCount();
if (k!=0) {
if (c.moveToFirst()) {
do {
id = ceilDb.update(USERS_TABLE_NAME, values, UserID + "=" + users.getUserID() , null);
} while (c.moveToNext());
}
System.out.println("Db :> User Updated ID : "+users.getUserID());
}
} catch (Exception e) {
System.out.println("Db :> Failed to Save User, Error is: "+ e.getMessage().toString());
e.printStackTrace();
}
c.close();
ceilDb.close();
return id;
}
//Function to Delete User
public long DeleteUsers(long Id){
SQLiteDatabase ceilDb = getReadableDatabase();
Cursor c = null;
String[] cols = { UserID, Email, Name, Role};
try{
c = ceilDb.query(USERS_TABLE_NAME, cols, UserID + "=" + Id, null, null, null, null);
int k=c.getCount();
if (k!=0) {
if (c.moveToFirst()) {
do {
ceilDb.delete(USERS_TABLE_NAME, UserID + "=" + Id , null);
} while (c.moveToNext());
}
System.out.println("Db :> User Deleted ID : "+Id);
}
} catch (Exception e) {
System.out.println("Db :> Failed to Save User, Error is: "+ e.getMessage().toString());
e.printStackTrace();
}
c.close();
ceilDb.close();
return Id;
}
}
Calling DB Functions
When you initialize the object of
DbHelper class it will check if the Db is exist if it doesn't exist it will create the Db and table by executing the methods
DbHelper and
onCreate. If there is a version change it will automatically call the unimplemented method
"onUpgrade". In this method we need to specify the modification we need to done on the database.
Database Initialization
private DbHelper dbHelper = new DbHelper(context);
Calling Database Operations
Insertion
private DbHelper dbHelper = new DbHelper(context);
dbHelper.SaveUsers(users) // here user is an object to insert.
Deletion
private DbHelper dbHelper = new DbHelper(context);
dbHelper.DeleteUsers(Id) // here Id is an id of the user to be deleted
Updation
private DbHelper dbHelper = new DbHelper(context);
dbHelper.UpdateUsers(users) // here user is an object to be updated.
Retrive
private DbHelper dbHelper = new DbHelper(context);
users = dbHelper.getUser(Id)// here Id is an id of the user
