SQLite Database with OrmLite

July 08, 2014
5 min read

​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)

Share on:

Want to stay on top of everything?

Get updates on industry developments and the software solutions we can now create for a smooth digital transformation.

* I read and understood the ASSIST Software website's terms of use and privacy policy.

Frequently Asked Questions

1. What is ASSIST Software's development process?  

The Software Development Life Cycle (SDLC) we employ defines the following stages for a software project. Our SDLC phases include planning, requirement gathering, product design, development, testing, deployment, and maintenance.

2. What software development methodology does ASSIST Software use?  

ASSIST Software primarily leverages Agile principles for flexibility and adaptability. This means we break down projects into smaller, manageable sprints, allowing continuous feedback and iteration throughout the development cycle. We also incorporate elements from other methodologies to increase efficiency as needed. For example, we use Scrum for project roles and collaboration, and Kanban boards to see workflow and manage tasks. As per the Waterfall approach, we emphasize precise planning and documentation during the initial stages.

3. I'm considering a custom application. Should I focus on a desktop, mobile or web app?  

We can offer software consultancy services to determine the type of software you need based on your specific requirements. Please explore what type of app development would suit your custom build product.   

  • A web application runs on a web browser and is accessible from any device with an internet connection. (e.g., online store, social media platform)   
  • Mobile app developers design applications mainly for smartphones and tablets, such as games and productivity tools. However, they can be extended to other devices, such as smartwatches.    
  • Desktop applications are installed directly on a computer (e.g., photo editing software, word processors).   
  • Enterprise software manages complex business functions within an organization (e.g., Customer Relationship Management (CRM), Enterprise Resource Planning (ERP)).

4. My software product is complex. Are you familiar with the Scaled Agile methodology?

We have been in the software engineering industry for 30 years. During this time, we have worked on bespoke software that needed creative thinking, innovation, and customized solutions. 

Scaled Agile refers to frameworks and practices that help large organizations adopt Agile methodologies. Traditional Agile is designed for small, self-organizing teams. Scaled Agile addresses the challenges of implementing Agile across multiple teams working on complex projects.  

SAFe provides a structured approach for aligning teams, coordinating work, and delivering value at scale. It focuses on collaboration, communication, and continuous delivery for optimal custom software development services. 

5. How do I choose the best collaboration model with ASSIST Software?  

We offer flexible models. Think about your project and see which models would be right for you.   

  • Dedicated Team: Ideal for complex, long-term projects requiring high continuity and collaboration.   
  • Team Augmentation: Perfect for short-term projects or existing teams needing additional expertise.   
  • Project-Based Model: Best for well-defined projects with clear deliverables and a fixed budget.   

Contact us to discuss the advantages and disadvantages of each model. 

ASSIST Software Team Members

See the past, present and future of tech through the eyes of an experienced Romanian custom software company. The ASSIST Insider newsletter highlights your path to digital transformation.

* I read and understood the ASSIST Software website's terms of use and privacy policy.

Follow us

© 2025 ASSIST Software. All rights reserved. Designed with love.