Android Training – Steps to Work with SQLite Database for Data Management SQLite is an open source database that provides a facility for storing and management of the user data in Android device. This is present in every device having Android OS and hence needs to be handled CRUD (Create, Read, Update & Delete) functionalities for data management. In this article, we are going to learn about the easy ways to create a table in the database, add and read data from it, upgrade the existing table data and finally delete the data. Now let us take an example of storing the trainee details in the database. For this here we are using the Class called Trainee to store the details. This table contains three columns – first is to store the ID of the trainee, second stores the name of the student and the last third column is used to store name of the course the trainee has joined. Field Trainee_ID Trainee_Name Course_Name
Type Int Text Text
Key Primary Key
Step 1: Defining the Trainee Class Now initially we will define the Trainee class using the get and set methods. For Example: Trainee.java package com.androidhive.androidsqlite; public class Trainee { //We declare the variables to be used in the class. These are the private variables. int _trainee_id; String _trainee_name; String _course_name; // Empty constructor to prevent someone from accidentally instantiating the trainee class public Trainee() { } // Constructor public Trainee(int trainee_id, String trainee_name, String course_name){ this._ trainee_id = trainee_id; this._ trainee_name = trainee_name; this._ course_name = course_name; } // Get & Set the Trainee ID
public int getTraineeID() { return this._ trainee_id;//getting ID } public void setTraineeID(int trainee_id) { this._ trainee_id = trainee_id; // setting id } // Get & Set Trainee Name public String getTraineeName() { return this._ trainee_name; // getting name } public void setTraineeName(String trainee_name) { this._ trainee_name = trainee_name; // setting name } // Get & Set Course Name
}
public String getCourseName() { return this._ course_name; // getting course name } public void setCourseName(String course_name) { this._ course_name = course_name; // setting course name }
Step 2: Defining a CustomDatabaseHandler Class to handle all the database operations Now we will create a class CustomDatabaseHandler that extends from the SQLiteOpenHelper class. The SQLiteOpenHelper class is used for database creation. This class will automatically create a database if it does not exist already or otherwise open the database if it exists and thus helps to implement the upgrading processes as necessary. As we create a subclass of SQLiteOpenHelper class we are required to redefine its methods on Create(SQLiteDatabase db) and onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) in the subclass. onCreate method is called for creating tables and onUpgrade method is to upgrade the table with modifications if any.
For Example: public class CustomDatabaseHandler extends SQLiteOpenHelper { private static final int DATABASE_VERSION = 1; // defining the database version is mandatory private static final String DATABASE_NAME = "traineeManager"; // Database Name private static final String TABLE_NAME = "traineeDetails"; // table name private static final String MAIN_TRAINEE_ID = "trainee_id"; private static final String MAIN_TRAINEE _NAME = "trainee_name"; private static final String MAIN_COURSE_NAME = "course_name"; public CustomDatabaseHandler(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } // Override onCreate method to create table public void onCreate(SQLiteDatabase db) { String CREATE_TRAINEE_TABLE = "CREATE TABLE " + TABLE_NAME + "(" + MAIN_TRAINEE_ID + " INTEGER PRIMARY KEY," + MAIN_TRAINEE _NAME + " TEXT," + MAIN_ COURSE_NAME + " TEXT" + ")"; db.execSQL(CREATE_ TRAINEE _TABLE); } // Override onUpgrade method public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); //drop the table if there already exists table with this name onCreate(db);//Hence, create new table once again } Step 3: Inserting Data into the Table Once we have created a table, our next step is to add data into the table. For this we will create a class ContentValues and hence, by passing the object of this class to insert method we will insert the data into database. public void addTraineeDetails(Trainee trainee) { CustomDatabaseHandler cDbHelper = new CustomDatabaseHandler((getContext());//instantiate the subclass of SQLiteOpenHelper Class SQLiteDatabase db = cDbHelper.getWritableDatabase();//Setting the database in write mode to allow insertion of values ContentValues values = new ContentValues();
values.put(MAIN_TRAINEE_ID, trainee.getTraineeID();//This calls the getTraineeID() method that was defined in Trainee.java values.put(MAIN_TRAINEE_NAME, trainee.getTraineeName());//This calls the getTraineeName() method that was defined in Trainee.java values.put(MAIN_COURSE_NAME, trainee.getCourseName()); //This calls the getCourseName() method that was defined in Trainee.java db.insert(TABLE_NAME, null, values);//Inserting Values into Database db.close(); // Closing database connection upon completion of insert task } Step 4: Reading the Data from Database Table Now, we are ready to display or read the data present in the table. Following code shows how we can derive all trainee details present in the database in array list format of Trainee class type. Here we are using the if and do-while loops to extract each and every row data from the table public List<Trainee> getAllTraineeDetails() {// getAllTraineeDetails() will return all trainee details from database in array list format List<Trainee> traineeList = new ArrayList<Trainee>(); String selectQuery = "SELECT * FROM " + TABLE_NAME; CustomDatabaseHandler cDbHelper = new CustomDatabaseHandler((getContext()); SQLiteDatabase db = cDbHelper.getWritableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); if (cursor.moveToFirst()) {//loop to derive all the values do { Trainee trainee = new Trainee(); trainee.setTraineeID(Integer.parseInt(cursor.getString(0)));//Here the setTraineeID method that was defined in Trainee.java trainee.setTraineeName(cursor.getString(1)); //Here the setTraineeName method that was defined in Trainee.java trainee.setCourseName(cursor.getString(2)); //Here the setCourseName method that was defined in Trainee.java traineeList.add(trainee);//adding the retrieved values to the created arraylist } while (cursor.moveToNext()); } return traineeList;//return the list of } Step 5: Updating the Table We are now updating a single trainee details in database using the updateTrainee method that takes object of the Trainee class as parameter. public int updateTrainee(Trainee trainee) {
CustomDatabaseHandler cDbHelper = new CustomDatabaseHandler((getContext());//setting the database write mode SQLiteDatabase db = cDbHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(MAIN_TRAINEE_NAME, trainee.getTraineeName()); values.put(MAIN_COURSE_NAME, trainee.getCourseName()); return db.update(TABLE_NAME, values, MAIN_TRAINEE_ID + " = ?", new String[] { String.valueOf(Trainee.getTraineeID()) }); } Step 6: Deleting the Data from Table Finally we are deleting a single trainee details from the database using the deleteTrainee method that takes object of Trainee class as parameter. public void deleteTrainee(Trainee trainee) { CustomDatabaseHandler cDbHelper = new CustomDatabaseHandler((getContext());//setting the database write mode SQLiteDatabase db = cDbHelper.getWritableDatabase(); db.delete(TABLE_NAME, MAIN_TRAINEE_ID + " = ?", new String[] { String.valueOf(Trainee.getTraineeID()) }); db.close(); } Hence, by using the above mentioned steps one can perform SQLite database operations for the Android apps. This allows the developer to create a mechanism to store data in the android device database. Android training is the best way to learn this SQLite database activity for data storage. TOPS Technologies provides Android training on SQLite concepts to the learners so that they can get familiar with effective database handling concepts including methods to add, update and delete content from SQLite, SQLiteOpenHelper class and data types used in this database.