Android SQLite Database Tutorial

SQLite is a opensource SQL database that stores data to a text file on a device. Android comes in with built in SQLite database implementation. SQLite is a very light weight database. In this tutorial I’ll be discussing how to write classes to handle all SQLite operations.

In this tutorial I am taking an example of storing products in SQLite database. I am using a table called Products to
store user products. This table contains three columns id (INT), name (TEXT), price(TEXT).

Writing Product Class

Before you go further you need to write your Product class with all getter and setter methods to maintain single product as an object.

Product.java

package com.w2class.androidsqlite; 

public class Product {

public String product_name;
public int id;
public String price;

public Product(int id, String product_name, String price) {
this.product_name = product_name;
this.id = id;
this.price = price;
}

public Product(String product_name, String price) {
this.product_name = product_name;
this.price = price;
}

public Product() {
}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getPrice() {
return price;
}

public void setPrice(String price) {
this.price = price;
}

public String getProduct_name() {
return product_name;
}

public void setProduct_name(String product_name) {
this.product_name = product_name;
}
}

Writing SQLite Database Handler Class

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

1. Create a new project by going to File ⇒ New Android Project.
2. Once the project is created, create a new class in your project src directory and name it as DatabaseHandler.java ( Right Click on src/package ⇒ New ⇒ Class)
3. Now extend your DatabaseHandler.java class from SQLiteOpenHelper.
DatabaseHandler.java

package com.w2class.androidsqlite;

import java.util.ArrayList;
import java.util.List;

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

public class DatabaseHandler extends SQLiteOpenHelper {

// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "productsManager";

// Products table name
private static final String TABLE_PRODUCTS = "products";

// Products Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PRICE = "price";

public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_PRODUCTS_TABLE = "CREATE TABLE " + TABLE_PROCUCTS + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
+ KEY_PRICE + " TEXT" + ")";
db.execSQL(CREATE_PRODUCTS_TABLE);
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROCUCTS);

// Create tables again
onCreate(db);
}

/**
* All CRUD(Create, Read, Update, Delete) Operations
*/

// Adding new product
void addProduct(Product product) {
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(KEY_NAME, product.getProduct_name()); // Product Name
values.put(KEY_PRICE, product.getPrice()); // Product Phone

// Inserting Row
db.insert(TABLE_PRODUCTS, null, values);
db.close(); // Closing database connection
}

// Getting single product
Product getProduct(int id) {
SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.query(TABLE_PRODUCTS, new String[] { KEY_ID,
KEY_NAME, KEY_PRICE }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();

Product product = new Product(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), cursor.getString(2));
// return product
return product;
}

// Getting All Products
public List<Product> getAllProducts() {
List<Product> productList = new ArrayList<Product>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_PRODUCTS;

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);

// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Product product = new Product();
product.setID(Integer.parseInt(cursor.getString(0)));
product.setProduct_name(cursor.getString(1));
product.setPrice(cursor.getString(2));
// Adding product to list
productList.add(product);
} while (cursor.moveToNext());
}

// return product list
return productList;
}

// Updating single product
public int updateProduct(Product product) {
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(KEY_NAME, product.setProduct_name());
values.put(KEY_PRICE, product.getPrice());

// updating row
return db.update(TABLE_PRODUCTS, values, KEY_ID + " = ?",
new String[] { String.valueOf(product.getID()) });
}

// Deleting single product
public void deleteProduct(Product product) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_PRODUCTS, KEY_ID + " = ?",
new String[] { String.valueOf(product.getID()) });
db.close();
}


// Getting products Count
public int getProductsCount() {
int count = 0;

String countQuery = "SELECT * FROM " + TABLE_PRODUCTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
if(cursor != null && !cursor.isClosed()){
count = cursor.getCount();
cursor.close();
}

// return count
return count;
}

}

Usage:

AndroidSQLiteTutorialActivity

package com.w2class.androidsqlite;

import java.util.List;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;

public class AndroidSQLiteTutorialActivity extends Activity {
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);

DatabaseHandler db = new DatabaseHandler(this);

/**
* CRUD Operations
* */
// Inserting Products
Log.d("Insert: ", "Inserting .."); 
db.addProduct(new Product("Shoes", "2000")); 
db.addProduct(new Product("Shirt", "1500"));
db.addProduct(new Product("Jeans", "1800"));
db.addProduct(new Product("T-shirt", "800"));

// Reading all products
Log.d("Reading: ", "Reading all products.."); 
List<Product> products = db.getAllProducts(); 

for (Product cn : products) {
String log = "Id: "+cn.getID()+" ,Name: " + cn.getProduct_name() + " ,Price: " + cn.getPrice();
// Writing Products to log
Log.d("Name: ", log);
}
}
}