Archive for the ‘ORMLite’ Category

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 !!

ORMLITE – Light Weight Object Relational Mapping – An Introduction

OVERVIEW ORM Lite provides a lightweight Object Relation Mapping between Java classes and SQL databases. ORM Lite supports JDBC connections to MySQL, Postgres, H2, SQLite, Derby, HSQLDB, and Microsoft SQL Server. ORM Lite also supports native database calls on Android OS. Using ORM Lite with Android Downloading ORMLITE To get started with ORM Lite, We need to download the ORM Lite jar files. These can be downloaded from ORM Lite release page Once we have downloaded ORM Lite, we will need to add it as an external library to our android project. Just Drop the jar file into your project’s libs/ subdirectory. We only need the ormlite-android-4.14.jar, not the ORM lite-core or any other packages. Getting Started with ORM Lite To get started with ORM lite we need to create our own database helper class which should extend the OrmLiteSqliteOpenHelper class. This class creates and upgrades the database when the application is installed and also provide the DAO(Data Access Object) classes used by other classes. The helper class must implement the methods

onCreate(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource)

onUpgrade(SQLiteDatabase database, ConnectionSource connectionSource, int oldVersion, int newVersion)

onCreate creates the database when app is first installed while onUpgrade handles the upgrading of the database tables when we upgrade our app to a new version. The helper should be kept open across all activities in the app with the same SQLite database connection reused by all threads. If we open multiple connections to the same database, stale data and unexpected results may occur. It is recommended to use the OpenHelperManager to monitor the usage of the helper – it will create it on the first access, track each time a part of our code is using it, and then it will close the last time the helper is released. Once we define our database helper and are managing it correctly, We will use it in our Activity classes. An easy way to use the OpenHelperManager is to extend OrmLiteBaseActivity for each of your activity classes – there is also OrmLiteBaseListActivity, OrmLiteBaseService, and OrmLiteBaseTabActivity. These classes provide a helper protected field and a getHelper() method to access the database helper whenever it is needed and will automatically create the helper in the onCreate() method and release it in the onDestroy() method. Here is sample DatabaseHelper class Creating Table using ORM Lite There are a few things to notice when we use ORM Lite:
  1. We just annotate our class as a table and its members as fields and we’ re almost done with creating a table
  2. ORM Lite handles all of the basic data types without any explicit work on your part (integers, strings, floats, dates, and more).
  3. It is mandatory to have a no argument constructor in our class
In onCreate method of Databasehelper we create tables with help of TableUtils like
TableUtils.createTable(connectionSource, SimpleData.class);,
SimpleData here is name of table An example code to create Table in the onCreate callback:
 * This is called when the database is first created. Usually you should call createTable statements here to create
 * the tables that will store your data.
 public void onCreate(SQLiteDatabase db, ConnectionSource connectionSource) {
 try {
 TableUtils.createTable(connectionSource, SimpleData.class);
 } catch (SQLException e) {
 Log.e(DatabaseHelper.class.getName(), "Can't create table", e);
 throw new RuntimeException(e);

Here is sample ORMLite table class

 * A simple demonstration object we are creating and persisting to the database.
 public class SimpleData {

// id is generated by the database and set on the object automagically
 @DatabaseField(generatedId = true)
 int id;
 @DatabaseField(index = true)
 String string;
 long millis;
 Date date;
 boolean even;

SimpleData() {
 // needed by ormlite

public SimpleData(long millis) { = new Date(millis);
 this.string = (millis % 1000) + "ms";
 this.millis = millis;
 this.even = ((millis % 2) == 0);

 public String toString() {
 StringBuilder sb = new StringBuilder();
 sb.append(", ").append("string=").append(string);
 sb.append(", ").append("millis=").append(millis);
 SimpleDateFormat dateFormatter = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss.S");
 sb.append(", ").append("date=").append(dateFormatter.format(date));
 sb.append(", ").append("even=").append(even);
 return sb.toString();

Hope that the blog gets you running on ORMLite, In the subsequent posts, we will explore more about ORMLite and how to use it to manipulate/retrieve data objects. References:
  1. ORMLite Sample table class (
  2. ORMLite example: (