Android SQLite Database Example Tutorial

Total Views : 1,202
Zoom In Zoom Out Read Later Print

In this article we are going to learn basics of SQLite database with a real time example of food adding and modifying..

There are several ways to store persistent data in android. Android SQLite is the mostly preferred way to store data for android applications. It is very lightweight database that comes with Android OS. SQLite is a typical relational database, containing tables (consists of rows and columns), indexes etc. We can create our own tables to hold the data. This structure is referred to as a schema. Consider SQLite when your app needs to store simple data. Alternatively you can consider Library for better APIs and easier integration.

Step#1 SQLiteOpenHelper

We need to create a class that extends from SQLiteOpenHelper. This class perform CRUD operations (Create, Read, Update and Delete) on the database.

Method in SQLiteOpenHelper

1. Constructor : Basically it takes 4 parameters. Activity Context, name of database, optional cursor factory, and integer value for database version.
2. onCreate(SQLiteDatabase db) : It's is called when there is no database, only first time when needed to create a database.
3. onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) : It's is called when schema version we need does not match with our provide version.

For Database connection we are creating two class, one Databasehelper and second for managing Databasehelper class.

1. Opening a Database Connection

open()

public DBManager open() throws SQLException {
dbHelper = new DatabaseHelper(context);
database = dbHelper.getWritableDatabase();
return this;
}

2. Closing a Database Connection

close()

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

3. Inserting new Data in Database

insert()

public void insert(String name, int price) {
ContentValues contentValue = new ContentValues();
contentValue.put(DatabaseHelper.COLUMN_NAME, name);
contentValue.put(DatabaseHelper.COLUMN_PRICE, price);
database.insert(DatabaseHelper.TABLE_NAME, null, contentValue);
}

3.Update Data in Database

update()

public int update(long _id, String name, int price) {
ContentValues contentValues = new ContentValues();
contentValues.put(DatabaseHelper.COLUMN_NAME, name);
contentValues.put(DatabaseHelper.COLUMN_PRICE, price);
int i = database.update(DatabaseHelper.TABLE_NAME, contentValues, DatabaseHelper._ID + " = " + _id, null);
return i;
}

3. Fatching Data from Database

Cursor represents result of the query. Before getting any data from cursor we should call cursor.moveToFirst(), so we can know query returned data or not. Because it move to first when cursor hold some data. For now we are only returning cursor in following method which will be checked at the time of use in activity.

get()

public Cursor get() {
String[] columns = new String[] { DatabaseHelper._ID, DatabaseHelper.COLUMN_NAME, DatabaseHelper.COLUMN_PRICE };
Cursor cursor = database.query(DatabaseHelper.TABLE_NAME, columns, null, null, null, null, null);

return cursor;
}

3.Deleting Data from Database

delete()

public void delete(long _id) {
database.delete(DatabaseHelper.TABLE_NAME, DatabaseHelper._ID + "=" + _id, null);
}


Complete Code for DBManager and DatabaseHandler class.

DatabaseHelper.java

package com.prodevsblog.sqliteexample;

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

public class DatabaseHelper extends SQLiteOpenHelper {
// Table Name
public static final String TABLE_NAME = "FOODS";

// Table columns
public static final String _ID = "_id";
public static final String COLUMN_NAME = "name";
public static final String COLUMN_PRICE = "price";
// Database Information
static final String DB_NAME = "FOODS.DB";
// database version
static final int DB_VERSION = 1;

// Creating table query
private static final String CREATE_TABLE = "create table " + TABLE_NAME + "(" + _ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_NAME + " TEXT NOT NULL, " + COLUMN_PRICE + " INTEGER);";

public DatabaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}
}

DBManager.java

package com.prodevsblog.sqliteexample;

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

public class DBManager {
private DatabaseHelper dbHelper;
private Context context;
private SQLiteDatabase database;

public DBManager(Context c) {
context = c;
}

public DBManager open() throws SQLException {
dbHelper = new DatabaseHelper(context);
database = dbHelper.getWritableDatabase();
return this;
}

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

public void insert(String name, int price) {
ContentValues contentValue = new ContentValues();
contentValue.put(DatabaseHelper.COLUMN_NAME, name);
contentValue.put(DatabaseHelper.COLUMN_PRICE, price);
database.insert(DatabaseHelper.TABLE_NAME, null, contentValue);
}

public Cursor get() {
String[] columns = new String[] { DatabaseHelper._ID, DatabaseHelper.COLUMN_NAME, DatabaseHelper.COLUMN_PRICE };
Cursor cursor = database.query(DatabaseHelper.TABLE_NAME, columns, null, null, null, null, null);

return cursor;
}

public int update(long _id, String name, int price) {
ContentValues contentValues = new ContentValues();
contentValues.put(DatabaseHelper.COLUMN_NAME, name);
contentValues.put(DatabaseHelper.COLUMN_PRICE, price);
int i = database.update(DatabaseHelper.TABLE_NAME, contentValues, DatabaseHelper._ID + " = " + _id, null);
return i;
}

public void delete(long _id) {
database.delete(DatabaseHelper.TABLE_NAME, DatabaseHelper._ID + "=" + _id, null);
}
}


Step#2 Creating UI

2.1 Creating Recycle View

TouchListener class for handling click on items.

TouchListener.java

package com.prodevsblog.sqliteexample;

import android.content.Context;
import android.support.v7.widget.RecyclerView;
import android.view.GestureDetector;
import android.view.MotionEvent;
import android.view.View;

public class TouchListener implements RecyclerView.OnItemTouchListener {

private ClickListener clicklistener;
private GestureDetector gestureDetector;

public TouchListener(Context context, final RecyclerView recycleView, final ClickListener clicklistener) {

this.clicklistener = clicklistener;
gestureDetector = new GestureDetector(context, new GestureDetector.SimpleOnGestureListener() {
@Override
public boolean onSingleTapUp(MotionEvent e) {
return true;
}

@Override
public void onLongPress(MotionEvent e) {
View child = recycleView.findChildViewUnder(e.getX(), e.getY());
if (child != null && clicklistener != null) {
clicklistener.onLongClick(child, recycleView.getChildAdapterPosition(child));
}
}
});
}

@Override
public boolean onInterceptTouchEvent(RecyclerView rv, MotionEvent e) {
View child = rv.findChildViewUnder(e.getX(), e.getY());
if (child != null && clicklistener != null && gestureDetector.onTouchEvent(e)) {
clicklistener.onClick(child, rv.getChildAdapterPosition(child));
}

return false;
}

@Override
public void onTouchEvent(RecyclerView rv, MotionEvent e) {

}

@Override
public void onRequestDisallowInterceptTouchEvent(boolean disallowIntercept) {

}

public interface ClickListener {
void onClick(View view, int position);
void onLongClick(View view, int position);
}
}

MyDivider class for inserting divider between item row.

MyDivider.java

package com.prodevsblog.sqliteexample;

import android.content.Context;
import android.content.res.Resources;
import android.content.res.TypedArray;
import android.graphics.Canvas;
import android.graphics.Rect;
import android.graphics.drawable.Drawable;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.util.TypedValue;
import android.view.View;

public class MyDivider extends RecyclerView.ItemDecoration {

private static final int[] ATTRS = new int[]{
android.R.attr.listDivider
};

public static final int HORIZONTAL_LIST = LinearLayoutManager.HORIZONTAL;
public static final int VERTICAL_LIST = LinearLayoutManager.VERTICAL;

private Drawable mDivider;
private int mOrientation;
private Context context;
private int margin;

public MyDivider(Context context, int orientation, int margin) {
this.context = context;
this.margin = margin;
final TypedArray a = context.obtainStyledAttributes(ATTRS);
mDivider = a.getDrawable(0);
a.recycle();
setOrientation(orientation);
}

public void setOrientation(int orientation) {
if (orientation != HORIZONTAL_LIST && orientation != VERTICAL_LIST) {
throw new IllegalArgumentException("invalid orientation");
}
mOrientation = orientation;
}

@Override
public void onDrawOver(Canvas c, RecyclerView parent, RecyclerView.State state) {
if (mOrientation == VERTICAL_LIST) {
drawVertical(c, parent);
} else {
drawHorizontal(c, parent);
}
}

public void drawVertical(Canvas c, RecyclerView parent) {
final int left = parent.getPaddingLeft();
final int right = parent.getWidth() - parent.getPaddingRight();

final int childCount = parent.getChildCount();
for (int i = 0; i < childCount; i++) {
final View child = parent.getChildAt(i);
final RecyclerView.LayoutParams params = (RecyclerView.LayoutParams) child .getLayoutParams();
final int top = child.getBottom() + params.bottomMargin;
final int bottom = top + mDivider.getIntrinsicHeight();
mDivider.setBounds(left + dpToPx(margin), top, right - dpToPx(margin), bottom);
mDivider.draw(c);
}
}

public void drawHorizontal(Canvas c, RecyclerView parent) {
final int top = parent.getPaddingTop();
final int bottom = parent.getHeight() - parent.getPaddingBottom();

final int childCount = parent.getChildCount();
for (int i = 0; i < childCount; i++) {
final View child = parent.getChildAt(i);
final RecyclerView.LayoutParams params = (RecyclerView.LayoutParams) child
.getLayoutParams();
final int left = child.getRight() + params.rightMargin;
final int right = left + mDivider.getIntrinsicHeight();
mDivider.setBounds(left, top + dpToPx(margin), right, bottom - dpToPx(margin));
mDivider.draw(c);
}
}

@Override
public void getItemOffsets(Rect outRect, View view, RecyclerView parent, RecyclerView.State state) {
if (mOrientation == VERTICAL_LIST) {
outRect.set(0, 0, 0, mDivider.getIntrinsicHeight());
} else {
outRect.set(0, 0, mDivider.getIntrinsicWidth(), 0);
}
}

private int dpToPx(int dp) {
Resources r = context.getResources();
return Math.round(TypedValue.applyDimension(TypedValue.COMPLEX_UNIT_DIP, dp, r.getDisplayMetrics()));
}
}

XML layout for item row.

food_row.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:clickable="true"
android:foreground="?attr/selectableItemBackground"
android:paddingBottom="10dp"
android:paddingLeft="@dimen/activity_margin"
android:paddingRight="@dimen/activity_margin"
android:paddingTop="10dp">


<TextView
android:id="@+id/foodName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:textColor="@color/colorAccent"
android:textSize="15sp"
android:text="Name"/>

<TextView
android:id="@+id/foodPrice"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_below="@+id/foodName"
android:textSize="13sp"
android:textColor="#000"
android:text=""
android:layout_marginTop="5dp"/>

</RelativeLayout>

RecycleView adapter for rendering our custom layout for item row.

FoodAdapter.java

package com.prodevsblog.sqliteexample;

import android.content.Context;
import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;

import java.util.List;

public class FoodAdapter extends RecyclerView.Adapter<FoodAdapter.MyViewHolder> {

private Context context;
private List<FoodItem> foodList;

public class MyViewHolder extends RecyclerView.ViewHolder {
public TextView textViewName;
public TextView textViewPrice;

public MyViewHolder(View view) {
super(view);
textViewName = view.findViewById(R.id.foodName);
textViewPrice = view.findViewById(R.id.foodPrice);
}
}

public FoodAdapter(Context context, List<FoodItem> foodList) {
this.context = context;
this.foodList = foodList;
}

@Override
public MyViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
View itemView = LayoutInflater.from(parent.getContext()).inflate(R.layout.food_row, parent, false);
return new MyViewHolder(itemView);
}

@Override
public void onBindViewHolder(MyViewHolder holder, int position) {
FoodItem food = foodList.get(position);
holder.textViewName.setText(food.getName());
holder.textViewPrice.setText("$"+food.getPrice());
}

@Override
public int getItemCount() {
return foodList.size();
}

}

Dialog for creating and update of food items.

dialog_food.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"
android:paddingLeft="@dimen/activity_margin"
android:paddingRight="@dimen/activity_margin"
android:paddingTop="@dimen/activity_margin">

<TextView android:id="@+id/dialog_title"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginBottom="10dp"
android:fontFamily="sans-serif-medium"
android:lineSpacingExtra="8sp"
android:text="Add Food"
android:textColor="@color/colorAccent"
android:textSize="15sp"
android:textStyle="normal" />

<EditText
android:id="@+id/edtName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@android:color/transparent"
android:gravity="top"
android:hint="Name"
android:inputType="textCapSentences|textMultiLine"
android:lines="2" />
<EditText
android:id="@+id/edtPrice"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@android:color/transparent"
android:gravity="top"
android:hint="$0"
android:inputType="textCapSentences|textMultiLine"
android:lines="2" />

</LinearLayout>

XML for main activity.

activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<android.support.design.widget.CoordinatorLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:id="@+id/coordinator_layout"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">

<android.support.design.widget.AppBarLayout
android:layout_width="match_parent"
android:layout_height="wrap_content">
<android.support.v7.widget.Toolbar
android:id="@+id/toolbar"
android:layout_width="match_parent"
android:layout_height="?attr/actionBarSize"
android:background="?attr/colorPrimary"/>

</android.support.design.widget.AppBarLayout>

<include layout="@layout/content_main" />

<android.support.design.widget.FloatingActionButton
android:id="@+id/fab"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="bottom|end"
android:layout_margin="@dimen/fab_margin"
app:srcCompat="@android:drawable/ic_input_add"
android:tint="@android:color/white"/>

</android.support.design.widget.CoordinatorLayout>

content_main.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
app:layout_behavior="@string/appbar_scrolling_view_behavior"
tools:context=".MainActivity"
tools:showIn="@layout/activity_main">

<android.support.v7.widget.RecyclerView
android:id="@+id/recycler_view"
android:layout_width="match_parent"
android:layout_height="match_parent" />

<TextView
android:id="@+id/no_food"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_centerHorizontal="true"
android:layout_marginTop="10dp"
android:fontFamily="sans-serif-light"
android:text="No Food"
android:layout_centerInParent="true"/>

</RelativeLayout>

And finally our Main Activity..

MainActivity.java

package com.prodevsblog.sqliteexample;

import android.content.DialogInterface;
import android.database.Cursor;
import android.support.design.widget.CoordinatorLayout;
import android.support.design.widget.FloatingActionButton;
import android.support.v7.app.AlertDialog;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.support.v7.widget.DefaultItemAnimator;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.support.v7.widget.Toolbar;
import android.text.TextUtils;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

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

public class MainActivity extends AppCompatActivity {
private DBManager dbManager;

private FoodAdapter mAdapter;
private List<FoodItem> foodList = new ArrayList<>();
private CoordinatorLayout coordinatorLayout;
private RecyclerView recyclerView;
private TextView noFoodView;

private DatabaseHelper db;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Toolbar toolbar = findViewById(R.id.toolbar);
setSupportActionBar(toolbar);

init();
}
void init() {
FloatingActionButton fab = findViewById(R.id.fab);
fab.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
foodDialog(false, null, -1);
}
});

coordinatorLayout = findViewById(R.id.coordinator_layout);
recyclerView = findViewById(R.id.recycler_view);
noFoodView = findViewById(R.id.no_food);

db = new DatabaseHelper(this);

dbManager = new DBManager(this);
dbManager.open();
Cursor cursor = dbManager.get();
if (cursor.moveToFirst()) {
do {
FoodItem food = new FoodItem();
food.setId(cursor.getInt(cursor.getColumnIndex(DatabaseHelper._ID)));
food.setName(cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_NAME)));
food.setPrice(cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_PRICE)));
foodList.add(food);
} while (cursor.moveToNext());
}
toggleView();
mAdapter = new FoodAdapter(this, foodList);
RecyclerView.LayoutManager mLayoutManager = new LinearLayoutManager(getApplicationContext());
recyclerView.setLayoutManager(mLayoutManager);
recyclerView.setItemAnimator(new DefaultItemAnimator());
recyclerView.addItemDecoration(new MyDivider(this, LinearLayoutManager.VERTICAL, 16));
recyclerView.setAdapter(mAdapter);

recyclerView.addOnItemTouchListener(new TouchListener(this,
recyclerView, new TouchListener.ClickListener() {
@Override
public void onClick(View view, final int position) {
//Handle short Clicks if you want
}

@Override
public void onLongClick(View view, int position) {
foodAction(position);
}
}));
}
private void foodAction(final int position) {
CharSequence colors[] = new CharSequence[]{"Edit", "Remove"};
AlertDialog.Builder builder = new AlertDialog.Builder(this);
builder.setTitle("Choose option");
builder.setItems(colors, new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
if (which == 0) {
foodDialog(true, foodList.get(position), position);
} else {
deleteFood(position);
}
}
});
builder.show();
}
private void foodDialog(final boolean shouldUpdate, final FoodItem food, final int position) {
LayoutInflater layoutInflaterAndroid = LayoutInflater.from(getApplicationContext());
View view = layoutInflaterAndroid.inflate(R.layout.dialog_food, null);

AlertDialog.Builder alertDialogBuilderUserInput = new AlertDialog.Builder(MainActivity.this);
alertDialogBuilderUserInput.setView(view);

final EditText inputName = view.findViewById(R.id.edtName);
final EditText inputPrice = view.findViewById(R.id.edtPrice);
TextView dialogTitle = view.findViewById(R.id.dialog_title);
dialogTitle.setText(shouldUpdate ? "Update" : "Add Food Item");

if (shouldUpdate && food != null) {
inputName.setText(food.getName());
inputPrice.setText(""+food.getPrice());
}
alertDialogBuilderUserInput
.setCancelable(false)
.setPositiveButton(shouldUpdate ? "update" : "save", new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialogBox, int id) {
}
})
.setNegativeButton("cancel",
new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialogBox, int id) {
dialogBox.cancel();
}
});

final AlertDialog alertDialog = alertDialogBuilderUserInput.create();
alertDialog.show();

alertDialog.getButton(AlertDialog.BUTTON_POSITIVE).setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String name = inputName.getText().toString().trim();
// Show toast message when input is empty
if (TextUtils.isEmpty(name)) {
Toast.makeText(MainActivity.this, "Enter Name!", Toast.LENGTH_SHORT).show();
return;
}
String pr = inputPrice.getText().toString().trim();
if (TextUtils.isEmpty(pr)) {
Toast.makeText(MainActivity.this, "Enter Price!", Toast.LENGTH_SHORT).show();
return;
}
int price = Integer.parseInt(pr);
alertDialog.dismiss();

// check if user updating food item
if (shouldUpdate && food != null) {
// update food item by it's id
food.setName(name);
food.setPrice(price);
updateFood(food,position);
} else {
// create new food
createFood(name,price);
}
}
});
}
void createFood(String name, int price) {
dbManager.insert(name, price);
FoodItem f = new FoodItem();
f.setName(name);
f.setPrice(price);
Log.i("FoodItem Object",f.toString());
foodList.add(f);
mAdapter.notifyDataSetChanged();
toggleView();
}
void updateFood(FoodItem food, int position){
dbManager.update(food.getId(),food.getName(),food.getPrice());
mAdapter.notifyDataSetChanged();
// refreshing the list
foodList.set(position, food);
mAdapter.notifyItemChanged(position);
}
private void deleteFood(int position) {
FoodItem food = foodList.get(position);
// deleting the food item from db
dbManager.delete(food.getId());
// removing the food item from the list
foodList.remove(position);
mAdapter.notifyItemRemoved(position);
toggleView();
}
private void toggleView() {
if (foodList.size() > 0) {
noFoodView.setVisibility(View.GONE);
} else {
noFoodView.setVisibility(View.VISIBLE);
}
}

@Override
protected void onDestroy() {
super.onDestroy();
dbManager.close();
}
}

If you have any problem with above code you can comment or simply download our working code for this project.

See More

Latest Photos