SQLite Database with OrmLite

​There is a great need to store info about the app state, user profile, game scores, etc. Android provides several ways to accomplish this based on the app's needs (how much space is needed, whether the data should be private or public). In this case, we will cover using SQLite Database with OrmLite.

To demonstrate the power of OrmLite, we will model and implement a shopping app. To simplify it, we will consider only the shopping lists with products. We will use the following structure for storing the state of a product in a shopping list (checked, unchecked, quantity, etc).

Product.java
package .db.model;

@DatabaseTable(tableName = "product")
public class Product {

   @Expose
   @DatabaseField(generatedId = true)
   private String id;

   @Expose
   @DatabaseField
   private String name;

   @Expose
   @DatabaseField
   private Float rating;

   //list of constructors, setters and getters
.................
}

To also store the products that belong to a shopping list, OrmLite strongly recommends the use of ForeignCollectionField, basically a special type of array in which every member is of ProductInList type.

ProductInList.java
package .db.model;

@DatabaseTable(tableName = "productInList")
public class ProductInList {

   @Expose
   @DatabaseField(generatedId = true)
   public Long id;

   @Expose
   @DatabaseField(foreign = true, foreignAutoRefresh = true)
   private ShoppingList theShoppingList;

   @Expose
   @DatabaseField(foreign = true, foreignAutoRefresh = true)
   private Product product;

   @Expose
   @DatabaseField
   private int qty;

   @Expose
   @DatabaseField
   private boolean checked = false;
   
   //list of constructors, setters and getters
........
}

The code is quite self explanatory and easy to understand. The novelty is the use of ForeignCollectionField. Instead of keeping all the data related to a shopping list spread across multiple models, we will keep it compact in the ShoppingList model.

ShoppingList.java
package .db.model;

@DatabaseTable(tableName = "item")
public class ShoppingList {

   @Expose
   @DatabaseField(id = true)
   public String id;

   @Expose
   @DatabaseField
   private String name;

   @Expose
   @ForeignCollectionField(eager = true)
   private ForeignCollection products;
   
   //list of constructors, setters and getters
.......
}

 

Now we must create other classes which will handle all interactions with the database and models.

DatabaseHelper.java
package .db.data;

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {
   public static final String LOG_TAG = "DatabaseHelper";

   // name of the database
   private static final String DATABASE_NAME = "shopping_db";
   // any time you make changes to your database objects, you may have to increase the database version
   private static final int DATABASE_VERSION =     1;

   // the DAO objects
   private Dao shoppingListDao = null;
   private Dao productDao = null;
   private Dao productInListDao = null;

   public DatabaseHelper(Context context) {
       super(context, DATABASE_NAME, null, DATABASE_VERSION);
   }

   @Override
   public void onCreate(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource) {
       try {
           Log.d(LOG_TAG, "Executing the onCreate method...");
           TableUtils.createTableIfNotExists(connectionSource, ShoppingList.class);
           TableUtils.createTableIfNotExists(connectionSource, Product.class);
           TableUtils.createTableIfNotExists(connectionSource, ProductInList.class);
} catch (SQLException e) {
           Log.e(LOG_TAG, "SQL Exception creating db", e);
       }
   }

   @Override
   public void onUpgrade(SQLiteDatabase sqLiteDatabase, ConnectionSource connectionSource, int i, int i2) {
       try {
           Log.d(LOG_TAG, "Executing the onUpgrade method...");

           //Drop old tables
           TableUtils.dropTable(connectionSource, ShoppingList.class, true);
           TableUtils.dropTable(connectionSource, Product.class, true);
           TableUtils.dropTable(connectionSource, ProductInList.class, true);

    //Recreate tables
           TableUtils.createTableIfNotExists(connectionSource, ShoppingList.class);
           TableUtils.createTableIfNotExists(connectionSource, Product.class);
           TableUtils.createTableIfNotExists(connectionSource, ProductInList.class);
       } catch (SQLException e) {
           Log.e(LOG_TAG, "SQL Exception upgrading db", e);
       }
public Dao getShoppingListDao() throws SQLException {
       if(shoppingListDao == null)
           shoppingListDao = DaoManager.createDao(connectionSource, ShoppingList.class);
       return shoppingListDao;
   }
//getters for ProductDao, ProductInListDao. Similar to //getShippingListDao. Exercise for the reader
...........

DatabaseManager.java is a standard file and DatabaseManager.java contains getters for our DAO objects (through which all interactions to actual tables and database is made) and of course methods OnCreate, OnUpgrade that are self explanatory.

DatabaseManager.java
package .db.data;
public class DatabaseManager {

private DatabaseHelper databaseHelper = null;

   //gets a helper once one is created ensures it doesnt create a new one
   public DatabaseHelper getHelper(Context context)
   {
       if (databaseHelper == null) {
           databaseHelper = OpenHelperManager.getHelper(context, DatabaseHelper.class);
       }
       return databaseHelper;
   }

   //releases the helper once usages has ended
   public void releaseHelper(DatabaseHelper helper)
   {
       if (databaseHelper != null) {
           OpenHelperManager.releaseHelper();
           databaseHelper = null;
       }
   }

}

Next, we propose implementing some kind of repositories that will implement common database queries over the models, like get all entities, update/create/delete entity, find by id, etc.  To accomplish this we need to create another three classes that should also reside in the data folder:

ShoppingListRepository.java
package .db.data;

public class ShoppingListRepository {

   private DatabaseHelper db;
   Dao itemDao;

   public ShoppingListRepository(Context context) {
       try{
           DatabaseManager dbManager = new DatabaseManager();
           db = dbManager.getHelper(context);
           itemDao = db.getShoppingListDao();
       }
       catch (SQLException e) {
           e.printStackTrace();
       }
   }

   public long create(ShoppingList shoppingList) {
       try{
           return itemDao.create(shoppingList);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return 0;
   }

   public long update(ShoppingList shoppingList) {
       try{
           return itemDao.update(shoppingList);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return 0;
   }

   public long delete(ShoppingList shoppingList) {
       try{
           return itemDao.delete(shoppingList);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return 0;
   }

   public List getAll()
   {
       try {
           return itemDao.queryForAll();
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return null;
   }

   public ShoppingList findById(String listID) {
       try {
           List res = itemDao.queryForEq("id", listID);
           if(res.isEmpty()) return null;
           return res.get(0);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return null;
   }

   public void deleteAll() {
       DeleteBuilder deleteBuilder = itemDao.deleteBuilder();
       try {
           itemDao.delete(deleteBuilder.prepare());
       } catch (SQLException e) {
           e.printStackTrace();
       }
   }
}

To make use of all this novel functionality in our shopping app main activity we will globally declare the repositories and an array to hold our shopping lists:

private ShoppingListRepository shoppingListRepository;
private ProductInListRepo inListRepo;
private ProductRepository productRepo;

private ArrayList shoppingLists = new ArrayList();


And in the onCreate method:
shoppingListRepository = new ShoppingListRepository(this);
inListRepo = new ProductInListRepo(this);
productRepo = new ProductRepository(this);

//Insert dummy products
Product theProduct = new Product();
theProduct.setName("Coca cola");
theProduct.setRating((float)4.5);
productRepo.create(theProduct);
//Homework: Add 3 more dummy products

//Create a shopping list
theList = new ShoppingList();
theList.setName("Party");
shoppingListRepository.create(theList);

//Add product in shopping list
productInList = new ProductInList();
productInList.setQty(6);
productInList.setChecked(false);
productInList.setProduct(theProduct);
productInList.setTheShoppingList(theList);
inListRepo.create(productInLIst)