In software applications, it is mostly required to save information for some internal use or off course to provide user to great features depending on the data. And when we talk about android so SQLite is that default feature which is used as a database and also used as a local database for any application. This tutorial shows a very simple example which is to just store important data like shops address or contacts using SQLite Database in the android studio.
Android provides many ways to store data, SQLite Database is one of them that is already include in android OS. We have to just simply use it according to our need.
Here, we will take a simple example to store shops and their addresses. Here I’m taking only one simple table ‘Shops’ with following columns id (INT), name (TEXT), shop_address(TEXT).
[thrive_lead_lock id=’63725′]
Table Structure:
Now, first, create a new Android project. And create a class ‘Shop’, to refer a shop as an object in our application which just has the same fields as defined in Shops table.
Here is Shop code with getter and setter
package com.mobilesiri.sqliteexample;
public class Shop {
private int id;
private String name;
private String address;
public Shop()
{
}
public Shop(int id,String name,String address)
{
this.id=id;
this.name=name;
this.address=address;
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setAddress(String address) {
this.address = address;
}
public int getId() {
return id;
}
public String getAddress() {
return address;
}
public String getName() {
return name;
}
}
Read More: Develop your first android app using Android Studio – Tutorial
Read More: Android Recycler View and Card View Tutorial
Creating SQLite Database Handler
We need a class to handle database Create, Read, Update and Delete (CRUD) , simply create a class by right clicking on application package>New>Java Class, give name ‘DBHandler’ to class.
And extend with SQLiteOpenHelper class.
Now, we override two method onCreate() and onUpgrade().
onCreate: It is called first time when database is created. We usually create tables and the initialize here.
onUpgrade: Run when database is upgraded / changed, like drop tables, add tables etc.
package com.mobilesiri.sqliteexample;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHandler extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = “shopsInfo”;
// Contacts table name
private static final String TABLE_SHOPS = “shops”;
// Shops Table Columns names
private static final String KEY_ID = “id”;
private static final String KEY_NAME = “name”;
private static final String KEY_SH_ADDR = “shop_address”;
public DBHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_CONTACTS_TABLE = “CREATE TABLE ” + TABLE_SHOPS + “(”
+ KEY_ID + ” INTEGER PRIMARY KEY,” + KEY_NAME + ” TEXT,”
+ KEY_SH_ADDR + ” TEXT” + “)”;
db.execSQL(CREATE_CONTACTS_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL(“DROP TABLE IF EXISTS ” + TABLE_SHOPS);
// Creating tables again
onCreate(db);
}
}
Read, Insert, Update and Delete operations
Now, we write basic operation of database, insert, read, update and delete.
Insert Record:
First, start with insert, we add a method addShop() which take Shop as a parameter and map our shop values with table’s column using ContentValues object. getWritableDatabase is used for creating and/or opening database. So, after inserting data into the database table, we need to close the database connection.
// Adding new shop
public void addShop(Shop shop) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, shop.getName()); // Shop Name
values.put(KEY_SH_ADDR, shop.getAddress()); // Shop Phone Number
// Inserting Row
db.insert(TABLE_SHOPS, null, values);
db.close(); // Closing database connection
}
Read Record(s):
We write a method that will read only a recode (Shop) and take primary key (shop id) as parameter
// Getting one shop
public Shop getShop(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_SHOPS, new String[] { KEY_ID,
KEY_NAME, KEY_SH_ADDR }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();
Shop contact = new Shop(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), cursor.getString(2));
// return shop
return contact;
}
To get all record from the table we write a method getAllShops() that return list of all shops.
// Getting All Shops
public List<Shop> getAllShops() {
List<Shop> shopList = new ArrayList<Shop>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_SHOPS;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Shop shop = new Shop();
shop.setId(Integer.parseInt(cursor.getString(0)));
shop.setName(cursor.getString(1));
shop.setAddress(cursor.getString(2));
// Adding contact to list
shopList.add(shop);
} while (cursor.moveToNext());
}
// return contact list
return shopList;
}
To get total numbers of shop records in database write getShopsCount
// Getting shops Count
public int getShopsCount() {
String countQuery = "SELECT * FROM " + TABLE_SHOPS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();
// return count
return cursor.getCount();
}
Updating Record(s):
Write a updateShop() to update a shop/record . It requires updated shop object as a parameter.
// Updating a shop
public int updateShop(Shop shop) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, shop.getName());
values.put(KEY_SH_ADDR, shop.getAddress());
// updating row
return db.update(TABLE_SHOPS, values, KEY_ID + " = ?",
new String[]{String.valueOf(shop.getId())});
}
Delete Record:
Write method deleteShop to delete single record/Shop from the table. It requires shop I.d to be deleted.
// Deleting a shop
public void deleteShop(Shop shop) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_SHOPS, KEY_ID + " = ?",
new String[] { String.valueOf(shop.getId()) });
db.close();
}
Complete Code of DBHandler class:
package com.mobilesiri.sqliteexample;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;
public class DBHandler extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = “shopsInfo”;
// Contacts table name
private static final String TABLE_SHOPS = “shops”;
// Shops Table Columns names
private static final String KEY_ID = “id”;
private static final String KEY_NAME = “name”;
private static final String KEY_SH_ADDR = “shop_address”;
public DBHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_CONTACTS_TABLE = “CREATE TABLE ” + TABLE_SHOPS + “(”
+ KEY_ID + ” INTEGER PRIMARY KEY,” + KEY_NAME + ” TEXT,”
+ KEY_SH_ADDR + ” TEXT” + “)”;
db.execSQL(CREATE_CONTACTS_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL(“DROP TABLE IF EXISTS ” + TABLE_SHOPS);
// Creating tables again
onCreate(db);
}
// Adding new shop
public void addShop(Shop shop) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, shop.getName()); // Shop Name
values.put(KEY_SH_ADDR, shop.getAddress()); // Shop Phone Number
// Inserting Row
db.insert(TABLE_SHOPS, null, values);
db.close(); // Closing database connection
}
// Getting one shop
public Shop getShop(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_SHOPS, new String[]{KEY_ID,
KEY_NAME, KEY_SH_ADDR}, KEY_ID + “=?”,
new String[]{String.valueOf(id)}, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();
Shop contact = new Shop(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), cursor.getString(2));
// return shop
return contact;
}
// Getting All Shops
public List<Shop> getAllShops() {
List<Shop> shopList = new ArrayList<Shop>();
// Select All Query
String selectQuery = “SELECT * FROM ” + TABLE_SHOPS;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Shop shop = new Shop();
shop.setId(Integer.parseInt(cursor.getString(0)));
shop.setName(cursor.getString(1));
shop.setAddress(cursor.getString(2));
// Adding contact to list
shopList.add(shop);
} while (cursor.moveToNext());
}
// return contact list
return shopList;
}
// Getting shops Count
public int getShopsCount() {
String countQuery = “SELECT * FROM ” + TABLE_SHOPS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();
// return count
return cursor.getCount();
}
// Updating a shop
public int updateShop(Shop shop) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, shop.getName());
values.put(KEY_SH_ADDR, shop.getAddress());
// updating row
return db.update(TABLE_SHOPS, values, KEY_ID + ” = ?”,
new String[]{String.valueOf(shop.getId())});
}
// Deleting a shop
public void deleteShop(Shop shop) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_SHOPS, KEY_ID + ” = ?”,
new String[] { String.valueOf(shop.getId()) });
db.close();
}
}
Using DBHandler
As we have written our DBHandler class completely let use it in our application.
package com.mobilesiri.sqliteexample;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import android.util.Log;
import java.util.List;
public class MainActivity extends ActionBarActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
DBHandler db = new DBHandler(this);
// Inserting Shop/Rows
Log.d(“Insert: “, “Inserting ..”);
db.addShop(new Shop(“Dockers”, ” 475 Brannan St #330, San Francisco, CA 94107, United States”));
db.addShop(new Shop(“Dunkin Donuts”, “White Plains, NY 10601”));
db.addShop(new Shop(“Pizza Porlar”, “North West Avenue, Boston , USA”));
db.addShop(new Shop(“Town Bakers”, “Beverly Hills, CA 90210, USA”));
// Reading all shops
Log.d(“Reading: “, “Reading all shops..”);
List<Shop> shops = db.getAllShops();
for (Shop shop : shops) {
String log = “Id: ” + shop.getId() + ” ,Name: ” + shop.getName() + ” ,Address: ” + shop.getAddress();
// Writing shops to log
Log.d(“Shop: : “, log);
}
}
}
Now run the application and check output result on android Log. If you are unable to see Logs then go to
View>Tools Windows> Android and select the logcat tab.
Output:
[thrive_lead_lock id=’63725′]