Request a Call back


captcha

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

Type

Key

 Trainee_ID  Int  Primary Key
 Trainee_Name  Text
 Course_Name  Text

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.

Leave a Reply

Your email address will not be published.

Leave a Reply

Your email address will not be published. Required fields are marked *