Android Tutorial: SQLite Database with OrmLite

August 20, 2016 13626

Android operating system offers many different ways to store user and app data. One of them is SQLite, which is a very light-weight database available within Android OS.

In this tutorial, we’ll be discussing how to handle all SQLite operations with Object Relational Mapping Lite.

Let’s Understand – What is OrmLite?

Object Relational Mapping Lite (ORM Lite) offers some lightweight functionality to persevere Java objects to SQL databases. It avoids the complexity and overhead of more standard ORM packages.

ORMLite supports many SQL databases by using JDBC and also supports Sqlite with native calls to Android OS database APIs. There are many reasons for android developers to use OrmLite such as:

·It is used for complicated database operations.
·Developers don’t have to remember to SQL queries.
·It also prefers for big size applications.

Student Table Diagram

In this, we are taking an example of storing Student information in SQlite database.Check below database diagram for more detail.

Add Dependencies for OrmLite

We need to add two dependencies for OrmLite in-app level build.gradle file.

dependencies {

	...

	// Dependency for OrmLite
	compile 'com.j256.ormlite:ormlite-android:4.48'
	compile 'com.j256.ormlite:ormlite-core:4.48'
}

Configuring a Class for Database Table

The following is an example class that is configured to be persisted to a database by using ORMLite annotations. The @DatabaseTable annotation configures the Student class to be persisted to the database table named students. The @DatabaseField annotations map the fields on the Student to the database columns with the ‘columnName’.

@DatabaseTable(tableName = "students")
public class Student {

   // Fields

   // Primary key defined as an auto generated integer
   // If the database table column name differs than the Model class variable name, the way to map to use columnName
  
   @DatabaseField(columnName = "id",generatedId = true)
   private int id;

   @DatabaseField(columnName = "first_name")
   private String firstName;

   @DatabaseField(columnName = "last_name")
   private String lastName;

   @DatabaseField(columnName = "address")
   private String address;

   @DatabaseField(columnName = "mobile_number")
   private String mobileNumber;


   // Getter and Setter method of fields

   public int getId() {
       return id;
   }

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

   public String getFirstName() {
       return firstName;
   }

   public void setFirstName(String firstName) {
       this.firstName = firstName;
   }

   public String getLastName() {
       return lastName;
   }

   public void setLastName(String lastName) {
       this.lastName = lastName;
   }

   public String getAddress() {
       return address;
   }

   public void setAddress(String address) {
       this.address = address;
   }

   public String getMobileNumber() {
       return mobileNumber;
   }

   public void setMobileNumber(String mobileNumber) {
       this.mobileNumber = mobileNumber;
   }
}

Create SQLite Database Handler Class

After extending DBHelper class from OrmLiteSqliteOpenHelper we need to override two method onCreate() and onUpgrade()

onCreate() – In this method, we need to write create Table statements.This is called when a database is created.
onUpgrade() – In this method we can modify table structure. This is called when database version is upgraded.

public class DBHelper extends OrmLiteSqliteOpenHelper {

   // Fields

   public static final String DB_NAME = "student_manager.db";
   private static final int DB_VERSION = 1;

   // Public methods

   public DBHelper(Context context) {
       super(context, DB_NAME, null, DB_VERSION);
       getWritableDatabase();
   }

   @Override
   public void onCreate(SQLiteDatabase db, ConnectionSource cs) {
       try {

           // Create Table with given table name with columnName
           TableUtils.createTable(cs, Student.class);

       } catch (SQLException e) {
           throw new RuntimeException(e);
       }
   }

   @Override
   public void onUpgrade(SQLiteDatabase db, ConnectionSource cs, int oldVersion, int newVersion) {

   }
}

All CRUD Operations (Create, Read, Update, and Delete) using OrmLite

Here, we have created all generic methods with getDao. Insert, delete, read, and update everything will be happened through DAOs.

public  List getAll(Class clazz) throws SQLException {
   Dao<T, ?> dao = getDao(clazz);
   return dao.queryForAll();
}

public  T getById(Class clazz, Object aId) throws SQLException {
   Dao<T, Object> dao = getDao(clazz);
   return dao.queryForId(aId);
}

public  CreateOrUpdateStatus createOrUpdate(T obj) throws SQLException {
   Dao<T, ?> dao = (Dao<T, ?>) getDao(obj.getClass());
   return dao.createOrUpdate(obj);
}

public  int deleteById(Class clazz, Object aId) throws SQLException {
   Dao<T, Object> dao = getDao(clazz);
   return dao.deleteById(aId);
}

Inserting New Student Record

For any operation of a database, we need to create database helper object on appropriate class.

DBHelper  dbHelper = new DBHelper(this);

public void addStudent() {

   Student student = new Student();

   student.setFirstName("XYZ");
   student.setLastName("ABC");
   student.setMobileNumber("1234567895");
   student.setAddress("USA");


   try {
       dbHelper.createOrUpdate(student);
   } catch (SQLException e) {
       e.printStackTrace();
   }
}

Reading All Student Record

public List getAllStudent() {

   List mStudentList = new ArrayList<>();
   try {
       mStudentList.addAll(dbHelper.getAll(Student.class));
   } catch (SQLException e) {
       e.printStackTrace();
   }
   return mStudentList;
}

Updating Record

public void updateStudent(Student student) {

    try {
       dbHelper.createOrUpdate(student);
   } catch (SQLException e) {
       e.printStackTrace();
   }
}

createOrUpdate() – If a row in the database with the same id exists then all of the columns in the database will be updated from the fields in the data parameter. If the id is null (or 0 or some other default value) or doesn’t exist in the database then the object will be created in the database.

Delete Record

public void deleteRecord(Student student) {
   try {
       dbHelper.deleteById(Student.class, student);
   } catch (SQLException e) {
       e.printStackTrace();
   }
}

What’s Next?

If you find this Android tutorial helpful, then start using it to handle all SQLite operations with Object Relational Mapping Lite.

Get a free copy of SQL Database with OrmLite Demo from Github.

If you have any query related to Android SQLite, ask our android developer for a solution. You can also contact to hire android app developer for your next project.