Android SQLite database @AndroidMonk
Published in
4 min readFeb 9, 2017
Android provides multiple ways to store data in app locally, one of these ways is SQLite database.
SQLite is a very light weight database which comes with Android OS.
Lets dive into the code.
For this tutorial we will be creating a table of students with there studentID, studentName and studentEmail. In this case studentID will act as primary key. Table name will be students and database name will be studentDetails.
We will create similar table in SQLite. We will perform CRUD operations of this table .
C-Create/Insert R-Read U-Update D-Delete
We will need MainActivity.java to add our data to database and also to retrieve our data for this tutorial purpose.
We will need DBHandler.java to control our SQLite database.
We will need of StudentModel.java to access each individual row easily.
The codes are well commented for your ease.
StudentModel.java
/**
* Created by rajat@wiselteach.com
*/
public class StudentModel {
//We will use this file for easy access to each row in the table.
int studentID;
String studentName;
String studentEmail;
//Constructor to set data in StudentModel directly.
public StudentModel(int studentID, String studentName, String studentEmail) {
this.studentID = studentID;
this.studentName = studentName;
this.studentEmail = studentEmail;
}
public int getStudentID() {
return studentID;
}
public void setStudentID(int studentID) {
this.studentID = studentID;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public String getStudentEmail() {
return studentEmail;
}
public void setStudentEmail(String studentEmail) {
this.studentEmail = studentEmail;
}
}DBHandler.java
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;
public class DBHandler extends SQLiteOpenHelper {
//Database version.
//Note: Increase the database version every-time you make changes to your table structure.
private static final int DATABASE_VERSION = 1;
//Database Name
private static final String DATABASE_NAME = "studentDetails";
//You will declare all your table names here.
private static final String TABLE_STUDENTS = "students";
// Students Table Columns names
private static final String KEY_STUDENT_ID = "studentID";
private static final String KEY_STUDENT_NAME = "studentName";
private static final String KEY_STUDENT_EMAIL = "studentEmail";
//Here context passed will be of application and not activity.
public DBHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
//This method will be called every-time the file is called.
@Override
public void onCreate(SQLiteDatabase db) {
//Query to create table
String CREATE_STUDENTS_TABLE = "CREATE TABLE IF NOT EXISTS "
+ TABLE_STUDENTS + "("
+ KEY_STUDENT_ID + " INTEGER (10) PRIMARY KEY, "
+ KEY_STUDENT_NAME + " TEXT, "
+ KEY_STUDENT_EMAIL + " TEXT" + ")";
//Create table query executed in sqlite
db.execSQL(CREATE_STUDENTS_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//This method will be called only if there is change in DATABASE_VERSION.
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_STUDENTS);
// Create tables again
onCreate(db);
}
/**
* All CRUD(Create, Read, Update, Delete) Operations
*/
// Add New Student
public void addStudent(StudentModel student) {
SQLiteDatabase db = this.getWritableDatabase();
//Content values use KEY-VALUE pair concept
ContentValues values = new ContentValues();
values.put(KEY_STUDENT_ID, student.getStudentID());
values.put(KEY_STUDENT_NAME, student.getStudentName());
values.put(KEY_STUDENT_EMAIL, student.getStudentEmail());
db.insert(TABLE_STUDENTS, null, values);
db.close();
}
// Getting single student details through ID
public StudentModel getStudent(int studentID) {
SQLiteDatabase db = this.getReadableDatabase();
//You can browse to the query method to know more about the arguments.
Cursor cursor = db.query(TABLE_STUDENTS,
new String[] { KEY_STUDENT_ID, KEY_STUDENT_NAME, KEY_STUDENT_EMAIL },
KEY_STUDENT_ID + "=?",
new String[] { String.valueOf(studentID) },
null,
null,
null,
null);
if (cursor != null)
cursor.moveToFirst();
StudentModel student = new StudentModel(
Integer.parseInt(cursor.getString(0)),
cursor.getString(1),
cursor.getString(2));
//Return Student
return student;
}
// Getting All Students
public List<StudentModel> getAllStudents() {
List<StudentModel> studentList = new ArrayList<StudentModel>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_STUDENTS;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// Looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
StudentModel student = new StudentModel(
cursor.getInt(0),
cursor.getString(1),
cursor.getString(2));
studentList.add(student);
} while (cursor.moveToNext());
}
// return student list
return studentList;
}
// Updating single student
public int updateStudent(StudentModel student) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_STUDENT_NAME, student.getStudentName());
values.put(KEY_STUDENT_EMAIL, student.getStudentEmail());
// updating student row
return db.update(TABLE_STUDENTS,
values,
KEY_STUDENT_ID + " = ?",
new String[] { String.valueOf(student.getStudentID())});
}
// Deleting single student
public void deleteStudent(StudentModel student) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_STUDENTS, KEY_STUDENT_ID + " = ?",
new String[] { String.valueOf(student.getStudentID()) });
db.close();
}
// Getting students count
public int getStudentsCount() {
String countQuery = "SELECT * FROM " + TABLE_STUDENTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();
// return count
return cursor.getCount();
}
}MainActivity.java
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import java.util.List;
public class MainActivity extends AppCompatActivity {
DBHandler db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//Creating object to use DBHandler
db = new DBHandler(this);
addDataToDatabase();
readDataFromDatabase();
}
private void readDataFromDatabase() {
// Reading all students
Log.d("MainActivity", "Reading all students..");
List<StudentModel> students = db.getAllStudents();
for (StudentModel i : students) {
String log = "Id: " + i.getStudentID() + " ,Name: " + i.getStudentName() + " ,Email: " + i.getStudentEmail();
// Writing students to log
Log.d("MainActivity", log);
}
}
private void addDataToDatabase() {
// Inserting Students
Log.d("MainActivity", "Inserting ..");
db.addStudent(new StudentModel(234567, "Rajat", "rajat@gmail.com"));
db.addStudent(new StudentModel(955689, "ABC", "abc@gmail.com"));
db.addStudent(new StudentModel(345129, "XYZ", "xyz@gmail.com"));
}
}Result in Logcat
