Database Operations with ORM Lite

In the first blog post, we discovered about ORMLite as a formidable ORM for use in android applications. Here we will delve deeper into how to perform various database operations using ORMLite Query Operation in ORM Lite The DAOs provide basic methods to query for an object that matches an id (queryForId) and query for all objects (queryForAll), iterating through all of the objects in a table . However, for more custom queries, there is the queryBuilder() method which returns a QueryBuilder object for the DAO with which you can construct custom queries to return a sub-set of your tables. Query Builder is something that assists in building SQL query (SELECT) statements for a particular table in a particular database. 1.Query for all Returns the list of all records in the table we have in build function queryForAll();
// get our dao
 RuntimeExceptionDao<DemoORMLite, Integer> DemoORMLiteDao = getHelper().getDemoORMLiteDao ();

// query for all of the data objects in the database
 List<SimpleData> list = simpleDao.queryForAll();
2. Query for id Returns the record corresponding to given id we have in build function queryForId(id);
TEntity entity = <strong>this</strong>.dao.queryForId(id);
3. Query for particular field name Here we use QueryBuilder to query for field “lastname” and return the list of records that have last_name =”lastname”
Public List<DemoORMLite> RetrieveByLastName(String lastname)throws SQLException {
QueryBuilder<TEntity,Integer> queryBuilder = dao.queryBuilder();
List list;
queryBuilder.where().eq("last_name", lastname);
list = queryBuilder.query();
return list;
Deleting a record in ormlite DeleteBuilder assists in building sql DELETE statements for a particular table in a database. Sample Code that deletes elements from table by argument
DatabaseHelper helper = OpenHelperManager.getHelper(App.getContext(), DatabaseHelper.class);</pre>
//get helper
 Dao dao = helper.getDao(YOUR_CLASS.class);

//get your Dao
 DeleteBuilder<CanteenLog, Integer> deleteBuilder = dao.deleteBuilder();
 // CanteenLog here is table name

deleteBuilder.where().eq("FIELD_NAME", arg);


Order by in ormlite Syntax: orderBy(String columnName, boolean ascending) Add “ORDER BY” clause to the SQL query statement to order the results by the specified column name. Use the ascending boolean to get a ascending or descending order. This can be called multiple times to group by multiple columns.
QueryBuilder<Visit, Integer> qb = getHelper().getyourdaoname().queryBuilder();
 qb.where().eq("Field_name", fieldname);
 qb.orderBy("order_according_to_this_field", false);

Using Max in Ormlite

long max = fooDao.queryRawValue(

"select max(modified) from foo where userid = ?", id);
 // now perform a second query to get the max row
 Foo foo = fooDao.queryBuilder().where().eq("modified", max).queryForFirst();

Set default value to column in ORMLite
@DatabaseField(defaultValue = "unknownName", canBeNull = true)
Here we explored various simple ORMLite operations. If you have used ORMLite in more advanced way, Please feel free to drop a comment below !!
  • Winscle Jay Salva Tumaob