Android SQLite Database Tutorial | SQLite step by step Tutorial

Hello warriors what going on guys hope you all are doing well , Today I am going to share with you a topic which is Backbone of any app called SQLite Database.

No doubt you can save small and simple data in SharedPrefrences but when you need to save complex and repeated data SQLite play a big role in that situation.So like every post i will make module of steps and we will follow all step to complete this tutorial. In this post we are going to
make a sample project in which we will save user data in First Activity and will show it in Recycler view of Second Activity,we will also add a delete button in Recyclerview of second Activity to delete that row you can see preview in above picture.

• Create a new project in your Android Studio Go to File-> New ->New project and select Empty Activity from template and leave every thing as default.
• Add Five EditText for username,college,place,userId and number, also add a button to activity_main.xml.
• Create one more activity  and add Recyclerview to its xml.
• Create a new Java class and name it DbHelper.
• Extend SQLiteOpenHelper in DbHelper class.
• Create a new Java class name it UserData and implement Serializable Interface to it and declare Five String varriable name,college,place,user_id and number in this class.
• Write method in DbHelper class to insert user data in to Database and to delete from Database.
• Write one more method to fetch all data from Database to show into Recyclerview 
• Create a new Java class for Recyclerview  Adapter and complete all the code for Recyclerview Adapter.
• Save user data in MainActivity on Button click and call second Activity.
• Show all the inserted data in Recyclerview of Second Activity.

Time to start coding with our First step.

1.Create a new project in your Android Studio Go to File-> New ->New project and select Empty Activity from template and leave every thing as default. like below picture.

2.Add Five EditText for username,college,place,userId and number, also add a button to activity_main.xml. here is my activity_main.xml.

	<?xml version="1.0" encoding="utf-8"?>
	<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
	xmlns:tools="http://schemas.android.com/tools"
	android:layout_width="match_parent"
	android:layout_height="match_parent"
	android:paddingBottom="@dimen/activity_vertical_margin"
	android:paddingLeft="@dimen/activity_horizontal_margin"
	android:paddingRight="@dimen/activity_horizontal_margin"
	android:paddingTop="@dimen/activity_vertical_margin"
	tools:context="com.databasetest.MainActivity">
	<EditText
	android:layout_margin="10dp"
	android:id="@+id/et_name"
	android:hint="Enter your name"
	android:layout_width="match_parent"
	android:layout_height="45dp" />
	<EditText
	android:layout_margin="10dp"
	android:layout_below="@+id/et_name"
	android:id="@+id/et_college"
	android:hint="Enter your college name"
	android:layout_width="match_parent"
	android:layout_height="45dp" />
	<EditText
	android:layout_below="@+id/et_college"
	android:layout_margin="10dp"
	android:id="@+id/et_userid"
	android:hint="Enter your user id"
	android:layout_width="match_parent"
	android:layout_height="45dp" />
	<EditText
	android:layout_below="@+id/et_userid"
	android:layout_margin="10dp"
	android:id="@+id/et_place"
	android:hint="Enter your place"
	android:layout_width="match_parent"
	android:layout_height="45dp" />
	<EditText
	android:layout_below="@+id/et_place"
	android:layout_margin="10dp"
	android:id="@+id/et_number"
	android:hint="Enter your number"
	android:layout_width="match_parent"
	android:layout_height="45dp" />
	<Button
	android:id="@+id/btn_next"
	android:layout_marginTop="10dp"
	android:layout_below="@+id/et_number"
	android:layout_centerHorizontal="true"
	android:text="Next"
	android:layout_width="150dp"
	android:layout_height="45dp" />
	</RelativeLayout>

view raw activit_main.xml for SQLite Demo hosted with ❤ by GitHub

3.Create one more activity  and add Recyclerview to its xml. To add Recyclerview you also need to add Library for Recyclerview in my case I added Design Support Library to build.gradle of app module. you can use either Support Library for Recyclerview or Design Support Library Simply copy and Paste one library from below code.

compile 'com.android.support:recyclerview-v7:23.1.0'

  compile 'com.android.support:design:23.1.1'

Now Add Recyclerview to xml of Second Activity here is my xml of Second Activity.

	<?xml version="1.0" encoding="utf-8"?>
	<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
	xmlns:tools="http://schemas.android.com/tools"
	android:layout_width="match_parent"
	android:layout_height="match_parent"
	android:paddingBottom="@dimen/activity_vertical_margin"
	android:paddingLeft="@dimen/activity_horizontal_margin"
	android:paddingRight="@dimen/activity_horizontal_margin"
	android:paddingTop="@dimen/activity_vertical_margin"
	tools:context="com.databasetest.UserDetailList">
	<android.support.v7.widget.RecyclerView
	android:id="@+id/rv_contactlist"
	android:layout_width="match_parent"
	android:layout_height="wrap_content">
	</android.support.v7.widget.RecyclerView>
	</RelativeLayout>

view raw activity_userdetail for SQLiteDemo hosted with ❤ by GitHub

4.Create a new Java class and name it DbHelper. Now we are going to play with SQLite.

5.Extend SQLiteOpenHelper in DbHelper class. when you will extend this class it will ask you to implement its methods onCreate(SQLiteDatabase db) and onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)  it will also ask you to create a constructor matching super. after messing with all these your class will be like this.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

public class DbHelper extends SQLiteOpenHelper { public DbHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase db) { } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }

Now I will add some variables for Database name,Table name,Database version and all the column name like this.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

private static final String TAG = "DbHelper"; // Database Info private static final String DATABASE_NAME = "UserDatabase"; private static final int DATABASE_VERSION = 1; //Table Names private static final String TABLE_USERdETAIL = "userdetail"; //userdetail Table Columns private static final String _ID = "_id"; private static final String NAME = "name"; private static final String COLLEGE = "college"; private static final String PLACE = "place"; private static final String USER_ID = "userId"; private static final String NUMBER = "number";

Now i will add SQL query to create Table inside onCreate(),for that i am making a string of query to create Table and here is that String.

1 2 3 4 5 6 7 8 9

String CREATE_USERDETAIL_TABLE = "CREATE TABLE " + TABLE_USERdETAIL + "(" + _ID + " INTEGER PRIMARY KEY ," + USER_ID + " TEXT," + NAME + " TEXT," + COLLEGE + " TEXT," + PLACE + " TEXT," + NUMBER + " TEXT" + ")";

you can see i am making _id as primary key and it will auto increase. Now  use execSQL() method to execute query. here is my onCreate() method .

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

/* Called when the database is created for the FIRST time. If a database already exists on disk with the same DATABASE_NAME, this method will NOT be called. */ @Override public void onCreate(SQLiteDatabase db) { String CREATE_USERDETAIL_TABLE = "CREATE TABLE " + TABLE_USERdETAIL + "(" + _ID + " INTEGER PRIMARY KEY ," + USER_ID + " TEXT," + NAME + " TEXT," + COLLEGE + " TEXT," + PLACE + " TEXT," + NUMBER + " TEXT" + ")"; db.execSQL(CREATE_USERDETAIL_TABLE); }

Since onUpgrade()  method is use to update your Database But i will refer you to Drop complete table and call onCreate() method inside onUpgrade() and add updation code inside onCreate(),here is my onUpgrade() method.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

/* Called when the database needs to be upgraded. This method will only be called if a database already exists on disk with the same DATABASE_NAME, but the DATABASE_VERSION is different than the version of the database that exists on disk. */ @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { if (oldVersion != newVersion) { // Simplest implementation is to drop all old tables and recreate them db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERdETAIL); onCreate(db); } }

Now i am updating existing constructor and also adding a method which will return instance of DbHelper I will use this method in complete code where i need instance of DbHelper to insert,update or delete data. here is my constructor and getInstance() method.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

private static DbHelper mDbHelper; public static synchronized DbHelper getInstance(Context context) { // Use the application context, which will ensure that you // don't accidentally leak an Activity's context. if (mDbHelper == null) { mDbHelper = new DbHelper(context.getApplicationContext()); } return mDbHelper; } /** * Constructor should be private to prevent direct instantiation. * Make a call to the static method "getInstance()" instead. */ private DbHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); }

5.Create a new Java class name it UserData and implement Serializable Interface to it and declare Five String varriable name,college,place,user_id and number in this class.
here is my UserData class.

	import java.io.Serializable;
	/**
	* Created by androidwarriors on 2/18/2016.
	*/
	public class UserData implements Serializable {
	String name,college,place,user_id,number;
	}

view raw UserData pojo for SQLite demo hosted with ❤ by GitHub

6.Write method in DbHelper class to insert user data in to Database and to delete from Database.
In this step we will make three method one for Insert data,one for delete data and one for fetch all data. steps are simple.

• Call getWritableDatabase()/getReadableDatabase() according to need.
• Begin Transaction
• Execute query
• End Transaction

Here is all three method..

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90

/* Insert a user detail into database */ public void insertUserDetail(UserData userData) { SQLiteDatabase db = getWritableDatabase(); db.beginTransaction(); try { ContentValues values = new ContentValues(); values.put(NAME, userData.name); values.put(COLLEGE, userData.college); values.put(PLACE, userData.place); values.put(USER_ID, userData.user_id); values.put(NUMBER, userData.number); db.insertOrThrow(TABLE_USERdETAIL, null, values); db.setTransactionSuccessful(); } catch (SQLException e) { e.printStackTrace(); Log.d(TAG, "Error while trying to add post to database"); } finally { db.endTransaction(); } } /* fetch all data from UserTable */ public List<UserData> getAllUser() { List<UserData> usersdetail = new ArrayList<>(); String USER_DETAIL_SELECT_QUERY = "SELECT * FROM " + TABLE_USERdETAIL; SQLiteDatabase db = getReadableDatabase(); Cursor cursor = db.rawQuery(USER_DETAIL_SELECT_QUERY, null); try { if (cursor.moveToFirst()) { do { UserData userData = new UserData(); userData.name = cursor.getString(cursor.getColumnIndex(NAME)); userData.college = cursor.getString(cursor.getColumnIndex(COLLEGE)); userData.place = cursor.getString(cursor.getColumnIndex(PLACE)); userData.user_id = cursor.getString(cursor.getColumnIndex(USER_ID)); userData.number = cursor.getString(cursor.getColumnIndex(NUMBER)); usersdetail.add(userData); } while (cursor.moveToNext()); } } catch (Exception e) { Log.d(TAG, "Error while trying to get posts from database"); } finally { if (cursor != null && !cursor.isClosed()) { cursor.close(); } } return usersdetail; } /* Delete single row from UserTable */ void deleteRow(String name) { SQLiteDatabase db = getWritableDatabase(); try { db.beginTransaction(); db.execSQL("delete from " + TABLE_USERdETAIL + " where name ='" + name + "'"); db.setTransactionSuccessful(); } catch (SQLException e) { Log.d(TAG, "Error while trying to delete users detail"); } finally { db.endTransaction(); } }

7.Create a new Java class for Recyclerview  Adapter and complete all the code for Recyclerview Adapter.
In this step I am creating a new java class name as ListAdapter and extend Recyclerview.Adapter ,If you are not familiar with Recyclerview check our Recyclerview tutorial . So  first make a layout for single row of Recyclerview ,I am making a simple layout with one TextView and one ImageView. TextView to show user name and ImageView for delete icon. here is xml for single row

	<?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="60dp"
	android:layout_marginTop="16dp"
	android:background="@android:color/holo_orange_light"
	android:orientation="vertical">
	<TextView
	android:id="@+id/tv_name"
	android:layout_centerVertical="true"
	android:layout_margin="16dp"
	android:layout_width="wrap_content"
	android:layout_height="wrap_content" />
	<ImageView
	android:id="@+id/iv_delete"
	android:src="@drawable/delete"
	android:layout_alignParentRight="true"
	android:layout_centerVertical="true"
	android:layout_marginRight="16dp"
	android:layout_width="wrap_content"
	android:layout_height="wrap_content" />
	</RelativeLayout>

view raw List row for SQLite demo hosted with ❤ by GitHub

Now i am creating a Interface for callback to get position of item to delete. and then we will call delete  method of SQLite to delete that row from Activity. Here is My Interface.

	/**
	* Created by androidwarriors on 2/18/2016.
	*/
	public interface Listener {
	void nameToChnge(String name);
	}

view raw Delete item Listener hosted with ❤ by GitHub

Now here is complete code for ListAdapter class.

	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.ImageView;
	import android.widget.TextView;
	import java.util.ArrayList;
	import java.util.List;
	/**
	* Created by androidwarriors on 2/18/2016.
	*/
	public class ListAdapter extends RecyclerView.Adapter<ListAdapter.ListViewHolder> {
	Context context;
	List<UserData> dataList = new ArrayList<>();
	LayoutInflater inflater;
	Listener listener;
	public ListAdapter(Context context, List<UserData> dataList1) {
	this.context = context;
	this.dataList = dataList1;
	this.listener= (Listener) context;
	inflater = LayoutInflater.from(context);
	}
	@Override
	public ListViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
	View convertView = inflater.inflate(R.layout.row, parent, false);
	ListViewHolder viewHolder = new ListViewHolder(convertView);
	return viewHolder;
	}
	@Override
	public void onBindViewHolder(ListViewHolder holder, int position) {
	holder.iv_delete.setTag(position);
	holder.tv_name.setText(dataList.get(position).name);
	holder.iv_delete.setOnClickListener(new View.OnClickListener() {
	@Override
	public void onClick(View v) {
	listener.nameToChnge(dataList.get((Integer) v.getTag()).name);
	}
	});
	}
	@Override
	public int getItemCount() {
	return dataList.size();
	}
	class ListViewHolder extends RecyclerView.ViewHolder {
	TextView tv_name;
	ImageView iv_delete;
	public ListViewHolder(View itemView) {
	super(itemView);
	tv_name = (TextView) itemView.findViewById(R.id.tv_name);
	iv_delete= (ImageView) itemView.findViewById(R.id.iv_delete);
	}
	}
	}

view raw ListAdapter for SQLite demo hosted with ❤ by GitHub

8.Save user data in MainActivity on Button click and call second Activity. Now open your MainActivity and Register all your EditText and Button and save all data in SQLite by using Insert method of DbHelper on Button click. here is my MainActivity.java.

	import android.content.Intent;
	import android.support.v7.app.AppCompatActivity;
	import android.os.Bundle;
	import android.view.View;
	import android.widget.Button;
	import android.widget.EditText;
	public class MainActivity extends AppCompatActivity {
	EditText et_name, et_college, et_place, et_userid, et_number;
	Button btn_next;
	DbHelper dbHelper;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
	super.onCreate(savedInstanceState);
	setContentView(R.layout.activity_main);
	dbHelper = DbHelper.getInstance(getApplicationContext());
	et_name = (EditText) findViewById(R.id.et_name);
	et_college = (EditText) findViewById(R.id.et_college);
	et_place = (EditText) findViewById(R.id.et_place);
	et_userid = (EditText) findViewById(R.id.et_userid);
	et_number = (EditText) findViewById(R.id.et_number);
	btn_next = (Button) findViewById(R.id.btn_next);
	btn_next.setOnClickListener(new View.OnClickListener() {
	@Override
	public void onClick(View v) {
	UserData userData = new UserData();
	if (!et_name.getText().toString().isEmpty()) {
	userData.name = et_name.getText().toString();
	} else {
	userData.name = "";
	}
	if (!et_college.getText().toString().isEmpty()) {
	userData.college = et_college.getText().toString();
	} else {
	userData.college = "";
	}
	if (!et_userid.getText().toString().isEmpty()) {
	userData.user_id = et_userid.getText().toString();
	} else {
	userData.user_id = "";
	}
	if (!et_number.getText().toString().isEmpty()) {
	userData.number = et_number.getText().toString();
	} else {
	userData.number = "";
	}
	if (!et_place.getText().toString().isEmpty()) {
	userData.place = et_place.getText().toString();
	} else {
	userData.place = "";
	}
	dbHelper.insertUserDetail(userData);
	Intent intent=new Intent(MainActivity.this,UserDetailList.class);
	startActivity(intent);
	}
	});
	}
	}

view raw MainActivity for SQLite Demo hosted with ❤ by GitHub

Don't be confused because code is very simple. onClick of button I am checking Text of all EditText  for null and Empty in that case I am saving empty string. I am saving Text of Every EditText into UserData object and calling insertUserDetail() method to save data into SQLite.

9.Show all the inserted data in Recyclerview of Second Activity. Open your Second Activity and Register Recyclerview.  get all user data by calling getAllUser() method of DbHelper and pass List of type UserData type to ListAdapter. here is code of  Second Activity .

	import android.content.Intent;
	import android.support.v7.app.AppCompatActivity;
	import android.os.Bundle;
	import android.support.v7.widget.LinearLayoutManager;
	import android.support.v7.widget.RecyclerView;
	public class UserDetailList extends AppCompatActivity implements Listener {
	RecyclerView recyclerView;
	DbHelper dbHelper;
	ListAdapter adapter;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
	super.onCreate(savedInstanceState);
	setContentView(R.layout.activity_user_detail_list);
	dbHelper = DbHelper.getInstance(getApplicationContext());
	recyclerView = (RecyclerView) findViewById(R.id.rv_contactlist);
	adapter = new ListAdapter(this, dbHelper.getAllUser());
	recyclerView.setAdapter(adapter);
	recyclerView.setLayoutManager(new LinearLayoutManager(this));
	}
	@Override
	public void onBackPressed() {
	super.onBackPressed();
	Intent intent = new Intent(this, MainActivity.class);
	startActivity(intent);
	finish();
	}
	@Override
	public void nameToChnge(String name) {
	dbHelper.deleteRow(name);
	adapter = new ListAdapter(this, dbHelper.getAllUser());
	recyclerView.setAdapter(adapter);
	recyclerView.setLayoutManager(new LinearLayoutManager(this));
	}
	}

view raw UserList Activity for SQLite demo hosted with ❤ by GitHub

Hey finally we have completed this tutorial, I know it was a lengthy tutorial but trust me If you follow all the steps it will help you a lot. Still having any problem feel free to comment below.