CRUD tutorial on android using SQLite as database

# CRUD tutorial on android using SQLite as database

## Introduction
In this tutorial, we will learn how to create a CRUD application in android. The CRUD stands for Create, Read, Update and Delete. It is an essential feature in every application. We will create an application where student will fill the its details. The details will be stored in the database and displayed in the list on the screen. The user can also update and delete the details if required.

## Creating new Android Project
- Open Android Studio and create a new project. Name it as User Registration using SQLite Database and company domain application.example.com (We have used our own company domain i.e saifytech.com. Similarly, you can use yours).
- Select SDK, we have used API 23: Android 6.0 (Marshmallow) and then select the Blank Activity. You can also select other activity as per the requirement. Click next and it will open a new window to select the activity. Select Blank Activity and proceed.
- Now, your project has been created.

- We will create a class named as Student.java
- Add the following code to it.

```
public class Student {
//attributes of student object
private int id;
private String name;
private int age;
//empty constructor which initializes the student object
}
//constructor with attributes that initializes the student object
public Student(int id, String name, int age) {
public Student() {
}

//getter and setter methods
public int getId() {
return id;
}

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

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}
//method to display details of the student
public String toString() {
return "Student details: \n\tId: " + id + "\n\tName: " + name + "\n\tAge: " + age;
}

public int getAge() {
return age;
}

public void setAge(int age) {
this.age = age;
}
}
```
-Create XML for student_item for StudentAdapter for binding
<?xml version="1.0" encoding="utf-8"?>
<!-- this is item for recycle for student record which contain three text with each ids as textViewName ,textViewAge , textViewRollNo giving equal weight to each text with orientation horizontal-->
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"

android:orientation="horizontal"
android:gravity="center_vertical"
android:padding="10dp"

>

<TextView
android:id="@+id/textViewName"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:textSize="20dp"
android:textColor="#000000"
android:gravity="center_vertical"
android:text="Name"
android:padding="10dp"
/>

<TextView
android:id="@+id/textViewAge"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:textSize="20dp"
android:textColor="#000000"
android:gravity="center_vertical"
android:text="Age"
android:padding="10dp"
/>

<TextView
android:id="@+id/textViewRollNo"
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:textSize="20dp"
android:textColor="#000000"

android:gravity="center_vertical"
android:text="RollNo"
android:padding="10dp"
/>

 

 

</LinearLayout>

```
- Now Create Adapter for ListView Which we are going to use as StudentAdapter.java

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

import java.util.List;

//This is listview adapte for students with custom layout for each item in the list view student_list_item.xml is used as a layout for each item in the list view textViewName,textViewAge are the textview id in the student_list_item.xml

public class StudentAdapter extends android.widget.BaseAdapter {
private Context context;
private List<Student> studentList;

public StudentAdapter(Context context, List<Student> studentList) {
this.context = context;
this.studentList = studentList;
}

// return number of items in the list
@Override
public int getCount() {
return studentList.size();

// return item of the list at specific position
}

@Override
public Object getItem(int position) {
// return id of the item at specific position
return studentList.get(position);
}

@Override
// return the view of the item at specific position

public long getItemId(int position) {
return position;
}

```

## Creating database
- To create database first create a class named as DatabaseHelper.java.
- Add the following code to it.

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

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

public class DatabaseHelper extends SQLiteOpenHelper {

private static final String TAG = "DatabaseHelper"; //logcat

private static final String DATABASE_NAME = "student.db"; //name of database

private static final int DATABASE_VERSION = 1; //database version

private static final String TABLE_NAME = "student_table"; //name of table

private static final String COLUMN_ID = "id"; //column name

private static final String COLUMN_NAME = "name"; //column name

private static final String COLUMN_AGE = "age"; //column name

 

private static final String CREATE_TABLE_QUERY = "CREATE TABLE " + TABLE_NAME + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_NAME + " TEXT, " + COLUMN_AGE + " INTEGER)"; //query to create table

private static final String DROP_TABLE_QUERY = "DROP TABLE IF EXISTS " + TABLE_NAME; //query to drop table

public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION); //constructor to create database
}

@Override
public void onCreate(SQLiteDatabase db) { //function to create table
db.execSQL(CREATE_TABLE_QUERY);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //function to upgrade table
db.execSQL(DROP_TABLE_QUERY);
onCreate(db);
}

public void addStudent(Student student) {
SQLiteDatabase db = this.getWritableDatabase(); //get writable database
ContentValues values = new ContentValues(); //object for content values
values.put(COLUMN_NAME, student.getName()); //put values in object
values.put(COLUMN_AGE, student.getAge());
db.insert(TABLE_NAME, null, values); //insert values into table
db.close();
}

public List<Student> getAllStudents() {
List<Student> students = new ArrayList<>();
String query = "SELECT * FROM " + TABLE_NAME; //fetch all values from table
SQLiteDatabase db = this.getWritableDatabase(); //get writable database
Cursor cursor = db.rawQuery(query, null);
if (cursor.moveToFirst()) { //loop to get values from table
do {
Student student = new Student(); //create object of student
student.setId(cursor.getInt(0));
student.setName(cursor.getString(1));
student.setAge(cursor.getInt(2));
students.add(student); //add object to list
} while (cursor.moveToNext());
}
cursor.close(); //close cursor
db.close();
return students; //return list
}

public Student getStudent(int id) {
Student student = new Student();
String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_ID + " = " + id; //get values according to id
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
if (cursor.moveToFirst()) { //loop to get values from table
student.setId(cursor.getInt(0));
student.setName(cursor.getString(1));
student.setAge(cursor.getInt(2));
}
cursor.close(); //close cursor
db.close();
return student; //return student object
}

public int updateStudent(Student student) {
SQLiteDatabase db = this.getWritableDatabase(); //get writable database
ContentValues values = new ContentValues(); //object for content values
values.put(COLUMN_NAME, student.getName());
values.put(COLUMN_AGE, student.getAge());
int i = db.update(TABLE_NAME, values, COLUMN_ID + " = ?", new String[]{String.valueOf(student.getId())});
db.close();
return i; //return int
}

public void deleteStudent(int id) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_NAME, COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
db.close();
}

public void deleteAllStudents() {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_NAME, null, null);
db.close();
}

public int getStudentsCount() {
String countQuery = "SELECT * FROM " + TABLE_NAME;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
int count = cursor.getCount();
cursor.close();
return count;
}

}

```
- Now, import the DatabaseHelper in our main activity.
-Create Empty Activity using android studio
- Add the following code in your main activity
```
Copy paste below code in xml for activity_main.xml

<?xml version="1.0" encoding="utf-8"?>

<!-- this contains student ListView with add , edit , delete button for students -->
<LinearLayout 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"
android:orientation="vertical"
android:padding="10dp">

<TextView
android:id="@+id/textViewTitle"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:gravity="center_vertical"
android:padding="10dp"
android:text="Student List"
android:textColor="#000000"
android:textSize="20dp" />
<LinearLayout
android:id="@+id/linearLayoutAddStudent"
android:layout_width="match_parent"
android:layout_height="50dp"
android:orientation="horizontal"
android:layout_gravity="center_vertical"
android:layout_margin="10dp"

>

<Button
android:id="@+id/buttonAddStudent"
android:layout_width="wrap_content"
android:layout_height="wrap_content"

android:gravity="center_vertical"

android:text="Add "
android:textColor="#000000"
android:textSize="20dp" />

<Button
android:id="@+id/buttonEditStudent"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="center_vertical"
android:text="Edit"

android:textColor="#000000"
android:textSize="20dp" />

<Button
android:id="@+id/buttonDeleteStudent"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:gravity="center_vertical"

android:text="Delete"
android:textColor="#000000"
android:textSize="20dp" />

 

</LinearLayout>
<ListView
android:id="@+id/listViewStudent"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_gravity="center_vertical"
android:layout_margin="10dp"
android:layout_marginLeft="10dp"
android:layout_marginTop="10dp"
android:layout_marginRight="10dp"
android:layout_marginBottom="10dp"
android:layout_weight="1">

</ListView>

```
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;

import java.util.List;

public class MainActivity extends AppCompatActivity {

// Register xml elements with java code
private DatabaseHelper databaseHelper;
private Student student;
private List<Student> studentList;
private StudentAdapter studentAdapter;
private android.widget.ListView listView;
private android.widget.Button buttonAdd;
private android.widget.Button buttonView;
private android.widget.Button buttonUpdate;
private android.widget.Button buttonDelete;
private android.widget.Button buttonDeleteAll;
private android.widget.EditText editTextName;
private android.widget.EditText editTextAge;

 

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

// Initialize xml elements
registerAllElements();

// Initialize database helper
databaseHelper = new DatabaseHelper(this);

// Initialize student
student = new Student();

// Initialize student adapter
studentList = databaseHelper.getAllStudents();
studentAdapter = new StudentAdapter(this, studentList);

// Set student adapter to list view
listView.setAdapter(studentAdapter);

// Add student button click listener
clickEventOnAllButtons();

}

// register all elements id in the mainactivity

public void registerAllElements() {

listView = (android.widget.ListView) findViewById(R.id.listViewStudent);
buttonAdd = (android.widget.Button) findViewById(R.id.buttonAddStudent);
buttonUpdate = (android.widget.Button) findViewById(R.id.buttonEditStudent);
buttonDelete = (android.widget.Button) findViewById(R.id.buttonDeleteStudent);

}

public void clickEventOnAllButtons() {

buttonAdd.setOnClickListener(new android.view.View.OnClickListener() {
@Override
public void onClick(android.view.View v) {
Student student = new Student();
student.setName("Taher");
student.setAge(20);
student.setId(1);
// Refresh to load new data
databaseHelper.addStudent(student);
studentList.add(student);
studentAdapter.notifyDataSetChanged();

}
});

buttonUpdate.setOnClickListener(new android.view.View.OnClickListener() {
@Override
public void onClick(android.view.View v) {
Student student = new Student();
student.setName("Taher Updated");
student.setAge(20);
student.setId(1);
databaseHelper.updateStudent(student);
// Refresh to load new data

studentList.set(0, student);

studentAdapter.notifyDataSetChanged();

 

}
// Refresh to load new data
});

buttonDelete.setOnClickListener(new android.view.View.OnClickListener() {
@Override
public void onClick(android.view.View v) {

databaseHelper.deleteStudent(0);
studentList.remove(0);
studentAdapter.notifyDataSetChanged();

}
});

}

}

```

Above code is available at github on this link