Thursday 7 September 2017

Android SQLite Database Tutorial | SQLite step by step Tutorial

 Today I am going to share with you a topic which is Backbone of any app called SQLiteDatabase.
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>

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>
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 callonCreate() 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;
}

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>

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 rawDelete 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);
}
}
}
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);
}
});
}
}
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 callinginsertUserDetail() 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));
}
}

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.

0 comments: